دالة VLOOKUP و HLOOKUP في Microsoft Excel: دليل شامل لاستخدامهما في العثور على البيانات المحددة

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

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

أولًا: مفهوم دالة VLOOKUP

دالة VLOOKUP (اختصار Vertical Lookup) تعني البحث العمودي، وهي تستخدم للبحث عن قيمة معينة في العمود الأول من جدول بيانات ثم إرجاع قيمة من نفس الصف في عمود آخر تحدده.

الصيغة الأساسية لدالة VLOOKUP:

scss
CopyEdit
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: القيمة التي تريد البحث عنها.

• table_array: نطاق الجدول الذي يحتوي على البيانات.

• col_index_num: رقم العمود في نطاق الجدول الذي تريد إرجاع القيمة منه.

• range_lookup: خيار اختياري لتحديد نوع البحث، TRUE أو FALSE.

شرح الوسائط:

• lookup_value: القيمة التي تريد البحث عنها قد تكون نصًا أو رقمًا أو مرجعًا لخلية.

• table_array: الجدول الذي يحتوي على البيانات، يجب أن يكون العمود الأول فيه يحتوي على القيم التي تبحث عنها.

• col_index_num: عمود الجدول الذي تريد جلب البيانات منه، يبدأ التعداد من 1 للعمود الأول في النطاق.

• range_lookup:

  • TRUE (أو يُترك فارغًا): بحث تقريبي، يجب أن يكون العمود الأول مرتّبًا تصاعديًا.

  • FALSE: بحث دقيق عن القيمة.

مثال عملي على دالة VLOOKUP

لنفترض أن لديك جدول بيانات يحتوي على أسماء الموظفين في العمود الأول، وأعمارهم في العمود الثاني، ورواتبهم في العمود الثالث، وتريد إيجاد راتب موظف معين بناءً على اسمه.

إذا أردت معرفة راتب “سارة”، تستخدم الصيغة:

php
CopyEdit
=VLOOKUP("سارة", A2:C4, 3, FALSE)

والنتيجة ستكون 4500، حيث تم البحث عن “سارة” في العمود الأول وأرجع العمود الثالث من نفس الصف.

ثانيًا: مفهوم دالة HLOOKUP

دالة HLOOKUP (اختصار Horizontal Lookup) تعني البحث الأفقي، وتعمل بنفس مبدأ VLOOKUP ولكن عبر الصفوف، حيث تبحث عن قيمة في الصف الأول من الجدول وترجع قيمة من صف آخر تحدده.

الصيغة الأساسية لدالة HLOOKUP:

scss
CopyEdit
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

• lookup_value: القيمة المراد البحث عنها في الصف الأول.

• table_array: نطاق الجدول الذي يحتوي على البيانات.

• row_index_num: رقم الصف الذي تريد إرجاع القيمة منه.

• range_lookup: خيار اختياري لنوع البحث (TRUE أو FALSE).

شرح الوسائط:

• lookup_value: القيمة التي تريد البحث عنها في الصف الأول.

• table_array: نطاق الجدول، حيث يكون الصف الأول هو الصف الذي يتم البحث فيه.

• row_index_num: رقم الصف (داخل نطاق الجدول) الذي تريد إرجاع بياناته.

• range_lookup: نفس خيارات VLOOKUP.

مثال عملي على دالة HLOOKUP

لنفترض وجود جدول بيانات يحتوي على أسماء الأشهر في الصف الأول، ومبيعات كل شهر في الصف الثاني:

للحصول على مبيعات “مارس”، نستخدم الصيغة:

php
CopyEdit
=HLOOKUP("مارس", A1:D2, 2, FALSE)

والنتيجة تكون 2000.

الفروقات الأساسية بين VLOOKUP و HLOOKUP

نصائح مهمة عند استخدام دوال VLOOKUP و HLOOKUP

1. ترتيب البيانات عند البحث التقريبي

عندما تستخدم range_lookup كـ TRUE (أو تتركه فارغًا)، يجب ترتيب العمود الأول (في VLOOKUP) أو الصف الأول (في HLOOKUP) تصاعديًا لكي تعمل الدالة بشكل صحيح.

2. تجنب الخطأ #N/A

يظهر الخطأ #N/A عندما لا تجد الدالة القيمة المطلوبة. للتعامل مع ذلك، يمكن استخدام دالة IFERROR مع VLOOKUP أو HLOOKUP، مثلًا:

arduino
CopyEdit
=IFERROR(VLOOKUP(...), "غير موجود")

3. استخدام مراجع الخلايا بدلًا من القيم الثابتة

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

4. استخدام النطاق المطلق في table_array

عند نسخ الصيغة لعدة خلايا، من الأفضل تثبيت النطاق باستخدام علامة الدولار ($)، مثال:

swift
CopyEdit
=VLOOKUP(A2, $A$1:$C$100, 3, FALSE)

5. الحد من عدد الأعمدة المستخدمة

يفضل أن يكون col_index_num ضمن نطاق الأعمدة المتاحة في table_array لتجنب الأخطاء.

مقارنة أداء VLOOKUP مع دوال البحث الحديثة

بالرغم من قوة VLOOKUP و HLOOKUP، فقد ظهرت دوال جديدة في إصدارات Excel الحديثة مثل XLOOKUP و LOOKUP التي توفر مرونة أكبر، على سبيل المثال إمكانية البحث في أي اتجاه، وعدم الحاجة لترتيب البيانات، ودعم القيم الافتراضية.

لكن تبقى VLOOKUP و HLOOKUP أساسيتين في برامج Excel القديمة، كما أن تعلمهما يُعتبر خطوة مهمة لفهم آليات البحث داخل الجداول.

استخدام دالة VLOOKUP مع دمجها بدوال أخرى

تُستخدم دالة VLOOKUP كثيرًا بالتوازي مع دوال أخرى لتعزيز وظائفها، مثل:

• MATCH: للحصول على رقم العمود ديناميكيًا.

• IFERROR: لتجنب ظهور أخطاء.

• CONCATENATE أو &: للبحث باستخدام قيم مركبة من أكثر من خلية.

• LEFT, RIGHT, MID: للبحث بناءً على أجزاء من النصوص.

مثال على دمج VLOOKUP مع MATCH

بدلاً من تحديد رقم العمود يدويًا في VLOOKUP، يمكن استخدام MATCH لتحديده تلقائيًا:

swift
CopyEdit
=VLOOKUP(A2, $A$1:$D$100, MATCH("الراتب", $A$1:$D$1, 0), FALSE)

في هذا المثال، يتم البحث عن كلمة “الراتب” في الصف الأول لمعرفة رقم العمود ثم إرجاع القيمة بناءً عليه.

جدول توضيحي لأمثلة استخدام دوال VLOOKUP و HLOOKUP

الخلاصة

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

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

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

المصادر والمراجع:

• كتاب “Excel 2019 Bible” – John Walkenbach

• Microsoft Support: VLOOKUP function

• Microsoft Support: HLOOKUP function