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

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

المشكلة: قيمة البحث غير في العمود الأول في الوسيطة table_array

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

في الجدول التالي، نريد استرداد عدد الوحدات المباعة ل باه.

#NA الخطأ في VLOOKUP: قيمة البحث غير في العمود الأول من صفيف الجدول

ينتج #N/A لأن قيمة البحث "كهة" تظهر في العمود الثاني (إنتاج) من الوسيطة 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(array أو reference, MATCH(lookup_value,lookup_array,[match_type])

فلنستخدم INDEX/MATCH لاستبدال VLOOKUP من المثال أعلاه. سيبدو بناء الجملة كما يلي:

=INDEX(C2:C10,MATCH(B13,B2:B10,0))

باللغة الإنجليزية البسيطة، يعني ذلك ما يلي:

=INDEX(إرجاع قيمة من C2:C10، والتي ستتطابق(كهة، وهي في مكان ما في الصفيف B2:B10، حيث القيمة المرجعة هي القيمة الأولى المطابقة ل "كهة"))

يمكن استخدام الدالتين INDEX و MATCH كبديل ل VLOOKUP

تبحث الصيغة عن القيمة الأولى في C2:C10 التي تتطابق مع كرة (في B7) وترجع القيمة في C7 (100)،وهي القيمة الأولى التي تتطابق مع كرة.

المشكلة: لم يتم العثور على تطابق دقيق

عندما range_lookup الوسيطة FALSE — و VLOOKUP غير قادر على العثور على تطابق دقيق في بياناتك — فإنه يرجع الخطأ #N/A.

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

يمكنك أيضا استخدام الدالة CLEAN أو TRIM لتنظيف البيانات في الخلايا.

المشكلة: قيمة البحث أصغر من أصغر قيمة في الصفيف

إذا تم range_lookup الوسيطة TRUE — وكانت قيمة البحث أصغر من أصغر قيمة في الصفيف — سترى الخطأ #N/A. تبحث TRUE عن تطابق تقريبي في الصفيف وترجع القيمة الأقرب أقل من قيمة البحث.

في المثال التالي، تكون قيمة البحث 100، ولكن لا توجد قيم في نطاق B2:C10 أقل من 100؛ وبالتالي الخطأ.

الخطأ N/A في VLOOKUP عندما تكون قيمة البحث أصغر من أصغر قيمة في الصفيف

الحل:

  • تصحيح قيمة البحث كما هو ضروري.

  • إذا لم تتمكن من تغيير قيمة البحث وكانت بحاجة إلى مزيد من المرونة مع القيم المطابقة، فنظر في استخدام INDEX/MATCH بدلا من VLOOKUP— راجع المقطع أعلاه في هذه المقالة. باستخدام INDEX/MATCH، يمكنك البحث عن قيم أكبر من قيمة البحث أو أقل منها أو مساوية لها. لمزيد من المعلومات حول استخدام INDEX/MATCH بدلا من VLOOKUP، راجع المقطع السابق في هذا الموضوع.

المشكلة: لا يتم فرز عمود البحث بالترتيب التصاعدي

إذا range_lookup الوسيطة TRUE، ولم يتم فرز أحد أعمدة البحث بالترتيب التصاعدي (أ-ي)، سترى الخطأ #N/A.

الحل:

  • غير الدالة VLOOKUP للبحث عن تطابق دقيق. للقيام بذلك، قم بتعيين الوسيطة range_lookup إلى FALSE. لا يلزم إجراء فرز ل FALSE.

  • استخدم الدالة INDEX/MATCH للبحث عن قيمة في جدول غير مفرز.

المشكلة: القيمة هي رقم نقطة عائمة كبير

إذا كانت لديك قيم وقت أو أرقام عشرية كبيرة في الخلايا، Excel الخطأ #N/A بسبب دقة النقطة العائمة. إن أرقام النقاط العائمة هي أرقام تتبع الفاصلة العشرية. (Excel القيم الزمنية كأرقم نقاط عائمة.) Excel تخزين الأرقام بنقاط عائمة كبيرة جدا، لذا لكي تعمل الدالة بشكل صحيح، يجب تقريب أرقام النقاط العائمة إلى 5 أماكن عشرية.

الحل: قم بتقصير الأرقام عن طريق تقريبها إلى ما يصل إلى خمسة أماكن عشرية باستخدام الدالة ROUND.

هل تحتاج إلى مزيد من المساعدة؟

يمكنك دائماً الاستفسار من أحد الخبراء في مجتمع Excel التقني أو الحصول على الدعم في مجتمع الإجابات.

راجع أيضاً

هل تحتاج إلى مزيد من المساعدة؟

توسيع المهارات
استكشاف التدريب
الحصول على الميزات الجديدة أولاً
الانضمام إلى المشاركين في برنامج Microsoft Office Insider

هل كانت المعلومات مفيدة؟

ما مدى رضاك عن جودة الترجمة؟
ما الذي أثّر في تجربتك؟

نشكرك على ملاحظاتك!

×