بحث هذه المدونة الإلكترونية

الأحد، 2 أبريل 2017

شرح دالة VLOOKUP

شرح دالة VLOOKUP





السلام عليكم, سنتحدث من خلال هذا الموضوع عن احدى أشهر دوال برنامج اكسل وبالتحديد دالة VLOOKUP والتي لأهميتها, لا اعتقد أنه يوجد مستخدم لبرنامج اكسل لم يحتاج إليها يوماً في عمله. إلا أنه و بالرغم من سهولة استخدامها وتطبيقها إلا أنني قليل ما أجد من يتقن استخدامها بشكل صحيح.
تنتمي دالة VLOOKUP الى أدوات البحث والمراجع Lookup & Reference وهي اختصار لـ Vertical Lookup والتي تعني البحث العمودي كونها تقوم بالبحث عن قيمة ما ضمن أول عمود من جهة اليسار في جدول بيانات فإذا وجدت تطابق اعادت قيمة من نفس صف التطابق من عمود أنت تحدده. كمثال تقريبي, تخيل أنك تنظر إلى جدول علامات طلاب المرحلة الثانوية تبحث عمودياً (من الأعلى للأسفل) في عمود الأسماء عن اسم ما, عندما تجده فإنك تبحث أفقياً عن علامته في مادة العلوم مثلاً والتي انت تعلم موقعها من الجدول, العمود رقم 4 على سبيل المثال.

بنية الدالة VLOOKUP (مكوناتها):


مكونات دالة Vlookup
محارف / مكونات دالة VLOOKUP
بتفصيل أكثر:
Lookup_Value: قيمة اجبارية وهي القيمة التي تبحث عنها (في مثالنا السابق, تمثل هذه القيمة اسم  الطالب الذي تبحث عنه في جدول العلامات). قد تكون هذه القيمة متمثلة بقيمة رقمية (كالبحث عن الرقم الوطني أو الرقم الوظيفي) أو قيمة نصية (كالبحث عن اسم محدد او مجموعة رموز). كما أنه ومن الممكن استخدام معادلة أو دالة في هذا المحرف كاحتساب معدل النجاح لطالب ما من خلال معادلة المتوسط الحسابي ومن ثم استخدام هذه القيمة للبحث عن الاختصاصات المسموح بها لهذا المعدل.
Table_Array: قيمة اجبارية وتمثل مصفوفة (جدول) البيانات أو نطاق البيانات. في مثالنا السابق, كامل جدول علامات طلاب المرحلة الثانوية يمثل مايسمى Table Arrayy. يجب أن يحوي أول عمود من اليسار لمستخدمي الواجهة الإنكليزية (من اليمين للواجهة العربية) على العمود الذي ستبحث فيه على قيمة البحث Lookup Value. لن يطلب اكسل منك تحديد هذا العمود لأن هذا هو مكانه الإفتراضي والذي يتوقع منك اكسل أن تحترمه وتتبعه عند استخدام دالة VLOOKUP.
يمكنك كتابة عنوان هذا الجدول أو النطاق عن طريق تحديد عنوانه مثلاً A1:B100 مع أو بدون تثبيت من خلالاستخدام المراجع المطلقة, باستخدام اسماء النطاقات أو عن طريق بعض الدوال التي تنتج نطاق (سيتم شرح هكذا نطاقات لاحقاً إن شاء الله). أما مايحويه هذا الجدول, النطاق فقد يكون نصاً, رقماً أو قيماً منطقية.
Col_Index_Num: قيمة اجبارية وتمثل رقم العمود الذي يحوي النتيجة التي نبحث عنها (والتي يجب على اكسل أن يظهرها لنا كنتيجة لتطبيق دالة VLOOKUP) وذلك في جدول البيانات Table_Arrayy المذكور اعلاه انطلاقاً من جهة اليسار للواجهة الإنكليزية (من اليمين للواجهة العربية). يجب التنويه إلى وجوب ذكر ترتيب عمود النتيجة بالنسبة لموضعه داخل جدول البيانات Table Array  وذلك بغض النظر عن ترتيب العمود الفعلي في صفحة اكسل. الترتيب يجب أن يكون رقمياً, للعمود الثالث نكتب 3 وهكذا.
ادخال الرقم 1 سيعيد نفس القيمة المبحوث عنها في حين ان ادخال رقم أقل من 1 سيعيد الخطأ !VALUE# أما ادخال رقم أكبر من عدد اعمدة جدول البيانات Table Array سيعيد الخطأ !REF#.
Range_Lookup: قيمة اختيارية وتمثل طريقة البحث التي يجب على اكسل أن يتبعها (بحث مطابق أو تقريبي). حذفها, اعطائها القيمة 1 أو True يعني أن اكسل سيبحث بحثاً تقريباً, بمعنى أنه في حال لم يجد اكسل تطابقاً للقيمة المبحوث عنها Lookup_Value فإنه سيعيد نتيجة أقرب أصغر قيمة للقيمة المبحوث عنها Lookup Value. في حين أن القيمة الفارغة, 0 أو False يعني أن اكسل سيبحث بحثاً مطابقاً ليعيد النتيجة الصحيحة أو الخطأ N/A# في حال عدم وجود تطابق مع القيمة المبحوث عنها.
خيارات طرق البحث في الدالة Vlookup
VLOOKUP Function
لاحظ أول خيار من قائمة البحث المتطابق في الصورة اعلاه تبين لنا كيف أن حذف محرف Range_Lookup كليّاً يعني لاكسل البحث المتطابق لدالة VLOOKUP في حين أن إبقاء هذا المحرف فارغاً دون حذف يعني أن بحث VLOOKUP سيكون تقريبياً كما في أول خيار من القائمة بحث تقريبي اعلاه.

ملاحظات على ماسبق:
  • في حال الإعتماد على VLOOKUP مع البحث المتطابق وفي حال وجود أكثر من تطابق فإن اكسل سيعيد نتيجة أول تطابق يجده.
  • في حال الإعتماد على VLOOKUPمع  البحث التقريبي, فإنه يجب عليك ترتيب جدول البيانات Table Array ترتيباً تصاعدياً باستخدام أول عمود من اليسار لمستخدمي الواجهة الإنكليزية (أول عمود من اليمين لمستخدمي الواجهة العربية).
  • عطفاً على النقطة السابقة, في حال الإعتماد على VLOOKUP مع البحث المتطابق فإنه لاحاجة لترتيب جدول البيانات Table Array.
  • في حال استخدام VLOOKUP مع البحث التقريبي عند البحث عن قيمة غير موجودة في جدول البيانات Table Array فإن اكسل سيعيد نتيجة أقرب أصغر قيمة للقيمة المبحوث عنها Lookup Value. في حال كانت القيمة المبحوث عنها Lookup Value أصغر من كل قيم عمود البحث (العمود الأول) في جدول البيانات Table Array فإن اكسل سيعيد الخطأ N/A#.
  • عند البحث عن قيم نصية في العمود الأول من Table Array، تأكد من عدم احتواء البيانات في العمود الأول من Table Array على مسافات بادئة أو مسافات زائدة، أو استخدام غير متناسق لعلامة الاقتباس المستقيمة ( ‘ أو ” ) وعلامة الاقتباس المتعرجة ( ‘ أو “)، أو أحرف غير قابلة للطباعة. في حالة وجود أي من هذه الحالات، قد تُرجع VLOOKUP قيمة غير صحيحة أو غير متوقعة.
  • عند البحث عن قيمة عددية أو تاريخ, تأكد بأن تنسيق بيانات عمود البحث (أول عمود) في جدول البيانات متطابق مع تنسيق قيمة البحث تجنباً للحصول على قيمة غير صحيحة أو غير متوقعة.
  • في حال البحث المتطابق عن قيمة نصية, يمكنك ابدال بعض الأحرف الناقصة من النص المبحوث عنه Lookup Value عن طريق استبدالها باداة الإستفهام ؟ أو يمكنك استبدال مجموعة من الأحرف المتتابعة (كتتمة جملة ما لاتريد كتابتها كاملةً) وذلك عن طريق اضافة  رمز النجمة *

مثل عملي:

لنفترض أنك تريد أن تبحث عن أرقام جوالات بعض عملاءك باستخدام رقم العميل وذلك عن طريق استخدام دالة VLOOKUP,
مثال تطبيقي لاستخدام دالة VLOOKUP
مثال تطبيقي لاستخدام دالة VLOOKUP

كما سبق وشرحنا أعلاه, يمكنك استخدام VLOOKUP للبحث المطابق أو التقريبي والذي سيكون له تأثير عند البحث عن قيم غير موجودة في جدول البيانات. إليك نتيجة البحث باستخدام كلا الإسلوبين (قد لايكون البحث التقريبي ذا معنى هنا كوننا نبحث عن رقم جوال للعملاء لكن المثال التطبيقي هو المهم بغض النظر عن معنى المثال ومدى واقعيته).
مثال عملي لاستخدام دالة VLOOKUP
مثال عملي لاستخدام دالة VLOOKUP

الحل بصورة أشمل,
استخدام دالة VLOOKUP
استخدام دالة VLOOKUP


إرسال تعليق