كيف يبحث VLOOKUP في جدول

يرمز VLOOKUP إلى البحث العمودي. فهو يبحث نزولًا في العمود الأول من جدول عن قيمة تحددها، ثم يُرجع قيمة من الصف نفسه في عمود آخر.

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

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

تتلقى VLOOKUP أربع معلومات تفصل بينها فواصل:

• lookup_value - القيمة التي تبحث عنها
• table_array - نطاق الخلايا الذي يحتوي على بياناتك
• col_index_num - رقم العمود الذي ستُرجع القيمة منه
• [range_lookup] - TRUE للتطابق التقريبي، وFALSE للتطابق التام

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

تخيّل جدولًا صغيرًا للمنتجات في الخلايا A1:C4:

• عناوين الصف 1: الرمز، الاسم، السعر
• الصف 2: A100، التفاح، 0.50
• الصف 3: B200، الموز، 0.30
• الصف 4: C300، الكرز، 1.20

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

للعثور على سعر الرمز B200، نبحث عن B200 في العمود 1، ثم نُرجع القيمة من العمود 3 (السعر):

بقراءة الصيغة بصوت عالٍ: ابحث عن القيمة "B200" في الجدول A1:C4، وعند العثور عليها، أرجع القيمة من العمود الثالث باستخدام تطابق تام (FALSE).

والنتيجة هي 0.30. عثر VLOOKUP على B200 في الصف 3، ثم قرأ القيمة من العمود الثالث.

يُحسب col_index_num من الحافة اليسرى لـ table_array، وليس من العمود A في ورقة العمل.

في النطاق A1:C4، تكون أرقام الأعمدة كما يلي:

• العمود 1 = الرمز (عمود البحث)
• العمود 2 = الاسم
• العمود 3 = السعر

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

إن كتابة "B200" مباشرةً داخل الصيغة أمر نادر. فعادةً تكون القيمة التي تريد البحث عنها موجودةً في خلية أخرى. لنفترض أن شخصًا يكتب رمزًا في E2. وجّه VLOOKUP إلى تلك الخلية بدلًا من استخدام نص ثابت.

عندها، كلما تغيّرت قيمة E2، تُحدَّث النتيجة تلقائيًا. هكذا تُستخدم عمليات البحث في الفواتير ولوحات المعلومات ومربعات البحث.

لدى VLOOKUP قاعدة صارمة واحدة: يمكنه البحث في العمود الموجود أقصى اليسار فقط من table_array. ولا يمكنه البحث في العمود 2 ثم الرجوع إلى العمود 1.

لذلك يجب أن يكون العمود الذي تريد البحث فيه هو العمود الأول في النطاق. إذا كانت رموزك في العمود B، فابدأ table_array من العمود B، مثل B1:D4.

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

يمكنك تضمين صف العناوين في table_array أو استبعاده. كلا الخيارين يعمل:

• A1:C4 يتضمن العناوين (الرمز، الاسم، السعر)
• A2:C4 يستبعد العناوين

مع المطابقة التامة (FALSE)، لا تسبب العناوين نتائج خاطئة لأنها لن تطابق رمز منتج. يضمّنها كثيرون لأن ذلك يجعل قراءة النطاق أسهل. تذكّر فقط أن فهرس العمود يُحتسب من اليسار ضمن النطاق الذي اخترته، أيًا كان.

لنفترض أنك تنشئ فاتورة. يوجد رمز المنتج في A10، وتريد جلب اسمه وسعره من الجدول.

الاسم في B10:

السعر في C10:

يمكن لجدول واحد تزويد عدة خلايا بالبيانات. اكتب الرمز مرة واحدة، وستُملأ بقية البيانات، وهذه هي الفائدة العملية اليومية من VLOOKUP.

هل لاحظت علامات $ في $A$1:$C$4؟ عند نسخ VLOOKUP إلى أسفل عمود، تريد أن تتغير قيمة البحث (A10 وA11 وA12...)، لكن تريد أن يبقى الجدول ثابتًا.

تثبّت المراجع المطلقة، باستخدام علامات الدولار، الجدول في مكانه. من دونها، سيؤدي النسخ إلى تحريك الجدول بعيدًا عن بياناتك، مما ينتج عنه أخطاء. ثبّت table_array واترك lookup_value مرجعًا نسبيًا.

غالبًا ما يوجد جدول البيانات في علامة تبويب مختلفة. للإشارة إلى نطاق في ورقة باسم Products، ضع اسم الورقة وعلامة التعجب قبل النطاق.

إذا كان اسم الورقة يحتوي على مسافات، فأحِطه بعلامتي اقتباس مفردتين، مثل 'Price List'!A:C. تعمل عملية البحث بالطريقة نفسها تمامًا، لكنها تقرأ البيانات من علامة تبويب أخرى.

أصبحت تعرف الآن أساسيات VLOOKUP:

• يبحث نزولًا في العمود الأول من table_array
• يقبل أربع وسيطات: lookup_value وtable_array وcol_index_num وrange_lookup
• يُحتسب col_index_num من الحافة اليسرى للنطاق
• استخدم FALSE للمطابقة التامة في معظم الحالات
• ثبّت الجدول باستخدام $ حتى يبقى في مكانه عند النسخ إلى أسفل

ستتعمق في الدرس التالي في الفرق بين المطابقة التامة والمطابقة التقريبية.