دالة XLOOKUP مقابل VLOOKUP في العمل المالي: أيهما تستخدم ولماذا
الموقف
لدى دلتا تك للخدمات دفتر أستاذ عام من 400 حساب، ويحتاج أدهم إلى ربط كل حساب ببند في قائمة الأرباح والخسائر الإدارية قبل صدور الحزمة. عند فريدة جدول الربط في ورقة، ومستخرج الدفتر في ورقة أخرى. وهناك اثنا عشر حسابا فُتحت الشهر الماضي ولم تدخل الجدول بعد.
تستخدم فريدة VLOOKUP منذ الجامعة. ويقول لها زميل إن عليها التحول إلى XLOOKUP. يشرح هذا المقال ما الذي يتغير وما الذي لا يتغير، ومتى تبقى الدالة القديمة هي الخيار الصحيح.
الشرح
تجد الدالتان قيمة في مكان وتعيدان قيمة مرتبطة بها من مكان آخر. والفروق مهمة في العمل المالي.
الصياغة. بحسب توثيق مايكروسوفت، تأخذ VLOOKUP قيمة البحث ونطاق الجدول ورقم العمود ونوع المطابقة الاختياري. وتأخذ XLOOKUP قيمة البحث ونطاق البحث ونطاق الإرجاع، ووسائط اختيارية للقيمة المعروضة عند عدم الوجود ووضع المطابقة ووضع البحث.
المطابقة الافتراضية. إذا حذفت الوسيطة الأخيرة من VLOOKUP فإنها تجري مطابقة تقريبية، وهي الطريقة الافتراضية. وهذا خطر مع رموز الحسابات، لأن مايكروسوفت تذكر أن المطابقة التقريبية تفترض أن العمود الأول مرتب، فقد يعيد رمز غير موجود في الجدول أقرب رمز أصغر، أو جوابا خاطئا إن لم يكن الجدول مرتبا، دون أي تحذير. عليك أن تكتب القيمة المنطقية الخاطئة (أو 0) في كل مرة لتحصل على مطابقة تامة. أما وضع المطابقة الافتراضي في XLOOKUP فهو التام، وتعيد خطأ إذا لم يوجد تطابق.
الاتجاه. تبحث VLOOKUP عن القيمة في العمود الأول من الجدول وتعيد من عمود إلى يمينه. وتفصل XLOOKUP نطاق البحث عن نطاق الإرجاع، فيمكن أن تقع النتيجة يسار المفتاح أو يمينه، ويمكن أن يجري البحث من الأخير إلى الأول.
أرقام الأعمدة. تطلب VLOOKUP رقم عمود. وإذا أدخل أحدهم عمودا في الجدول قد يشير الرقم إلى بيانات خاطئة وتظل الصيغة تعيد جوابا. أما XLOOKUP فتشير إلى نطاق، فتتبع البيانات.
المفاتيح المفقودة. في XLOOKUP وسيطة مدمجة للنص الذي يظهر عند عدم وجود تطابق. وبهذا تعنون النتيجة بأنها غير مربوطة بدل إخفاء المشكلة.
الإصدارات. تذكر صفحة مايكروسوفت أن XLOOKUP غير متاحة في Excel 2016 وExcel 2019، وتذكر مايكروسوفت 365 وExcel 2021 وExcel 2024 ضمن الإصدارات التي تدعمها. ويمكن فتح مصنف فيه XLOOKUP أنشئ في إصدار أحدث داخل إصدار أقدم، لكن قد تظهر الصيغة أخطاء. تحقق من إصدارك من ملف ثم حساب، وتحقق مما يستخدمه زملاؤك قبل أن تشارك نموذجا.
فالقاعدة بسيطة: استخدم XLOOKUP حين يملكها كل من يفتح الملف. وأبقِ VLOOKUP، بمطابقة تامة صريحة، للملفات التي يجب أن تُفتح في إصدارات أقدم.
مثال عملي
في دفتر دلتا تك 400 حساب. وفي جدول الربط 388 منها. والاثنا عشر الباقية جديدة.
مع VLOOKUP تحتاج الصيغة إلى رقم العمود والمطابقة التامة الصريحة. ومع XLOOKUP تسمي عمود الإرجاع وتعطي تسمية المفتاح المفقود.
=VLOOKUP(A2, Map!A:C, 3, FALSE)
=XLOOKUP(A2, Map!A:A, Map!C:C, "Unmapped")تعيد الصيغة الأولى خطأ لكل واحد من الحسابات الجديدة وعددها 12، وإذا أدخل أحدهم عمودا في الجدول فإن الرقم 3 يشير الآن إلى عمود خاطئ. وتعيد الصيغة الثانية كلمة غير مربوط للحسابات الـ 12، وتستمر في العمل بعد الإدراج.
والآن عُدّ النتيجة: 388 حسابا تعيد بند الأرباح والخسائر و12 تعيد غير مربوط. وتفحص خلية رقابية المجموع.
تفلتر فريدة الحسابات الـ 12 وترسل القائمة إلى يوسف، فيضيفها إلى الجدول. وتسويتها بسيطة ومرئية: 400 حساب، 388 مربوطا، 12 مدرجة في قائمة. ولا تغلف البحث بدالة تحول الأخطاء إلى صفر. فهذا يخفي الحسابات التي تحتاج إلى اهتمام بالضبط، وتصبح قائمة الأرباح والخسائر خاطئة دون أن يراها أحد.
الخطأ الشائع
الخطأ الشائع أن تستخدم VLOOKUP وتترك نوع المطابقة. فهي تعيد مطابقات تقريبية افتراضيا، فيمكن لرمز حساب مفقود أن يلتقط البند الخطأ بصمت.
الخطأ الثاني إخفاء الأخطاء. تغليف البحث بحيث يصبح كل فشل صفرا يجعل الورقة تبدو نظيفة والتقرير خاطئا. أظهر المفاتيح المفقودة، وعُدّها، وأصلح الجدول.
الثالث التحول إلى XLOOKUP دون التحقق ممن سيفتح الملف. نموذج مشترك يعرض أخطاء على Excel أقدم عند زميل أسوأ من نموذج يستخدم صيغة أبطأ.
قائمة المراجعة
- أستخدم المطابقة التامة ما لم أحتج إلى شرائح، وأذكر ذلك في الصيغة.
- أستخدم XLOOKUP حين يملك كل مستخدم إصدارا يدعمها.
- تعيد المفاتيح المفقودة تسمية ظاهرة، لا صفرا ولا فراغا.
- أعد الصفوف المطابقة وغير المطابقة وأطابقها مع الإجمالي.
- مفاتيح جدول البحث فريدة.
- أتحقق من إصدار Excel عند كل من يستلم الملف.
للمتابعة
شاهد الحلقة كاملة TECH-A6 البحث اللي يفرق وحمّل تمرين الربط في الدرس TECH-A6 البحث اللي يفرق.
الشركات والأرقام في هذا المقال خيالية. Masar-Fintech مستقلة وغير تابعة لأي هيئة مهنية أو جهة ضريبية.