ربط الجداول (VLOOKUP)

ما هو VLOOKUP

تعد الوظيفة المضمنة VLOOKUP واحدة من أقوى الوظائف في Excel. إنه ينتمي إلى الثلاثة الأكثر شيوعًا في Excel — بعد SUM وAVERAGE. وتتمثل مهمتها في العثور على القيمة المطلوبة في جدول البيانات وعرضها في الخلية المحددة.

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

يتم استخدام الدالة VLOOKUP بشكل شائع للتحليل المالي وإدارة البيانات والعمل مع قواعد البيانات. باستخدام هذه الميزة، يمكن للمستخدمين العثور على المعلومات بسرعة وسهولة في مجموعة بيانات كبيرة، مما يوفر الوقت ويحسن الدقة.

تبحث الدالة VLOOKUP عن القيم عموديًا، أي بين الصفوف (يُشار إلى ذلك بالحرف الأول V - Vertical). في Excel، توجد أيضًا وظيفة مشابهة HLOOKUP، والتي لها وظيفة مشابهة، ولكنها تقوم بإجراء بحث أفقي (H - أفقي) حسب الأعمدة.

مثال على مشكلة

لنفترض أن لدينا جدولين مع البيانات - جدول القروض وجدول الضمانات :

نحتاج إلى إدراج الضمانات تلقائيًا في جدول القروض، بناءً على رقم الاتفاقية، لمزيد من التقارير.

حل

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

لذلك، دعونا نستخدم الدالة VLOOKUP . حدد الخلية التي سيتم إدخالها فيها ( ه2 ) وافتح معالج الصيغة (القائمة Inser - Function ). في الفئة (البحث والمرجع) ابحث عن الوظيفة VLOOKUP و اضغط نعم . ستظهر نافذة لإدخال وسيطات الوظيفة:

املأها واحدة تلو الأخرى:

• Lookup Value - رقم الاتفاقية الذي ستبحث عنه الوظيفة في أقصى العمود الأيسر من جدول التزويد. في حالتنا - رقم الاتفاقية "KF568" من الخلية ج2 .
• Table Array - الجدول الذي سنحصل منه على القيم المطلوبة هو جدول العرض الخاص بنا. سوف نستخدم نطاقًا كمرجع $G$2:$H$11 (النطاق ثابت بعلامة الدولار ($) ، بحيث لا ينتقل إلى الأسفل عند نسخ الصيغة).
• Column index number - الرقم التسلسلي (وليس الحرف!) للعمود في جدول المخصصات والذي سنأخذ منه اسم المخصص. يحتوي العمود الأول من جدول المخصصات على الرقم التسلسلي 1، لذلك نحتاج إلى أخذ نوع المخصص من العمود الثاني (رقم 2).
• Range Lookup - يمكننا إدخال قيمتين فقط في هذا الحقل: TRUE أو FALSE:
  
  1) إذا أدخلنا قيمة 0 أو FALSE ، فهذا يعني في الواقع أنه يُسمح بالبحث فقط مع تطابق تام أي أنه إذا لم تجد الدالة رقم الاتفاقية في جدول التوفير (إذا لم يكن موجودًا)، فسيتم عرض خطأ #غير متاح (لايوجد بيانات).
  
  2) إذا أدخلنا قيمة 1 أو TRUE ، وهذا يعني أنه يُسمح بالبحث ليس فقط بشكل دقيق، ولكن أيضًا صدفة تقريبية . في معظم الحالات، يمكن أن يؤدي هذا الاستبدال التقريبي إلى نتيجة خاطئة عندما VLOOKUP سوف تحل محل قيم الاتفاقيات الأخرى. لذلك، يجب عليك استخدام البحث التقريبي فقط عن القيم الرقمية (مرتبة مسبقًا بترتيب تصاعدي).

يبقى للضغط نعم وانسخ الوظيفة المدخلة عبر العمود بأكمله.

عيوب الدالة VLOOKUP

العيب الرئيسي هو أن البحث عن القيمة المطلوبة لا يمكن أن يتم إلا في العمود الأول من النطاق المحدد، ويمكن للوظيفة إرجاع القيمة المطلوبة فقط من الأعمدة الموجودة على اليمين.

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

كما أن الدالة VLOOKUP لها حد لطول البحث يصل إلى 255 حرفًا، وإلا فسيتم إرجاع الخطأ #VALUE!

مشاكل عند العمل مع الوظيفة

وظيفة VLOOKUP إرجاع خطأ (#غير متوفر) لو:

1. يُسمح بالبحث الدقيق (الوسيطة Range Lookup=0) والقيمة التي تم البحث عنها غير موجودة في الجدول (Table) .
2. البحث التقريبي مسموح به (Range Lookup=1)، ولكن في الجدول (Table) التي نبحث فيها عن القيم، ولا يتم ترتيب الأسماء ترتيبًا تصاعديًا.
3. تنسيق الخلية التي نأخذ منها رقم المعاملة (على سبيل المثال، ج2 في حالتنا) وتنسيق الخلية للعمود الأول (ج2:ج11) تختلف الجداول (على سبيل المثال، الرقمية والنصية). يعد هذا الموقف نموذجيًا بشكل خاص عند استخدام الرموز الرقمية بدلاً من الأسماء النصية (أرقام الحسابات، ورموز التعريف، والتواريخ، وما إلى ذلك). في هذه الحالة، يمكنك استخدام الوظائف VALUE و TEXT لتحويل صيغ البيانات. تبدو هكذا:
   =VLOOKUP(TEXT(C2);$G$2:$H$11;0) .
4. لم تعثر الدالة على قيمة لأن الكود يحتوي على مسافات وأحرف غير مرئية غير قابلة للطباعة (موجزات الأشرطة، وما إلى ذلك). في هذه الحالة، يمكنك استخدام وظيفة النص (TRIM) و (CLEAN) لإزالتها:
   =VLOOKUP(TRIM(CLEAN(C2));$G$2:$H$11;0) .

قمع الخطأ

لمنع رسالة الخطأ (#غير متوفر) ، عندما لا تتمكن الوظيفة من العثور على تطابق تام، يمكنك استخدام الوظيفة IFERROR . تتحقق هذه الوظيفة من وجود خطأ (#غير متوفر) هي نتيجة العمل VLOOKUP ، وإذا كان الأمر كذلك، فسيتم إخراج شريط فارغ ( "" ) أو صفر، إن لم يكن - نتيجة العمل VLOOKUP .

مقالات حول الموضوع:

• وظيفة محسنة VLOOKUP (VLOOKUP2)
• VLOOKUP للدمج بشرطين (VLOOKUP3)
• الدمج السريع للجداول الكبيرة (VLOOKUP2D)