01 من 03
البحث عن التطابق التقريبي للبيانات باستخدام VLOOKUP في Excel
كيف تعمل وظيفة VLOOKUP
يمكن استخدام وظيفة VLOOKUP الخاصة بـ Excel ، والتي تعني البحث العمودي ، للبحث عن معلومات محددة موجودة في جدول البيانات أو قاعدة البيانات.
تعيد VLOOKUP عادةً حقل واحد من البيانات كمخرج لها. كيف يفعل هذا هو:
- يمكنك تقديم اسم أو lookup_value يخبر VLOOKUP في أي صف أو سجل لجدول البيانات للبحث عن البيانات المطلوبة
- يمكنك تقديم رقم العمود - المعروف باسم col_index_num - للبيانات التي تطلبها
- تبحث الدالة عن lookup_value في العمود الأول من جدول البيانات
- ثم يحدد موقع VLOOKUP ويعيد المعلومات التي تطلبها من حقل آخر بنفس السجل باستخدام رقم العمود المرفق
فرز البيانات أولا
على الرغم من أنه ليس مطلوبًا دائمًا ، فمن الأفضل عادةً فرز نطاق البيانات التي تبحث عنها VLOOKUP بترتيب تصاعدي باستخدام العمود الأول لنطاق مفتاح الفرز.
إذا لم يتم فرز البيانات ، فقد تقوم VLOOKUP بإرجاع نتيجة غير صحيحة.
بنية وعلل الدالة VLOOKUP
يشير بناء جملة الدالة إلى تخطيط الدالة ويتضمن اسم الدالة والأقواس والحجج .
صيغة بناء الدالة VLOOKUP هي:
= VLOOKUP (lookup_value ، table_array ، col_index_num ، range_lookup)
lookup _value - (مطلوب) القيمة المطلوب البحث عنها - مثل الكمية المباعة في الصورة أعلاه
table_array - (مطلوب) هذا هو جدول البيانات الذي تبحث عنه VLOOKUP للعثور على المعلومات التي تتابعها.
- يجب أن يحتوي table_array على عمودين على الأقل من البيانات
- يحتوي العمود الأول عادةً على lookup_value
col_index_num - (مطلوب) رقم العمود للقيمة التي تريد العثور عليها.
- يبدأ الترقيم بعمود search_key كعمود 1
- إذا تم تعيين col_index_num على رقم أكبر من عدد الأعمدة المحددة في الوسيطة table_array #REF! يتم إرجاع الخطأ من قبل الوظيفة
يشير range_lookup - (اختياري) إلى ما إذا كان النطاق يتم فرزه بترتيب تصاعدي أم لا.
- يتم استخدام البيانات الموجودة في العمود الأول كمفتاح الفرز
- القيمة المنطقية - TRUE أو FALSE هي القيم الوحيدة المقبولة
- إذا تم حذفها ، فسيتم تعيين القيمة على TRUE افتراضيًا
- إذا تم تعيينها إلى TRUE أو تم حذفها ولم يتم فرز العمود الأول من النطاق بترتيب تصاعدي ، فقد تحدث نتيجة غير صحيحة
- إذا تم تعيينها على TRUE أو تم حذفها ولم يتم العثور على تطابق تام للبحث _value ، فسيتم استخدام أقرب تطابق أصغر في الحجم أو القيمة مثل search_key
- في حالة التعيين على FALSE ، تقبل VLOOKUP فقط المطابقة التامة لقيمة البحث _value . إذا كانت هناك قيم مطابقة متعددة ، فسيتم إرجاع أول قيمة مطابقة
- في حالة التعيين على FALSE وعدم العثور على قيمة مطابقة لـ search_key ، يتم إرجاع الخطأ # N / A بواسطة الدالة
مثال: ابحث عن سعر الخصم للكمية المشتراة
يستخدم المثال الموجود في الصورة أعلاه الدالة VLOOKUP للعثور على معدل الخصم الذي يختلف باختلاف كمية العناصر المشتراة.
يوضح المثال أن الخصم الخاص بشراء 19 عنصرًا هو 2٪. وذلك لأن عمود الكمية يحتوي على نطاقات من القيم. نتيجة لذلك ، لا يمكن لـ VLOOKUP العثور على تطابق تام. بدلاً من ذلك ، يجب العثور على تطابق تقريبي لإرجاع معدل الخصم الصحيح.
للعثور على مطابقات تقريبية:
- فرز البيانات في table_array بترتيب تصاعدي؛
- قم بتعيين الوسيطة range_lookup إلى TRUE
في المثال ، يتم استخدام الصيغة التالية التي تحتوي على الدالة VLOOKUP لإيجاد الخصم لكميات البضائع المشتراة.
= VLOOKUP (C2، $ C $ 5: $ D $ 8،2، TRUE)
على الرغم من أنه يمكن فقط كتابة هذه الصيغة في خلية ورقة عمل ، إلا أن هناك خيار آخر ، كما هو مستخدم مع الخطوات المذكورة أدناه ، هو استخدام مربع الحوار الخاص بالوظيفة لإدخال الوسيطات الخاصة به.
- يؤدي استخدام مربع الحوار إلى تسهيل إدخال وسائط الدالة بشكل صحيح.
فتح مربع حوار VLOOKUP
الخطوات المستخدمة لإدخال الدالة VLOOKUP المبينة في الصورة أعلاه في الخلية B2 هي:
- انقر فوق الخلية B2 لجعلها الخلية النشطة - الموقع حيث يتم عرض نتائج الدالة VLOOKUP
- انقر فوق علامة التبويب صيغ .
- اختر Lookup & Reference من الشريط لفتح القائمة المنسدلة الدالة
- انقر فوق VLOOKUP في القائمة لإظهار مربع الحوار الخاص بالوظيفة
02 من 03
الدخول إلى وسيطات الدالة VLOOKUP الخاصة بـ Excel
مشيرا إلى خلية المراجع
يتم إدخال الوسائط الخاصة بوظيفة VLOOKUP في سطور منفصلة بمربع الحوار كما هو موضح في الصورة أعلاه.
يمكن استخدام المراجع الخلوية لاستخدامها كإمكانية كتابة الوسيطات في الخط الصحيح ، أو ، كما هو الحال في الخطوات أدناه ، يمكن استخدام الإشارة ، والتي تتضمن تحديد النطاق المطلوب من الخلايا باستخدام مؤشر الماوس ، لإدخالها في مربع الحوار. .
مزايا استخدام الإشارة تتضمن:
- إنه أسرع من الكتابة
- يتم إجراء أخطاء أقل في إدخال مراجع الخلية الصحيحة.
استخدام مراجع الخلايا النسبية والمطلقة مع الوسيطات
ليس من غير المألوف استخدام نسخ متعددة من VLOOKUP لإرجاع معلومات مختلفة من نفس جدول البيانات. لتسهيل القيام بذلك ، يمكن في كثير من الأحيان نسخ VLOOKUP من خلية إلى أخرى. عندما يتم نسخ الدالات إلى خلايا أخرى ، يجب توخي الحذر للتأكد من صحة مراجع الخلية الناتجة بسبب الموقع الجديد للوظيفة.
في الصورة أعلاه ، تحيط علامات الدولار ( $ ) بمراجع الخلية لوسيطة table_array التي تشير إلى أنها مراجع خلية مطلقة ، مما يعني أنها لن تتغير إذا تم نسخ الدالة إلى خلية أخرى. هذا أمر مرغوب لأن النسخ المتعددة من VLOOKUP قد تشير جميعها إلى نفس جدول البيانات كمصدر للمعلومات.
من ناحية أخرى ، لا تحاط إشارة الخلية المستخدمة في lookup_value بعلامات الدولار ، مما يجعلها مرجعية خلية نسبية. تتغير مراجع الخلايا النسبية عندما يتم نسخها لتعكس موقعها الجديد نسبة إلى موضع البيانات التي تشير إليها.
إدخال وسيطات الدالة
- انقر فوق السطر _value Lookup في مربع الحوار VLOOKUP
- انقر فوق الخلية C2 في ورقة العمل لإدخال مرجع الخلية هذا كوسيطة search_key
- انقر فوق سطر Table_array لمربع الحوار
- قم بتمييز الخلايا من C5 إلى D8 في ورقة العمل لإدخال هذا النطاق كوسيطة Table_array - لا يتم تضمين عناوين الجدول
- اضغط المفتاح F4 على لوحة المفاتيح لتغيير النطاق إلى مراجع الخلية المطلقة
- انقر فوق سطر Col_index_num لمربع الحوار
- اكتب a 2 على هذا السطر كوسيطة Col_index_num ، حيث توجد معدلات الخصم في العمود 2 من الوسيطة Table_array
- انقر فوق سطر Range_lookup لمربع الحوار
- اكتب الكلمة True كوسيطة Range_lookup
- اضغط على مفتاح Enter على لوحة المفاتيح لإغلاق مربع الحوار والعودة إلى ورقة العمل
- يجب أن تظهر الإجابة 2٪ (معدل الخصم للكمية المشتراة) في الخلية D2 من ورقة العمل
- عند النقر فوق الخلية D2 ، تظهر الدالة الكاملة = VLOOKUP (C2 ، $ C $ 5: $ D $ 8،2، TRUE) في شريط الصيغة أعلى ورقة العمل
لماذا تم إرجاع VLOOKUP بنسبة 2٪ كنتيجة
- في المثال ، لا يحتوي عمود الكمية على تطابق تام لقيمة search_key 19.
- نظرًا لتعيين الوسيطة is_sorted على TRUE ، سيجد VLOOKUP تطابقًا تقريبيًا لقيمة search_key .
- القيمة الأقرب في الحجم والتي لا تزال أصغر من قيمة البحث 19 هي 19.
- لذلك ، تبحث VLOOKUP عن النسبة المئوية للخصم في الصف الذي يحتوي على 11 ، ونتيجة لذلك ، تُرجع نسبة خصم قدرها 2٪.
03 من 03
Excel VLOOKUP لا يعمل: # N / A و #REF أخطاء
رسائل خطأ VLOOKUP
ترتبط رسائل الخطأ التالية بـ VLOOKUP.
A # N / A ("القيمة غير متاحة") يتم عرض الخطأ في حالة:
- لم يتم العثور على قيمة _value في العمود الأول لوسيطة النطاق
- الوسيطة Table_array غير دقيقة. على سبيل المثال ، قد تتضمن الوسيطة أعمدة فارغة على الجانب الأيسر من النطاق
- تم تعيين الوسيطة Range_lookup على FALSE ولا يمكن العثور على تطابق تام لوسيطة search_key في العمود الأول من النطاق
- يتم تعيين الوسيطة Range_lookup على TRUE وتكون جميع القيم في العمود الأول للنطاق أكبر من قيمة البحث
ARERE! ("المرجع خارج النطاق") يتم عرض الخطأ في حالة:
- تعتبر الوسيطة Col_index_num أكبر من عدد الأعمدة في Table_array