كيفية استخدام دالة XLOOKUP في Microsoft Excel

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

ما هو XLOOKUP؟

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

في الوقت الحالي ، يتوفر XLOOKUP فقط للمستخدمين في برنامج المطلعين. يمكن لأي شخص الانضمام إلى برنامج Insiders للوصول إلى أحدث ميزات Excel بمجرد توفرها. ستبدأ Microsoft قريبًا في طرحه لجميع مستخدمي Office 365.

كيفية استخدام وظيفة XLOOKUP

دعنا نتعمق بشكل مباشر مع مثال على XLOOKUP أثناء العمل. خذ مثال البيانات أدناه. نريد إرجاع القسم من العمود F لكل معرّف في العمود A.

هذا مثال كلاسيكي لبحث المطابقة التامة. تتطلب وظيفة XLOOKUP ثلاث أجزاء فقط من المعلومات.

تُظهر الصورة أدناه XLOOKUP بستة وسيطات ، لكن الثلاثة الأولى فقط ضرورية لمطابقة تامة. لذلك دعونا نركز عليهم:

• Lookup_value: ما الذي تبحث عنه.
• Lookup_array: أين تبحث.
• Return_array: النطاق الذي يحتوي على القيمة المراد إرجاعها.

ستعمل الصيغة التالية مع هذا المثال: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

دعنا الآن نستكشف بعض المزايا التي يتمتع بها XLOOKUP على VLOOKUP هنا.

لا مزيد من رقم فهرس العمود

كانت الوسيطة الثالثة سيئة السمعة لـ VLOOKUP هي تحديد رقم عمود المعلومات التي سيتم إرجاعها من صفيف جدول. لم تعد هذه مشكلة لأن XLOOKUP يمكّنك من تحديد النطاق الذي تريد العودة منه (العمود F في هذا المثال).

ولا تنس أن XLOOKUP يمكنه عرض البيانات الموجودة على اليسار من الخلية المحددة ، على عكس VLOOKUP. المزيد عن هذا أدناه.

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

المطابقة التامة هي الافتراضي

كان الأمر محيرًا دائمًا عند تعلم VLOOKUP لماذا كان عليك تحديد المطابقة التامة.

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

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

يمكن أن ينظر XLOOKUP إلى اليسار

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

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

في المثال أدناه ، نحتاج إلى البحث عن معرف (العمود E) وإرجاع اسم الشخص (العمود D).

يمكن أن تحقق الصيغة التالية ذلك: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

ماذا تفعل إذا لم يتم العثور عليها

مستخدمو وظائف البحث على دراية كبيرة برسالة الخطأ # N / A التي ترحب بهم عندما يتعذر على VLOOKUP أو وظيفة MATCH الخاصة بهم العثور على ما يحتاج إليه. وغالبًا ما يكون هناك سبب منطقي لذلك.

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

يأتي XLOOKUP مع وسيطته المضمنة "إذا لم يتم العثور عليها" للتعامل مع مثل هذه الأخطاء. دعنا نراها في العمل مع المثال السابق ، ولكن مع معرف مكتوب بشكل خاطئ.

ستعرض الصيغة التالية النص "معرّف غير صحيح" بدلاً من رسالة الخطأ: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID")

استخدام XLOOKUP للبحث عن نطاق

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

هذه المرة نحن لا نبحث عن قيمة محددة. نحتاج إلى معرفة مكان تواجد القيم الموجودة في العمود B ضمن النطاقات الموجودة في العمود E. وهذا سيحدد الخصم المكتسب.

يحتوي XLOOKUP على وسيطة خامسة اختيارية (تذكر أنه يتم تعيينه افتراضيًا على المطابقة التامة) يسمى وضع المطابقة.

يمكنك أن ترى أن XLOOKUP لديه إمكانيات أكبر مع التطابقات التقريبية من تلك الخاصة بـ VLOOKUP.

يوجد خيار للعثور على أقرب تطابق أصغر من (-1) أو أقرب تطابق أكبر من (1) القيمة التي تم البحث عنها. يوجد أيضًا خيار لاستخدام أحرف البدل (2) مثل؟ أو ال *. هذا الإعداد ليس قيد التشغيل افتراضيًا كما كان مع VLOOKUP.

تُرجع الصيغة في هذا المثال أقرب قيمة أقل من القيمة التي تم البحث عنها إذا لم يتم العثور على تطابق تام: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)

ومع ذلك ، يوجد خطأ في الخلية C7 حيث تم إرجاع الخطأ # N / A (لم يتم استخدام الوسيطة "إذا لم يتم العثور"). من المفترض أن يؤدي هذا إلى إرجاع خصم بنسبة 0٪ لأن الإنفاق 64 لا يصل إلى معايير أي خصم.

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

أدخل صفًا جديدًا في أسفل جدول البحث ثم افتح الصيغة. قم بتوسيع النطاق المستخدم عن طريق النقر على الزوايا وسحبها.

الصيغة تصحح الخطأ على الفور. إنها ليست مشكلة في وجود "0" في أسفل النطاق.

أنا شخصياً ما زلت أفرز الجدول حسب عمود البحث. وجود "0" في الجزء السفلي قد يدفعني إلى الجنون. لكن حقيقة أن الصيغة لم تنكسر هي حقيقة رائعة.

يستبدل XLOOKUP وظيفة HLOOKUP أيضًا

كما ذكرنا ، فإن وظيفة XLOOKUP موجودة هنا أيضًا لاستبدال HLOOKUP. وظيفة واحدة لتحل محل اثنين. ممتاز!

وظيفة HLOOKUP هي البحث الأفقي ، وتستخدم للبحث على طول الصفوف.

ليس معروفًا جيدًا مثل شقيقه VLOOKUP ، ولكنه مفيد لأمثلة مثل أدناه حيث توجد الرؤوس في العمود A ، والبيانات موجودة على طول الصفين 4 و 5.

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

في هذا المثال ، تُستخدم الصيغة لإرجاع قيمة المبيعات المتعلقة بالاسم الموجود في الخلية A2. يبحث على طول الصف 4 للعثور على الاسم ، ويعيد القيمة من الصف 5: =XLOOKUP(A2,B4:E4,B5:E5)

يمكن أن ينظر XLOOKUP من الأسفل إلى الأعلى

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

في المثال أدناه ، نود العثور على مستوى المخزون لكل منتج في العمود A.

جدول البحث بترتيب التاريخ ، وهناك عدة شيكات مخزون لكل منتج. نريد إعادة مستوى المخزون من آخر مرة تم فحصه فيها (آخر ظهور لمعرف المنتج).

توفر الوسيطة السادسة للدالة XLOOKUP أربعة خيارات. نحن مهتمون باستخدام خيار "البحث من آخر إلى أول".

تظهر الصيغة المكتملة هنا: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)

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

جمع الشمل

وظيفة XLOOKUP هي الوريثة المنتظرة بفارغ الصبر لكل من دالتي VLOOKUP و HLOOKUP.

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

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

أيام VLOOKUP مرقمة. يتوفر XLOOKUP وسيصبح قريبًا صيغة البحث الفعلية.