كيفية استكشاف أخطاء VLOOKUP في Excel

كيف

بواسطةبن ستوكتون

تاريخ آخر تحديث13 يناير 2020

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

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

حدود VLOOKUP

قبل البدء في استخدام VLOOKUP ، يجب أن تدرك أنه ليس الخيار الأفضل دائمًا لمستخدمي Excel.

بادئ ذي بدء ، لا يمكن استخدامه للبحث عن البيانات على يسارها. سيعرض أيضًا القيمة الأولى التي يعثر عليها فقط ، مما يعني أن VLOOKUP ليس خيارًا لنطاقات البيانات ذات القيم المكررة. يجب أن يكون عمود البحث الخاص بك أبعد عمود أيسر في نطاق بياناتك.

في المثال أدناه ، العمود الأبعد (العمود أ) كعمود البحث. لا توجد قيم مكررة في النطاق وبيانات البحث (في هذه الحالة ، بيانات من

العمود B) على يمين عمود البحث.

ستكون الدالتان INDEX و MATCH بدائل جيدة إذا كانت أي من هذه المشكلات تمثل مشكلة ، مثل وظيفة XLOOKUP الجديدة في Excel ، حاليًا في الاختبار التجريبي.

تتطلب VLOOKUP أيضًا ترتيب البيانات في صفوف لتكون قادرة على البحث عن البيانات وإرجاعها بدقة. سيكون HLOOKUP بديلاً جيدًا إذا لم يكن الأمر كذلك.

هناك بعض القيود الإضافية على صيغ VLOOKUP التي يمكن أن تسبب أخطاء ، كما سنوضح أكثر.

أخطاء VLOOKUP و # N / A

واحدة من أكثر أخطاء VLOOKUP الشائعة في Excel هي # N / A خطأ. يحدث هذا الخطأ عندما يتعذر على VLOOKUP العثور على القيمة التي تبحث عنها.

بادئ ذي بدء ، قد لا تكون قيمة البحث موجودة في نطاق بياناتك ، أو ربما استخدمت القيمة الخاطئة. إذا رأيت خطأ N / A ، تحقق جيدًا من القيمة في صيغة VLOOKUP.

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

في المثال أعلاه ، البحث عن أ هوية الطالب مع ال رقم 104 (في الخلية G4) بإرجاع # N / A خطأ لأن الحد الأدنى لرقم الهوية في النطاق هو 105.

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

ستعرض أيضًا خطأ # N / A إذا كانت قيمة البحث أصغر من القيمة الأدنى في النطاق.

في المثال أعلاه ، هوية الطالب يتم خلط القيم. على الرغم من قيمة 105 موجود في النطاق ، لا يمكن لـ VLOOKUP تشغيل بحث صحيح باستخدام مجموعة البحث تم تعيين الحجة على صحيح لان العمود أ لا يتم ترتيبها تصاعديًا.

تشمل الأسباب الشائعة الأخرى لأخطاء # N / A استخدام عمود بحث ليس الأبعد عن اليسار واستخدام الخلية مراجع لقيم البحث التي تحتوي على أرقام ولكن تم تنسيقها كنص ، أو تحتوي على أحرف فائضة مثل مسافات.

استكشاف أخطاء #VALUE وإصلاحها

ال #القيمة الخطأ عادة علامة على أن الصيغة التي تحتوي على دالة VLOOKUP غير صحيحة بطريقة أو بأخرى.

في معظم الحالات ، يكون هذا عادةً بسبب الخلية التي تشير إليها كقيمة البحث. ال أكبر مقاس قيمة بحث VLOOKUP هي 255 حرفًا.

إذا كنت تتعامل مع خلايا تحتوي على سلاسل أحرف أطول ، فلن تتمكن VLOOKUP من التعامل معها.

الحل الوحيد لذلك هو استبدال صيغة VLOOKUP الخاصة بك بصيغة INDEX و MATCH المدمجة. على سبيل المثال ، عندما يحتوي عمود واحد على خلية بها سلسلة تتكون من أكثر من 255 حرفًا ، يمكن استخدام دالة MATCH متداخلة داخل INDEX لتحديد موقع هذه البيانات بدلاً من ذلك.

في المثال أدناه ، فهرس إرجاع القيمة في الخلية ب 4، باستخدام النطاق في العمود أ لتحديد الصف الصحيح. يستخدم هذا المتداخل مباراة وظيفة لتحديد السلسلة العمود أ (يحتوي على 300 حرف) يطابق الخلية H4.

في هذه الحالة ، هذه هي الخلية A4مع فهرس عودة 108 (قيمة ال ب 4).

سيظهر هذا الخطأ أيضًا إذا كنت قد استخدمت مرجعًا غير صحيح للخلايا في صيغتك ، خاصة إذا كنت تستخدم نطاق بيانات من مصنف آخر.

يجب وضع مراجع المصنف في أقواس مربعة لكي تعمل الصيغة بشكل صحيح.

إذا واجهت خطأ #VALUE ، فتحقق مرة أخرى من صيغة VLOOKUP للتأكد من صحة مراجع الخلايا.

#NAME و VLOOKUP

إذا كان خطأ VLOOKUP ليس خطأ #VALUE أو خطأ # N / A ، فمن المحتمل أن يكون #اسم خطأ. قبل أن تصاب بالهلع من فكرة هذا الأمر ، كن مطمئنًا - إنه أسهل خطأ في VLOOKUP لإصلاحه.

يظهر خطأ #NAME عندما أخطأت في كتابة خطأ في دالة في Excel ، سواء كانت VLOOKUP أو وظيفة أخرى مثل SUM. انقر على خلية VLOOKUP وتحقق جيدًا من كتابة هجائية VLOOKUP بشكل صحيح.

إذا لم تكن هناك مشكلات أخرى ، فستعمل صيغة VLOOKUP بمجرد تصحيح هذا الخطأ.

استخدام وظائف Excel الأخرى

إنه بيان جريء ، ولكنه يعمل مثل VLOOKUP سيغير حياتك. على الأقل ، سيغير حياتك العملية ، مما يجعل Excel أداة أكثر قوة لتحليل البيانات.

إذا لم يكن VLOOKUP مناسبًا لك ، فاستغل هذه الميزات وظائف إكسل في حين أن.