عند استخدام صيغ VLOOKUP في Excel ، قد تواجه أحيانًا أخطاء # N / A القبيحة.يحدث هذا عندما لا تتمكن الصيغة من العثور على قيمة البحث.
في هذا البرنامج التعليمي ، سأوضح لك الطرق المختلفة لاستخدام IFERROR و VLOOKUP للتعامل مع أخطاء # N / A هذه في أوراق العمل.
استخدم IFERROR مع VLOOKUP لعرض شيء ذي معنى بدلاً من الخطأ # N / A (أو أي خطأ آخر).
قبل الدخول في تفاصيل كيفية استخدام هذه المجموعة ، دعنا نلقي نظرة سريعة على وظيفة IFERROR لنرى كيف تعمل.
المحتويات
وصف وظيفة IFERROR
باستخدام الدالة IFERROR ، يمكنك تحديد ما يجب أن يحدث عندما تُرجع صيغة أو مرجع خلية خطأ.
هذا هو بناء جملة الدالة IFERROR.
= IFERROR (القيمة ، value_if_error)
- القيمة - هذه هي المعلمة للتحقق من وجود أخطاء.في معظم الحالات ، تكون إما صيغة أو مرجع خلية.عند استخدام VLOOKUP مع IFERROR ، ستكون صيغة VLOOKUP هي هذه المعلمة.
- القيمة_إذا_خطأ - هذه هي القيمة التي يتم إرجاعها عند حدوث خطأ.تم تقييم أنواع الأخطاء التالية: # N / A و #REF! و # DIV / 0! و #VALUE! و #NUM! و #NAME؟ و # NULL !.
الأسباب المحتملة لإرجاع VLOOKUP أخطاء # N / A
قد ترجع الدالة VLOOKUP الخطأ # N / A لأي من الأسباب التالية:
- لم يتم العثور على قيمة البحث في صفيف البحث.
- توجد مسافات بادئة أو لاحقة أو مزدوجة في قيمة البحث (أو صفيف الجدول).
- يوجد خطأ مطبعي في قيمة البحث أو قيمة البحث في الصفيف.
يمكنك استخدام IFERROR مع VLOOKUP لمعالجة كل أسباب الخطأ هذه.ومع ذلك ، يجب أن تضع في اعتبارك الأسباب رقم 2 ورقم 3 ، وتصحيحها في بيانات المصدر ، بدلاً من السماح لـ IFERROR بمعالجتها.
ملاحظة: سيتعامل IFERROR مع الخطأ بغض النظر عن سبب ذلك.إذا كنت تريد فقط معالجة الأخطاء الناتجة عن عدم تمكن VLOOKUP من العثور على قيمة البحث ، فاستخدم IFNA بدلاً من ذلك.سيضمن ذلك عدم معالجة الأخطاء بخلاف # N / A ، ويمكنك التحقيق في هذه الأخطاء الأخرى.
يمكنك استخدام وظيفة TRIM للتعامل مع المسافات البادئة ، واللاحقة ، والمزدوجة.
استبدل أخطاء VLOOKUP # N / A بنص ذي معنى
افترض أن لديك مجموعة بيانات تبدو كالتالي:
كما ترى ، تُرجع صيغة VLOOKUP خطأً لأن قيمة البحث ليست في القائمة.نحن نبحث عن نتيجة غلين ، فهي ليست في جدول النتائج.
في حين أن هذه مجموعة بيانات صغيرة جدًا ، فقد ينتهي بك الأمر بمجموعة بيانات ضخمة حيث يتعين عليك التحقق من تكرارات العديد من العناصر.ستحصل على خطأ # N / A لكل حالة حيث لم يتم العثور على القيمة.
هذه هي الصيغة التي يمكنك استخدامها للحصول على شيء ذي معنى وليس خطأ # N / A.
= IFERROR (VLOOKUP (D2، $ A $ 2: $ B $ 10,2,0،XNUMX،XNUMX)، "Not Found")
ترجع الصيغة أعلاه النص "غير موجود" بدلاً من الخطأ # N / A.يمكنك أيضًا استخدام نفس الصيغة لإرجاع الفراغات أو الأصفار أو أي نص آخر ذي معنى.
VLOOKUP متداخلة مع وظيفة IFERROR
إذا كنت تستخدم VLOOKUP وكانت جداول البحث الخاصة بك منتشرة عبر نفس ورقة العمل أو أوراق عمل مختلفة ، فأنت بحاجة إلى التحقق من قيمة VLOOKUP من خلال كل هذه الجداول.
على سبيل المثال ، في مجموعة البيانات الموضحة أدناه ، يوجد جدولين منفصلين لأسماء الطلاب ودرجاتهم.
إذا كان عليّ العثور على درجة Grace في مجموعة البيانات هذه ، فأنا بحاجة إلى استخدام وظيفة VLOOKUP للتحقق من الجدول الأول ، وإذا لم يتم العثور على القيمة فيه ، فتحقق من الجدول الثاني.
فيما يلي صيغة IFERROR المتداخلة التي يمكنني استخدامها للعثور على القيمة:
=IFERROR(VLOOKUP(G3,$A$2:$B$5,2,0),IFERROR(VLOOKUP(G3,$D$2:$E$5,2,0),"Not Found"))
استخدام VLOOKUP مع IF و ISERROR (ما قبل Excel 2007)
تم تقديم الدالة IFERROR في Excel 2007 for Windows و Excel 2016 في Mac.
إذا كنت تستخدم إصدارًا سابقًا ، فلن تعمل وظيفة IFERROR على نظامك.
يمكنك نسخ وظيفة IFERROR عن طريق دمج الدالة IF مع الدالة ISERROR.
دعني أوضح لك بسرعة كيفية استخدام مزيج من IF و ISERROR بدلاً من IFERROR.
في المثال أعلاه ، بدلاً من استخدام IFERROR ، يمكنك أيضًا استخدام الصيغة الموضحة في الخلية B3:
= IF (ISERROR (A3)، "Not Found"، A3)
يتحقق جزء ISERROR من الصيغة من وجود أخطاء (بما في ذلك أخطاء # N / A) ويعيد TRUE إذا تم العثور على خطأ ، و FALSE بخلاف ذلك.
- إذا كانت TRUE (تشير إلى خطأ) ، فستُرجع الدالة IF القيمة المحددة ("غير موجود" في هذه الحالة).
- إذا كانت FALSE (مما يعني عدم وجود خطأ) ، فستقوم الدالة IF بإرجاع هذه القيمة (A3 في المثال أعلاه).
IFERROR و IFNA
يعالج IFERROR جميع أنواع الأخطاء ، بينما يعالج IFNA أخطاء # N / A فقط.
عند التعامل مع الأخطاء التي تسببها VLOOKUP ، تحتاج إلى التأكد من استخدام الصيغة الصحيحة.
عندما تريد معالجة الأخطاء المختلفة ، من فضلكاستخدم IFERROR.يمكن أن تحدث الأخطاء الآن بسبب مجموعة متنوعة من العوامل (مثل الصيغ الخاطئة والنطاقات المسماة التي بها أخطاء إملائية وقيم البحث غير الموجودة والقيم الخاطئة التي تم إرجاعها من جداول البحث).لا يهم IFERROR ، فهو يستبدل كل هذه الأخطاء بالقيمة المحددة.
استخدم IFNA عندما تريد فقط معالجة أخطاء # N / A، والذي يحدث على الأرجح بسبب عدم تمكن صيغة VLOOKUP من العثور على قيمة البحث.
