Skip to main content

Kutools for Office — مجموعة واحدة. خمسة أدوات. أنجز المزيد.

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

Author Siluvia Last modified

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

a screenshot of the original data range and the cells you want to exclude from a sum

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

باستخدام العمليات الحسابية البسيطة داخل صيغة الجمع (SUM)، يمكنك استبعاد الخلايا غير المرغوب فيها مباشرة من حسابك. هذا النهج مناسب للحسابات السريعة عندما يكون لديك عدد صغير من الاستثناءات للتعامل معها. اتبع الخطوات التالية:

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

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

a screenshot of using formula to exclude the cell A3 and A4 from sum

التفسير والنصائح:

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

=SUM(A2:A7)-A3-A6

a screenshot of using formula to exclude discontinuous cells A3 and A6 from a sum

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

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

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

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

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

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 button تشغيل في نافذة 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)
ملاحظة: هذه الصيغة تجمع كل القيم في A2:A7 التي لا تساوي القيمة الموجودة في C1. إذا كانت هناك خلايا متعددة في A2:A7 تحتوي على نفس القيمة الموجودة في C1، سيتم استبعادها جميعًا من الجمع.

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

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

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

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

مثال – استبعاد القيم المحددة (مثل 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 مثالية عندما تكون الاستثناءات كثيرة ومتنوعة أو تتطلب أتمتة. تحقق دائمًا من مراجع الخلايا وتعديلات الصيغ عند تغيير بيانات المصدر. إذا واجهت أخطاء، تحقق من النطاقات أو قوائم الاستبعاد وحاول إعادة تطبيق الصيغ أو إعادة تشغيل الماكرو.

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

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

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

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

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

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

جميع إضافات Kutools. مثبت واحد

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

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