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

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

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

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

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

بناء جملة عام

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

لحساب إجمالي عدد القيم المفقودة في القائمة "ب" في القائمة "أ"، يرجى نسخ أو إدخال الصيغة أدناه في الخلية H6 ، ثم اضغط أدخل للحصول على النتيجة:

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

شرح الصيغة

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

• MATCH (F6: F8، B6: B10,0،XNUMX): يرأس نوع المباراة 0 يفرض على دالة MATCH إرجاع القيم الرقمية التي تشير إلى المواضع النسبية للقيم عبر الخلايا F6 إلى F8 في النطاق B6: B10. إذا كانت القيمة غير موجودة في القائمة "أ" ، فسيتم إرجاع الخطأ # N / A. إذن ، ستكون النتائج في مصفوفة كالتالي: {2؛ 3؛ # N / A}.
• ISNA (MATCH (F6: F8، B6: B10,0،XNUMX)) = ISNA ({2؛ 3؛ # N / A}): تعمل ISNA على اكتشاف ما إذا كانت القيمة خطأ "# N / A" أم لا. إذا كانت الإجابة بنعم ، فستقوم الوظيفة بإرجاع TURE ؛ إذا لم يكن كذلك ، فسوف ترجع خطأ. لذلك ، ستعود صيغة ISNA {FALSE؛ FALSE؛ TRUE}.
• إنتاج (-ISNA (MATCH (F6: F8، B6: B10,0،XNUMX))) = SUMPRODUCT (-{FALSE؛ FALSE؛ TRUE}): تحول العلامة السالبة المزدوجة TRUEs إلى 1s و FALSEs إلى 0s: {0 ؛ 1 ؛ 0}. ثم ترجع الدالة SUMPRODUCT المجموع: 1.

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

لحساب إجمالي عدد القيم المفقودة في القائمة "ب" في القائمة "أ"، يمكنك أيضًا استخدام دالة COUNTIF لمعرفة ما إذا كانت القيمة موجودة في القائمة A أم لا مع الشرط "= 0" ، حيث سيتم إنشاء 0 إذا كانت القيمة مفقودة. سيقوم SUMPRODUCT بعد ذلك بحساب العدد الإجمالي للقيم المفقودة.

بناء جملة عام

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

لحساب إجمالي عدد القيم المفقودة في القائمة "ب" في القائمة "أ"، يرجى نسخ أو إدخال الصيغة أدناه في الخلية H6 ، ثم اضغط أدخل للحصول على النتيجة:

= 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 مصفوفة TRUE و FALSE {FALSE؛ FALSE؛ TRUE}. ثم تحول علامة السالب المزدوجة TRUEs إلى 1s و FALSEs إلى 0s. المصفوفة النهائية هي كالتالي: {0 ؛ 0 ؛ 1}.
• إنتاج (- (COUNTIF (B6: B10، F6: F8)= 0)) = SUMPRODUCT ({0 ؛ 0 ؛ 1}): ثم يقوم SUMPRODUCT بإرجاع المجموع: 1.

وظائف ذات الصلة

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

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

كوتولس ل إكسيل - يساعدك على التميز من بين الحشود

هل ترغب في إكمال عملك اليومي بسرعة وبشكل مثالي؟ يوفر Kutools for Excel 300 ميزة متقدمة قوية (الجمع بين المصنفات ، والجمع حسب اللون ، ومحتويات الخلية المنقسمة ، وتحويل التاريخ ، وما إلى ذلك ...) وتوفير 80 ٪ من الوقت لك

• مصمم لسيناريوهات عمل 1500 ، يساعدك على حل 80٪ من مشاكل Excel.
• قلل الآلاف من نقرات لوحة المفاتيح والماوس كل يوم ، وقم بإراحة عينيك ويديك المتعبة.
• كن خبيرًا في برنامج Excel في 3 دقائق. لم تعد بحاجة إلى تذكر أي صيغ مؤلمة ورموز VBA.
• نسخة تجريبية مجانية غير محدودة لمدة 30 يومًا. ضمان استرداد الأموال لمدة 60 يومًا. ترقية مجانية ودعم لمدة عامين.

التفاصيل تحميل

---

علامة تبويب Office - تمكين القراءة والتحرير المبوب في Microsoft Office (بما في ذلك Excel)

• ثانية واحدة للتبديل بين عشرات المستندات المفتوحة!
• قلل مئات النقرات بالماوس كل يوم ، وداعًا ليد الماوس.
• يزيد من إنتاجيتك بنسبة 50٪ عند عرض مستندات متعددة وتحريرها.
• يجلب علامات التبويب الفعالة إلى Office (بما في ذلك Excel) ، تمامًا مثل Chrome و Firefox و Internet Explorer الجديد.

التفاصيل تحميل