كيفية استخدام وظيفة Excel VLOOKUP

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

إذا لم يتم فرز البيانات ، فقد تقوم VLOOKUP بإرجاع نتيجة غير صحيحة.

مقابل التطابق التقريبي

يمكن تعيين VLOOKUP بحيث يقوم بإرجاع المعلومات التي تطابق تمامًا ابحث عن القيمة أو يمكن تعيينه لإرجاع التطابق التقريبي

العامل الحاسم هو مجموعة البحث جدال:

• لتعيين FALSE ، فإنها تُرجع فقط المعلومات المتعلقة بالمطابقة التامة إلى ابحث عن القيمة
• مضبوطة على TRUE أو تم حذفها تقوم بإرجاع معلومات دقيقة أو تقريبية متعلقة بـ ابحث عن القيمة

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

ملحوظة: VLOOKUP ليست حساسة لحالة الأحرف - كليهما الحاجيات و الحاجيات تهجئة مقبولة للمثال أعلاه.

في حالة وجود قيم مطابقة متعددة - على سبيل المثال ، الأدوات هي سرد أكثر من مرة في العمود 1 من جدول البيانات - يتم إرجاع المعلومات المتعلقة بالقيمة المطابقة الأولى التي تمت مواجهتها من أعلى إلى أسفل بواسطة الدالة.

إدخال وسيطات الدالة VLOOKUP الخاصة بـ Excel باستخدام Pointing

في المثال الأول للصورة أعلاه ، يتم استخدام الصيغة التالية التي تحتوي على الدالة VLOOKUP للعثور على سعر الوحدة الحاجيات الموجود في جدول البيانات.

= VLOOKUP (A2، $ A $ 5: $ B $ 8،2، FALSE)

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

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

تم استخدام الخطوات التالية لإدخال الدالة VLOOKUP في الخلية B2 باستخدام مربع الحوار الخاص بالوظيفة.

فتح مربع حوار VLOOKUP

1. انقر فوق الخلية B2 لجعلها الخلية النشطة - الموقع حيث يتم عرض نتائج الدالة VLOOKUP
2. اضغط على الصيغ التبويب.
3. أختر البحث والمراجع من الشريط لفتح القائمة المنسدلة للوظيفة
4. انقر فوق VLOOKUP في القائمة لإظهار مربع الحوار الخاص بالوظيفة

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

مشيرا إلى خلية المراجع

يتم إدخال الوسائط الخاصة بوظيفة VLOOKUP في سطور منفصلة بمربع الحوار كما هو موضح في الصورة أعلاه.

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

استخدام مراجع الخلايا النسبية والمطلقة مع الوسيطات

ليس من غير المألوف استخدام نسخ متعددة من VLOOKUP لإرجاع معلومات مختلفة من نفس جدول البيانات.

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

في الصورة أعلاه ، علامات الدولار ( $ ) تحيط مراجع الخلية ل Table_array حجة تشير إلى أنها مراجع خلية مطلقة ، مما يعني أنها لن تتغير إذا تم نسخ الدالة إلى خلية أخرى.

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

مرجع الخلية المستخدمة ل lookup_value - A2 - من ناحية أخرى , لا تحيط بها علامات الدولار ، مما يجعلها مرجعية خلية نسبي. تتغير مراجع الخلايا النسبية عندما يتم نسخها لتعكس موقعها الجديد نسبة إلى موضع البيانات التي تشير إليها.

تسمح مراجع الخلايا النسبية بالبحث عن عناصر متعددة في نفس جدول البيانات بنسخ VLOOKUP إلى مواقع متعددة والدخول إلى عناصر مختلفة lookup_values .

إدخال وسيطات الدالة

1. اضغط على ابحث عن القيمة خط في VLOOKUP صندوق المحادثة
2. انقر فوق الخلية A2 في ورقة العمل لإدخال مرجع الخلية هذا SEARCH_KEY جدال
3. اضغط على Table_array خط من مربع الحوار
4. قم بتمييز الخلايا من A5 إلى B8 في ورقة العمل لإدخال هذا النطاق باسم Table_array حجة - لا يتم تضمين عناوين الجدول
5. اضغط على F4 المفتاح على لوحة المفاتيح لتغيير النطاق إلى مراجع الخلايا المطلقة
6. اضغط على Col_index_num خط من مربع الحوار
7. نوع أ 2 على هذا الخط باسم Col_index_num حجة ، حيث توجد معدلات الخصم في العمود 2 من Table_array جدال
8. اضغط على مجموعة البحث خط من مربع الحوار
9. اكتب الكلمة خاطئة كما مجموعة البحث جدال
10. اضغط على أدخل مفتاح على لوحة المفاتيح لإغلاق مربع الحوار والعودة إلى ورقة العمل
11. يجب أن تظهر الإجابة $ 14.76 - سعر الوحدة لـ Widget - في الخلية B2 من ورقة العمل
12. عند النقر فوق الخلية B2 ، فإن الدالة كاملة = VLOOKUP (A2، $ A $ 5: $ B $ 8،2، FALSE) يظهر في شريط الصيغة أعلى ورقة العمل

Excel VLOOKUP رسائل الخطأ

ترتبط رسائل الخطأ التالية بـ VLOOKUP:

يتم عرض خطأ # N / A ("القيمة غير متاحة") في حالة:

• ال ابحث عن القيمة لم يتم العثور عليه في العمود الأول من نطاق جدال
• ال Table_array الحجة غير دقيقة. على سبيل المثال ، قد تتضمن الوسيطة أعمدة فارغة على الجانب الأيسر من النطاق
• ال مجموعة البحث يتم تعيين الوسيطة إلى FALSE ومطابقة تامة لـ SEARCH_KEY الوسيطة لا يمكن العثور عليها في العمود الأول من نطاق
• ال مجموعة البحث يتم تعيين الوسيطة إلى TRUE وجميع القيم في العمود الأول من نطاق أكبر من SEARCH_KEY

ARERE! يتم عرض الخطأ إذا:

• ال Col_index_num الوسيطة أكبر من عدد الأعمدة في صفيف الجدول.