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

تبحث دالة VLOOKUP في Excel عن قيمة في نطاق من الخلايا ، ثم تعرض قيمة موجودة في نفس الصف مثل القيمة التي تبحث عنها.

VLOOKUP التي تعني "البحث العمودي" ، هي وظيفة بحث تبحث عن قيمة في العمود الموجود في أقصى اليسار (العمود الأول) من النطاق وتعيد القيمة المتوازية من العمود إلى اليمين. تبحث الدالة VLOOKUP فقط (من أعلى إلى أسفل) عن قيمة أعلى في جدول مرتب عموديًا.

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

قد تبدو وظيفة VLOOKUP شاقة في البداية ، لكنها في الواقع سهلة الاستخدام بمجرد فهم كيفية عملها. هنا ، في هذا البرنامج التعليمي ، سنوضح لك كيفية استخدام وظيفة VLOOKUP في Excel.

تركيب وحجج VLOOKUP

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

بناء جملة وظيفة VLOOKUP:

= VLOOKUP (lookup_value، table_array، col_index_num، [range_lookup])

تتكون هذه الوظيفة من 4 معلمات أو وسيطات:

• ابحث عن القيمة: يحدد هذا القيمة التي تبحث عنها في العمود الأول من صفيف الجدول المحدد. يجب أن تكون قيمة البحث دائمًا في أقصى اليسار (عمود جدول البحث.
• table_array: هذا هو الجدول (نطاق الخلايا) الذي تريد البحث عن قيمة فيه. يمكن أن يكون هذا الجدول (جدول البحث) في نفس ورقة العمل أو ورقة عمل مختلفة ، أو حتى في مصنف مختلف.
• col_index_num: يحدد هذا رقم العمود الخاص بمصفوفة الجدول الذي يحتوي على القيمة التي ترغب في استخراجها.
• [مجموعة البحث]: تحدد هذه المعلمة ما إذا كنت تريد استخراج تطابق تام أو تطابق تقريبي. إنها إما صحيحة أو خاطئة ، أدخل "خطأ" إذا كنت تريد القيمة الدقيقة أو أدخل "TRUE" إذا كنت موافقًا على القيمة التقريبية.

استخدام وظيفة VLOOKUP في برنامج Excel

دعنا نستكشف كيفية استخدام VLOOKUP في Microsoft Excel.

مثال أساسي

لاستخدام VLOOKUP ، تحتاج أولاً إلى إنشاء قاعدة بيانات أو جدول (انظر أدناه).

ثم قم بإنشاء جدول أو نطاق من حيث تريد البحث واستخراج القيم من جدول البحث.

بعد ذلك ، حدد الخلية حيث تريد القيمة المستخرجة وأدخل صيغة VLOOKUP التالية. على سبيل المثال ، نريد البحث عن رقم هاتف "Ena" ، ثم يتعين علينا إدخال قيمة البحث مثل B13 و A2: E10 كمصفوفة الجدول ، و 5 لرقم العمود الخاص برقم الهاتف ، و FALSE لإرجاع القيمة الدقيقة القيمة. ثم اضغط على "إدخال" لإنهاء الصيغة.

= VLOOKUP (B13، A2: E10،5، FALSE)

لا تحتاج إلى كتابة نطاق الجدول يدويًا ، يمكنك فقط تحديد النطاق أو الجدول باستخدام الماوس للوسيطة table_array. وستتم إضافته إلى الوسيطة تلقائيًا.

تذكر ، لكي يعمل هذا ، يجب أن تكون قيمة البحث في أقصى يسار جدول البحث (A2: E10). أيضًا ، ليس بالضرورة أن يكون Lookup_value في العمود A من ورقة العمل ، بل يجب أن يكون العمود الموجود في أقصى اليسار من النطاق الذي تريد البحث فيه.

VLOOKUP تبدو صحيحة

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

مطابقة تامة

تحتوي وظيفة Excel VLOOKUP على طريقتين للمطابقة ، هما: دقيق وتقريبي. تحدد المعلمة "range_lookup" في وظيفة VLOOKUP نوع ما تبحث عنه ، دقيق أو تقريبي.

إذا أدخلت range_lookup كـ "FALSE" أو "0" ، فستبحث الصيغة عن قيمة تساوي تمامًا lookup_value (يمكن أن تكون رقمًا أو نصًا أو تاريخًا).

= VLOOKUP (A9، A2: D5،3، FALSE)

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

يمكنك إدخال إما الرقم "0" أو "FALSE" في الوسيطة النهائية. كلاهما يعني نفس الشيء في Excel.

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

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

= VLOOKUP (B10، A2: B7،2، TRUE)

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

إذا عثر VLOOKUP على تطابق تام ، فسيعيد هذه القيمة. في المثال أعلاه ، إذا لم تتمكن الصيغة من العثور على قيمة look_up 89 في العمود الأول ، فستُرجع القيمة التالية الأكبر (80).

المباراة الأولى

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

على سبيل المثال ، تم تكوين VLOOKUP للعثور على الاسم الأخير للاسم الأول "Mia". نظرًا لوجود مدخلين بالاسم الأول "Mia" ، فإن الوظيفة ترجع الاسم الأخير للمدخل الأول ، "Bena".

مباراة البدل

تتيح لك وظيفة VLOOKUP العثور على تطابق جزئي على قيمة محددة باستخدام أحرف البدل. إذا كنت تريد تحديد موقع قيمة تحتوي على قيمة البحث في أي موضع ، فقم بإضافة علامة العطف (&) للانضمام إلى قيمة البحث الخاصة بنا باستخدام حرف البدل (*). استخدم علامات "$" لإنشاء مراجع خلية مطلقة وإضافة علامة البدل "*" قبل قيمة البحث أو بعدها.

في المثال ، لدينا فقط جزء من قيمة البحث (Vin) في الخلية B13. لذلك ، من أجل إجراء مطابقة جزئية على الأحرف المحددة ، قم بتوصيل حرف بدل "*" بعد مرجع الخلية.

= VLOOKUP ($ B $ 13 & "*"، $ A $ 2: $ E $ 10،3، FALSE)

عمليات بحث متعددة

تتيح لك وظيفة VLOOKUP إنشاء بحث ديناميكي ثنائي الاتجاه ، ومطابقة في كل من الصفوف والأعمدة. في المثال التالي ، تم إعداد VLOOKUP لإجراء بحث بناءً على الاسم الأول (Mayra) والمدينة. الصيغة في B14 هي:

= VLOOKUP (B13، A2: E10، MATCH (A14، A1: E1،0)، 0)

كيفية VLOOKUP من ورقة أخرى في Excel

عادةً ما تُستخدم الدالة VLOOKUP لإرجاع القيم المطابقة من ورقة عمل منفصلة ونادرًا ما يتم استخدامها مع البيانات الموجودة في نفس ورقة العمل.

إلى Vlookup من ورقة Excel أخرى ولكن في نفس المصنف ، أدخل اسم الورقة قبل table_array بعلامة تعجب (!).

على سبيل المثال ، للبحث عن قيمة الخلية A2 في ورقة العمل "المنتجات" في النطاق A2: B8 في ورقة العمل "ItemPrices" وإرجاع القيمة المقابلة من العمود B:

= VLOOKUP (A2، ItemPrices! $ A $ 2: $ C 8،2، FALSE)

توضح الصورة أدناه جدولاً في ورقة عمل "أسعار السلع".

عندما ندخل معادلة VLOOKUP في العمود C من ورقة عمل "المنتجات" ، فإنها تسحب البيانات المتطابقة من ورقة العمل "ItemPrices".

كيفية VLOOKUP من مصنف آخر في Excel

يمكنك أيضًا البحث عن القيمة في مصنف مختلف تمامًا. إذا كنت تريد VLOOKUP من مصنف آخر ، فأنت بحاجة إلى وضع اسم المصنف بين قوسين مربعين متبوعين باسم الورقة قبل table_array بعلامة تعجب (!) (كما هو موضح أدناه).

على سبيل المثال ، استخدم هذه الصيغة للبحث عن قيمة الخلية A2 لورقة عمل مختلفة من ورقة العمل المسماة "ItemPrices" في المصنف "Item.xlsx":

= VLOOKUP (A2، [Item.xls] أسعار السلعة! $ A $ 2: $ B $ 8،2، FALSE)

أولاً ، افتح كلا المصنفين ، ثم ابدأ في إدخال الصيغة في الخلية C2 من ورقة العمل (ورقة عمل المنتج) ، وعندما تصل إلى وسيطة table_array ، انتقل إلى مصنف البيانات الرئيسي (Item.xlsx) وحدد نطاق الجدول. بهذه الطريقة لن تضطر إلى كتابة اسم المصنف وورقة العمل يدويًا. اكتب بقية الوسائط واضغط على مفتاح "Enter" لإنهاء الوظيفة.

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

استخدم وظيفة VLOOKUP من شريط Excel

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

ثم أدخل الوسيطات في مربع الحوار "وسيطات الوظيفة". ثم انقر فوق الزر "موافق".

في المثال ، بحثنا عن الاسم الأول "Sherill" في الجدول لإرجاع حالته المقابلة في العمود D.

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