اكسل

وظيفة Excel XLOOKUP

Excel Xlookup Function

وظيفة Excel XLOOKUPملخص

تعد وظيفة Excel XLOOKUP بديلاً حديثًا ومرنًا للوظائف القديمة مثل VLOOKUP و HLOOKUP و LOOKUP. يدعم XLOOKUP المطابقة التقريبية والتامة ، وأحرف البدل (*؟) للمطابقات الجزئية وعمليات البحث في النطاقات الرأسية أو الأفقية.





قيم البحث الغرض في النطاق أو المصفوفة قيمة الإرجاع مطابقة القيمة (القيم) من بنية مصفوفة الإرجاع = XLOOKUP (lookup، lookup_array، return_array، [not_found]، [match_mode]، [search_mode]) وسيطات
  • ابحث عن - قيمة البحث.
  • lookup_array - المصفوفة أو النطاق المطلوب البحث فيه.
  • Return_array - المصفوفة أو النطاق المطلوب عرضه.
  • غير موجود - [اختياري] القيمة المراد إرجاعها إذا لم يتم العثور على تطابق.
  • match_mode - [اختياري] 0 = المطابقة التامة (افتراضي) ، -1 = المطابقة التامة أو الأصغر التالي ، 1 = المطابقة التامة أو التالية الأكبر ، 2 = مطابقة حرف البدل.
  • وضع البحث - [اختياري] 1 = بحث من الأول (افتراضي) ، -1 = بحث من الأخير ، 2 = بحث ثنائي تصاعدي ، -2 = بحث ثنائي تنازلي.
إصدار اكسل 365 ملاحظات الاستخدام

XLOOKUP هو بديل حديث لوظيفة VLOOKUP. إنها وظيفة مرنة ومتعددة الاستخدامات يمكن استخدامها في مجموعة متنوعة من المواقف.

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





لم يتم العثور على الرسالة

عندما يتعذر على XLOOKUP العثور على تطابق ، فإنه يُرجع الخطأ # N / A ، مثل وظائف المطابقة الأخرى في Excel. بخلاف دالات المطابقة الأخرى ، يدعم XLOOKUP وسيطة اختيارية تسمى غير موجود يمكن استخدامه لتجاوز الخطأ # N / A عندما يظهر بطريقة أخرى. القيم النموذجية لـ غير موجود يمكن أن تكون 'غير موجود' أو 'لا يوجد تطابق' أو 'لا توجد نتيجة' وما إلى ذلك. عند تقديم قيمة لـ غير موجود ، ضع النص بين علامتي اقتباس ('').

ملاحظة: كن حذرًا إذا قدمت سلسلة فارغة ('') لـ not_found. إذا لم يتم العثور على تطابق ، فلن يعرض XLOOKUP أي شيء بدلاً من # N / A. إذا كنت تريد رؤية الخطأ # N / A عندما لا يتم العثور على تطابق ، فاحذف الوسيطة بالكامل.



نوع مباراة

بشكل افتراضي ، سيقوم XLOOKUP بتنفيذ ملف تطابق تام . يتم التحكم في سلوك المطابقة من خلال وسيطة اختيارية تسمى نوع مباراة، الذي يحتوي على الخيارات التالية:

كيفية الفرز حسب الرقم في Excel
نوع مباراة سلوك
0 (افتراضي) تطابق تام. سيعود # N / A إذا لم يكن هناك تطابق.
-1 تطابق تام أو العنصر الأصغر التالي.
1 تطابق تام أو العنصر الأكبر التالي.
2 مباراة البدل (* ،؟ ، ~)

وضع البحث

بشكل افتراضي ، سيبدأ XLOOKUP في المطابقة من قيمة البيانات الأولى. يتم التحكم في سلوك البحث من خلال وسيطة اختيارية تسمى وضع البحث ، والذي يوفر الخيارات التالية:

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

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

المثال رقم 1 - المطابقة التامة الأساسية

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

 
= XLOOKUP (H4,B5:B9,E5:E9)

XLOOKUP - مثال أساسي للمطابقة التامة

شرح أكثر تفصيلا هنا .

المثال الثاني - المطابقة التقريبية الأساسية

لتمكين التطابق التقريبي ، قم بتوفير قيمة للوسيطة 'match_mode'. في المثال أدناه ، يتم استخدام XLOOKUP لحساب خصم بناءً على الكمية ، الأمر الذي يتطلب تطابقًا تقريبيًا. توفر الصيغة في F5 -1 لوضع match_mode لتمكين التطابق التقريبي مع سلوك 'المطابقة التامة أو التالية الأصغر':

 
= XLOOKUP (E5,B5:B9,C5:C9,,-1)

XLOOKUP - مثال أساسي للمطابقة التقريبية

شرح أكثر تفصيلا هنا .

مثال # 3 - قيم متعددة

يمكن لـ XLOOKUP إرجاع أكثر من قيمة في نفس الوقت لنفس المطابقة. يوضح المثال أدناه كيف يمكن تكوين XLOOKUP لإرجاع ثلاث قيم متطابقة مع صيغة واحدة. الصيغة في C5 هي:

 
= XLOOKUP (B5,B8:B15,C8:E15)

XLOOKUP - مثال متعدد القيم

لاحظ أن مصفوفة الإرجاع (C8: E15) تتضمن 3 أعمدة: الأول ، الأخير ، القسم. يتم إرجاع جميع القيم الثلاث و لعبه في النطاق C5: E5.

المثال رقم 4 - بحث ثنائي الاتجاه

يمكن استخدام XLOOKUP لإجراء بحث ثنائي الاتجاه من خلال التعشيش XLOOKUP واحد داخل الآخر. في المثال أدناه ، يسترد XLOOKUP 'الداخلي' صفًا كاملاً (جميع قيم Glass) ، والذي يتم تسليمه إلى XLOOKUP 'الخارجي' باعتباره صفيف إرجاع. يبحث XLOOKUP الخارجي عن المجموعة المناسبة (B) ويرجع القيمة المقابلة (17.25) كنتيجة نهائية.

 
= XLOOKUP (I6,C4:F4, XLOOKUP (I5,B5:B9,C5:F9))

XLOOKUP - مثال بحث ثنائي الاتجاه

مزيد من التفاصيل هنا .

المثال رقم 5 - لم يتم العثور على رسالة

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

 
= XLOOKUP (H4,B5:B9,E5:E9,'Not found')

XLOOKUP - مثال غير موجود

يمكنك تخصيص هذه الرسالة كما تريد: 'لا يوجد تطابق' ، 'لم يتم العثور على الفيلم' ، إلخ.

الجمع بين الأعمدة في التفوق مع الفاصلة

مثال # 6 - معايير معقدة

مع القدرة على التعامل مع المصفوفات محليًا ، يمكن استخدام XLOOKUP مع معايير معقدة. في المثال أدناه ، يطابق XLOOKUP السجل الأول حيث: يبدأ الحساب بـ 'x' و المنطقة 'شرق' و الشهر ليس أبريل:

 
= XLOOKUP (1,( LEFT (B5:B16)='x')*(C5:C16='east')* NOT ( MONTH (D5:D16)=4),B5:E16)

XLOOKUP - مثال معايير معقدة

التفاصيل: (1) مثال بسيط ، (2) مثال أكثر تعقيدًا .

فوائد XLOOKUP

يقدم XLOOKUP العديد من المزايا الهامة ، خاصة بالمقارنة مع VLOOKUP:

  • يمكن لـ XLOOKUP البحث عن البيانات إلى اليمين أو اليسار من قيم البحث
  • يمكن لـ XLOOKUP إرجاع نتائج متعددة (المثال رقم 3 أعلاه)
  • يتم تعيين XLOOKUP افتراضيًا على المطابقة التامة (افتراضيات VLOOKUP تقريبية)
  • يمكن أن يعمل XLOOKUP مع البيانات الرأسية والأفقية
  • يمكن لـ XLOOKUP إجراء بحث عكسي (من الأخير إلى الأول)
  • يمكن لـ XLOOKUP إرجاع ملفات صفوف أو أعمدة ، ليست مجرد قيمة واحدة
  • يمكن أن يعمل XLOOKUP مع ملفات المصفوفات أصلاً لتطبيق معايير معقدة

ملحوظات

  1. يمكن أن يعمل XLOOKUP مع كل من المصفوفات الرأسية والأفقية.
  2. سيعود XLOOKUP # N / A إذا لم يتم العثور على قيمة البحث.
  3. ال lookup_array يجب أن يكون له بُعد متوافق مع Return_array وسيطة ، وإلا ستُرجع الدالة XLOOKUP #VALUE!
  4. إذا تم استخدام XLOOKUP بين المصنفات ، فيجب فتح كلا المصنفين ، وإلا فسيرجع XLOOKUP #REF !.
  5. مثل ال دالة INDEX ، يقوم XLOOKUP بإرجاع ملف المرجعي نتيجة ل.
XLOOKUP هي وظيفة جديدة متاحة في اكسل 365 فقط.


^