ملخص شرح دوال البحث والمراجع في الإكسل Excel
تُعدّ دوال البحث والمراجع في 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 الصيغتان
- Vector form:
LOOKUP(lookup_value, lookup_vector, [result_vector])
- 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. المصادر والمراجع
- دالة VLOOKUP – الوثائق الرسمية من Microsoft Support
- دالة XLOOKUP – الوثائق الرسمية من Microsoft Support
- شرح INDEX و MATCH – Exceljet
- دالة OFFSET – Microsoft Support