البحث عن القيم المفقودة

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

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

---

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

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

بناء جملة عام

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

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

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

= إذا (إيسنا (تطابق (30002,6 دولارات أمريكية: 10 مليار دولار أمريكي، 0))، "مفقود"، "تم العثور عليه")

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

= إذا (إيسنا (تطابق (G6,6 دولارات أمريكية: 10 مليار دولار أمريكي، 0))، "مفقود"، "تم العثور عليه")

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

شرح الصيغة

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

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

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

---

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

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

بناء جملة عام

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

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

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

= IF (ISNA (VLOOKUP (30002,6 دولارات أمريكية: 10 مليار دولار أمريكي، 1، FALSE))، "مفقود"، "تم العثور عليه")

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

= IF (ISNA (VLOOKUP (G6,6 دولارات أمريكية: 10 مليار دولار أمريكي، 1، FALSE))، "مفقود"، "تم العثور عليه")

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

شرح الصيغة

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

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

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

---

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

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

بناء جملة عام

=IF(COUNTIF("lookup_range",lookup_value),"Found","Missing")

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

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

= إذا (COUNTIF (6 دولارات أمريكية: 10 مليار دولار أمريكي,30002) ، "تم العثور عليها" ، "مفقود")

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

= إذا (COUNTIF (6 دولارات أمريكية: 10 مليار دولار أمريكي,G6) ، "تم العثور عليها" ، "مفقود")

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

شرح الصيغة

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

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

• COUNTIF (مليار مليار دولار أمريكي: 6 دولارات أمريكية ، مجموعة 10 دولارات أمريكية): تحسب الدالة COUNTIF عدد المرات 3004، القيمة الموجودة في الخلية G8 ، تظهر في الصفيف 6 دولارات أمريكية: 10 مليار دولار أمريكي. على ما يبدو ، 3004 غير موجود في المصفوفة ، لذلك ستكون النتيجة 0.
• إذا(COUNTIF (مليار مليار دولار أمريكي: 6 دولارات أمريكية ، مجموعة 10 دولارات أمريكية)، "Found"، "Missing") = IF (0، "تم العثور عليها"، "مفقود"): ستقيم الدالة IF القيمة 0 على أنها FALSE. لذا ، ستعود الصيغة مفقود، القيمة التي يجب إرجاعها عند تقييم الزيادة الأولى إلى FALSE.

---

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

---

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

---

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