في هذا الدرس سنتعرف على واحدة من أهم دوال الاكسل عامة ودوال البحث خاصة وهي دالة 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"
ليست هناك تعليقات:
إرسال تعليق