حساب القيم المفقودة

لنفترض أن لديك قائمتين، ولحساب العدد الإجمالي للقيم في إحدى القوائم التي لا توجد في القائمة الأخرى، يمكنك استخدام صيغة 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(ISNA(MATCH(F6:F8,B6:B10,0))) = ISNA(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;1;0}. بعد ذلك، تقوم دالة 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)=0) = --(--({1;1;0}=0)=0): الشريحة {1;1;0}=0 تنتج صفيف TRUE و FALSE كالتالي: {FALSE;FALSE;TRUE}. ثم تقوم علامة السالب المزدوجة بتحويل TRUE إلى 1 وFALSE إلى 0. الصفيف النهائي سيكون كالتالي: {0;0;1}.
• SUMPRODUCT(--(COUNTIF(B6:B10,F6:F8)=0)) = SUMPRODUCT({0;0;1}): تقوم دالة SUMPRODUCT بعد ذلك بإرجاع المجموع: 1.

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

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