دالة VLOOKUP في Excel: شرح مفصل وأمثلة تطبيقية

دالة VLOOKUP في Excel: شرح مفصل وأمثلة تطبيقية

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

بعبارات بسيطة، تقوم دالة VLOOKUP بإخبار Excel بما يلي: “ابحث عن هذه المعلومة (مثل الموز) في مجموعة البيانات هذه (جدول) وأخبرني بأي معلومات مرتبطة بها (مثل سعر الموز) بعبارات واضحة”.

صيغة دالة VLOOKUP

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

=VLOOKUP (lookup value, table array, col index num, [range lookup])

وبمعنى آخر، فإن هذه الصيغة تعني “ابحث عن هذه المعلومة في الحقل التالي وأرسل لي بعض التفاصيل المطابقة من عمود آخر.” يتم تمرير الوسائط التالية إلى دالة VLOOKUP:

• Lookup value (المعلمة المطلوبة): تحدد قيمة البحث القيمة التي سيتم البحث عنها في العمود الأول من الجدول.
• The table array (المعلمة المطلوبة): يمثل مصفوفة البيانات التي سيتم مسحها ضوئياً. يبحث وظيفة VLOOKUP في العمود الموجود في أقصى يسار المصفوفة.
• Col index num (المعلمة المطلوبة): هو رقم صحيح يحدد رقم العمود الذي يجب استرجاع قيمة منه من مصفوفة الجدول المحددة.
• range lookup (وسيطة اختيارية): يحدد هذا الخيار ما يمكن أن تقدمه هذه الميزة إذا تعذر عليها العثور على تطابق دقيق لقيمة البحث. يمكن أن تكون قيمة العبارة TRUE أو FALSE، مما يعني:
  • TRUE – المطابقة التقديرية تعني أنه إذا لم يتم العثور على تطابق دقيق، فسيتم استخدام أقرب تطابق أسفل قيمة البحث.
  • FALSE – المطابقة الدقيقة؛ سيتم إصدار خطأ إذا لم يتم العثور على تطابق دقيق.

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

1. فرز المعلومات

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

على سبيل المثال،

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

2. تحديد ما تبحث عنه، وإخبار الدالة بذلك

خلال هذه العملية، تخبر Excel بما يجب البحث عنه. للبدء، اكتب =VLOOKUP (" في الخلية التي تحتوي على المعلومات التي تريد البحث عنها. إنها الخلية التي تقول “Bananas” في هذه الحالة.

3. تحديد مكان البحث، وإخبار الدالة بذلك

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

4. تحديد العمود الذي سيتم إخراج البيانات منه

يجب عليك تحديد العمود الذي يحتوي في Excel على البيانات التي ترغب في إخراجها كنتيجة لـ VLOOKUP. سيتطلب Excel رقمًا يتوافق مع رقم عمود الجدول. في الموقف الحالي، يمكنك استخدام الرقم “3” في الحساب لأن بيانات الإخراج موجودة في العمود الثالث من الجدول.

5. البحث عن مطابقة تقريبية أم دقيقة

يمكنك إخبار Excel ما إذا كنت تبحث عن تطابق دقيق أم تقريبي عن طريق إدخال “False” أو “True” في الحساب. في هذا المثال لصيغة دالة VLOOKUP، فأنت تريد نفس أو التطابق الدقيق (“Bananas”)؛ وبالتالي، تكتب “FALSE” في الصيغة. باستخدام “TRUE” كعامل بدلاً من ذلك، يمكنك تحقيق مطابقة قريبة.

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

دالة VLOOKUP في النمذجة المالية والتحليل المالي

تستخدم دالة VLOOKUP على نطاق واسع في النمذجة والتحليل المالي لربط البيانات من جداول مختلفة وتسهيل العمليات الحسابية المعقدة.

في هذا القسم، سيتم تقديم أمثلة لكيفية استخدام VLOOKUP في مجالات مثل:

• استخلاص أسعار الأسهم
• إنشاء جداول تحليل التدفق النقدي
• تحليل المبيعات

كل هذا يعتبر من صميم عمل وظيفة VLOOKUP.

نصائح حول دالة VLOOKUP

فيما يلي بعض النقاط المهمة التي يجب تذكرها حول دالة VLOOKUP في Excel:

• إذا تم حذف “range lookup”، فستستخدم ميزة VLOOKUP تطابقًا غير دقيق، ولكن سيتم استخدام تطابق دقيق إذا كان موجودًا.
• أخطر عيب في الدالة هو أنها لا تزال صحيحة. يمكن استرداد البيانات من الأعمدة الموجودة على يمين العقدة الأولى للجدول.
• يمكن لـ VLOOKUP أن تتناسب فقط مع القيمة الأولى إذا كان عمود البحث يحتوي على قيم مكررة.
• لا علاقة للموقف بالوظيفة.

• لنفترض أنه توجد بالفعل صيغة VLOOKUP في جدول بيانات. في مثل هذه الحالة، سيؤدي إضافة عمود إلى الجدول إلى فصل الصيغ. هذا لأن قيم فهرس العمود المشفرة بشدة لا تتغير عند إضافة أو حذف الأعمدة.
• تتيح لك VLOOKUP استخدام أحرف البدل مثل علامة النجمة (*) أو علامة الاستفهام (؟).
• لنفترض أنك تعمل على قاعدة بيانات بأرقام مضافة كنص في الدالة. لا يهم ما إذا كنت تأخذ فقط أرقامًا من لوحة جدول كنص. ستتلقى #N/A إذا كان العمود الأول من الجدول يحتوي على أرقام تم إرسالها كنص. إذا لم تكن قيمة البحث من النوع النصي، فسيحدث خطأ.
• #NONE. يتم إعطاء خطأ إذا تعذر على دالة VLOOKUP العثور على تطابق لقيمة البحث المحددة.
• عندما يتم تلبية واحدة أو أكثر من الحالات التالية، يحدث خطأ #REF:
  • تحتوي مصفوفة الجدول المحددة على أعمدة أكثر من وسيطة رقم فهرس العمود؛ أو
  • حاولت الوصفة مناشدة الخلايا غير الوظيفية.
• #WORTH. الخطأ – عندما يتم تلبية واحدة أو أكثر من الحالات التالية، يحدث الخطأ:
  • عبارة range lookup غير مدرجة كقيمة منطقية TRUE أو FALSE، أو أن عبارة رقم فهرس العمود أقل من واحد أو ليست قيمة رقمية.

VLOOKUP وأخطاء #N/A

ستتلقى تحذير #N/A إذا استخدمت VLOOKUP. رمز خطأ #N/A يعني ببساطة “غير قابل للتحديد”. إذا لم تظهر تكلفة البحث أو قيمة “Toy Story 2” في جدول البحث على الشاشة أدناه، فإن كل خوارزميات VLOOKUP الثلاثة تُرجع #N/A:

يمكن استخدام دالة IFNA “لالتقاط” خطأ N/A، كما هو موضح أدناه:

يعرض H6 الصيغة = IFNA، وهي (VLOOKUP (H4، B5؛ E9، 2، FALSE)، “Not found”)

سوف يعلمك هذا الخطأ بما يلي:

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

4 MS Excel: دالة HLOOKUP

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

الصيغة

=HLOOKUP (table area, value for looking up, row number)

5 كيفية استخدام دالة HLOOKUP في Excel؟

ضع في اعتبارك الحالة أدناه. درجات أربعة موضوعات لأربعة طلاب هي كما يلي:

بعد الحصول على علامات الطالب D في الإدارة، يمكنك استخدام دالة HLOOKUP كما هو موضح أدناه.

في Excel، تأخذ دالة HLOOKUP الوسائط التالية:

GOOGLE HLOOKUP (lookup value, table array, row index num, [range lookup])

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

كخطوة تالية، سيتم تسليم مصفوفة الجدول. سيتم التحقق من قيمة البحث في قائمة الجدول، والتي تتكون من صفوف من السجلات. يمكن إنشاء مصفوفة جدول باستخدام نطاق عادي أو مجموعة مسماة أو حتى جدول Excel. سوف تستخدم الصفوف من A1 إلى F5 كمرجع.

بعد ذلك، سوف تنتقل عبر “row index num”، وهو رقم خلية مصفوفة الجدول التي يتم من خلالها الحصول على العنصر. لأنك تحصل على القيمة من الصف الرابع من الجدول المقدم، فسيكون أربعة.

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

ستكون النتيجة اثنان وسبعون.

6 نصائح لوظيفة HLOOKUP

• البحث لا يهتم بالحالة. على سبيل المثال، سيعامل المصطلحات “TIM” و”Tim” بالتبادل.
• يجب أن يكون “قيمة البحث” هي أعلى صفوف “مصفوفة الجدول” عند استخدام HLOOKUP. إذا كنت بحاجة إلى البحث في مكان آخر، فسيتعين عليك استخدام صيغة Excel أخرى.
• يقبل HLOOKUP أحرف البدل ‘*’ و’؟’ في معلمة ‘قيمة البحث’ (فقط إذا كانت ‘قيمة البحث’ هي نص).
• ألق نظرة على مثال لفهم هذا بشكل أفضل. لنفترض أن أسماء ودرجات الطلاب التالية مقدمة إلينا:

الصيغة للحصول على علامات الطالب في الرياضيات التي يبدأ اسمها بالحرف “D” باستخدام تقنية البحث الأفقية هي:

يتم استخدام الحرف ‘*’ كحرف بدل.

• خطأ #N/A – ستقوم HLOOKUP بإرجاع “قيمة البحث” إذا كان “البحث عن النطاق” هو FALSE ولا يمكن لدالة HLOOKUP تحديدها في الوجهة المقدمة. يمكنك استخدام دالة مثل =IFERROR لعرض رسالتك عن طريق استخدام ميزة (HLOOKUP (A4, A1:I2, 2, FALSE)، “لم يتم العثور على معنى”) في IFERROR.
• إذا كان ‘رقم فهرس الصف’ أقل من واحد، فسوف تقوم HLOOKUP بإنشاء خطأ #VALUE. إذا تجاوز عدد الأعمدة في مصفوفة الجدول عدد الصفوف في رقم فهرس الصف، يتم استخدام #REF. سيكون هناك خطأ.
• تذكر أن أداة HLOOKUP في Excel تُرجع قيمة واحدة فقط. القيمة الأولى التي تتوافق مع قيمة البحث هي هذه. ماذا لو كان لدى الجدول عدد قليل من السجلات المتماثلة؟ من الأفضل إما إزالتها أو تجميعها في جدول Pivot في أي حال. بعد ذلك، يمكنك استخدام صيغة الصفيف في جدول Pivot للحصول على قائمة بجميع القيم المكررة المحددة في مجموعة البحث.