البحث عن القيم المفقودة
هناك حالات تحتاج فيها إلى مقارنة قائمتين للتحقق مما إذا كانت قيمة القائمة 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")
√ ملاحظة: يمكنك تغيير "مفقود" ، "تم العثور عليه" إلى أي قيم تريدها.
- ابحث عن القيمة: تستخدم القيمة MATCH لاسترداد موقعها إذا كانت موجودة في lookup_range أو خطأ # N / A إذا لم يكن كذلك. هنا يشير إلى المنتجات في قائمتك.
- lookup_range: نطاق الخلايا المراد مقارنتها مع ابحث عن القيمة. هنا يشير إلى قائمة منتجات المورد.
تجده في الخارج إذا كانت جميع المنتجات الموجودة في قائمتك موجودة في قائمة مورديك، يرجى نسخ أو إدخال الصيغة أدناه في الخلية 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")
√ ملاحظة: يمكنك تغيير "مفقود" ، "تم العثور عليه" إلى أي قيم تريدها.
- ابحث عن القيمة: تستخدم القيمة VLOOKUP لاسترداد موقعها إذا كانت موجودة في lookup_range أو خطأ # N / A إذا لم يكن كذلك. هنا يشير إلى المنتجات في قائمتك.
- lookup_range: نطاق الخلايا المراد مقارنتها مع ابحث عن القيمة. هنا يشير إلى قائمة منتجات المورد.
لمعرفة ما إذا كانت جميع المنتجات الموجودة في قائمتك موجودة في قائمة الموردين ، يرجى نسخ أو إدخال الصيغة أدناه في الخلية 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")
√ ملاحظة: يمكنك تغيير "موجود" ، "مفقود" إلى أي قيم تريدها.
- lookup_range: نطاق الخلايا المراد مقارنتها مع ابحث عن القيمة. هنا يشير إلى قائمة منتجات المورد.
- ابحث عن القيمة: القيمة COUNTIF المستخدمة لإرجاع عدد التكرارات بها lookup_range. هنا يشير إلى المنتجات في قائمتك.
لمعرفة ما إذا كانت جميع المنتجات الموجودة في قائمتك موجودة في قائمة الموردين ، يرجى نسخ أو إدخال الصيغة أدناه في الخلية 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.
وظائف ذات الصلة
تعد الدالة IF واحدة من أبسط الوظائف وأكثرها فائدة في مصنف Excel. يقوم بإجراء اختبار منطقي بسيط يعتمد على نتيجة المقارنة ، ويعيد قيمة واحدة إذا كانت النتيجة TRUE ، أو قيمة أخرى إذا كانت النتيجة FALSE.
تبحث دالة Excel MATCH عن قيمة معينة في نطاق من الخلايا ، وتُرجع الموضع النسبي للقيمة.
تبحث الدالة Excel VLOOKUP عن قيمة عن طريق المطابقة في العمود الأول من الجدول وإرجاع القيمة المقابلة من عمود معين في نفس الصف.
دالة COUNTIF هي دالة إحصائية في Excel تُستخدم لحساب عدد الخلايا التي تفي بمعيار. وهو يدعم عوامل التشغيل المنطقية (<> و = و> و <) ، وحروف البدل (؟ و *) للمطابقة الجزئية.
الصيغ ذات الصلة
ابحث عن قيمة تحتوي على نص محدد مع أحرف البدل
للعثور على أول تطابق يحتوي على سلسلة نصية معينة في نطاق في Excel ، يمكنك استخدام صيغة INDEX و MATCH مع أحرف البدل - العلامة النجمية (*) وعلامة الاستفهام (؟).
هناك أوقات تحتاج فيها إلى Excel لاسترداد البيانات بناءً على معلومات جزئية. لحل المشكلة ، يمكنك استخدام صيغة VLOOKUP مع أحرف البدل - العلامة النجمية (*) وعلامة الاستفهام (؟).
هناك أوقات نحتاج فيها إلى العثور على تطابقات تقريبية في Excel لتقييم أداء الموظفين ، وتقدير درجات الطلاب ، وحساب طابع البريد بناءً على الوزن ، وما إلى ذلك. في هذا البرنامج التعليمي ، سنتحدث عن كيفية استخدام وظائف INDEX و MATCH لاسترداد النتائج التي نحتاجها.
البحث عن أقرب قيمة مطابقة بمعايير متعددة
في بعض الحالات ، قد تحتاج إلى البحث عن أقرب قيمة مطابقة تقريبية بناءً على أكثر من معيار واحد. من خلال الجمع بين وظائف INDEX و MATCH و IF ، يمكنك إنجاز ذلك بسرعة في Excel.
أفضل أدوات إنتاجية المكتب
كوتولس ل إكسيل - يساعدك على التميز من بين الحشود
Kutools for Excel يضم أكثر من 300 ميزة، التأكد من أن ما تحتاجه هو مجرد نقرة واحدة...
علامة تبويب Office - تمكين القراءة والتحرير المبوب في Microsoft Office (بما في ذلك Excel)
- ثانية واحدة للتبديل بين عشرات المستندات المفتوحة!
- قلل مئات النقرات بالماوس كل يوم ، وداعًا ليد الماوس.
- يزيد من إنتاجيتك بنسبة 50٪ عند عرض مستندات متعددة وتحريرها.
- يجلب علامات التبويب الفعالة إلى Office (بما في ذلك Excel)، تمامًا مثل Chrome وEdge وFirefox.
