دليل مبسط لوظائف VLOOKUP وXLOOKUP في LibreOffice Calc

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

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

ما الذي يجب أن تعرفه قبل استخدام دوال البحث؟

تبحث عملية البحث في نطاق معين عن مفتاح، مثل معرّف المنتج، ثم تُعيد قيمة ذات صلة، مثل سعر المنتج. قد يكون مفتاح البحث نصًا أو رقمًا أو تاريخًا أو مرجع خلية.

في دليل LibreOffice الحالي، يُوثَّق XLOOKUP كبديل حديث لـ VLOOKUP وHLOOKUP وLOOKUP. يتوفر XLOOKUP منذ إصدار LibreOffice 24.8. يوثِّق دليل LibreOffice 26.8 الحالي المطابقة التامة والتقريبية، والمطابقة باستخدام الأحرف البديلة والتعبيرات النمطية، والبحث العكسي، والبحث الثنائي. راجع توثيق LibreOffice الرسمي لـ XLOOKUP .

من التفاصيل التي قد تُفاجئ مستخدمي مايكروسوفت إكسل: أن أمثلة الصيغ في ليبر أوفيس تستخدم عادةً الفواصل المنقوطة بين الوسائط. على سبيل المثال، =XLOOKUP(F2;A2:A6;D2:D6)قد يُكيّف برنامج كالكَل الفواصل وفقًا للغة المستخدمة، لذا اتبع الفاصل الذي يدعمه برنامجك.

نظرة سريعة على الفرق بين VLOOKUP و XLOOKUP

قم بإعداد طاولة تدريب صغيرة

أنشئ الأعمدة من A إلى D مع العناوين التالية: معرّف المنتج ، اسم المنتج ، الفئة ، والسعر . أضف بعض المعرّفات مثل P001 إلى P005. ضع المعرّف الذي تريد البحث عنه في الخلية F2، وضع الصيغة في الخلية G2.

تبدأ الصيغ في برنامج Calc بعلامة يساوي. يمكنك كتابتها مباشرة في خلية أو في شريط الصيغة؛ ويشرح دليل إدخال الصيغ الرسمي لبرنامج LibreOffice كلا الطريقتين.

الخطوة 1: استخدم دالة VLOOKUP لإجراء مطابقة دقيقة أساسية

لنفترض أن P004 موجود في F2 وتريد السعر من العمود D. أدخل:

=VLOOKUP(F2;$A$2:$D$6;4;0)

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

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

الخطوة الثانية: استخدم دالة XLOOKUP عندما يكون عمود الإرجاع على اليسار

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

تفصل دالة XLOOKUP مصفوفة البحث عن مصفوفة النتائج . للعثور على P003 في العمود C وإرجاع اسم المنتج من العمود A، استخدم:

=XLOOKUP(F2;$C$2:$C$6;$A$2:$A$6;"Not found")

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

الخطوة 3: معالجة المعرفات المفقودة دون إظهار #N/A

إذا أدخل المستخدم معرّفًا غير موجود، فإن البحث الدقيق العادي ينتج عنه عادةً خطأ #N/A. في دالة XLOOKUP، ضع نتيجةً مناسبةً في الوسيط الرابع.

=XLOOKUP(F2;$A$2:$A$6;$D$2:$D$6;"Not found";0)

يطلب وضع 0المطابقة صراحةً مطابقةً تامة، مع أن المطابقة التامة هي الإعداد الافتراضي لدالة XLOOKUP. ومع ذلك، قد يكون من المفيد كتابة هذا الوضع عندما تريد أن يكون الغرض من الصيغة واضحًا لمن يراجع ورقة العمل.

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

الخطوة الرابعة: ابحث من الأسفل للحصول على أحدث سجل مطابق

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

لإرجاع المبلغ من الصف الأخير الذي يحتوي على رقم تعريف العميل في الخلية F2، استخدم ما يلي:

=XLOOKUP(F2;$A$2:$A$8;$D$2:$D$8;"Not found";0;-1)

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

متى يجب عليك استخدام INDEX و MATCH؟

قبل ظهور دالة XLOOKUP في برنامج Calc 24.8، كان البديل المرن الشائع هو استخدام دالتي INDEX وMATCH . تُحدد دالة MATCH الموضع النسبي للمفتاح، بينما تُعيد دالة INDEX القيمة الموجودة في ذلك الموضع. على سبيل المثال:

=INDEX($D$2:$D$6;MATCH(F2;$A$2:$A$6;0))

يظل هذا مفيدًا عند الحاجة إلى التوافق مع إصدارات LibreOffice القديمة أو عندما يستخدم مصنف موجود بالفعل دالتي INDEX/MATCH بشكل متسق. وتوثق المراجع الرسمية لوظائف جداول البيانات كلاً من INDEX وMATCH .

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

أخطاء شائعة يجب تجنبها

اترك الوسيط الأخير لدالة VLOOKUP فارغًا عندما تحتاج إلى تطابق تام

عند استخدام دالة VLOOKUP، يؤدي حذف الوسيط الأخير إلى تفعيل خاصية البحث عن نطاق مُرتب. بالنسبة لمعرفات المنتجات، وأرقام الموظفين، ورموز SKU، وغيرها من المعرفات المنفصلة، ​​استخدم القيمة 00 أو FALSE للحصول على تطابق تام، إلا إذا كان التطابق التقريبي جزءًا مقصودًا من تصميمك.

حساب فهرس عمود VLOOKUP الخاطئ

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

استخدام نطاقات ذات أطوال مختلفة في دالة XLOOKUP

يجب أن تتطابق مصفوفة البحث ومصفوفة النتائج صفًا بصف أو عمودًا بعمود. يُعد نطاق البحث مثل A2:A100 المقترن بـ D2:D90 خطأً في التصميم لأن المواضع المتناظرة لا تغطي السجلات نفسها.

استخدام البحث الثنائي على البيانات غير المصنفة

يتوقع وضع البحث 2 في دالة XLOOKUP ترتيبًا تصاعديًا، بينما يتوقع الوضع -2 ترتيبًا تنازليًا. وتحذر وثائق LibreOffice صراحةً من أن البيانات غير المرتبة قد تُنتج نتائج غير صحيحة. إذا لم تتحكم في الترتيب، فاستخدم وضع البحث الأمامي أو العكسي العادي.

الخلط بين الأحرف البديلة والتعبيرات النمطية

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

ما هي دالة البحث التي ينبغي على المبتدئ اختيارها؟

لإنشاء جدول بيانات جديد يعمل على LibreOffice 24.8 أو إصدار أحدث، ابدأ باستخدام دالة XLOOKUP. فنطاقات البحث والنتائج المنفصلة فيها أسهل في القراءة، والمطابقة التامة هي الوضع الافتراضي، ونتائج البحث على الجانب الأيسر واضحة ومباشرة، كما أن خيارات البحث عن القيم غير الموجودة والبحث العكسي المدمجة تقلل من الحاجة إلى حلول بديلة باستخدام الصيغ.

استخدم دالة VLOOKUP عند صيانة مصنف موجود مبني عليها، أو عند مشاركته مع بيئات يكون فيها دعم دالة XLOOKUP غير مؤكد، أو عند العمل مع جدول بسيط وثابت حيث لا تهم قيوده. استخدم INDEX/MATCH عندما يكون التوافق مع إصدارات LibreOffice القديمة مهمًا أو عندما يتبع المصنف هذا النمط بالفعل.

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