يصف هذا الموضوع الأسباب الأكثر شيوعا ل VLOOKUP للحصول على نتيجة خاطئة على الدالة ويوفر اقتراحات لاستخدام INDEXوMATCH بدلا من ذلك.
تلميح: راجع أيضاً بطاقة المرجع السريع: نصائح استكشاف الأخطاء وإصلاحها لـ VLOOKUP والتي تقدم الأسباب الشائعة لمشكلات #NA في ملف PDF مناسب. يمكنك مشاركة ملف PDF مع الآخرين أو طباعته كمرجع لك.
المشكلة: قيمة البحث ليست في العمود الأول في وسيطة table_array
أحد قيود VLOOKUP هو أنه يمكن فقط البحث عن القيم في العمود الموجود في أقصى اليسار في صفيف الجدول. إذا لم تكن قيمة البحث في العمود الأول من الصفيف، فسترى الخطأ #N/A.
في الجدول التالي، نريد استرداد عدد الوحدات المباعة لـ Kale.
ينتج الخطأ #N/A لأن قيمة البحث "Kale" تظهر في العمود الثاني (إنتاج) للوسيطة table_array A2:C10. في هذه الحالة، يبحث Excel عنه في العمود A وليس العمود B.
الحل: يمكنك محاولة إصلاح ذلك عن طريق ضبط VLOOKUP للإشارة إلى العمود الصحيح. إذا لم يكن ذلك ممكناً، فحاول نقل الأعمدة. قد يكون هذا أيضًا غير عملي للغاية، إذا كان لديك جداول بيانات كبيرة أو معقدة حيث تكون قيم الخلايا نتائج حسابات أخرى - أو ربما تكون هناك أسباب منطقية أخرى لعدم تمكنك من تحريك الأعمدة. الحل هو استخدام مجموعة من دالتي INDEX وMATCH، والتي يمكن أن تبحث عن قيمة في عمود بغض النظر عن موضعه في جدول البحث. انظر في القسم التالي.
ضع في اعتبارك استخدام INDEX/MATCH بدلاً من ذلك
يعد INDEX وMATCH خيارات جيدة للعديد من الحالات التي لا يلبي فيها VLOOKUP احتياجاتك. الميزة الرئيسية لـ INDEX/MATCH هي أنه يمكنك البحث عن قيمة في عمود في أي مكان في جدول البحث. تُرجع الدالة INDEX قيمة من جدول / نطاق محدد - وفقًا لموضعها. تُرجع MATCH الموضع النسبي لقيمة في جدول / نطاق. استخدم INDEX وMATCH معًا في صيغة للبحث عن قيمة في جدول / صفيف عن طريق تحديد الموضع النسبي للقيمة في الجدول / الصفيف.
هناك العديد من الفوائد لاستخدام INDEX/MATCH بدلاً من VLOOKUP:
-
باستخدام INDEX وMATCH، لا يلزم أن تكون القيمة المرجعة في نفس العمود مثل عمود البحث. هذا يختلف عن VLOOKUP، حيث يجب أن تكون القيمة المعادة في النطاق المحدد. كيف هذا مهم؟ باستخدام VLOOKUP، يجب أن تعرف رقم العمود الذي يحتوي على قيمة الإرجاع. على الرغم من أن هذا قد لا يبدو صعبا، إلا أنه قد يكون مرهقًا عندما يكون لديك جدول كبير وعليك حساب عدد الأعمدة. أيضًا، إذا قمت بإضافة / إزالة عمود في الجدول الخاص بك، فيجب عليك إعادة فرز وسيطة col_index_num وتحديثها. باستخدام INDEX وMATCH، لا يلزم العد لأن عمود البحث يختلف عن العمود الذي يحتوي على القيمة المرجعة.
-
باستخدام INDEX وMATCH، يمكنك تحديد صف أو عمود في صفيف — أو تحديد كليهما. هذا يعني أنه يمكنك البحث عن القيم رأسياً وأفقياً.
-
يمكن استخدام INDEX وMATCH للبحث عن القيم في أي عمود. بخلاف VLOOKUP - حيث يمكنك فقط البحث عن قيمة في العمود الأول في الجدول - سيعمل INDEX وMATCH إذا كانت قيمة البحث في العمود الأول أو الأخير أو في أي مكان بينهما.
-
يوفر INDEX وMATCH المرونة في عمل مرجع ديناميكي للعمود الذي يحتوي على قيمة الإرجاع.هذا يعني أنه يمكنك إضافة أعمدة إلى جدولك دون كسر INDEX وMATCH. من ناحية أخرى، ينكسر VLOOKUP إذا كنت بحاجة إلى إضافة عمود إلى الجدول - لأنه يقوم بإنشاء مرجع ثابت للجدول.
-
يوفر INDEX وMATCH مزيدًا من المرونة مع التطابقات. يمكن لـ INDEX وMATCH العثور على تطابق تام، أو قيمة أكبر أو أقل من قيمة البحث. سيبحث VLOOKUP فقط عن أقرب تطابق لقيمة (افتراضيًا) أو قيمة دقيقة. تفترض VLOOKUP أيضا افتراضيًا أن العمود الأول في مصفوفة الجدول مرتبة أبجدياً، وافترض أن الجدول لم يتم إعداده بهذه الطريقة، فسيعيد VLOOKUP أول أقرب تطابق في الجدول، والذي قد لا يكون البيانات التي تبحث عنها.
بناء الجملة
لإنشاء بناء جملة لـ INDEX/MATCH، تحتاج إلى استخدام وسيطة الصفيف / المرجع من الدالة INDEX وتداخل بناء جملة MATCH بداخلها. يأخذ هذا النموذج:
= INDEX (صفيف أو مرجع، MATCH (lookup_value,lookup_array,[match_type])
دعنا نستخدم INDEX / MATCH لاستبدال VLOOKUP من المثال أعلاه. سيبدو بناء الجملة كما يلي:
=INDEX(C2:C10,MATCH(B13,B2:B10,0))
في اللغة الإنجليزية البسيطة، فهذا يعني:
=INDEX(إرجاع قيمة من C2:C10، والتي MATCH(Kale، الموجود في مكان ما في مجموعة B2:B10، حيث تكون القيمة المعادة هي القيمة الأولى المقابلة لـ Kale))
تبحث الصيغة عن القيمة الأولى في C2:C10 التي تتوافق مع Kale (في B7) وتُرجع القيمة في C7 (100)، وهي أول قيمة تطابق Kale.
المشكلة: لم يتم العثور على المطابقة التامة
عندما تكون الوسيطة range_lookup هي FALSE - وتعذر على VLOOKUP العثور على تطابق تام في بياناتك - فإنها تُرجع الخطأ #N/A.
الحل:إذا كنت متأكدًا من وجود البيانات ذات الصلة في جدول البيانات الخاص بك وأن VLOOKUP لا يلتقطها، فاستغرق بعض الوقت للتحقق من أن الخلايا المشار إليها لا تحتوي على مسافات مخفية أو أحرف غير قابلة للطباعة. تأكد أيضاً من أن الخلايا تتبع نوع البيانات الصحيح. على سبيل المثال، يجب تنسيق الخلايا التي تحتوي على أرقام على هيئة رقم وليس نص.
ضع في اعتبارك أيضاً استخدام وظيفة CLEAN أو TRIM لتنظيف البيانات في الخلايا.
المشكلة: قيمة البحث أصغر من أصغر قيمة في المصفوفة
إذا تم تعيين وسيطة range_lookup على TRUE - وكانت قيمة البحث أصغر من أصغر قيمة في الصفيف - فسترى الخطأ #N/A. تبحث TRUE عن تطابق تقريبي في الصفيف وترجع أقرب قيمة أقل من قيمة البحث.
في المثال التالي، تكون قيمة البحث 100، ولكن لا توجد قيم في النطاق B2:C10 أقل من 100؛ ومن هنا الخطأ.
الحل:
-
قم بتصحيح قيمة البحث حسب الضرورة.
-
إذا لم تتمكن من تغيير قيمة البحث وتحتاج إلى قدر أكبر من المرونة مع القيم المطابقة، ففكر في استخدام INDEX/MATCH بدلاً من VLOOKUP - راجع القسم أعلاه في هذه المقالة. باستخدام INDEX/MATCH، يمكنك البحث عن قيم أكبر من قيمة البحث أو أقل منها أو مساوية لها. لمزيد من المعلومات حول استخدام INDEX/MATCH بدلاً من VLOOKUP، راجع القسم السابق في هذا الموضوع.
المشكلة: لا يتم فرز عمود البحث بترتيب تصاعدي
إذا تم تعيين وسيطة range_lookup على TRUE - ولم يتم فرز أحد أعمدة البحث بالترتيب التصاعدي (A-Z) - فسترى الخطأ #N/A.
الحل:
-
قم بتغيير وظيفة VLOOKUP للبحث عن تطابق تام. للقيام بذلك، قم بتعيين وسيطة range_lookup على FALSE. لا يوجد فرز ضروري للدالة FALSE.
-
استخدم الدالة INDEX/MATCH للبحث عن قيمة في جدول غير مفرز.
المشكلة: القيمة عدد كبير من الفاصلة العائمة
إذا كانت لديك قيم زمنية أو أرقام عشرية كبيرة في الخلايا، يُرجع Excel الخطأ #N/A بسبب دقة الفاصلة العائمة. أرقام الفاصلة العائمة هي الأرقام التي تلي الفاصلة العشرية. (يخزن Excel قيم الوقت كأرقام فاصلة عائمة.) لا يمكن لـ Excel تخزين الأرقام بنقاط عائمة كبيرة جدًا، لذا لكي تعمل الوظيفة بشكل صحيح، يجب تقريب أرقام الفاصلة العائمة إلى 5 منازل عشرية.
الحل: قم بتقصير الأرقام عن طريق تقريبها حتى خمسة منازل عشرية باستخدام الدالة ROUND.
هل تحتاج إلى مزيد من المساعدة؟
يمكنك دائماً الاستفسار من أحد الخبراء في مجتمع Excel التقني أو الحصول على الدعم في المجتمعات.
