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

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

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

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

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

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

=IF(ISNA(MATCH()))«lookup_value»,lookup_range,0)),«Missing»,«Found»)

√ ملاحظة: يمكنك تغيير القيمتين «Missing» و«Found» إلى أي قيمتين تفضلهما.

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

=IF(ISNA(MATCH()))30002,$B$6:$B$10,0)),«Missing»,«Found»)

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

=IF(ISNA(MATCH()))G6,$B$6:$B$10,0)),«Missing»,«Found»)

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

شرح الصيغة

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

=IF()ISNA()MATCH(G8,$B$6:$B$10,0)),«Missing»,«Found»)

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

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

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

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

=IF(ISNA(VLOOKUP()))«lookup_value»,lookup_range,1,FALSE)),«Missing»,«Found»)

√ ملاحظة: يمكنك تغيير القيمتين «Missing» و«Found» إلى أي قيم تريدها.

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

=IF(ISNA(VLOOKUP()))30002,$B$6:$B$10،1،FALSE))،«Missing»،«Found»)

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

=IF(ISNA(VLOOKUP()))G6,$B$6:$B$10،1،FALSE))،«Missing»،«Found»)

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

شرح الصيغة

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

=IF()ISNA()VLOOKUP(G8,$B$6:$B$10,1,FALSE)),«Missing»,«Found»)

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

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

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

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

=IF(COUNTIF())«lookup_range»,lookup_value),«Found»,«Missing»)

√ ملاحظة: يمكنك تغيير القيمتين «Found» و«Missing» إلى أي قيم تريدها.

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

=IF(COUNTIF())$B$6:$B$10,30002)،«Found»،«Missing»)

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

=IF(COUNTIF())$B$6:$B$10,G6)،«Found»،«Missing»)

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

شرح الصيغة

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

=IF()COUNTIF($B$6:$B$10,G8),«Found»,«Missing»)

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

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

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