حساب عدد الخلايا غير المساوية لقيم عديدة في Excel

في Excel، يمكنك بسهولة حساب عدد الخلايا غير المساوية لقيمة محددة باستخدام دالة COUNTIF، ولكن هل سبق أن حاولت حساب الخلايا غير المساوية لعدة قيم دفعة واحدة؟ على سبيل المثال، أرغب في الحصول على العدد الإجمالي للمنتجات في العمود A مع استبعاد العناصر المذكورة في النطاق C4:C6، كما يظهر في لقطة الشاشة أدناه. وفي هذه المقالة، سأعرض لك بعض الصيغ الفعّالة لأداء هذه المهمة في Excel.

• حساب عدد الخلايا غير المساوية لقيم عديدة باستخدام الصيغة 1
• حساب عدد الخلايا غير المساوية لقيم عديدة باستخدام الصيغة 2

حساب عدد الخلايا غير المساوية لقيم عديدة باستخدام الصيغة 1

لحساب الخلايا غير المساوية لقيم معيّنة عديدة، يمكنك استخدام مجموعة من دالات MATCH وISNA وSUMPRODUCT، والصيغة العامة هي:

• range: قائمة الخلايا التي تريد حسابها؛
• exclude_values: القيم التي سيتم تجاهلها من نطاق القائمة.

1. أدخل أو انسخ الصيغة أدناه في خلية فارغة لعرض النتيجة:

2. بعد ذلك، اضغط على مفتاحEnter للحصول على النتيجة الصحيحة. انظر لقطة الشاشة:

شرح الصيغة:

• MATCH(A2:A13,C2:C4,0): تقوم دالةMATCH هذه بإرجاع موضع كل قيمة مطابقة كرقم، وتعيد خطأ#N/A إذا لم يتم العثور على تطابق. بمعنى آخر، كلما تطابقت قيمة في النطاقA2:A13 مع إحدى القيم فيC2:C4، تُرجع الدالة رقم الموضع؛ وإلا، تُرجع#N/A. والنتيجة هنا هي: {1؛#N/A؛#N/A؛1؛3؛2؛#N/A؛ #N/A؛3؛#N/A؛ #N/A؛ #N/A؛}.
• ISNA(MATCH(A2:A13,C2:C4,0))= ISNA({1;#N/A;#N/A;1;3;2;#N/A; #N/A;3;#N/A; #N/A; #N/A;}): تقوم دالة ISNA هنا بتحويل قيم الخطأ إلى TRUE، والأرقام إلى FALSE، ليُنتج ذلك المصفوفة التالية: {FALSE؛TRUE؛TRUE؛FALSE؛FALSE؛FALSE؛TRUE؛TRUE؛FALSE؛TRUE؛TRUE؛TRUE}.
• --(ISNA(MATCH(A2:A13,C2:C4,0))))=--({FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE}): يقوم عامل النفي المزدوج-- بتحويل جميع القيمTRUE إلى1 وجميع القيمFALSE إلى0، كالتالي: SUMPRODUCT(--(ISNA(MATCH(A2:A13,C2:C4,0))))=SUMPRODUCT({0;1;1;0;0;0;1;1;0;1;1;1}).
• بعد ذلك تُستخدم دالة SUMPRODUCT لجمع الأرقام في المصفوفة وستحصل على النتيجة: 7.

حساب عدد الخلايا غير المساوية لقيم عديدة باستخدام الصيغة 2

لحل هذه المهمة في Excel، يمكنك أيضًا تطبيق مجموعة من دالات COUNTA وSUMPRODUCT وCOUNTIF، والصيغة العامة هي:

• range: قائمة الخلايا التي تريد حسابها؛
• exclude_values: القيم المراد تجاهلها من قائمة النطاق.

1. يُرجى إدخال الصيغة أدناه أو نسخها في خلية فارغة لعرض النتيجة:

2. بعد ذلك، اضغط على مفتاحEnter للحصول على النتيجة كما هو موضح في لقطة الشاشة التالية:

شرح الصيغة:

• COUNTA(A2:A13): تقوم دالةCOUNTAبإرجاع عدد جميع الخلايا غير الفارغة في النطاق A2:A13، تحصل على النتيجة: 12.
• COUNTIF(A2:A13,C2:C4): تقوم دالة COUNTIF هذه بحساب عدد الخلايا في النطاق A2:A13 التي تطابق القيم المحددة في C2:C4، لتُعطيك النتيجة التالية: {2؛1؛2}، أي أن «Apple» تظهر مرتين، و«Grape» تظهر مرة واحدة، و«Lemon» تظهر مرتين.
• SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=SUMPRODUCT({2;1;2}): تقوم دالةSUMPRODUCT بجمع جميع العناصر في المصفوفة وترجع الناتج النهائي: 5.
• COUNTA(A2:A13)-SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=12-5: يتم طرح نتيجة دالة SUMPRODUCT (5) من إجمالي عدد الخلايا غير الفارغة (12) للحصول على النتيجة النهائية التي تبحث عنها.

ملاحظة: كلا الصيغتين أعلاه غير حساسية الحالة.

الدالة ذات الصلة المستخدمة:

مقالات إضافية: