العثور على القيم المفقودة

هناك حالات تحتاج فيها إلى مقارنة قائمتين للتحقق مما إذا كانت قيمة من القائمة A موجودة في القائمة B في Excel. على سبيل المثال، لديك قائمة من المنتجات، وترغب في التحقق مما إذا كانت المنتجات الموجودة في قائمتك موجودة في قائمة المنتجات التي يقدمها موردك. لتحقيق هذه المهمة، قمنا هنا بسرد ثلاث طرق أدناه، يمكنك اختيار الطريقة التي تفضلها.

العثور على القيم المفقودة باستخدام MATCH، ISNA و IF
العثور على القيم المفقودة باستخدام VLOOKUP، ISNA و IF
العثور على القيم المفقودة باستخدام COUNTIF و IF

العثور على القيم المفقودة باستخدام MATCH، ISNA و IF

لمعرفة ما إذا كانت جميع المنتجات في قائمتك موجودة في قائمة موردك كما هو موضح في لقطة الشاشة أعلاه، يمكنك أولاً استخدام دالة MATCH لاسترداد موقع المنتج في قائمتك (قيمة القائمة A) في قائمة المورد (القائمة B). ستعيد MATCH خطأ #N/A عندما لا يتم العثور على المنتج. بعد ذلك، يمكنك إدخال النتيجة في ISNA لتحويل أخطاء #N/A إلى TRUE، مما يعني أن تلك المنتجات مفقودة. ستقوم دالة IF بعد ذلك بإرجاع النتيجة التي تتوقعها.

صيغة عامة

=IF(ISNA(MATCH("lookup_value",lookup_range,0)),"مفقود","موجود")

√ ملاحظة: يمكنك تغيير "مفقود"، "موجود" إلى أي قيم حسب الحاجة.

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

=IF(ISNA(MATCH(30002,$B$6:$B$10,0)),"مفقود","موجود")

أو، استخدم مرجع خلية لجعل الصيغة ديناميكية:

=IF(ISNA(MATCH(G6,$B$6:$B$10,0)),"مفقود","موجود")

√ ملاحظة: علامات الدولار ($) أعلاه تشير إلى المراجع المطلقة، مما يعني أن lookup_range في الصيغة لن يتغير عند نقل أو نسخ الصيغة إلى خلايا أخرى. ومع ذلك، لم يتم إضافة علامات دولار إلى lookup_value لأنك تريد أن تكون ديناميكية. بعد إدخال الصيغة، اسحب مقبض التعبئة لأسفل لتطبيق الصيغة على الخلايا أدناه.

شرح الصيغة

هنا نستخدم الصيغة أدناه كمثال:

=IF(ISNA(MATCH(G8,$B$6:$B$10,0)),"مفقود","موجود")

• MATCH(G8,$B$6:$B$10,0): نوع المطابقة 0 يجبر دالة MATCH على إرجاع قيمة عددية تشير إلى موقع أول تطابق لـ 3004، القيمة في الخلية G8، في المصفوفة $B$6:$B$10. ومع ذلك، في هذه الحالة، لم تستطع MATCH العثور على القيمة في مجموعة البحث، لذلك ستعيد خطأ #N/A.
• ISNA(ISNA(MATCH(G8,$B$6:$B$10,0))) = ISNA(ISNA(#N/A)): تعمل ISNA على تحديد ما إذا كانت القيمة هي خطأ “#N/A” أم لا. إذا كان الأمر كذلك، ستعيد الدالة TURE؛ إذا كانت القيمة أي شيء آخر غير خطأ “#N/A”، ستعيد FALSE. لذلك، ستعيد صيغة ISNA هذه TURE.
• IF(ISNA(MATCH(G8,$B$6:$B$10,0)),"مفقود","موجود") = IF(TRUE,"مفقود","موجود"): ستقوم دالة IF بإرجاع مفقود إذا كانت المقارنة التي أجراها ISNA و MATCH صحيحة، وإلا ستعيد موجود. لذلك، ستعيد الصيغة مفقود.

العثور على القيم المفقودة باستخدام VLOOKUP، ISNA و IF

لمعرفة ما إذا كانت جميع المنتجات في قائمتك موجودة في قائمة موردك، يمكنك استبدال دالة MATCH أعلاه بـ VLOOKUP، حيث تعمل بنفس الطريقة مثل MATCH التي ستعيد خطأ #N/A إذا لم تكن القيمة موجودة في قائمة أخرى، أو نقول إنها مفقودة.

صيغة عامة

=IF(ISNA(VLOOKUP("lookup_value",lookup_range,1,FALSE)),"مفقود","موجود")

√ ملاحظة: يمكنك تغيير "مفقود"، "موجود" إلى أي قيم حسب الحاجة.

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

=IF(ISNA(VLOOKUP(30002,$B$6:$B$10,1,FALSE)),"مفقود","موجود")

أو، استخدم مرجع خلية لجعل الصيغة ديناميكية:

=IF(ISNA(VLOOKUP(G6,$B$6:$B$10,1,FALSE)),"مفقود","موجود")

√ ملاحظة: علامات الدولار ($) أعلاه تشير إلى المراجع المطلقة، مما يعني أن lookup_range في الصيغة لن يتغير عند نقل أو نسخ الصيغة إلى خلايا أخرى. ومع ذلك، لم يتم إضافة علامات دولار إلى lookup_value لأنك تريد أن تكون ديناميكية. بعد إدخال الصيغة، اسحب مقبض التعبئة لأسفل لتطبيق الصيغة على الخلايا أدناه.

شرح الصيغة

هنا نستخدم الصيغة أدناه كمثال:

=IF(ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"مفقود","موجود")

• VLOOKUP(G8,$B$6:$B$10,1,FALSE): البحث عن القيمة FALSE يجبر دالة VLOOKUP على البحث وإرجاع القيمة التي تتطابق تمامًا مع 3004، القيمة في الخلية G8. إذا كانت lookup_value 3004 موجودة في العمود الأول من المصفوفة $B$6:$B$10، ستعيد VLOOKUP تلك القيمة؛ وإلا، ستعيد قيمة الخطأ #N/A. هنا، 3004 غير موجودة في المصفوفة، لذلك سيكون الناتج #N/A.
• ISNA(ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE))) = ISNA(ISNA(#N/A)): تعمل ISNA على تحديد ما إذا كانت القيمة هي خطأ “#N/A” أم لا. إذا كان الأمر كذلك، ستعيد الدالة TURE؛ إذا كانت القيمة أي شيء آخر غير خطأ “#N/A”، ستعيد FALSE. لذلك، ستعيد صيغة ISNA هذه TURE.
• IF(ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"مفقود","موجود") = IF(TRUE,"مفقود","موجود"): ستقوم دالة IF بإرجاع مفقود إذا كانت المقارنة التي أجراها ISNA و VLOOKUP صحيحة، وإلا ستعيد موجود. لذلك، ستعيد الصيغة مفقود.

العثور على القيم المفقودة باستخدام COUNTIF و IF

لمعرفة ما إذا كانت جميع المنتجات في قائمتك موجودة في قائمة موردك، يمكنك استخدام صيغة أبسط باستخدام دوال COUNTIF و IF. تستفيد الصيغة من حقيقة أن Excel سيقيم أي رقم باستثناء الصفر (0) على أنه TRUE. لذلك إذا كانت القيمة موجودة في قائمة أخرى، ستعيد دالة COUNTIF عدد مرات ظهورها في تلك القائمة، ثم ستأخذ IF الرقم كـ TRUE؛ إذا لم تكن القيمة موجودة في القائمة، ستعيد دالة COUNTIF 0، وستأخذ IF هذا كـ FALSE.

صيغة عامة

=IF(COUNTIF("lookup_range",lookup_value),"موجود","مفقود")

√ ملاحظة: يمكنك تغيير "موجود"، "مفقود" إلى أي قيم حسب الحاجة.

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

=IF(COUNTIF($B$6:$B$10,30002),"موجود","مفقود")

أو، استخدم مرجع خلية لجعل الصيغة ديناميكية:

=IF(COUNTIF($B$6:$B$10,G6),"موجود","مفقود")

√ ملاحظة: علامات الدولار ($) أعلاه تشير إلى المراجع المطلقة، مما يعني أن lookup_range في الصيغة لن يتغير عند نقل أو نسخ الصيغة إلى خلايا أخرى. ومع ذلك، لم يتم إضافة علامات دولار إلى lookup_value لأنك تريد أن تكون ديناميكية. بعد إدخال الصيغة، اسحب مقبض التعبئة لأسفل لتطبيق الصيغة على الخلايا أدناه.

شرح الصيغة

هنا نستخدم الصيغة أدناه كمثال:

=IF(COUNTIF($B$6:$B$10,G8),"موجود","مفقود")

• COUNTIF($B$6:$B$10,G8): تقوم دالة COUNTIF بحساب عدد المرات التي يظهر فيها 3004، القيمة في الخلية G8، في المصفوفة $B$6:$B$10. من الواضح أن 3004 غير موجودة في المصفوفة، لذلك سيكون الناتج 0.
• IF(COUNTIF($B$6:$B$10,G8),"موجود","مفقود") = IF(0,"موجود","مفقود"): ستقوم دالة IF بتقييم 0 على أنه FALSE. لذلك، ستعيد الصيغة مفقود، وهي القيمة التي تُرجع عند تقييم الوسيطة الأولى على أنها FALSE.

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

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