هذا هو سبب استخدامي لـ XLOOKUP

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

بناء الجملة: XLOOKUP وVLOOKUP

قبل أن أشرح لماذا أفضّل XLOOKUP على VLOOKUP، سأوضح لك كيفية عملهما.

XLOOKUP

يحتوي XLOOKUP على ست وسيطات:

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

أين

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

في هذا المثال، أريد من Excel أن يبحث عن معرف الموظف بناءً على الاسم الموجود في الخلية H1، ويعيد النتيجة إلى الخلية H2.

للقيام بذلك، سأكتب الصيغة التالية في الخلية H2:

=XLOOKUP(H1,B2:B12,A2:A12,"Invalid name",0,1)

في هذه الحالة، تحتوي الخلية H1 على القيمة التي يحتاج Excel إلى البحث عنها (ماري)، ومن B2 إلى B12 حيث يمكن العثور على هذه القيمة (أسماء الموظفين)، ومن A2 إلى A12 حيث سيتم سحب النتيجة المقابلة من (معرفات الموظفين )، و”اسم غير صالح” هو ما أريد أن يُرجعه Excel إذا لم تكن قيمة البحث موجودة في أي مكان في مصفوفة البحث.

لقد ذهبت للقيم الافتراضية للوسائط ه و و لأنني أريد تطابقًا تامًا، وأريد أن يقوم Excel بالبحث من أعلى مصفوفة البحث (المزيد حول ذلك لاحقًا).

فلوكوب

إليك بناء جملة VLOOKUP، الذي يحتوي على أربع وسائط:

=VLOOKUP(a,b,c,d)

أين

• أ (مطلوب) هي قيمة البحث،
• ب (مطلوب) هو مصفوفة البحث والإرجاع،
• ج (مطلوب) هو رقم فهرس العمود، و
• د (اختياري) هو وضع المطابقة.

في هذا المثال، أريد من Excel أن يبحث عن الجنسية بناءً على معرف الموظف في الخلية H4، ويعيد النتيجة إلى الخلية H5.

للقيام بذلك، سأكتب في الخلية H5

=VLOOKUP(H4,A2:E12,5,FALSE)

نظرًا لأن الخلية H4 تحتوي على قيمة البحث (المعرف 3264)، فإن الخلايا من A2 إلى E12 هي المكان الذي يحتاج فيه Excel إلى العثور على تلك القيمة والإرجاع المقابل، والعمود الخامس (البلد) هو المصفوفة التي سيتم العثور على النتيجة فيها، وأريد تطابق تام (خطأ).

لا حاجة لحساب الأعمدة

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

فلوكوب

يمكن أن تؤدي الحاجة إلى تحديد رقم فهرس العمود في VLOOKUP إلى عدة مشكلات:

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

• قد تؤثر إضافة الأعمدة أو إزالتها على دقة رقم فهرس العمود.

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

XLOOKUP

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

• ليس هناك العد المعنية! ما عليك سوى استخدام الماوس لتحديد مصفوفة الإرجاع عندما تصل إلى هذا الجزء من الصيغة. وهذا يوفر الوقت ويساعد على الدقة.

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

• يمكن أن تكون مصفوفة الإرجاع على جانبي مصفوفة البحث، مما يعني أن XLOOKUP أكثر تنوعًا من VLOOKUP.

المزيد من خيارات المطابقة التقريبية

يمكن لكل من VLOOKUP وXLOOKUP إرجاع تطابق تام (القيمة المقابلة تمامًا في الصف) ومطابقة تقريبية (قيمة مقابلة قريبة).

فلوكوب

وبشكل أكثر تحديدًا، تبحث المطابقة التقريبية لـ VLOOKUP (المشار إليها بواسطة TRUE في الصيغة) في مصفوفة البحث حتى تعثر على قيمة أكبر من قيمة البحث. ثم تقوم بإرجاع القيمة التي تقع على بعد صف واحد من هناك.

احصل على درجة الطالب D البالغة 65 في المثال أدناه. تأخذ VLOOKUP قيمة البحث 65، وتقوم بمراجعة صفيف البحث، وتبحث عن القيمة الأولى الأكبر من قيمة البحث (في هذه الحالة، 70)، ثم تقوم بإرجاع التقدير من الصف أعلاه (الدرجة C).

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

XLOOKUP

من ناحية أخرى، يقدم XLOOKUP ثلاثة بدائل للمطابقة التقريبية أحادية البعد لـ VLOOKUP:

• -1: يؤدي ذلك إلى إرجاع القيمة الأصغر التالية في مصفوفة البحث إذا لم يكن هناك تطابق تام.

• 1: يؤدي ذلك إلى إرجاع القيمة الأكبر التالية في مصفوفة البحث إذا لم يكن هناك تطابق تام.

• 2: يستخدم هذا أحرف البدل للسماح بعمليات بحث أكثر مرونة.

مرة أخرى، خذ الطالب د كمثال. مع الحصول على درجة 65، سيرى Excel أن القيمة الأصغر التالية في مصفوفة البحث هي 60، والقيمة الأكبر التالية هي 70. وبما أن الطالب لم يصل بعد إلى عتبة الدرجة B (70)، فأنا بحاجة لبرنامج Excel خذ القيمة الأصغر التالية في مصفوفة البحث (60) لإرجاع الدرجة C من مصفوفة الإرجاع. لذلك، سأكتب -1 كخيار المطابقة في الصيغة.

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

المزيد من أوضاع البحث (الاتجاهات)

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

فلوكوب

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

XLOOKUP

ومع ذلك، يتيح لك XLOOKUP اختيار اتجاه البحث:

• 1: البحث من الأول إلى الأخير

• -1: البحث من الأخير إلى الأول

• 2: البحث الثنائي (مع مصفوفة البحث بترتيب تصاعدي)

• -2 بحث ثنائي (مع مصفوفة البحث بترتيب تنازلي)

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

تحديد إخراج الخطأ

إحدى الميزات المفيدة حقًا في XLOOKUP هي الوسيطة “إذا لم يتم العثور عليها”، والتي لا تتوفر في VLOOKUP.

فلوكوب

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

=IFERROR(VLOOKUP(B6,$E$2:$F$8,2,TRUE)," ")

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

XLOOKUP

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

إرجاع مصفوفة منسكبة

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

XLOOKUP

في هذا المثال، الكتابة

=XLOOKUP(I1,A2:A7,B2:F7)

يبحث عن القيمة الموجودة في الخلية I1 (في هذه الحالة، Quizpicable Me)، ويبحث عن تلك القيمة في الخلايا من A2 إلى A7، ويعيد كافة القيم المقابلة كمصفوفة ممتدة.

فلوكوب

إذا كنت سأحاول تكرار هذا باستخدام VLOOKUP، فسأكتب

=VLOOKUP(I1,A2:F7,2:6)

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

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