شرح دالة VLOOKUP في الاكسيل

شرح دالة VLOOKUP في الاكسيل : الدليل الشامل

تعتبر الدالة vlookup من أكثر دوال الاكسيل شهرة لما لها من استخدامات في الاكسيل خاصا في المجال المحاسبي حيث انها دالة بحث عمودي يمكن من خلالها الوصول إلى نتيجة بحث في أي عمود في الجدول بدلالة شرط للبحث و ارجاع القيمة المقابلة في العمود لنتيجة البحث و هناك العديد من الافكار التي يمكن الاستخدام المباشر للدالة و تطويرها الدالة في عمليات البحث الديناميكي و البحث باكثر من شرط للبحث و عكس اتجاة البحث مع العلم أن في بعض تحديثات الاكسيل تم تطوير بعض الدوال للبحث باكثر من شرط الا ان مازلت الدالة vlookup هي الاكثر استخدام

تستطيع الدالة البحث عن ارقام او نصوص او اي مرجع داخل الخلية

شرح صيغة الدالة (Syntax)

يتم كتابة الدالة في ابسط صوره لها بالشكل التالي للمعاملات :

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

شرح المعاملات

1. lookup_value ( معيار البحث اي انها القيمه المرجعية لعمليه البحث ):
   و هي القيمة المرجعية او معيار البحث  و التي نرغب في ان تكون شرط البحث للوصول القيمة المقابلة في الجدول للوصول الي نتيجة البحث يمكن ان تكون نص او رقم 
2. table_array (نطاق جدول البحث):
   هو الجدول الذي يحتوي على نطاق البحث و الذي سوف يتم البحث فية . يفضل ان تكون القيمة المطلوب البحث عنها في العمود الاول حيث اذا كانت القيمة ليست في العمود الاول سوف نظر الي استخدام بعض الصيغ المركبة لاعادة بناء الجدول داخل المعادلة و ترتيب الاعمدة مرة اخري ليكون عمود البحث هو العمود الاول و هذة الفكرة سوف نستعرضها لاحقا 
3. col_index_num (رقم عمود  المستهدف اي نتيجه البحث ):
   هو رقم العمود داخل الجدول او داخل نطاق البحث  الذي نريد إرجاع منة نتيجة البحث . يبدأ الترقيم من 1 للعمود الأول في النطاق علي سبيل المثال اذا كنت تريد البحث عن النتيجة في العمود الرابع تكتب الرقم مباشرة 4 و ذلك في ابسط صورة للمعادلة حيث ان في بعض الصيغ المتقدمة يمكن ان نعدل هذا المعامل الي معامل ديناميكي متغير لتطوير البحث بشكل بشكل افضل و سوف نتعرف علي بعض من هذة الافكار لاحقا
4. [range_lookup] (دقة البحث و المطابقة ):
   معامل اختياري يأخذ إحدى قيمتين:
   • FALSE أو 0: بحث مطابق بنتيجة دقيق 100% لا تحتمل اي نتيجة للخطأ 
   • TRUE أو 1: بحث تقريبي لاقرب نتيجة بشرط ان يكون قيم الجدول مرتبة ترتيب تصاعدي لكي يعمل البحث بشكل صحيح و هذا المعامل هي القيمة الافتراضية للمعامل اي اذا تركت هذا المعامل بدون قيمة سوف ياخذ قيمة افتراضية TRUE

أمثلة عملية

المثال الأول: بحث دقيق

لنفترض أن لدينا جدول الاسعار في النطاق A1:F20 يحتوي على في العمود A كود المنتج ، و اسم الصنف في العمود B، نوع الوحده في العمود C و العمود D يحتوي علي السعر و العمود E يحتوي علي الكمية و العمود F يحتوي علي القيمة

فاذا اردنا الاستعلام عن سعر منتج من العمود D بدرجة مطابقة تامة بشرط بحث ان كود المنتج هو اللي موجود في الخليه H2 في هذه الحالة يمكن كتابة الداله بالصيغة التالية

=VLOOKUP(H2, A1:F20, 4, FALSE)

فتقوم هذة الدالة بالبحث في العمود D بدرجة مطابقة تامة بشرط ان يكون القيمة مقابلة لنفس شرط البحث من العمود A 

و هتكون النتيجة المقابله علي سبيل المثال للكود 10001 السعر 40000

المثال الثاني: البحث التقريبي

لنفترض اننا نريد دالة تساعد في عملية تكويد الحسابات في تصميم دليل الحسابات و نريد تكويد الحساب رقم 10002 و نريد تحديد الحساب الاب لهذا الحساب

اذا استخدمنا الدالة vlookup بالصيغة المطابقة التامة سوف تعود بالنتيجه #N/A حيث لا يوجد بالجدول حساب برقم 10002 بينما اذا استخدمنا صيغة المطابقة التقريبية في البحث سوف تعود بنتيجة صحيحة

لنفترض اننا نريد البحث عن كود حساب 10002

=VLOOKUP(H2, A1:F20, 4, TRUE)

يجب ان نكون علي علم ان شرط ان تعمل هذه الدالة في حالة المطابقة التقريبية ان يكون قيم العمود A مرتبة تصاعدي 

الأخطاء الشائعة وكيفية تفاديها 

1. خطأ #N/A:
   يعتبر هذا الخطأ من الاخطاء الاكثر انتشار و يظهر عندما لا تستطيع الدالة العثور على النتيجة الصحية و هنا يجب التاكد من صحة المعادلة المكتوبة و التاكد من وجود النتيجة فعلا في الجدول يمكن  استخدام الدالة IFERROR للتعامل مع هذه النوعية من الخطأ :

   =IFERROR(VLOOKUP(H2, A1:F20, 4, FALSE), "غير موجود")

2. خطأ #REF!:
   يظهر هذا الخطأ اذا كان العمود المستهدف في البحث خارج نطاق البحث المحدد في الجدول  الحل: التأكد من أن col_index_num ضمن حدود النطاق المحدد و تاكد من حدود النطاق بشكل صحيح
3. نتائج غير صحيحة في حالة استخدام المطابقة التقريبية :
   قد يكون نتيجة الدالة غير صحية في حالة ان البيانات غير مرتبة ترتيب تصاعدي بشكل صحيح في حالة المطابقة غير التامية المطابقة التقريبية فهنا يجب التاكد من صحة الترتيب للبيانات في العمود الاول
4. تثبيت الخلايا المستخدمة في الدالة بشكل غير صحيح:
   قد يحدث أيضا اخطاء اذا تم تثبيت الخلايا بشكل غير صحيح في الغالب يكون للدالة نتيجه صحيحة في الخلية الاولي و عند سحب النتيجة الي خلايا اخري يتغير الخلايا و تظهر النتيجهة غير صحية و هنا يجب التاكد من الخلاية المثبتة و المستخدمة في خلية اخري
5. المسافات المخفية أو الأحرف غير المرئية او عدم تطابق أنواع البيانات (الرقم مقابل النص)
6. 
   فقد يكون هناك بعض المسافات في النص او الارقام غير مرئية مما يسبب خطاء او ان يسبب ان البيانات الرقمية يفهمها الاكسيل علي انها بيانات نصية فيقوم بارجاع اخطاء لانة لم يصل الي النتجة الصحية

قيود دالة VLOOKUP

هناك بعض الملاحظات علي استخدام الدالة 

• البحث من اليسار إلى اليمين فقط: حيث لا تستطيع الدالة البحث عن قيمة في عمود علي اليمين لعمود القيمة المرجعية في الحالات العادية يمكن تطبيق هذة الفكرة بعمل جدول افتراضي داخل الدالة بفكره سوف نتناولها بشي من التفصيل لاحقا 
• إرجاع أول تطابق فقط: ان الدالة تقوم بارجع اول نتيجة فقط حتي اذا تكررت القيمة في العمود فلن يظهر الا النتيجة الاولي فقط
• حساسية للأداء: مع البيانات الكبيرة و الضخمة يسبب هذة الدالة بطء في الاداء و خاصا اذا تم استخدام هذه الدالة بشكل مركب مع دالة اخري 

للاسباب السابقة قامت شركة ميكروسوفت بتطوير الدالة ، ظهرت بدائل أكثر اخري مثل الدالة XLOOKUP في الإصدارات الحديثة من Excel

=VLOOKUP(E2, CHOOSE({1,2}, B:B, A:A), 2, FALSE)

=VLOOKUP("A", IF($B$2:$B$10>1000, $A$2:$C$10), 3, FALSE)

خاتمة

تعتبر الدالة VLOOKUP دالة هامة لاي محاسب حيث انها دالة مرنة يمكن تطويرها في عمليات البحث المتقدم و الديناميكي بشكل متقدم حيث يمكن دمج العديد من الدوال معها لتكون اكثر مرونة حيث يمكن دمج معها دوال كثيرة مثل COLUMN , ROW, MATCH في تحديد عمود البحث بشكل مرن اكثر فهي دالة لا تساعد في عمليات الربط بين الجدول و العديد من افكار البحث المرنة يمكن استخدام الدالة في عمليات مطابقة البيانات و خاصا كشف الحساب في ازالة التكرار البحث المتقدم و العديد من الافكار