كيف يمكن استبعاد خلايا معيّنة في عمود من عملية الجمع في Excel؟

عند العمل مع البيانات في Excel، قد تجد نفسك غالبًا بحاجة إلى حساب مجموع نطاق معين مع تجاهل خلايا محددة عمداً—مثلًا، إذا احتوت تلك الخلايا على قيم شاذة، أخطاء، بيانات غير ذات صلة، أو قيم معيّنة ترغب في استبعادها من الحساب الإجمالي. كما يظهر في لقطة الشاشة أدناه، تخيل أن لديك عمودًا من الأرقام، لكنك تريد جمع جميع القيم باستثناء الخليتين A3 وA4. يقدم هذا المقال طرقًا عملية وشاملة لجمع نطاق في Excel مع استبعاد خلية واحدة أو أكثر من الخلايا المحددة، مما يمكّنك من الحصول بدقة على النتائج التي تبحث عنها في مهام التحليل المالي، إعداد التقارير، أو إدارة المخزون التي تتطلب حسابات انتقائية.

• استبعاد خلايا في عمود من المجموع باستخدام صيغة
• رمز VBA – جمع نطاق برمجيًا مع تخطي/استبعاد خلايا محددة
• صيغة Excel – استخدام SUMIF/SUMIFS لتضمين القيم التي لا تطابق معايير الاستبعاد فقط
• صيغة Excel – استخدام دالة FILTER في إصدارات Excel الأحدث لتصفية الخلايا المستبعدة قبل الجمع

استبعاد خلايا في عمود من المجموع باستخدام صيغة

باستخدام عمليات حسابية بسيطة داخل صيغة SUM، يمكنك استبعاد الخلايا غير المرغوبة مباشرةً من الحساب. يُعدّ هذا الأسلوب مثاليًا للحسابات السريعة عندما يكون لديك عدد قليل من الخلايا التي تحتاج إلى استبعادها. اتبع الخطوات التالية:

1. حدد خلية فارغة لعرض نتيجة الجمع، ثم أدخل الصيغة التالية في شريط الصيغة واضغطEnter لحساب المجموع مع استبعاد خلايا محددة. على سبيل المثال:

=SUM(A2:A7)-SUM(A3:A4)

الشرح والنصائح:

• الدالةSUM(A2:A7) تحسب النطاق بأكمله، بينما تطرح الدالةSUM(A3:A4) قيم الخلايا المستبعدة. وتُعد هذه الطريقة أكثر فعالية عندما تكون الخلايا المراد استبعادها متتالية.
• يمكنك بسهولة استبعاد عدة خلايا غير متجاورة. على سبيل المثال، لاستبعاد الخليتين A3 وA6 من النطاق، عدّل الصيغة كما يلي:

=SUM(A2:A7)-A3-A6

• إذا كانت الخلايا المستبعدة متناثرة أو عديدة، فقد يؤدي سردها يدويًا إلى جعل الصيغ أطول وأكثر تعقيدًا في الإدارة.
• احرص على مراجع الخلايا: إذا تغيّرت بياناتك أو نطاقك، فقم بتحديث الصيغة وفقًا لذلك لتجنب الأخطاء.

رمز VBA – جمع نطاق برمجيًا مع تخطي/استبعاد خلايا محددة

في السيناريوهات التي تتضمّن العديد من الاستبعادات أو تتطلّب تكرار العملية بشكل متكرر، يوفّر استخدام ماكرو VBA مرونةً عاليةً وأتمتةً فعّالة. فمن خلال VBA، يمكنك تحديد نطاق معيّن واستبعاد أي عدد من الخلايا—سواء كانت متتالية أو غير متتالية—بطريقة برمجية دقيقة. ويُعدّ هذا الأسلوب مثاليًا للمستخدمين المتمرسين في بيئة VBA والراغبين في تبسيط منطق الاستبعاد المعقد.

احتياطات:يمكن أن تقوم ماكروهات VBA بتعديل ملفك. احفظ عملك دائمًا قبل تشغيل أي كود جديد. ويجب تمكين الماكروهات لتشغيل الكود أعلاه.

1. انتقل إلىأدوات المطور > Visual Basic لفتح محرر VBA. في نافذة المشروع، انقر بزر الماوس الأيمن على ملفك، ثم اخترإدراج > وحدة نمطية (Module)، والصق الكود التالي في الوحدة النمطية:

Sub SumWithExclusions()
    Dim sumRange As Range
    Dim excludeCells As Range
    Dim cell As Range
    Dim result As Double
    Dim xTitleId
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set sumRange = Application.InputBox("Select the range to sum", xTitleId, Type:=8)
    Set excludeCells = Application.InputBox("Select cells to exclude (use Ctrl+Click to select multiple)", xTitleId, Type:=8)
    
    result = 0
    If Not sumRange Is Nothing Then
        For Each cell In sumRange
            If Not Application.Intersect(cell, excludeCells) Is Nothing Then
                ' Skip excluded cells
            Else
                result = result + cell.Value
            End If
        Next
        
        MsgBox "The sum excluding specified cells is: " & result, vbInformation
    Else
        MsgBox "No range selected.", vbExclamation
    End If
End Sub

2. انقر على تشغيل (Run) في نافذة VBA أو اضغط علىF5 لتنفيذ الماكرو. بعد التنفيذ، ستظهر نافذة منبثقة تطلب منك تحديد النطاق الكامل للجمع، ثم تحديد الخلايا المراد استبعادها (اضغط Ctrl لتحديد أكثر من خلية). وسيعرض الماكرو النتيجة في مربع رسالة.

• إذا حددت خلايا عن طريق الخطأ، ما عليك سوى إعادة تشغيل الماكرو. العملية تفاعلية ومرنة!
• الماكرو هو الخيار الأمثل للمهام الروتينية أو حالات الاستبعاد المعقدة التي تعتمد على معايير محددة.

صيغة Excel – استخدام SUMIF أو SUMIFS لتضمين القيم التي لا تطابق معايير الاستبعاد فقط

للحصول على استبعادات أكثر تقدمًا أو قائمة على المنطق، يمكنك استخدام دالةSUMIF أوSUMIFS. تعمل هذه الصيغ بشكل ممتاز عندما يعتمد الاستبعاد على قيمة أو معيار معيّن، أو عندما تكون لديك قائمة بقيم ترغب في استثنائها.

مثال – الاستبعاد بناءً على قيمة محددة

1.إذا كنت تريد جمع A2:A7 ولكن باستبعاد القيمة ‹16›، أدخل الصيغة التالية في الخلية المستهدفة (على سبيل المثال، في الخلية B1):

=SUMIF(A2:A7,"<>16")

تجمع هذه الصيغة جميع القيم في النطاق A2:A7، باستثناء القيم التي تساوي 16.

2.بعد كتابة الصيغة، اضغط على Enter. يمكنك نسخ الصيغة أو تعديل مراجع النطاق/الخلايا حسب الحاجة.

مثال – استبعاد جميع الخلايا التي تطابق قيمة خلية معينة

افترض أن الخلية C1 تحتوي على القيمة التي ترغب في استبعادها من المجموع:

=SUMIF(A2:A7,"<>"&A3)

قم بتحديث الخلية C1 حسب الحاجتك، وستعمل الصيغة تلقائيًا على استبعاد جميع القيم المطابقة.

• للمعايير المتعددة أو القواعد الأكثر تعقيدًا، يُوصى باستخدام SUMIFS بالاشتراك مع أعمدة مساعدة أو مصفوفات. ومع ذلك، فإن دالتي SUMIF وSUMIFS تؤديان بشكل أفضل عندما تستند عمليات الاستبعاد إلى معايير محددة وثابتة، وليس إلى مواضع خلايا عشوائية.
• إذا احتوى نطاقك على نصوص أو خلايا فارغة، تتجاهلها دالة SUMIF تلقائيًا؛ لذا تأكد من أن هذا هو السلوك الذي تريده.

صيغة Excel – استخدام دالة FILTER (إصدارات Excel الحديثة) لتصفية الخلايا المستبعدة قبل الجمع

إذا كنت تستخدم Excel لـ Microsoft 365 أو Excel 2021 فأحدث، فإن دالةFILTER تتيح لك استبعاد الخلايا بشكل ديناميكي ومرن قبل تطبيق SUM — وهو أمرٌ مثالي خصوصًا لمجموعات البيانات الكبيرة أو عند وجود معايير استبعاد متغيرة.

مثال – استبعاد قيم محددة (مثل 16 و13)

1.أدخل الصيغة التالية في الخلية المستهدفة (مثل B1):

=SUM(FILTER(A2:A7,(A2:A7<>16)*(A2:A7<>13)))

تجمع هذه الصيغة جميع القيم في النطاق A2:A7، باستثناء القيم التي تساوي 16 و13. فدالة FILTER تُنشئ مصفوفة تحتوي فقط على الخلايا التي لا تتطابق مع هاتين القيمتين، ثم تقوم دالة SUM بجمعها.

2. اضغط على Enter. سيتم تحديث الحساب تلقائيًا إذا تغيّرت الاستبعادات أو البيانات الأصلية.

• لاستبعاد القيم ديناميكيًا بناءً على قائمة (مثل وجود قائمة الاستبعاد في C2:C4):

=SUM(FILTER(A2:A7,ISNA(MATCH(A2:A7,C2:C4,0))))

تستبعد هذه الصيغة أي قيمة في النطاق A2:A7 تتطابق مع أيٍّ من القيم الموجودة في النطاق C2:C4. ما عليك سوى تحديث قائمة الاستبعاد في العمود C، وستُحدَّث نتيجة الصيغة تلقائيًا.

• يُوصى باستخدام النهج القائم على FILTER للمستخدمين الذين يستخدمون إصدارات Excel الحديثة ويرغبون في تطبيق منطق استبعاد ديناميكي وقابل للتوسعة.
• إذا ظهر خطأ #CALC!، فتأكد من أنه بعد تطبيق جميع عمليات الاستبعاد، لا يزال هناك قيمة واحدة على الأقل في النطاق؛ وإلا فستُرجع الدالة FILTER خطأً.

باختصار، يوفّر Excel عدة حلول عملية لجمع نطاق مع استبعاد خلايا أو قيم محددة. فالصيغ البسيطة مثالية للاستبعادات الصغيرة والسريعة، بينما تُعدّ دالتا SUMIF وSUMIFS ودالة FILTER خيارًا أكثر مرونة للسيناريوهات القائمة على الشروط. أما VBA، فهو الحل الأمثل عندما تكون الاستبعادات عديدة أو متنوعة أو تتطلب أتمتة متكررة. تأكد دائمًا من صحة مراجع الخلايا وتحديث الصيغ عند تعديل البيانات الأصلية. وإذا واجهت أخطاءً، فافحص النطاقات أو قوائم الاستبعاد، ثم حاول إعادة تطبيق الصيغ أو تشغيل الماكرو من جديد.

مقالات ذات صلة:

• كيف يمكن استبعاد خلية أو نطاق معيّن من الطباعة في Excel؟
• كيف يمكن استبعاد القيم الموجودة في قائمة من قائمة أخرى في Excel؟
• كيف يمكن العثور على القيمة الدنيا في نطاق مع استثناء القيمة صفر في Excel؟

أفضل أدوات الإنتاجية لمكتبتك

🤖	KUTOOLS AI مساعد: ثوّر تحليل البيانات استنادًا إلى:التنفيذ الذكي   |  إنشاء الكود|  إنشاء الصيغ المخصصة  |  تحليل البيانات وإنشاء المخططات البيانية|  استدعاء الوظائف المحسنة…
	الميزات الشائعة:البحث، التمييز أو وضع علامة على المكررات   |  حذف الصفوف الفارغة   |  دمج الأعمدة أو الخلايا دون فقدان البيانات   |  التقريب بدون استخدام الصيغة...
	بحث متقدم:VLookup متعدد المعايير  |  VLookup متعدد القيم  |   VLookup عبر أوراق متعددة   |   مطابقة غامضة....
	قائمة منسدلة متقدمة:إنشاء قائمة منسدلة بسرعة   |  قائمة منسدلة تابعة   |  قائمة منسدلة متعددة الاختيار....
	مدير الأعمدة:إضافة عدد محدد من الأعمدة|نقل الأعمدة|تبديل حالة ظهور الأعمدة المخفية|مقارنة النطاقات والأعمدة...
	ميزات مميزة:التركيز على الشبكة   |  عرض التصميم   |شريط الصيغ المحسن   | مدير الدفتر والورقة   |  مكتبة الموارد(نص تلقائي)|  أداة اختيار التاريخ   |  تجميع ورقات العمل  |  تشفير/فك تشفير الخلايا   | إرسال رسائل البريد الإلكتروني حسب القائمة   |  مرشح متقدم   |   تصفية خاصة(تصفية الخلايا التي تحتوي على خط عريض/مائل/يتوسطه خط...) ...
	أفضل 15 مجموعات الأدوات:12 أدواتالنصوص(إضافة نص،حذف الأحرف المحددة، ...)|   50+أنواعالمخططات البيانية(مخطط جانت، ...)|   40+ صيغعملية(حساب العمر بناءً على تاريخ الميلاد، ...)|   19 أدواتالإدراج(إدراج رمز QR،إدراج صورة من المسار، ...)|   12 أدواتالتحويل(تحويل إلى كلمات،تحويل العملة، ...)|   7 أدواتدمج وتقسيم(دمج متقدم للصفوف،تقسيم الخلايا، ...)|... وأكثر من ذلك

استخدم Kutools باللغة التي تفضلها – يدعم الإنجليزية والإسبانية والألمانية والفرنسية والصينية و40+ لغات أخرى!

عزِّز مهاراتك في Excel باستخدام Kutools لـ Excel، وعايش الكفاءة كما لم تفعل من قبل.يقدّم Kutools لـ Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية ووقت الحفظ.انقر هنا للحصول على الميزة التي تحتاجها أكثر من غيرها...

مزيد من التفاصيل حول Kutools لـ Excel...تنزيل مجاني...

---

يجلب Office Tab واجهة ذات علامات تبويب إلى Office، ويجعل عملك أسهل بكثير

• تمكّن من التحرير والقراءة باستخدام علامات التبويب في Word وExcel وPowerPoint، وPublisher وAccess وVisio وProject.
• افتح وأنشئ مستندات متعددة في علامات تبويب جديدة داخل النافذة نفسها، بدلاً من فتح نوافذ جديدة.
• يزيد إنتاجيتك بنسبة 50% ويوفّر لك مئات نقرات الفأرة كل يوم!

مزيد من التفاصيل حول Office Tab...تنزيل مجاني...

---

جميع الإضافات من Kutools في برنامج تثبيت واحد!

Kutools for Office حزمةٌ تحتوي على إضافاتٍ مخصصة لتطبيقات Excel وWord وOutlook وPowerPoint، إلى جانب Office Tab Pro، مما يجعلها الخيار المثالي للفِرق التي تعمل عبر تطبيقات Office.

• حزمة شاملة واحدة— إضافات Excel وWord وOutlook وPowerPoint بالإضافة إلى Office Tab Pro
• برنامج تثبيت واحد، ترخيص واحد— الإعداد خلال دقائق (جاهز لـ MSI)
• يعمل بشكل أفضل معًا— إنتاجية ميسَّرة عبر تطبيقات Office
• تجربة مجانية لمدة 30 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
• أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل

مزيد من التفاصيل حول Kutools for Office...تنزيل مجاني...