الشبكات

ملخص شرح دوال البحث والمراجع في الإكسل Excel

منذ أسبوعينآخر تحديث: 05/05/2025
55 3 دقائق

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

1. نظرة عامة على دوال البحث والمراجع

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

مقالات ذات صلة
  • دوال البحث الرأسية والأفقية: مثل VLOOKUP وHLOOKUP.
  • دوال مرجعية متعددة الأغراض: مثل INDEX، MATCH، XLOOKUP.
  • الدوال الديناميكية: كتوليد مراجع خلوية أو نطاقية تلقائياً عبر OFFSET وINDIRECT.
  • دوال الاختيار: مثل CHOOSE التي تسمح باختيار قيمة من مجموعة ثابتة بناءً على مؤشر.

في الفقرات التالية، سنستعرض كل دالة بالتفصيل من خلال شرح المعاملات (arguments)، ومن ثم استعراض أمثلة عملية وتطبيقات واقعية.

2. دالة VLOOKUP

2.1 الصيغة (Syntax)

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  • lookup_value: القيمة المراد البحث عنها (يمكن أن تكون خلية أو قيمة ثابتة).
  • table_array: النطاق الذي يحتوي على جدول البيانات (يجب أن تكون القيمة المراد البحث عنها في العمود الأيسر).
  • col_index_num: رقم العمود من الجدول الذي سيتم استرجاع القيمة منه (يبدأ العد من 1).
  • range_lookup (اختياري): TRUE (بحث تقريبي) أو FALSE (بحث دقيق). القيمة الافتراضية هي TRUE.

2.2 مثال عملي

لنفترض وجود جدول المبيعات في النطاق A2:C10 حيث يحتوي العمود A على كود الصنف، والعمود B على اسم الصنف، والعمود C على سعر الوحدة. لاسترجاع السعر بناءً على كود الصنف في الخلية E2:

=VLOOKUP(E2, A2:C10, 3, FALSE)

2.3 ملاحظات وأخطاء شائعة

  • إذا لم يتم العثور على lookup_value، ستظهر #N/A.
  • البحث التقريبي (TRUE) يتطلب ترتيب العمود الأول تصاعدياً.
  • لا يدعم البحث في الأعمدة اليسرى للعمود الأول.

3. دالة HLOOKUP

3.1 الصيغة (Syntax)

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
  • تشابه تام مع VLOOKUP، ولكن البحث يتم أفقياً عبر الصفوف.
  • row_index_num: رقم الصف في table_array الذي يتم جلب القيمة منه.

3.2 مثال عملي

في جدول الإنتاج الموضوع في النطاق A1:F3، يمثل الصف الأول رؤوس الأعمدة (شهور السنة)، والصف الثالث كميات الإنتاج. لاسترجاع كمية الإنتاج لشهر مايو المخزن في الخلية H2:

=HLOOKUP(H2, A1:F3, 3, FALSE)

4. الجمع بين دالتي INDEX و MATCH

4.1 مقدمة

يُمثّل الجمع بين INDEX وMATCH بديلاً مرناً وأكثر قوة من VLOOKUP وHLOOKUP، حيث يدعم البحث ثنائي الاتجاه (صفوف وأعمدة) ويسمح بالبحث في أي عمود أو صف دون التقيد بالجهة.

4.2 الصيغتان

MATCH(lookup_value, lookup_array, [match_type])
INDEX(array, row_num, [column_num])
  • MATCH تُرجع موضع القيمة ضمن نطاق.
  • INDEX تُرجع القيمة بناءً على رقم الصف والعمود المحددين ضمن نطاق.

4.3 صيغة بحث منسقة

=INDEX(C2:C10, MATCH(E2, A2:A10, 0))

تبحث MATCH عن E2 في العمود A، ثم تعيد الرقم إلى INDEX لاسترجاع القيمة المقابلة من العمود C.

4.4 مثال متعدد الأبعاد

للبحث داخل جدول متعدد الصفوف والأعمدة في النطاق A1:D20 عن قيمة عمود محدد:

=INDEX(A1:D20, MATCH("اسم المنتج", A1:A20, 0), MATCH("السعر", A1:D1, 0))

5. دالة XLOOKUP

5.1 الصيغة (Syntax)

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
  • if_not_found: القيمة البديلة عند عدم وجود نتيجة.
  • match_mode: 0 (دقيق)، -1 (>, أصغر)، 1 (>=، أكبر)، 2 (wildcards).
  • search_mode: 1 (من الأعلى للأسفل)، -1 (بالعكس)، 2 (binary ascending)، -2 (binary descending).

5.2 ميزات XLOOKUP

  • بحث دقيق وشامل دون الحاجة لمنطقة بحث مُرتبة.
  • دعم أخطاء مخصصة بدلاً من #N/A.
  • البحث ثنائي الاتجاه (عمودياً وأفقياً) دون دمج دوال.

5.3 مثال عملي

=XLOOKUP(E2, A2:A10, C2:C10, "غير موجود", 0, 1)

6. دالة LOOKUP

6.1 الصيغتان

  1. Vector form: LOOKUP(lookup_value, lookup_vector, [result_vector])
  2. Array form: LOOKUP(lookup_value, array)

6.2 ملاحظات

  • البحث التقريبي؛ يعتمد على ترتيب النطاق تصاعدياً.
  • لا يمكن استخدامه للبحث الدقيق دون ترتيب.

7. دالة OFFSET

7.1 الصيغة (Syntax)

OFFSET(reference, rows, cols, [height], [width])
  • تُنشئ مرجعاً ديناميكياً من الخلية الأساسية reference.
  • يُستخدم في جداول البيانات المتحركة.

7.2 مثال عملي

لعرض مجموع 5 خلايا بدءاً من الخلية B2:

=SUM(OFFSET(B2, 0, 0, 5, 1))

8. دالة INDIRECT

8.1 الصيغة (Syntax)

INDIRECT(ref_text, [a1])
  • تنشئ مرجعاً نصياً إلى خلية أو نطاق.
  • تمكّن من تغيير نطاقات الصيغ ديناميكياً عبر محتوى الخلايا.

8.2 مثال عملي

إذا كان في الخلية A1 نص “Sheet2!B5″، يمكن استرجاع قيمة الخلية B5 في الورقة الثانية عبر:

=INDIRECT(A1)

9. دالة CHOOSE

9.1 الصيغة (Syntax)

CHOOSE(index_num, value1, [value2], ...)
  • تختار قيمة من مجموعة ثوابت بناءً على index_num.

9.2 مثال عملي

=CHOOSE(MATCH(E2, {"يناير","فبراير","مارس"},0), 100, 200, 300)

10. أفضل الممارسات والنصائح المتقدمة

  • يفضَّل دائماً استخدام XLOOKUP (في الإصدارات الأحدث) لمرونته وأدائه الأعلى.
  • يُنصح بالابتعاد عن المراجع الديناميكية المعقدة (كـ OFFSET وINDIRECT) إن لم تكن ضرورية؛ لتجنب الأخطاء وصعوبة الصيانة.
  • تنظيم البيانات في جداول Excel (Table) يسهّل الربط ويتيح استخدام أسماء الأعمدة بدلاً من مراجع الخلايا الصريحة.
  • المزيج بين INDEX وMATCH مثالي للبحث ثنائي الأبعاد وللبحث في أعمدة يسار العمود الأول.
  • مراقبة الأداء: مع الجداول الكبيرة، يمكن أن تتباطأ دوال البحث؛ استبدال VLOOKUP بـ XLOOKUP أو INDEX/MATCH يقلل زمن الحساب.
  • عند الحاجة لإرجاع عدة قيم متطابقة، يمكن استخدام الصيغ المصفوفية المتقدمة أو دوال مثل FILTER (في الإصدارات الحديثة).

11. المصادر والمراجع

منذ أسبوعينآخر تحديث: 05/05/2025
55 3 دقائق
زر الذهاب إلى الأعلى