شرح دالة vlookup بالصور

دالة Vlookup

Vlookup هو اختصار لـ Vertical يعني العمودي و lookup يعني البحث (البحث العمودي) فهي دالة بحث من دوال البحث التي يوفرها برنامج Microsoft Excel فهي تستخدم للبحث عن قيمة معينة (القيمة المبحوث عنها ) في عمود محدد من خلال الاعتماد على قيمة أخرى معلومة مقابلة لها في نفس الصف فهذه العملية تتم علي جدول يحتوي علي بيانات مختلفة ، اي اذا كان لديك كمية كبيرة من البيانات وتريد جلب معلومة او اكثر من هذه البيانات ، لذلك فإن دالة Vlookup تعتبر من اهم الدوال واكثر الدوال استخداماً في برنامج Microsoft excel حيث يمكن استخدامها في البحث عن رواتب الموظفين او درجات الطلاب او أسعار المنتجات وغيرها من الاستخدامات حيث ان دالة vlookup لا غنى  عنها أياً كان المجال الخاص بك ، لذلك سنتحدث في هذا الموضوع عن شرح دالة vlookup بالصور التوضيحية و تطبيقات عملية و الأخطاء التي قد تواجهك عند استخدام دالة vlookup .

شرح دالة Vlookup

قبل ان نقوم بشرح الدالة نود ان نعرف اولاً ما هي صيغة دالة Vlookup ، فالدالة تتكون من اربع بنود

تطبيق على دالة Vlookup

ولكي يتضح لنا بشكل مبسط شرح دالة Vlookup نقوم بتطبيق مثال بسيط يوضح لنا ذلك :

مثلاً لدينا جدول بأسماء الطلاب و درجاتهم في كل مادة

كما عرفنا ان دالة Vlookup تشمل على اربعة بنود ، سنتطرق الآن الي كل بند بها مع شرحه من خلال التطبيق العملي :

1- اسم الطالب وهو lookup value وهو عبارة عن خلية محددة تكتب فيها اسم الطالب الذي تريد معرفة درجته ولتكن هذه الخلية مثلا D11 و ليكن اسم الطالب هو ” حسن ”

مع العلم : 

– اسم الطالب هو القيمة المعلومة

– درجة المادة هي القيمة المبحوث عنها

2- الجدول الذي ستقوم الدالة بالبحث فيه وهو table array ( نقوم بتحديد الجدول بالكامل ) ، واذا كنا سنقوم بتكرار المعادلة على اكثر من خلية فيجب تثبيت الجدول من خلال الضغط علي زر F4

 

3- تحديد العمود الذي تريد الدالة بالبحث فيه وهو col index num فمثلاً نريد معرفة درجة مادة الرياضيات فكيف نقوم بتحديد العمود ؟

دالة vlookup لا يمكنها تحديد العمود يدوي ولكن يتم التحديد من خلال رقم العمود وذلك من خلال الاتي :

يتم عد الاعمدة يدوياً في الجدول من الاتجاه الرئيسي لشيت الاكسل  ، فمثلا في هذا الشيت الاتجاه من اليمين الي اليسار اذا نقوم بالعد بداية من عمود ” اسم الطالب ” حتي نصل الي عمود مادة ” الرياضيات ”

فيكون عمود الرياضيات هو رقم 3 ، اذا فـ col index num تساوي 3

سؤال : 

كيف نعرف اذا كان الشيت من اليمين الي اليسار او العكس بطريقة سهلة ؟

اذا كان العمود A يمين الشيت اذا فهو من اليمين الي اليسار واذا كان العمود A في اليسار اذا فاتجاه الشيت من اليسار الي اليمين

 

4- اما خانة [range_lookup] فهي المسؤولة عن دقة النتائج ، لجعل النتيجة دقيقة 100% اكتب 0 او False ، اما في حالات معينة قد نحتاج الي نتائج تقريبية فنكتب 1 او True .

بعد ادخال هذه المدخلات فتصبح المعادلة بالشكل التالي :

=VLOOKUP(   D11  ,  $B$2:$H$8 ,  3  ,   FALSE  )

 

ملحوظة : 

يمكنك عمل قائمة منسدلة في خلية ” اسم الطالب ” بدلا من كتابة الاسم في كل مرة ، ولعمل قائمة منسدلة قم بزيارة هذا الموضوع : تعديل قائمة منسدلة في Excel

 

المطلوب منك الآن هو تطبيق الدرس وكتابة المعادلة لمعرفة درجة مادة العلوم

 

رابط تحميل تطبيق دالة Vlookup 

رابط التحميل

كيفية استخدام الدالة vlookup في excel

يوجد طريقتان لاستخدام دالة vlookup ، الطريقة الاولي 

هي كتابة الدالة بشكل عادي بمعني الكتابة اليدوية للدالة وهي كالتالي :

1- كتابة علامة يساوي ثم كتابة vlookup والضغط على زر tab

2- ثم اختر الخلية التي ستكتب بها اسم الطالب الذي تريد معرفة درجته

وبعد تحويل لغة الكيبورد ( لوحة المفاتيح ) الي اللغة الإنجليزية قم بالضغط على زر حرف الكاف لكتابة العلامة الفاصلة بين كل مدخل وهي هذه العلامة ;

او العلامة , الموجودة في حرف الواو  على حسب اصدار الاكسل عندك

3- ثم قم بتحديد الجدول بأكمله ثم العلامة ; او ,

4- قم بكتابة رقم العمود الخاص بالمادة التي تريد ان تعرف درجتها ثم العلامة ; او ,

5- ثم قم باختيار false ثم اغلق المعادلة من خلال علامة القوس (

 

الطريقة الثانية لاستخدام دالة vlookup

1- من خلال الضغط على علامة fx المشار اليها في الصورة التالية واختر من select category اختر ALL واكتب في مربع البحث Vlookup وقم بالضغط على GO ، ستظهر لك الدالة بالأسفل قم باختيارها

2- سيظهر لك هذه النافذة

في خانة lookup value قم باختيار خلية اسم الطالب الذي تريد معرفة درجته

وفي خانة table array قم بتحديد الجدول بالكامل وقم بتثبيته من خلال زر F4

وفي خانة col index num اكتب رقم العمود الذي تريد معرفة درجة المادة كما تعرفنا منذ قليل

وفي خانة [range_lookup] اكتب 0

ثم اضغط OK

استخدامات دالة vlookup

1- اذا كان لديك الكثير من الموظفين ولديك كشف بمعلومات عنهم وتريد معرفة معلومة معينة بدون مجهود .

2- اذا كنت مدرس ولديك كشف بأسماء الطلاب و معلومات عنهم او درجاتهم او تقديراتهم .

3- اذا كنت تاجر ولديك قائمة كبيرة من أسعار المنتجات وتريد معرفة سعر منتج بعينة بدون عناء البحث .

4- اذا كنت تعمل في مكتب محاسبة فهي لا غني عنه

5- بشكل عام فهي تفيد اكثر عندما تكون كمية البيانات كبيرة وتريد البحث او جلب معلومة او اكثر من هذه البيانات .

الخلاطة ان أي مجال من المجالات العملية يمكن ان تستخدم فيها دالة Vlookup لتسهل عليك امر ما او عملية تقوم بها العديد من المرات .

دالة vlookup باكثر من شرط

بعد ان فهمنا جيدا طريقة عمل دالة vlookup فعلمنا ان الدالة تبحث من خلال شرط واحد ( اسم الطالب مثلاً ) ، فماذا لو كنا بحاجة للبحث بأكثر من شرط ؟! .. نوضح ذلك من خلال المثال التالي :

لدينا جدول لأسماء الطلاب والفصول الخاصة بهم ولكن هنا أسماء طلاب متكررين كما هو واضح في الصورة التالية ، فهناك اكثر من ” محمد ” واكثر من ” علي ” .. فماذا نفعل ؟!

 

حل المثال 

1- نقوم بعمل عمود قبل الاسم ولنسميه مثلا ” مساعد ” ونكتب به علامة يساوي ثم اختر اسم الطالب ثم اكتب علامة & ثم اختر الفصل ، ليكون شكل الدالة كالاتي :

=C4&D4

معني ذلك اننا قمنا بإضافة الاسم مع الفصل ليكونوا في خلية واحدة

ونقوم بتكرار المعادلة لاسفل

2- وفي الخلية التي سنكتب بها دالة vlookup نقوم بكتابة يساوي vlookup وعند اختيار lookup value نقوم باختيار اسم الطالب ثم كتابة علامة & ثم اختيار الفصل ، واكمال باقي الدالة كما تعرفنا ، لتكون الدالة كالآتي :

=VLOOKUP(  F12&G12   , $B$3:$J$9  ,  4  ,  0  )

 

كما لاحظنا في الصورة السابقة ان المعادلة تمت بنجاح وذلك من خلال اننا قمنا بتعديل دالة vlookup لكي تبحث عن شرطين ( اسم الطالب + الفصل ) وبنفس الطريقة يمكنك تعديل دالة vlookup لكي تبحث عن 3 شروط او اكثر

دالة vlookup لا تعمل

يجب ان نأخذ في الاعتبار ان لكل دالة قواعد يجب ان نلتزم بها حتي لا يحدث أخطاء بسبب عدم اتباع هذه القواعد ، فدعونا نعرف ما هي قواعد والاشياء التي يجب ان نلاحظها عند استخدام دالة vlookup :

1- يجب ان يكون القيمة الذي نبحث عنه في اول عمود في الجدول ( اذا كان الشيت من اليمين الي اليسار فيكون اول عمود في اليمين واذا كان الشيت من اليسار الي اليمين فيكون او عمود في اليسار ) .

2- يجب ان يكون هناك تطابق بين المبحوث عنه في lookup value و الموجود في العمود الأول ، حيث ان اذا كان هناك حرف زيادة او نافص او رقم زيادة او ناقص فستكون النتيجة error خطأ ، لذلك يُنصح دائماً بعمل قائمة منسدلة في الخلية لتفادي هذا الخطأ .

3- يجب ألا يحتوي table array عن اي خلايا مدمجة Merge .

4- افهم جيدا طريقة معرفة رقم العمود في الجدول كما شرحناها في بداية الدرس col index num واذا احتجت الي اي مساعدة لا تتردد في كتابة استفسارك في التعليقات .

5- في حالة اختيار ان تكون المطابقة غير تامة true او 1 ( البند الرابع في المعادلة )  فيجب ان يكون العمود الاول في table array مُرتب ترتيب تصاعدي وإلا ستكون النتيجة خاطئة

خيار المطابقة غير تامة او True يتم استخدامه عندما تكون النتيجة المطلوبة تقريبية ،

فمثلاً لدينا مجموعة من الطلاب و اجمالي درجاتهم ونريد ان نعطي كلاً منهم تقدير حسب درجته ، حيث ان الدرجة النهائية هي 250 ، حينئذ يجب استخدام خيار المطابقة غير التامة لان من التأكيد ان يكون هناك درجات حصلو عليها الطلاب وغير موجودة في الجدول ( انظر الصورة التالية ) ، لذلك فبرنامج الاكسل في هذه الحالة اذا لم يجد الدرجة موجودة ينظر الي الدرجة الأقل منها وبالتالي التقدير الأقل منه وهذا هو المطلوب ..

 

وهذه هي صيغة دالة vlookup المستخدمة :

=VLOOKUP(  C4  ,  $G$6:$H$11  ,  2  ,TRUE)

C4 هي خلية درجات الطالب 

$G$6:$H$11  هو جدول التقديرات 

2 هو رقم عمود التقديرات 

TRUE وهي حالة المطابقة غير التامة

6- لاحظ ان شكل الفاصلة تختلف من اختلاف إصدارات الاكسل ففي البعض يكون  ;   والبعض الاخر يكون   ,    ، فعليك ملاحظة ذلك من خلال التعليمات التي يظهرها لك اكسل عند كتابة دالة vlookup او قم باستخدام الطريقة الثانية التي تم شرحها لكتابة دالة vlookup .

 

موضوعات قد تُهمك : 

• كيفية عمل قائمة منسدلة تقبل البحث
• طريقة حساب ساعات العمل في Excel