صيغة XLOOKUP

يُعد XLOOKUP البديل العصري لـ VLOOKUP وHLOOKUP. فهو يعثر على قيمة في عمود أو صف، ويُرجع القيمة المطابقة من عمود أو صف آخر.

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

يتوفر XLOOKUP في Excel 365 وExcel 2021 وGoogle Sheets.

تحتاج XLOOKUP في أبسط صورة إلى ثلاث معلومات فقط:

• lookup_value - ما تبحث عنه
• lookup_array - النطاق الذي ستبحث فيه
• return_array - النطاق الذي ستستخرج منه الإجابة

اقرأ الصيغة على النحو التالي: اعثر على هذه القيمة في هذا العمود، وأعطني العنصر المطابق من ذلك العمود.

تخيّل قائمة منتجات. يحتوي العمود A على أسماء المنتجات، ويحتوي العمود B على الأسعار. وتريد معرفة سعر "Keyboard".

تبحث عن الاسم في A2:A20 وتُرجع السعر من B2:B20. تفحص XLOOKUP نطاق البحث، وتعثر على "Keyboard"، ثم تُرجع القيمة الموجودة في الصف نفسه من نطاق الإرجاع.

يُعد تثبيت "Keyboard" داخل الصيغة يدوياً أمراً هشاً. ومن الأفضل بكثير توجيه قيمة البحث إلى خلية.

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

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

إذا كان A2:A20 يتكون من 19 صفاً، فيجب أن يتكون نطاق الإرجاع أيضاً من 19 صفاً. يؤدي عدم التطابق إلى ظهور خطأ #VALUE!، لأن XLOOKUP لا يستطيع مطابقة الصفوف بشكل صحيح.

لا يلزم أن يكون نطاق الإرجاع مجاوراً لنطاق البحث. لنفترض أن الأسماء في العمود A، لكنك تريد عدد المخزون في العمود E.

ما عليك سوى توجيه نطاق الإرجاع إلى E2:E20. لا يهتم XLOOKUP بالمسافة بين الأعمدة، بخلاف VLOOKUP الذي يعدّ الأعمدة باتجاه اليمين.

من أكثر مزايا XLOOKUP سهولةً أنه يبحث عن تطابق تام افتراضياً.

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

يتعامل XLOOKUP أيضاً مع البيانات الأفقية. فإذا كانت أسماء الأشهر تمتد عبر الصف 1 وكانت المبيعات في الصف 2، يمكنك البحث في الصف بدلاً من العمود.

وتعمل البنية نفسها للصيغة: قيمة البحث، وصف الأشهر، وصف المبيعات. إذ تحل دالة واحدة محل كل من VLOOKUP وHLOOKUP.

بما أن XLOOKUP يُرجع قيمة واحدة، يمكنك إدراجه مباشرةً في عملية حسابية.

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

يمكنك استخدام مراجع الأعمدة الكاملة مثل A:A وB:B حتى تظل الصيغة تعمل عند إضافة صفوف جديدة.

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

بالمقارنة مع VLOOKUP، يتميز XLOOKUP بأنه:

• غير مقيّد بالاتجاه - يمكن أن تكون أعمدة الإرجاع إلى يسار نطاق البحث أو يمينه
• أكثر أماناً - يستخدم التطابق التام افتراضياً
• أكثر استقراراً - لا يؤدي إدراج أعمدة إلى تعطله
• أبسط - لا حاجة إلى عدّ فهارس الأعمدة

بمجرد تعلم الوسائط الأساسية الثلاثة، تصبح معظم عمليات البحث اليومية سهلة للغاية.

اختبر فهمك لبنية XLOOKUP الأساسية.

تعلمت الوسائط الأساسية الثلاثة لـ XLOOKUP:

• lookup_value - ما يجب العثور عليه
• lookup_array - مكان البحث
• return_array - ما يجب إرجاعه

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