عد القيم المفقودة

بافتراض أن لديك قائمتين، ولحساب العدد الإجمالي للقيم الموجودة في إحدى القائمتين والتي لا توجد في القائمة الأخرى، يمكنك استخدام صيغةSUMPRODUCT بالاستعانة بدالتيMATCH وISNA، أو باستخدام دالةCOUNTIF.

عد القيم المفقودة باستخدام SUMPRODUCT وMATCH وISNA
عد القيم المفقودة باستخدام SUMPRODUCT وCOUNTIF

عد القيم المفقودة باستخدام SUMPRODUCT وMATCH وISNA

لعَدّالعدد الإجمالي للقيم الموجودة في القائمة B والتي لا توجد في القائمة Aكما هو موضح أعلاه، يمكنك أولًا استخدام دالة MATCH لإرجاع مصفوفة تمثّل المواقع النسبية للقيم من القائمة B في القائمة A. وإذا لم توجد قيمة ما في القائمة A، فسيتم إرجاع خطأ #N/A. بعد ذلك، ستُحدّد دالة ISNA أخطاء #N/A، وستقوم دالة SUMPRODUCT بعدّ العدد الإجمالي لهذه الأخطاء.

التركيب العام

=SUMPRODUCT(--ISNA(MATCH()))range_to_count,lookup_range,0)))

لعَدّالعدد الإجمالي للقيم الموجودة في القائمة B والتي لا توجد في القائمة A، يُرجى نسخ الصيغة أدناه أو إدخالها في الخلية H6، ثم الضغط علىEnterللحصول على النتيجة:

=SUMPRODUCT(--ISNA(MATCH()))F6:F8,B6:B10,0)))

شرح الصيغة

=SUMPRODUCT(--)ISNA()MATCH(F6:F8,B6:B10,0)))

• MATCH(F6:F8,B6:B10,0):يُجبر الـنوع_المطابقة 0دالة MATCH على إرجاع قيم رقمية تشير إلى المواقع النسبية للقيم الموجودة في الخلاياF6 إلىF8 ضمن النطاقB6:B10. وإذا لم تُوجد إحدى القيم في القائمة A، فسيتم إرجاع خطأ #N/A. لذا ستكون النتائج في المصفوفة كالتالي: {2;3;#N/A}.
• ISNA()MATCH(F6:F8,B6:B10,0))=ISNA(){2;3;#N/A}):تتحقّق دالة ISNA مما إذا كانت القيمة تمثّل خطأ «#N/A» أم لا. فإذا كانت كذلك، تُرجع TRUE؛ وإذا لم تكن كذلك، تُرجع FALSE. لذا، تُرجع صيغة ISNA {FALSE;FALSE;TRUE}.
• SUMPRODUCT(--)ISNA()MATCH(F6:F8,B6:B10,0))) = SUMPRODUCT(--{FALSE;FALSE;TRUE}):تقوم العلامتان السالبتان (--) بتحويل القيم TRUE إلى 1 والقيم FALSE إلى 0، فتصبح:{0;0;1}. بعد ذلك، تحسب دالة SUMPRODUCT المجموع وتعيد الناتج:1.

عد القيم المفقودة باستخدام SUMPRODUCT وCOUNTIF

لعَدّالعدد الإجمالي للقيم الموجودة في القائمة B والتي لا توجد في القائمة A، يمكنك استخدام دالة COUNTIF للتحقق مما إذا كانت القيمة غير موجودة في القائمة A عبر تطبيق الشرط “=0”، حيث تُرجع الدالة القيمة 0 عندما تكون القيمة مفقودة. بعد ذلك، تقوم دالة SUMPRODUCT بعدّ العدد الإجمالي لهذه القيم المفقودة.

التركيب العام

=SUMPRODUCT(--(COUNTIF()))lookup_range,range_to_count)=0))

لعَدّالعدد الإجمالي للقيم الموجودة في القائمة B والتي لا توجد في القائمة A، يُرجى نسخ الصيغة أدناه أو إدخالها في الخلية H6، ثم الضغط علىEnterللحصول على النتيجة:

=SUMPRODUCT(--(COUNTIF()))B6:B10,F6:F8)=0))

شرح الصيغة

=SUMPRODUCT()--()COUNTIF(B6:B10,F6:F8)=0))

• COUNTIF(B6:B10,F6:F8):تقوم دالةCOUNTIF بعدّ عدد مرات ظهور القيم الموجودة في الخلايا منF6 إلىF8 ضمن النطاقB6:B10. وستكون النتيجة مصفوفةً كالتالي: {1;1;0}.
• --()COUNTIF(B6:B10,F6:F8)=0)=--(){1;1;0}=0):يُنتج التعبير {1؛1؛0}=0 مصفوفةً من القيم{FALSE;FALSE;TRUE}. بعد ذلك، تقوم العلامتان السالبتان (--) بتحويل القيم المنطقية TRUE إلى 1 وFALSE إلى 0، لتكون المصفوفة النهائية كالتالي: {0;0;1}.
• SUMPRODUCT()--()COUNTIF(B6:B10,F6:F8)=0)) = SUMPRODUCT({0;0;1}):ثم تُرجع دالة SUMPRODUCT المجموع التالي:1.

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

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