مقارنة بين دالة VLOOKUP ودالة XLOOKUP في الآلة الحاسبة: اختر صيغة البحث المناسبة

استخدم دالة XLOOKUP لمعظم عمليات البحث الجديدة إذا كان سيتم فتح المصنف في LibreOffice 24.8 أو إصدار أحدث؛ فهي تستخدم المطابقة التامة افتراضيًا وتتيح لك اختيار نطاقات بحث ونتائج منفصلة. استخدم دالة VLOOKUP عندما تحتاج إلى التوافق مع إصدارات Calc الأقدم أو عندما تكون قيمة البحث في العمود الأول من جدول مضغوط. إذا كنت ستستخدم VLOOKUP، فاجعل وسيطها الرابع 0 أو FALSE للمطابقة التامة.

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

ما هو الفرق العملي؟

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

على سبيل المثال، لنفترض أن النطاق A2:A5 يحتوي على معرّفات المنتجات، والنطاق B2:B5 يحتوي على الأسعار. تحتوي الخلية F2 على المعرّف المطلوب البحث عنه. في برنامج Calc، صيغة VLOOKUP للبحث عن التطابق التام هي:

=VLOOKUP(F2;A2:B5;2;0)

صيغة XLOOKUP المكافئة هي:

=XLOOKUP(F2;A2:A5;B2:B5;"Not found")

كلاهما يُعيد السعر المُطابق. يعرض برنامج Calc عادةً الفواصل المنقوطة بين وسائط الدوال وفقًا لإعدادات اللغة؛ إذا كانت الصيغ في برنامجك تستخدم الفواصل، فاستخدم الفاصل الذي يتوقعه Calc. تستخدم الأمثلة نتائج خلية واحدة ونطاقات بحث أحادية البُعد.

متى يجب عليك اختيار دالة XLOOKUP؟

اختر دالة XLOOKUP لمصنف جديد عندما تعلم أن المستلمين يستخدمون LibreOffice 24.8 أو إصدارًا أحدث. أصبحت هذه الدالة متاحة في LibreOffice 24.8، وفقًا لصفحة المساعدة الرسمية لدالة XLOOKUP . وضع المطابقة الافتراضي فيها هو المطابقة التامة، لذا فإن المفتاح المفقود لا يُرجع قيمة قريبة تلقائيًا.

نمط الدالة هو =XLOOKUP(search_criterion;search_array;result_array;[if_not_found];[match_mode];[search_mode]). تحدد الوسائط الثلاثة الأولى المفتاح، والنطاق أحادي البعد المراد البحث فيه، والنطاق المراد إرجاعه. أما الوسيط الرابع، مثل ، "Not found"فيحل محل النتيجة #N/A المعتادة عند عدم وجود تطابق.

أعد قيمة من عمود على اليسار

لا يمكن لدالة VLOOKUP إرجاع قيمة تقع على يسار عمود الجدول الأول. بينما يمكن لدالة XLOOKUP ذلك، لأن نطاقي البحث والنتيجة مستقلان. على سبيل المثال، إذا كانت المعرفات موجودة في النطاق C2:C100 وأسماء الأقسام في النطاق A2:A100، فاستخدم:

=XLOOKUP(F2;C2:C100;A2:A100;"ID not found")

يُجنّب هذا التصميم أيضًا مشكلة فهرسة الأعمدة غير الدقيقة. فإذا أُضيف عمود إلى جدول VLOOKUP، فقد يُشير الفهرس المُضمّن إلى حقل مختلف. أما نطاق نتائج XLOOKUP الصريح فيُسهّل فحص عمود الإرجاع المقصود.

أعد آخر إدخال مطابق

عند وجود مفاتيح مكررة وترغب في الحصول على آخر تطابق، تدعم دالة XLOOKUP البحث العكسي في وضع البحث الخاص بها. أما بالنسبة للنطاق الرأسي، -1فيبحث الوسيط الأخير من الأخير إلى الأول.

=XLOOKUP(F2;A2:A100;B2:B100;"Not found";0;-1)

يُعيد هذا الخيار النتيجة المرتبطة بآخر ظهور مُطابق تمامًا. استخدمه فقط عندما يكون لترتيب الصفوف معنى مُحدد، مثل ظهور أحدث السجلات في النهاية. إذا كان ينبغي التعامل مع التكرارات على أنها خطأ، فقم بتنظيف البيانات أو التحقق من صحتها بدلًا من اختيار أحد الظهورات ضمنيًا.

متى يكون VLOOKUP هو الخيار الأفضل؟

احتفظ بوظيفة VLOOKUP عند الحاجة لفتح الملف في إصدار قديم من LibreOffice لا يتضمن وظيفة XLOOKUP، أو عندما قد لا يتعرف التطبيق المستهدف على وظيفة XLOOKUP. يشرح دليل Calc الرسمي وسيطات الجدول والفهرس لوظيفة VLOOKUP، بالإضافة إلى وسيطة النطاق المُرتّب الاختيارية؛ راجع مرجع وظائف جداول البيانات الرسمي .

للحصول على تطابق تام، يكون النمط كالتالي =VLOOKUP(key;table;column_index;0): . يُحسب الفهرس عدد الأعمدة من الحافة اليسرى للجدول المُحدد: 1 يعني العمود الأول، و2 يعني العمود الثاني. يجب أن يكون مفتاح البحث في ذلك العمود الأول، ويجب أن يكون الحقل المُعاد على يمينه.

الوسيط الرابع مهم. إذا حذفته أو عيّنته إلى TRUE، فإن دالة VLOOKUP تستخدم المطابقة التقريبية وتتوقع أن يكون العمود الأول مُرتبًا تصاعديًا. قد يكون هذا السلوك مفيدًا لنطاقات مثل شرائح الضرائب، حيث يُمثل كل صف حدًا أدنى. ولكنه غير مناسب للمعرفات والأسماء والقوائم غير المُرتبة. في هذه الحالة، حدد 0 أو FALSE.

كيف تتم مقارنة سلوك التطابق وسلوك الخطأ؟

• المطابقة التامة: يستخدم XLOOKUP المطابقة التامة افتراضيًا. يتطلب VLOOKUP تعيين الوسيط الرابع إلى 0 أو FALSE لضمان مطابقة تامة.
• المفتاح المفقود: يمكن لدالة XLOOKUP إرجاع رسالة أو قيمة محددة في وسيطها الرابع. عادةً ما تُرجع دالة VLOOKUP القيمة #N/A؛ استخدم IFNA إذا كنت بحاجة إلى رسالة مخصصة.
• المطابقة التقريبية: يدعم كلا الخيارين المطابقة القائمة على النطاق، ولكن يجب أن تتوافق البيانات والوضع المحدد مع القاعدة المقصودة. رتب البيانات حسب الحاجة وتحقق من الحالات الحدية.
• اتجاه البحث: يمكن لدالة XLOOKUP البحث من الأسفل باستخدام وسيطة وضع البحث. أما دالة VLOOKUP فتعيد أول صف مطابق عند استخدامها بشكل طبيعي من الأعلى إلى الأسفل.

لتقديم استجابة مخصصة للمفتاح المفقود باستخدام دالة VLOOKUP، استخدم =IFNA(VLOOKUP(F2;A2:B5;2;0);"Not found"). هذا يُبقي إعداد المطابقة التامة ظاهرًا ويتعامل فقط مع خطأ عدم التوفر. تجنب تضمين الصيغة في معالج أخطاء عام إلا إذا كنت ترغب أيضًا في إخفاء مشاكل أخرى غير ذات صلة، مثل مرجع نطاق غير صالح.

ما الذي يجب عليك التحقق منه قبل مشاركة ملف عمل؟

تشير تعليمات LibreOffice إلى أن دالة XLOOKUP متاحة منذ الإصدار 24.8. كما توضح أن XLOOKUP ليست جزءًا من معيار OpenFormula الخاص بـ OpenDocument الإصدار 1.3، وتُحدد مساحة اسم دالة من Microsoft لضمان التوافق. هذا يعني أن تنسيق الملف وحده لا يضمن أن جميع تطبيقات جداول البيانات القديمة أو التابعة لجهات خارجية ستحسب هذه الدالة. في بيئات البرامج المختلطة، يُنصح باختبار المصنف في أقدم تطبيق مُستهدف أو استخدام صيغة مُثبتة مثل VLOOKUP للمطابقة التامة حيثما يتناسب تنسيقها.

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

كيف يمكنك التحقق من النتيجة؟

1. اختبر وجود مفتاح وقارن القيمة المُعادة مع صف المصدر الخاص به.
2. اختبر مفتاحًا غير موجود. تأكد من أن دالة XLOOKUP تعرض القيمة الاحتياطية التي اخترتها، أو أن دالة VLOOKUP تُرجع الرسالة المتوقعة #N/A أو IFNA.
3. للتأكد من التطابق التام باستخدام دالة VLOOKUP، تأكد من أن الوسيط الأخير يساوي 0 أو FALSE. إذا كنت تستخدم التطابق التقريبي عمدًا، فتحقق من ترتيب الفرز والقيم الموجودة أعلى وأسفل وأسفل الحدود مباشرةً.
4. بالنسبة لدالة XLOOKUP مع عمود إرجاع إلى اليسار أو بحث عكسي، اختبر مفتاحًا بنتيجة معروفة، وعند وجود تكرارات، تأكد من أي ظهور يجب إرجاعه.
5. احفظ المصنف وأعد فتحه في أقدم إصدار من برنامج Calc أو تطبيق الجداول الإلكترونية الذي يستخدمه المستلمون. لا يعني ظهور نتيجة صحيحة على جهازك أن إصدارًا أقدم يدعم نفس الوظيفة.

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