دالة VLOOKUP
في هذا الدرس سنتعرف على واحدة من أهم دوال الاكسل عامة ودوال البحث خاصة وهي دالة VLOOKUP
استخدام دالة VLOOKUP
دالة VLOOKUP تبحث عن قيمة داخل العمود الأول في نطاق , وعندما تجدها ترجع القيمة المقابلة لها في عمود أخر .
معاملات دالة VLOOKUP
lookup_value : القيمة التي تريد البحث عنها داخل العمود الأول .
table_array : النطاق الذي يحتوي على البيانات .
col_index_num : رقم العمود الذي يحتوي على القيمة التي سيتم ارجاعها .
[range_lookup] : تحديد هل سيتم البحث عن قيمة مطابقة أم قيمة تقريبية , ويأخذ أحد القيمتين : TRUE للبحث عن قيمة تقريبية أو FALSE للبحث عن قيمة مطابقة . لاحظ أن هذا المعامل اختياري والقيمة الافتراضية له هي TRUE
مثال على استخدام دالة VLOOKUP للبحث عن قيمة مطابقة
عند استخدام دالة VLOOKUP للبحث عن قيمة مطابقة سيتم عمل التالي :
- اذا وجدت دالة VLOOKUP القيمة التي تبحث عنها داخل العمود الأول سترجع القيمة المقابلة لها في العمود الذي حددت رقمه في المعامل الثالث .
- اذا لم تجد دالة VLOOKUP القيمة التي تبحث عنها داخل العمود الأول سترجع الخطأ N/A#
ملحوظة : ليس من الضروري أن تكون القيم داخل العمود الأول مرتبة للحصول على نتائج صحيحة .
على سبيل المثال الجدول التالي يحتوي على بيانات بعض الأشخاص ( رقم التعريف - الاسم - الوظيفة - العنوان ) , ونريد استخدام دالة VLOOKUP للبحث عن بيانات الشخص عن طريق رقم التعريف الخاص به .
لمعرفة اسم الشخص سنستخدام دالة VLOOKUP التالية :
VLOOKUP(C9,A2:D6,2,FALSE)=
دالة VLOOKUP ستبحث عن القيمة الموجودة في الخلية C9 داخل العمود الأول في النطاق A2:D6 وعندما تجدها ترجع القيمة المقابلة لها في العمود رقم 2
لمعرفة وظيفة الشخص سنستخدم نفس المعادلة مع تغيير رقم العمود المكتوب في المعامل الثالث كالتالي :
VLOOKUP(C9,A2:D6,3,FALSE)=
لمعرفة عنوان الشخص سنستخدم نفس المعادلة مع تغيير رقم العمود المكتوب في المعامل الثالث كالتالي :
VLOOKUP(C9,A2:D6,4,FALSE)=
لاحظ أنه اذا لم تجد دالة VLOOKUP القيمة التي تبحث عنها داخل العمود الأول سترجع الخطأ N/A#
مثال على استخدام دالة VLOOKUP للبحث عن قيمة تقريبية
عند استخدام دالة VLOOKUP للبحث عن قيمة تقريبية سيتم عمل التالي :
- اذا وجدت دالة VLOOKUP القيمة التي تبحث عنها داخل العمود الأول سترجع القيمة المقابلة لها في العمود الذي حددت رقمه في المعامل الثالث .
- اذا لم تجد دالة VLOOKUP القيمة التي تبحث عنها داخل العمود الأول ستقوم بالبحث عن أصغر قيمة قبلها وترجع القيمة المقابلة لها في العمود الذي حددت رقمه في المعامل الثالث , وفي حالة لم تجد أي قيمة أصغر منها سترجع الخطأ N/A#
ملحوظة : من الضروري أن تكون القيم داخل العمود الأول مرتبة تصاعديا للحصول على نتائج صحيحة .
في هذا المثال سنجد الجدول الموجود على اليمين يوضح كيف يتم حساب تقدير الطالب حسب النسبة المئوية لمجموع الطالب كالتالي :
- اذا كانت النسبة المئوية للطالب أكبر من أو تساوي %0 وأقل من %50 يكون تقدير الطالب راسب Fail
- اذا كانت النسبة المئوية للطالب أكبر من أو تساوي %50 وأقل من %65 يكون تقدير الطالب مقبول Pass
- اذا كانت النسبة المئوية للطالب أكبر من أو تساوي %65 وأقل من %75 يكون تقدير الطالب جيد Good
- اذا كانت النسبة المئوية للطالب أكبر من أو تساوي %75 وأقل من %85 يكون تقدير الطالب جيد جدا Very Good
- اذا كانت النسبة المئوية للطالب أكبر من أو تساوي %85 وأقل من أو تساوي %100 يكون تقدير الطالب ممتاز Excellent
وعلى اليسار سنقوم بحساب تقدير الطالب عن طريق دالة VLOOKUP التالية :
VLOOKUP(B2,$E$2:$F$6,2,TRUE)=
دالة VLOOKUP ستبحث عن القيمة الموجودة في الخلية B2 داخل العمود الأول في النطاق E2:F6 وعندما تجدها ترجع القيمة المقابلة لها في العمود رقم 2
لاحظ أن :
- الخلية B2 مرجع نسبي لأنه سيزيد كل مرة بمقدار 1 عند سحب المعادلة للأسفل .
- النطاق E2:F6 مرجع مطلق لأنه سيظل ثابت عند سحب المعادلة للأسفل .
لاحظ أن دالة VLOOKUP قامت بحساب التقدير للخمسة طلاب كالتالي :
الطالب الأول : دالة VLOOKUP ستبحث عن القيمة %69 داخل العمود الأول في النطاق E2:F6 فلن تجدها , لذا ستقوم بالبحث عن أصغر قيمة قبلها فتجد القيمة %65 وترجع القيمة المقابلة لها في العمود الثاني وهي القيمة "Good"
الطالب الثاني : دالة VLOOKUP ستبحث عن القيمة %85 داخل العمود الأول في النطاق E2:F6 فتجدها , وترجع القيمة المقابلة لها في العمود الثاني وهي القيمة "Excellent"
الطالب الثالث : دالة VLOOKUP ستبحث عن القيمة %53 داخل العمود الأول في النطاق E2:F6 فلن تجدها , لذا ستقوم بالبحث عن أصغر قيمة قبلها فتجد القيمة %50 وترجع القيمة المقابلة لها في العمود الثاني وهي القيمة "Pass"
الطالب الرابع : دالة VLOOKUP ستبحث عن القيمة %48 داخل العمود الأول في النطاق E2:F6 فلن تجدها , لذا ستقوم بالبحث عن أصغر قيمة قبلها فتجد القيمة %0 وترجع القيمة المقابلة لها في العمود الثاني وهي القيمة "Fail"
الطالب الخامس : دالة VLOOKUP ستبحث عن القيمة %79 داخل العمود الأول في النطاق E2:F6 فلن تجدها , لذا ستقوم بالبحث عن أصغر قيمة قبلها فتجد القيمة %75 وترجع القيمة المقابلة لها في العمود الثاني وهي القيمة "Very Good"