دالة XLOOKUP

سنتعرف في هذا الدرس على دالة XLOOKUP بالتفصيل من خلال الكثير من الأمثلة العملية لأنها تعتبر أهم دالة بحث على الاطلاق .

معاملات دالة XLOOKUP

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

lookup_array : النطاق الذي سيتم البحث داخله .

return_array : النطاق الذي يحتوي على القيمة التي سترجعها الدالة .

[if_not_found] : القيمة التي سترجعها الدالة في حالة عدم وجود القيمة التي تريد البحث عنها .

ملحوظة : هذا المعامل اختياري والقيمة الافتراضية له هي الخطأ N/A#

[match_mode] : تحديد هل سيتم البحث عن قيمة مطابقة أم قيمة تقريبية , ويأخذ احدى القيم التالية :

0 : البحث عن قيمة مطابقة .

1- : البحث عن قيمة تقريبية وفي حالة عدم وجود القيمة التي تريدها تستخدم الدالة أصغر قيمة قبلها .

1 : البحث عن قيمة تقريبية وفي حالة عدم وجود القيمة التي تريدها تستخدم الدالة أكبر قيمة بعدها .

2 : استخدام حروف Wildcard

3 : استخدام تعبير Regex

ملحوظة : هذا المعامل اختياري والقيمة الافتراضية له هي 0

[search_mode] : تحديد اتجاه البحث , ويأخذ احدى القيم التالية :

1 : البحث من أعلى إلى أسفل .

1- : البحث من أسفل إلى أعلى .

2 : البحث الثنائي وتكون البيانات مرتبة تصاعديا .

2- : البحث الثنائي وتكون البيانات مرتبة تنازليا .

ملحوظة : هذا المعامل اختياري والقيمة الافتراضية له هي 1

مثال على البحث عن قيمة مطابقة

الجدول التالي يحتوي على ( رقم الموظف - اسم الموظف - المرتب ) ونريد استخدام دالة XLOOKUP لمعرفة مرتب الموظف عن طريق رقمه .

في الشكل التالي دالة XLOOKUP ستبحث عن رقم الموظف 104 داخل النطاق A2:A6 فتجده وترجع المرتب المقابله له في النطاق C2:C6

في الشكل التالي دالة XLOOKUP ستبحث عن رقم الموظف 106 داخل النطاق A2:A6 فلا تجده وترجع النص "Not Found" , لاحظ أنه اذا لم تعطي قيمة للمعامل الرابع [if_not_found] كانت المعادلة سترجع الخطأ N/A#

ملحوظة : من المميزات المهمة في دالة XLOOKUP أنه ليس من الضروري أن يكون عمود البحث هو العمود الأول مثل دالة VLOOKUP

على سبيل المثال في الشكل التالي دالة XLOOKUP ستبحث عن اسم الموظف Youssef داخل النطاق B2:B6 فتجده وترجع المرتب المقابل له في النطاق C2:C6

مثال على البحث عن قيمة تقريبية

الجدول الموجود على اليمين يحنوي على الطريقة التي يتم احتساب بها تقدير الطالب كالتالي :

- اذا كانت النسبة المئوية للطالب أقل من %50 يكون تقدير الطالب راسب Fail

- اذا كانت النسبة المئوية للطالب أكبر من أو تساوي %50 وأقل من %65 يكون تقدير الطالب مقبول Pass

- اذا كانت النسبة المئوية للطالب أكبر من أو تساوي %65 وأقل من %75 يكون تقدير الطالب جيد Good

- اذا كانت النسبة المئوية للطالب أكبر من أو تساوي %75 وأقل من %85 يكون تقدير الطالب جيد جدا Very Good

- اذا كانت النسبة المئوية للطالب أكبر من أو تساوي %85 وأقل من أو تساوي %100 يكون تقدير الطالب ممتاز Excellent

في الجدول الموجود على اليسار نريد حساب تقدير كل طالب من خلال النسبة المئوية للطالب . دالة XLOOKUP ستبحث عن النسبة المئوية داخل النطاق E2:E6 وسيكون هناك احتمالين :

- اذا وجدت دالة XLOOKUP النسبة المئوية التي تريد البحث عنها سترجع القيمة المقابلة لها في النطاق F2:F6

- اذا لم تجد دالة XLOOKUP النسبة المئوية التي تريد البحث عنها ستبحث عن أكبر قيمة بعدها وترجع القيمة المقابلة لها في النطاق F2:F6

مثال على البحث من أسفل إلى أعلى

الجدول التالي يحتوي على الكمية المباعة من بعض أنواع الموبايلات في بعض الأيام , ونريد استخدام دالة XLOOKUP لمعرفة أخر كمية مباعة من موبايل معين .

في الشكل التالي دالة XLOOKUP ستبحث عن اسم الموبايل الموجود في الخلية F1 داخل النطاق B2:B11 ولكن يتم البحث من أسفل إلى أعلى وعندما تجده ترجع الكمية المقابله له في النطاق C2:C11

مثال على ارجاع أكثر من قيمة

الجدول التالي يحتوي على رقم الطالب واسم الطالب ودرجاته في ثلاثة مواد , ونريد استخدام دالة XLOOKUP للبحث عن رقم الطالب وارجاع أربعة معلومات ( اسم الطالب - درجة اللغة العربية - درجة اللغة الانجليزية - درجة الرياضيات ) . من المميزات الرائعة لدالة XLOOKUP أنك لن تحتاج إلى كتابة أربعة معادلات .

في الشكل التالي دالة XLOOKUP ستبحث عن رقم الطالب المكتوب في الخلية A10 داخل النطاق A2:A6 وعندما تجده ترجع الأربعة قيم المقابلة له . دالة XLOOKUP ستفهم أنك تريد ارجاع أربعة قيم لأن النطاق المكتوب في المعامل الثالث return_array وهو B2:E6 يحتوي على أربعة أعمدة ( B C D E ) .

اذا كنت تريد أن تكون الأربعة قيم التي سترجعهم دالة XLOOKUP بشكل رأسي يمكنك كتابة دالة XLOOKUP داخل دالة TRANSPOSE

مثال على استخدام أكثر من شرط

الجدول التالي يحتوي على عدد الموظفين في شركة انترناشيونال في كل مدينة داخل كل دولة . ونريد استخدام دالة XLOOKUP لمعرفة عدد في الموظفين في مدينة معينة داخل دولة معينة . طبقا لذلك سنحتاج إلى كتابة شرطين وهما ( اسم الدولة - اسم المدينة ) . 

لكتابة أكثر من شرط داخل دالة XLOOKUP ستضع كل شرط داخل قوسين ( ) وتفصل بينهم بعلامة الضرب * وتكون قيمة المعامل الأول ( القيمة التي تريد البحث عنها ) هي 1 , لماذا علامة الضرب والقيمة 1 تحديدا ؟

عند تقييم كل شرط , اذا كان الشرط صحيح سيرجع القيمة 1 واذا كان الشرط خطأ سيرجع القيمة 0 , طبقا لذلك سيكون هناك احتمالين :

- اذا كانت كل الشروط صحيحة ستكون النتيجة النهائية هي 1 لأن 1 * 1 = 1

- اذا كان أحد الشروط خطأ ستكون النتيجة النهائية هي 0 لأن 1 * 0 = 0