كيفية استخدام Xlookup مع معايير متعددة في Excel

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

بناء جملة Xlookup ومثال واحد

على الرغم من أن بناء الجملة لوظيفة Xlookup يبدو معقدًا في البداية ، إلا أنه يتبع بالفعل ترتيبًا منطقيًا:

=XLOOKUP(a,b,c,d,e,f)

أين

• أ (مطلوب) هي قيمة البحث ،
• ب (مطلوب) هو صفيف البحث ،
• ج (مطلوب) هو صفيف العودة ،
• د (اختياري) هو النص المراد إرجاعه إذا كانت قيمة البحث (أ) غير موجود في صفيف البحث (ب) ،
• ه (اختياري) هو وضع المطابقة (0 = المطابقة الدقيقة (افتراضي) ، -1 = مطابقة دقيقة أو عنصر أصغر التالي ، 1 = مطابقة دقيقة أو عنصر أكبر التالي ، 2 = مطابقة البدل) ، و
• و (اختياري) هو وضع البحث (1 = الأول إلى الماضي (افتراضي) ، -1 = آخر إلى الأول ، 2 = البحث الثنائي حيث ب هو في ترتيب تصاعدي ، -2 = البحث الثنائي حيث ب في ترتيب تنازلي).

لذلك ، في المثال أدناه ، بعد اختيار معرف في القائمة المنسدلة للتحقق من صحة البيانات في الخلية E1 ، الكتابة:

=XLOOKUP(E1,T_Scores(ID),T_Scores(Score),"No match",0,1)

في الخلية E2 إرجاع درجة 51.

هذا لأنه:

• E1 هل الخلية التي تحتوي على القيمة (1323) للبحث في الجدول ،
• T_SCORES (معرف) يطلب من Excel البحث عن القيمة في عمود ID لجدول T_SCORES ،
• T_SCORES (النتيجة) يخبر Excel بإرجاع القيمة المقابلة من عمود النتيجة في نفس الجدول ،
• “لا تطابق” هو ما يجب إرجاعه إذا كانت قيمة البحث ليست في أي مكان في صفيف البحث ،
• 0 يخبر Excel بإرجاع تطابق دقيق ، و
• 1 يطلب من Excel البحث عن قيمة البحث من أعلى عمود المعرف إلى الأسفل.

مثال 1: معايير Xlookup متعددة مع منطق منطقي

تتغير كيفية استخدام وظيفة Xlookup عندما يكون لديك أكثر من معيار واحد.

تخيل أنك تملك علامة تجارية تبيع ستة مشروبات في ستة بلدان مختلفة ، وقمت بتعيين مدير لكل مشروب في كل بلد.

هدفك هو إنشاء بحث يتيح لك إدخال مشروب و بلد ، ويعيد اسم المدير وعدد المبيعات.

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

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

=XLOOKUP(
1,
(T_Managers(Drink)=G1)*(T_Managers(Country)=G2),
T_Managers((Manager):(Sales)),
"No result"
)

دعونا نلقي نظرة أولاً على الحجة ب، صفيف البحث:

(T_Managers(Drink)=G1)*(T_Managers(Country)=G2)

أولاً ، تتحقق الصيغة من عمود مشروب جدول T_Managers لمباراة مقابل القيمة في G1 ، وهي القهوة. نتيجة لذلك ، فإنه يعيد الصفيف المؤقت التالي:

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE...}

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

بعد ذلك ، يتحقق عمود البلد في نفس الجدول لمباراة مقابل القيمة في G2 ، وهي إسبانيا ، عودة مؤقتًا:

{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE...}

منذ القيم الخامسة والحادية عشرة تتطابق مع معيار البلد. إليك ما يحسبه Excel في الخلفية:

الآن ، نظرًا لأننا قمنا بضرب المصفمين المؤقتين ، فإن هذه العملية تحول القيم الحقيقية إلى 1S والقيم الخاطئة إلى 0S:

{0;0;0;0;0;0;1;1;1;1;1;1;0...}

و

{0;0;0;0;1;0;0;0;0;0;1;0...}

هذا ينشئ صفيف بحث واحد ، حيث يتم ضرب الأرقام الأولى من كل صفيف ، يتم ضرب الأرقام الثانية من كل صفيف ، وهكذا.

{0;0;0;0;0;0;0;0;0;0;1;0...}

لذلك ، فإن الاقتران الحادي عشر هو الأول الذي يعود 1.

الآن ، دعنا نعود إلى الجدال أ، قيمة البحث:

=XLOOKUP(1

هنا ، نطلب من Excel العثور على الرقم 1 في مجموعة البحث. كما رأينا سابقًا ، فإن القيمة الحادية عشرة في الصفيف المشترك هي الأولى التي تطابق قيمة البحث هذه ، لذلك يجد Xlookup القيمة الحادية عشرة في كل عمود من صفيف الإرجاع (الوسيطة ج):

T_Managers((Manager):(Sales))

في هذا المثال ، تكون القيمة الحادية عشرة في عمود المدير هي أوليفيا ، والقيمة الحادية عشرة في عمود المبيعات هي 346.

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

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

من المعايير.

مثال 2: معايير Xlookup متعددة مع التسلسل

هناك طريقة أخرى لإدخال معايير متعددة في صيغة Xlookup الخاصة بك وهي التسلس (الجمع) جميع قيم البحث باستخدام رمز Ampersand (&) ، والقيام بنفس الشيء مع جميع صفائف البحث.

طاولة بحث في Excel جاهزة لسحب اسم المدير وإجمالي المبيعات عند اختيار الشراب والبلد.

=XLOOKUP(
G1&G2,
T_Managers(Drink)&T_Managers(Country),
T_Managers((Manager):(Sales)),
"No result"
)

صيغة Xlookup التي تستخدم قيمتين بحثين متسلقين ومصفوفتين بحثتين متسلسلتين. أولا ، حجةأ

G1&G2

، قيمة البحث ، تحتوي على مرجعين للخلايا ، مفصولة بأمبيرز:

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

T_Managers(Drink)&T_Managers(Country)

، صفيف البحث ، ينطبق نفس المنطق ، مع انضمام كل صفيف برمز Ampersand:

تصور للقيم متسلسلة من عمودين مجازين في جدول Excel. بعد ذلك ، حجةج

T_Managers((Manager):(Sales))

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

=XLOOKUP(G1&G2,T_Managers(Drink)&T_Managers(Country),T_Managers((Manager):(Sales)),"No result")

يخبر Excel بإعادة الكلمات “لا نتيجة”.

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

. في الواقع ، فإن وظيفة Xlookup تتخلف عن تطابق دقيق ، ولا يهم ما إذا كنت تبحث من أعلى أو أسفل الجدول.