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

يدعم Excel 2016 لمشتركي Office 365 على نظامي التشغيل Windows و Mac الآن وظيفة XLOOKUP الجديدة ، والتي توصف بأنها بديل أبسط إلى حد كبير وأكثر تنوعًا لوظيفة البحث العمودي الشائعة جدًا (التي غالبًا ما تكون خبيثة) ، VLOOKUP (لا تعرف ما هو X في XLOOKUP لتقف على ؛ ممتد ، ربما؟).

بالنسبة لأولئك الذين لم يكونوا على دراية بـ VLOOKUP (التي تعتبر الوظيفة الثالثة الأكثر استخدامًا بعد SUM و AVERAGE مباشرة) ، تبحث هذه الوظيفة عموديًا بصف في العمود الموجود في أقصى اليسار لجدول بحث معين من أعلى إلى أسفل حتى تعثر على قيمة في عمود البحث المعين برقم الإزاحة الذي يطابق أو يتجاوز الرقم الذي تبحث عنه. على الرغم من كونها مفيدة للغاية في تحديد موقع عناصر معينة في قائمة طويلة أو عمود من جدول البيانات في ورقة العمل الخاصة بك ، إلا أن وظيفة VLOOKUP لها العديد من القيود التي لا تشاركها وظيفة البحث الجديدة هذه ، مثل XLOOKUP:

• افتراضيات البحث عن التطابقات التامة لقيمة البحث الخاصة بك في نطاق البحث
• يمكن البحث عموديًا (حسب الصف) وأفقياً (حسب العمود) في جدول ، وبالتالي استبدال الحاجة إلى استخدام وظيفة HLOOKUP عند البحث أفقيًا بعمود
• يمكن البحث إلى اليسار أو اليمين حتى لا يكون نطاق البحث في جدول البحث الخاص بك موجودًا في عمود على يسار العمود المحدد كنطاق إرجاع حتى تعمل الوظيفة
• عند استخدام القيمة الافتراضية للمطابقة التامة ، تعمل حتى عندما لا يتم فرز القيم الموجودة في نطاق البحث بترتيب معين
• يمكن البحث من الصف السفلي إلى الأعلى في نطاق مصفوفة البحث ، باستخدام وسيطة وضع البحث الاختيارية

تحتوي الدالة XLOOKUP على خمس وسيطات محتملة ، أول ثلاث منها مطلوبة والأخيرة اختيارية ، باستخدام بناء الجملة التالي:

XLOOKUP ( lookup_value ، lookup_array ، return_array ، [ match_mode ]، [ search_mode ])

تحدد الوسيطة lookup_value المطلوبة القيمة أو العنصر الذي تبحث عنه. تعيّن وسيطة صفيف look_up المطلوبة نطاق الخلايا المطلوب البحث عنها لقيمة البحث هذه ، وتعين الوسيطة return_array نطاق الخلايا الذي يحتوي على القيمة التي تريد إرجاعها عندما يعثر Excel على تطابق تام.

* ضع في اعتبارك عند تعيين وسيطتي lookup_array و return_array في دالة XLOOKUP ، يجب أن يكون كلا النطاقين متساويين في الطول ، وإلا فسيرجع Excel الخطأ #VALUE! خطأ في الصيغة الخاصة بك. هذا هو سبب استخدامك لأسماء النطاقات أو أسماء الأعمدة لجدول بيانات معين عند تحديد هذه الوسائط بدلاً من الإشارة إليها أو كتابتها في مراجع الخلايا الخاصة بها .

يمكن أن تحتوي وسيطة match_mode الاختيارية على أي من القيم الأربع التالية:

• 0 لمطابقة تامة (الافتراضي ، كما هو الحال عندما لا يتم تعيين وسيطة match_mode )
• -1 للمطابقة التامة أو القيمة الأقل التالية
• 1 للمطابقة التامة أو القيمة التالية الأكبر
• 2 للمطابقة الجزئية باستخدام أحرف البدل المرتبطة بمرجع الخلية في وسيطة lookup_value

يمكن أن تحتوي وسيطة search_mode الاختيارية على أي من القيم الأربع التالية:

• 1 للبحث من أول إلى آخر ، أي من أعلى إلى أسفل (الافتراضي ، كما هو الحال عندما لا يتم تعيين وسيطة search_mode )
• -1 للبحث من الأخير إلى الأول ، أي من الأسفل إلى الأعلى
• 2 للبحث الثنائي بترتيب تصاعدي
• -2 للبحث الثنائي بترتيب تنازلي

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

ضع مؤشر الخلية في الخلية E4 من ورقة العمل

انقر فوق خيار البحث والمراجع في علامة التبويب الصيغ متبوعًا بـ XLOOKUP بالقرب من أسفل القائمة المنسدلة لفتح مربع حوار وسيطات الوظيفة.

انقر فوق الخلية D4 في ورقة العمل لإدخال مرجع الخلية الخاص بها في مربع نص وسيطة Lookup_value.

اضغط على Tab لتحديد مربع نص وسيطة Lookup_array ، ثم انقر فوق الخلية A4 واضغط باستمرار على مفتاح Shift أثناء الضغط على Ctrl-down السهم لتحديد A4: A8 كنطاق للبحث (لأن النطاق A3: B8 معرف كجدول بيانات Excel ، يظهر جدول 1 [البلد] في مربع النص بدلاً من النطاق A4: A8).

اضغط على Tab لتحديد مربع نص وسيطة Return_array ، ثم انقر فوق الخلية B4 واستمر في الضغط على مفتاح Shift أثناء الضغط على Ctrl-سهم لأسفل لتحديد B4: B8 كنطاق يحتوي على القيم التي سيتم إرجاعها بناءً على نتائج البحث (الذي يظهر على شكل جدول 1 [إجمالي المبيعات] في مربع النص).

انقر فوق "موافق" لإدخال صيغة XLOOKUP في الخلية E4.

إنشاء صيغة باستخدام XLOOKUP في الخلية E4 تُرجع المبيعات بناءً على الدولة التي تم إدخالها في الخلية D4.

يقوم Excel بإدخال صيغة XLOOKUP في الخلية E4 من ورقة العمل وإرجاع 4900 كنتيجة لأن كوستاريكا قد تم إدخالها حاليًا في خلية البحث D4 وكما ترى في جدول مبيعات 2019 ، فهذا بالفعل إجمالي المبيعات التي تم إجراؤها لهذا البلد.

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

بالإضافة إلى ذلك ، تقوم بإدخال -1 كوسيطة match_mode لتجاوز الإعداد الافتراضي للمطابقة التامة للوظيفة بحيث يقوم Excel بإرجاع البلد بمطابقة تامة لقيمة المبيعات التي تم إدخالها في خلية البحث E4 أو تلك التي تحتوي على إجمالي مبيعات أقل تالية (المكسيك مع 10000 دولار في هذه الحالة حيث لا يوجد بلد في هذا الجدول يبلغ إجمالي مبيعاته 11000 دولار). بدون تعيين وسيطة match_mode لهذه الصيغة ، سيرجع Excel #NA كنتيجة ، لأنه لا يوجد تطابق تام مع 11000 دولار في جدول المبيعات هذا.

إنشاء صيغة باستخدام XLOOKUP في الخلية D4 تُرجع الدولة بناءً على المبيعات التي تم إدخالها في الخلية E4

نظرًا لأن وظيفة XLOOKUP مريحة أيضًا في البحث أفقيًا بعمود لأنها تبحث عموديًا بصف ، يمكنك استخدامها لإنشاء صيغة تقوم بإجراء بحث ثنائي الاتجاه (استبدال الحاجة إلى إنشاء صيغة تجمع بين دالتي INDEX و MATCH مثل في الماضي). يوضح الشكل التالي ، الذي يحتوي على جدول جدول الإنتاج لعام 2019 لأرقام الأجزاء ، AB-100 حتى AB-103 للأشهر من أبريل إلى ديسمبر ، كيفية القيام بذلك.

إنشاء صيغة بدالات XLOOKUP المتداخلة لإرجاع عدد الوحدات المنتجة لجزء في شهر معين

في الخلية B12 ، أنشأت الصيغة التالية:

= XLOOKUP (part_lookup، $ A $ 3: $ A $ 6، XLOOKUP (date_lookup، $ B $ 2: $ J $ 2، $ B $ 3: $ J $ 6))

تبدأ هذه الصيغة بتعريف دالة XLOOKUP التي تبحث عموديًا حسب الصف عن تطابق تام مع إدخال الجزء الذي تم إجراؤه في الخلية المسماة part_lookup (الخلية B10 ، في هذه الحالة) في نطاق الخلايا $ A $ 3: $ A $ 6 من جدول الإنتاج . لاحظ ، مع ذلك ، أن الوسيطة return_array لدالة LOOKUP الأصلية هذه هي نفسها دالة XLOOKUP ثانية.

تبحث دالة XLOOKUP الثانية المتداخلة في نطاق الخلايا $ B $ 2: $ J $ 2 أفقيًا حسب العمود للحصول على مطابقة تامة لإدخال التاريخ الذي تم إجراؤه في الخلية المسماة date_lookup (الخلية B11 ، في هذه الحالة). الوسيطة return_array لدالة XLOOKUP الثانية المتداخلة هي $ B $ 3: $ J $ 6 ، نطاق الخلايا لجميع قيم الإنتاج في الجدول.

الطريقة التي تعمل بها هذه الصيغة هي أن Excel يحسب أولاً نتيجة دالة XLOOKUP الثانية المتداخلة عن طريق إجراء بحث أفقي يُرجع ، في هذه الحالة ، الصفيف في نطاق الخلايا D3: D6 لعمود Jun-19 (مع القيم: 438 و 153 و 306 و 779) كنتيجة لها. تصبح هذه النتيجة بدورها الوسيطة return_array لوظيفة XLOOKUP الأصلية التي تقوم بإجراء بحث عمودي حسب الصف لمطابقة تامة لإدخال رقم الجزء الذي تم إجراؤه في الخلية B11 (المسماة part_lookup). لأنه ، في هذا المثال ، تحتوي خلية part_lookup هذه على AB-102 ، تُرجع الصيغة قيمة إنتاج Jun-19 فقط ، 306 ، من نتيجة دالة XLOOKUP الثانية التالية.

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

* لسوء الحظ ، لا تتوافق وظيفة XLOOKUP مع الإصدارات السابقة من Microsoft Excel التي تدعم فقط وظائف VLOOKUP و HLOOKUP أو متوافقة مع الإصدارات الحالية التي لا تتضمنها حتى الآن كإحدى وظائف البحث الخاصة بها ، مثل Excel 2019 و Excel Online . هذا يعني أنك إذا قمت بمشاركة مصنف يحتوي على صيغ XLOOKUP مع زملاء العمل أو العملاء الذين يستخدمون إصدارًا من Excel لا يتضمن وظيفة البحث الجديدة هذه ، فستُرجع كل هذه الصيغ #NAME؟ قيم الخطأ عند فتح ورقة العمل الخاصة به.