Formulas and Functions
سيؤدي تعديل زر التحكم إلى إعادة تحميل هذه الصفحة تلقائيًا

XLOOKUP

تبحث دالة XLOOKUP عن قيمة معينة في نطاق ما، وتُرجع القيمة من نفس الصف في عمود آخر.

XLOOKUP(قيمة-البحث‏; نطاق-البحث‏; نطاق-الإرجاع‏; إذا-لم-يوجد‏; نوع-التطابق‏; نوع-البحث)

قيمة-البحث: القيمة التي يتم البحث عنها في نطاق-البحث. قيمة-البحث يمكن أن تتضمن أية قيمة، أو سلسلة REGEX.

نطاق-البحث: الخلايا المطلوب البحث فيها.

نطاق-الإرجاع: الخلايا التي سيتم إرجاعها.

إذا-لم-يوجد: وسيطة اختيارية لتحديد الرسالة التي تُعرض إذا لم يتم العثور على تطابق.

نوع-التطابق: وسيطة اختيارية تحدد نوع التطابق الذي سيتم البحث عنه.

تام أو الأصغر التالي (-1): إذا لم يكن هناك تطابق، تُرجع خطأ.

تطابق تام (0 أو محذوف): إذا لم يكن هناك تطابق تام، قم بإرجاع خطأ.

تام أو الأكبر التالي (1): إذا لم يكن هناك تطابق، تُرجع خطأ.

حرف بدل (2): أحرف * و ? و ~ لها معنى معين. يمكن استخدام REGEX فقط في XLOOKUP إذا كنت تستخدم حرف بدل.

نوع-البحث: وسيطة اختيارية تحدد الترتيب الذي سيتم البحث في النطاق وفقًا له.

تنازلي ثنائي (-2): بحث ثنائي يتطلب فرز النطاق بترتيب تنازلي، وإلا فسيُرجع خطأ.

الأخير إلى الأول (-1): البحث في النطاق من الأخير إلى الأول.

الأول إلى الأخير (1 أو محذوف): البحث في النطاق من الأول إلى الأخير.

تصاعدي ثنائي (2): بحث ثنائي يتطلب فرز النطاق بترتيب تصاعدي، وإلا فسيُرجع خطأ.

ملاحظات

  • إذا كان نطاق-البحث أو نطاق-الإرجاع مرجعًا ممتدًا (مثل "B")، فسيتم تجاهل الرؤوس والتذييلات تلقائيًا.

  • لعرض النتائج من مصفوفة، استخدم INDEX مع XLOOKUP.

مثال

يسرد الجدول أدناه، بعنوان المنتجات، المنتجات وسماتها، مثل الحجم والسعر:

A

B

C

D

E

1

المنتج

الطول (سم)

العرض (سم)

الوزن (كجم)

السعر

2

المنتج 1

16

17

10

82 دولارًا

3

المنتج 2

16

20

18

77 دولارًا

4

المنتج 3

11

11

15

88 دولارًا

5

المنتج 4

15

16

20

63 دولارًا

البحث باستخدام XLOOKUP

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

محرر الصيغ يعرض الصيغة ‎=XLOOKUP(Products::$A2‏;Products::A‏;Width)‎.

في هذه الصيغة، تُستخدم الوسيطات التالية:

  • قيمة-البحث: المنتجات::$A2، مرجع مطلق للخلية الموجودة في جدول المنتجات التي تحتوي على المنتج 1.

  • نطاق-البحث: المنتجات::A، العمود الذي يتم البحث فيه عن المنتج 1.

  • نطاق-الإرجاع: العرض، العمود الذي يحتوي على القيمة المطلوب إرجاعها والمرتبطة بالمنتج 1.

  • نوع-التطابق: محذوف. إذا كان نوع-التطابق محذوفًا، تبحث دالة XLOOKUP عن تطابق تام بشكل افتراضي.

تعيين سلسلة إذا-لم-يوجد

إذا أردت البحث عن طول منتج معين وإرجاع العرض المطابق له، بالإضافة إلى السلسلة المطلوب إرجاعها إذا لم يتم العثور على تطابق، يمكنك استخدام الصيغة التالية، التي تُرجع "No match":

محرر الصيغ يعرض الصيغة ‎=XLOOKUP(13‏;الطول‏;العرض‏;"لا يوجد تطابق"‏;0)‎.

في هذه الصيغة، يتم استخدام الوسيطة إذا لم يوجد لإجراء بحث أكثر تحديدًا:

  • قيمة-البحث: ۱۳، القيمة المطلوب البحث عنها في النطاق المحدد في نطاق-البحث.

  • نطاق-البحث: الطول، العمود المطلوب البحث فيه.

  • نطاق-الإرجاع: العرض، العمود الذي يحتوي على القيمة المطلوب إرجاعها إذا تم العثور على تطابق لوسيطة قيمة-البحث.

  • إذا-لم-يوجد: "لا يوجد تطابق"، السلسلة المطلوب عرضها إذا لم يتم العثور على منتج بطول 13 سم.

  • نوع-التطابق: تطابق تام (0). يبحث ذلك عن طول 13 سم فقط.

البحث عن أقرب قيمة تالية

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

محرر الصيغ يعرض الصيغة ‎=XLOOKUP(13‏;الطول‏;العرض‏;"لا يوجد تطابق"‏;1‏;-1)‎.

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

  • نوع-التطابق: تام أو الأصغر التالي (-1). يبحث هذا عن طول 13 سم، وإذا لم يتم العثور على هذه القيمة، يبحث عن القيمة الأصغر التالية في عمود الطول.

تغيير ترتيب البحث

في بعض الحالات، قد يكون من المفيد تغيير ترتيب البحث في الجدول باستخدام XLOOKUP. على سبيل المثال، في الجدول أعلاه، يوجد منتجان بطول 16 سم، لذلك يوجد تطابقان محتملان إذا كنت تبحث عن 16 سم في عمود الطول باستخدام قيمة-البحث ونطاق-البحث. يمكنك تعيين ترتيب البحث باستخدام صيغة مثل هذه، والتي تقوم بإرجاع 20 سم:

محرر الصيغ يعرض الصيغة ‎=XLOOKUP(16‏;الطول‏; العرض‏;"لا يوجد تطابق"‏;1‏;-1)‎.

في هذه الصيغة، يتم استخدام وسيطة نوع-البحث لتعيين الترتيب الذي تبحث به XLOOKUP في الجدول عن تطابق:

  • قيمة-البحث: 16، القيمة المطلوب البحث عنها في النطاق المحدد في نطاق-البحث.

  • نطاق-البحث: الطول، العمود المطلوب البحث فيه.

  • نطاق-الإرجاع: العرض، العمود الذي يحتوي على القيمة المطلوب إرجاعها إذا تم العثور على تطابق لوسيطة قيمة-البحث.

  • إذا-لم-يوجد: "لا يوجد تطابق"، السلسلة المطلوب عرضها إذا لم يتم العثور على منتج بطول 16 سم.

  • نوع-التطابق: تام أو الأكبر التالي (1). يبحث هذا عن طول 16 سم، وإذا لم يتم العثور على هذه القيمة، يبحث عن القيمة الأكبر التالية في عمود الطول.

  • نوع-البحث: الأخير إلى الأول (-1). يبحث هذا في العمود من القيمة الأخيرة إلى القيمة الأولى.

استخدام XLOOKUP مع الدوال الأخرى

يمكن أيضًا استخدام XLOOKUP مع دوال أخرى، مثل SUM. على سبيل المثال، يمكنك استخدام صيغة مثل الصيغة الواردة أدناه لإرجاع 247 دولارًا، مجموع أسعار المنتجات 1 و2 و3:

محرر الصيغ يعرض الصيغة ‎=المجموع(XLOOKUP(المنتجات::$A2‏;المنتجات::A‏;السعر):XLOOKUP(المنتجات::$A4‏;المنتجات::A‏;السعر))‎.

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

في الصيغة أدناه، يتم استخدام XLOOKUP مع REGEX لإرجاع المنتج 2، المنتج الأول بعرض يبدأ بالقيمة "2":

محرر الصيغ يعرض الصيغة ‎=XLOOKUP(REGEX("^2.*")‏; المنتجات::C2:C5‏; المنتجات::A2:A5، خطأ‏;2)‎.

في هذا المثال، يُستخدم "حرف البدل (2)" لوسيطة match-type لاستخدام أحرف البدل في دالة REGEX.

أمثلة إضافية

الجدول التالي المعطى:

A

B

C

1

الاسم

العمر

الراتب

2

إيمان

35

71000

3

ماهر

27

81000

4

كارلا

42

86000

5

صوفيا

51

66000

6

كندة

28

52000

7

تامر

49

62000

8

هارون

63

89000

9

مريم

2

34000

10

آلاء

29

52000

11

براء

35

52500

=XLOOKUP(49‏;B2:B11‏;C2:C11) تُرجع "62000" وهو راتب أول موظف يبلغ من العمر 49 عامًا.

=XLOOKUP(60000‏;C2:C11‏;B2:B11‏;"No match") تُرجع "لا يوجد تطابق"، حيث لا يوجد موظف براتب 60000 دولار.

=XLOOKUP(REGEX("^ك.*")‏; A2:A11‏; B2:B11‏; خطأ‏;2) تُرجع "42"، وهو عمر "كارلا"، الموظف الأول في النطاق الذي يبدأ اسمه بالحرف "ك".

= INDEX(‏XLOOKUP(‏A2‏;A2:A11‏;B2:C11)‏;2) ترجع 71000، القيمة الثانية في المصفوفة التي أرجعتها XLOOKUP.

انظر أيضًاXMATCH
Morty Proxy This is a proxified and sanitized view of the page, visit original site.
هل كان هذا مفيدًا؟
الحد الأقصى للأحرف: 250
الحد الأقصى للأحرف 250.
شكرًا على ملاحظاتك.
Morty Proxy This is a proxified and sanitized view of the page, visit original site.