مثال عملي على دالة VLOOKUP: دليلك الشامل لاستخدامها في Excel

يُعد برنامج Microsoft Excel أداة قوية ومتعددة الاستخدامات، فهو يضم مجموعة واسعة من الدوال التي تُسهّل على المستخدمين إجراء العمليات الحسابية وتحليل البيانات بكفاءة. وعلى الرغم من أن العديد من المستخدمين لا يستغلون سوى جزء بسيط من إمكانياته الهائلة، إلا أن بعض الدوال تُصبح ضرورية للغاية عند التعامل مع كميات كبيرة من المعلومات.

إذا كنت تجد نفسك تتصفح الجداول والصفوف باستمرار بحثًا عن نفس المعلومة مرارًا وتكرارًا، فمن المرجح أن دالة VLOOKUP ستُحدث ثورة في طريقة عملك. تُعرف VLOOKUP اختصارًا لـ “vertical lookup” أو “البحث العمودي”، وهي مصممة لمساعدتك في العثور بسرعة على البيانات المرتبطة بقيمة معينة تقوم بإدخالها. على سبيل المثال، قد يكون لديك جدول يحتوي على منتجات بمعرفات فريدة وأسعارها. باستخدام VLOOKUP، يمكنك معرفة سعر منتج معين بمجرد إدخال معرفه (ID).

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

ما هي دالة VLOOKUP؟

في المقام الأول، VLOOKUP هي دالة. لذلك، إذا كنت جديدًا في Excel، فقد ترغب في التعرف على بعض الدوال الأساسية مثل AVERAGE (المتوسط)، SUM (المجموع)، أو TODAY (التاريخ الحالي). بهذه الطريقة، سيكون من السهل عليك فهم كيفية عمل هذه الدالة.

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

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

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

كيف تعمل دالة VLOOKUP؟

فيما يلي الصيغة التي تصف دالة VLOOKUP:

=VLOOKUP(value, table, column_index, [range])

شرح مكونات الصيغة:

• value: هي القيمة التي ستبحث عنها الدالة في العمود الأول من الجدول.
• table: هو نطاق الجدول الذي ستسترد منه الدالة المعلومات الضرورية.
• column_index: هو رقم العمود الذي ستسترد منه الدالة المعلومات (يجب أن يكون رقمًا).
• range: هو معامل منطقي (Boolean parameter) يمكن أن يكون إما TRUE أو FALSE. القيمة الافتراضية هي TRUE، وتتوافق مع المطابقة التقريبية. أما FALSE فستعرض لك المطابقات التامة فقط.

حتى اسم هذه الدالة يتضمن كلمة “vertical” (عمودي)، ودالة VLOOKUP مخصصة فقط للجداول التي يتم فيها تنظيم البيانات في أعمدة رأسية. لذلك، إذا كنت تنظم بياناتك أفقيًا، فستكون هذه الدالة غير مجدية. في هذه الحالة، يمكنك استخدام دالة مماثلة للبحث الأفقي وهي HLOOKUP.

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

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

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

=VLOOKUP(value, table, column_index)

=VLOOKUP(value, table, column_index, TRUE)

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

=VLOOKUP(value, table, column_index, FALSE)

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

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

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

أخطاء #N/A في VLOOKUP

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

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

يمكنك تخصيص نص خطأ #N/A باستخدام دالة IFNA. في هذه الحالة، يجب عليك كتابة صيغة أطول مع IFNA تتضمن VLOOKUP. إليك مثال على صيغة ستُرجع “not found” (لم يتم العثور عليه) بدلاً من #N/A:

=IFNA(VLOOKUP(value, table, column_index, FALSE), "not found")

وهذه صيغة ستُرجع نتيجة فارغة:

=IFNA(VLOOKUP(value, table, column_index, FALSE), "")

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

كيفية استخدام VLOOKUP خطوة بخطوة

يمكنك كتابة الصيغ يدويًا من البداية، أو يمكنك أيضًا استخدام قائمة Excel. اتبع الخطوات التالية:

1. تحديد الخلية: حدد الخلية التي تريد عرض النتيجة فيها.
2. الوصول إلى الدوال: اذهب إلى علامة التبويب “Formulas” (صيغ).
3. إدراج دالة: انقر على “Insert Function” (إدراج دالة). ستظهر لك نافذة يمكنك من خلالها تحديد فئات الدوال واختيار دالة VLOOKUP. يمكنك أيضًا استخدام مربع “Search for a function” (البحث عن دالة)، وإدخال “vlookup”.
4. نافذة وسيطات الدالة: بعد تحديد الدالة، ستظهر نافذة “Function Arguments” (وسيطات الدالة). هنا يمكنك إدخال المعاملات الضرورية للدالة.

في هذه النافذة، يجب عليك تحديد المعرف الفريد الذي تبحث عنه (Lookup_value)، وموقع قاعدة البيانات (Table_array)، والمعلومات التي تتوافق مع المعرف والتي تريد استردادها (Col_index_num). هذه الحقول مكتوبة بخط عريض لأن هذه الوسيطات إلزامية. الوسيط الرابع مخصص لوضع البحث، وقد تحدده أو لا تحدده (الوضع التقريبي يتم تعيينه افتراضيًا).

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

• الوسيط الأول (Lookup_value): لإدخال الوسيط الأول، وهو المعرف الفريد، يمكنك تحديد الخلية المطلوبة والضغط على Enter. في هذه الحالة، سيتم إدخال قيمة هذه الخلية تلقائيًا كوسيط أول لدالة VLOOKUP.
• الوسيط الثاني (Table_array): الآن عليك إدخال الوسيط الثاني. لا يجب أن تبدأ قاعدة البيانات بالضرورة من الزاوية العلوية اليسرى. على سبيل المثال، قد يكون لديك صف يصف الأعمدة، والذي يعمل كرأس. كما تشير بريدجيت ألين، محاسبة في Best Writers Online: “أحد أفضل الأشياء في دالة VLOOKUP هو أن موقع قاعدة البيانات يمكن تخصيصه أيضًا.” نظرًا لأن VLOOKUP تعمل فقط مع أرقام الأعمدة، يجب عليك تحديد المنطقة التي تريد استخدامها للبحث في جدولك. هذا هو الغرض من مربع Table_array. على سبيل المثال، إذا كان جدولك يبدأ من الزاوية العلوية اليسرى، وكان صفه الأول رأسًا، فيمكنك تحديد قاعدة البيانات بأكملها باستثناء السطر الأول. إذا كانت قاعدة بياناتك تحتوي على أربعة أعمدة (A-D) وخمسة عناصر فيها، فسيكون نطاق الجدول A2:D6، لأن الخلايا A1-D1 ستحتوي على الرأس. يمكنك النقر على علامة تبويب ورقة العمل المطلوبة، وتحديد المنطقة التي تحتوي على قاعدة البيانات. اضغط على Enter وسيتم إضافة النطاق المحدد من الخلايا تلقائيًا إلى الوسيط الثاني لدالة VLOOKUP. إليك مثال على وسيط دالة:

  'database_name'!A2:D6

  في هذه الحالة، database_name هو اسم علامة تبويب ورقة العمل.

• الوسيط الثالث (Col_index_num): الآن ما عليك سوى تحديد المعلومات التي تريد استردادها وتقديم رقم العمود المطلوب. على سبيل المثال، إذا كنت تريد استرداد سعر عنصر من الجدول في بداية هذه المقالة، فيجب عليك استخدام الصف الثالث، وستكون قيمة Col_index_num هي 3.

الخلاصة التقنية

تُعد دالة VLOOKUP في Microsoft Excel أداة أساسية لا غنى عنها لكل من يتعامل مع تحليل البيانات وقواعد البيانات في بيئة العمل أو الدراسة. على الرغم من أن إعدادها قد يبدو معقدًا للمبتدئين بسبب وسيطاتها الأربعة، إلا أن فهم آليتها والتدرب عليها يفتح آفاقًا واسعة لتوفير الوقت والجهد. إن القدرة على استرداد معلومات محددة بسرعة من جداول ضخمة بناءً على معرف فريد تُعزز بشكل كبير كفاءة العمليات. من الضروري دائمًا الانتباه إلى نوع المطابقة (تامة أو تقريبية) وترتيب البيانات في العمود الأول لضمان الحصول على النتائج الصحيحة وتجنب الأخطاء الشائعة مثل #N/A. بمجرد إتقانها، ستصبح VLOOKUP جزءًا لا يتجزأ من مجموعة أدواتك في Excel.