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

ما هو VLOOKUP

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

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

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

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

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

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

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

حل

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

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

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

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

1) إذا أدخلنا القيمة 0 أو FALSE فهذا يعني في الواقع أن البحث مسموح فقط مع تطابق تامأي أنه إذا لم تجد الوظيفة رقم الاتفاقية في جدول التوفير (إذا لم يكن موجودًا)، فسيتم عرض الخطأ #N/A (لا توجد بيانات).

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

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

عيوب الدالة VLOOKUP

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

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

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

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

ترجع الدالة VLOOKUP خطأ (#N/A) إذا:

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

قمع الخطأ

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

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