حساب عدد الخلايا التي لا تحتوي على قيم متعددة

عادةً ما يُعد حساب الخلايا التي لا تحتوي على قيمة معيّنة أمرًا سهلاً باستخدام دالة COUNTIF. ويستعرض هذا المنشور كيفية حساب عدد الخلايا التي لا تحتوي على قيم متعددة ضمن نطاق محدد في Excel.

كيف تحسب الخلايا التي لا تحتوي على قيم متعددة؟

كما يظهر في لقطة الشاشة أدناه، لحساب عدد الخلايا في النطاق B3:B11 التي لا تحتوي على القيم المذكورة في D3:D4، اتبع الخطوات التالية:

الصيغة العامة

{=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(criteria_range),range))),ROW(criteria_range)^0)>0))}

المعاملات

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

كيف تستخدم هذه الصيغة؟

1. . حدد خلية فارغة لإظهار النتيجة.

2. أدخل الصيغة التالية، ثم اضغط على المفاتيحCtrl+Shift+Enter في آنٍ واحد للحصول على النتيجة.

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))

كيف تعمل هذه الصيغ؟

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))

1) --(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))):

• TRANSPOSE(D3:D4): تقوم دالة TRANSPOSE بتدوير اتجاه D3:D4 وتعيد{“count”,”blank”};
• SEARCH({“count”,”blank”},B3:B11): تقوم دالة SEARCH هنا بالبحث عن موضع السلسلتين الفرعيتين “count” و“blank” ضمن النطاق B3:B11، وتعيد مصفوفة بالشكل التالي:{#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,
  #VALUE!;1,7}.
• في هذه الحالة، سيتم البحث في كل خلية ضمن النطاق B3:B11 مرتين، نظرًا لوجود قيمتين ترغب في استبعادهما عند حساب الخلايا، مما يُنتج مصفوفة تحتوي على 18 قيمة. ويشير كل رقم في المصفوفة إلى موضع الحرف الأول من «count» أو «blank» في كل خلية من النطاق B3:B11.
• ISNUMBER{#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,
  #VALUE!;1,#VALUE!;1,7}: تُرجع دالة ISNUMBER القيمة TRUE إذا صادفت أرقامًا في المصفوفة، وترجع FALSE إذا صادفت أخطاءً. وهنا تُرجع النتيجة كما يلي:{FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;
  TRUE,TRUE}.
• --({FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,)
  FALSE;TRUE,TRUE}): يقوم هذان العلامتان السالبتان بتحويل القيمة “TRUE” إلى 1 وتحويل “FALSE” إلى 0. وهنا ستحصل على مصفوفة جديدة بالشكل التالي:{0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1}.

2)ROW(D3:D4)^0: تقوم دالة ROW بإرجاع أرقام الصفوف المُشار إليها في النطاق D3:D4، وهي {3؛4}، ثم يرفع عامل الرفع (^) كل رقم من هذين الرقمين إلى القوة صفر، ليُنتج في النهاية النتيجة التالية:{1;1}.

3) MMULT({0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1},{1;1}):تُرجع دالة MMULT ناتج ضرب هاتين المصفوفتين: {0؛0؛1؛1؛1؛0؛0؛1؛2}، ليتوافق مع البيانات الأصلية. ويمثّل أي رقم غير صفري في المصفوفة وجود إحدى السلاسل المستبعدة على الأقل، بينما يشير الصفر إلى عدم العثور على أيٍّ من السلاسل المستبعدة.

4) SUM(1-{0;0;1;1;1;0;0;1;2}>0):

• {0;0;1;1;1;0;0;1;2}>0: هنا يتم التحقق مما إذا كان كل رقم في المصفوفة أكبر من 0. إذا كان الرقم أكبر من 0، تُرجعTRUE، وإذا لم يكن كذلك، تُرجعFALSE. وستحصل بعد ذلك على مصفوفة جديدة بالشكل التالي: {FALSE;FALSE;TRUE;TRUE;TRUE;FALSE,FALSE,TRUE;TRUE}.
• 1-{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE,FALSE,TRUE;TRUE}: بما أننا نحتاج إلى حساب الخلايا التي لا تحتوي على القيم المحددة، فما علينا سوى عكس قيم هذه المصفوفة بطرحها من 1. هنا يقوم المعامل الحسابي تلقائيًا بتحويل قيمتي TRUE وFALSE إلى 1 و0، ليُنتج في النهاية النتيجة التالية: {1;1;0;0;0;1;1;0;0}.
• SUM{1;1;0;0;0;1;1;0;0}: تقوم دالة SUM بجمع جميع الأرقام في المصفوفة وتعيد النتيجة النهائية كالتالي: 4.

الدوال ذات الصلة

الصيغ ذات الصلة