Skip to main content

كيفية استخدام الدالة الجديدة والمتقدمة XLOOKUP في Excel (10 أمثلة)

Author: Zhoumandy Last Modified: 2025-05-30

تُعد الدالة الجديدة XLOOKUP في Excel أقوى وأسهل دالة بحث يمكن أن يقدمها Excel. بعد جهود متواصلة، أصدرت Microsoft أخيرًا هذه الدالة لتحل محل VLOOKUP وHLOOKUP وINDEX+MATCH ودوال البحث الأخرى.

في هذا الدليل، سنوضح لك مزايا XLOOKUP وكيفية الحصول عليها وتطبيقها لحل مشكلات البحث المختلفة.

كيفية الحصول على XLOOKUP؟

البنية

أمثلة

تحميل ملف عينة XLOOKUP

كيفية الحصول على XLOOKUP؟

نظرًا لأن دالة XLOOKUP متوفرة فقط في Excel لـ Microsoft365 وExcel2021 والإصدارات الأحدث، وExcel للويب. إذا كنت تستخدم Excel2019 أو إصدارًا أقدم، يُنصح بالترقية للوصول إلى XLOOKUP.

البنية

تبحث الدالة في نطاق أو مصفوفة ثم تُرجع قيمة أول نتيجة مطابقة. البنية كما يلي:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

A screenshot of the syntax of XLOOKUP function

المعطيات:

  1. Lookup_value (إلزامي): القيمة التي تبحث عنها. يمكن أن تكون في أي عمود من نطاق table_array.
  2. Lookup_array (إلزامي): المصفوفة أو النطاق الذي تبحث فيه عن قيمة البحث.
  3. Return_array (إلزامي): المصفوفة أو النطاق الذي تريد الحصول على القيمة منه.
  4. If_not_found (اختياري): القيمة التي يتم إرجاعها عند عدم العثور على تطابق صالح. يمكنك تخصيص النص في [if_not_found] لإظهار عدم وجود تطابق.
    خلاف ذلك، ستكون القيمة المرجعة هي #N/A بشكل افتراضي.
  5. Match_mode (اختياري): هنا يمكنك تحديد كيفية مطابقة lookup_value مع القيم في lookup_array.
    • 0 (افتراضي) = مطابقة تامة. إذا لم يتم العثور على تطابق، يتم إرجاع #N/A.
    • -1 = مطابقة تامة. إذا لم يتم العثور على تطابق، يتم إرجاع القيمة الأصغر التالية.
    • 1 = مطابقة تامة. إذا لم يتم العثور على تطابق، يتم إرجاع القيمة الأكبر التالية.
    • 2 = مطابقة جزئية. استخدم رموزًا نائبة مثل * و؟ و~ لتنفيذ مطابقة باستخدام الرموز النائبة.
  6. Search_mode (اختياري): هنا يمكنك تحديد ترتيب البحث الذي سيتم تنفيذه.
    • 1 (افتراضي) = البحث عن lookup_value من أول عنصر إلى آخر عنصر في lookup_array.
    • -1 = البحث عن lookup_value من آخر عنصر إلى أول عنصر. يساعد ذلك عندما تحتاج إلى الحصول على آخر نتيجة مطابقة في lookup_array.
    • 2 = إجراء بحث ثنائي يتطلب أن يكون lookup_array مرتبًا تصاعديًا. إذا لم يكن مرتبًا، ستكون النتيجة غير صالحة.
    • -2 = إجراء بحث ثنائي يتطلب أن يكون lookup_array مرتبًا تنازليًا. إذا لم يكن مرتبًا، ستكون النتيجة غير صالحة.

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

1. اكتب البنية أدناه في خلية فارغة، يرجى ملاحظة أنك تحتاج فقط إلى كتابة أحد جانبي القوس.

=XLOOKUP(

A screenshot of the XLOOKUP syntax in an Excel cell

2. اضغط على Ctrl+A، ثم ستظهر نافذة تعرض معطيات الدالة. وسيتم إغلاق القوس الآخر تلقائيًا.

A screenshot of the Function Arguments prompt box in Excel

3. اسحب لوحة البيانات لأسفل، وسترى جميع معطيات الدالة الستة لـ XLOOKUP.

A screenshot of the XLOOKUP Function Arguments panel showing details in Excel >>> A screenshot of the XLOOKUP Function Arguments panel showing details in Excel

أمثلة

أنا متأكد أنك الآن أتقنت المبادئ الأساسية لـ XLOOKUP. دعنا ننتقل مباشرة إلى الأمثلة العملية.

المثال1: مطابقة تامة

تنفيذ مطابقة تامة باستخدام XLOOKUP

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

الآن، افترض أن لديك قائمة بمخزون مستلزمات المكتب وتحتاج إلى معرفة سعر وحدة منتج معين، مثل الفأرة. يرجى اتباع الخطوات التالية.

A screenshot of the office supplies list in Excel for XLOOKUP

اكتب الصيغة التالية في الخلية الفارغة F2، ثم اضغط على مفتاح Enter للحصول على النتيجة.

=XLOOKUP(E2,A2:A10,C2:C10)

A screenshot of the XLOOKUP formula in an Excel cell

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

بضع نقرات فقط للحصول على المطابقة التامة

ربما تستخدم إصدارًا أقدم من Excel ولا تخطط للترقية إلى Excel2021 أو Microsoft365 في الوقت الحالي. في هذه الحالة، أوصي بميزة عملية - "البحث عن البيانات في نطاق". باستخدام هذه الميزة، يمكنك الحصول على النتيجة دون الحاجة إلى صيغ معقدة أو الوصول إلى XLOOKUP.

Kutools لـ Excel يقدم أكثر من 300 ميزة متقدمة لتبسيط المهام المعقدة، مما يعزز الإبداع والكفاءة. بالتكامل مع قدرات الذكاء الاصطناعي، يقوم Kutools بأتمتة المهام بدقة، مما يجعل إدارة البيانات أمرًا سهلاً للغاية. معلومات تفصيلية عن Kutools لـ Excel... نسخة تجريبية مجانية...

1. انقر على الخلية التي تريد وضع النتيجة المطابقة فيها.

2. انتقل إلى تبويب "Kutools"، ثم انقر على "مساعد الصيغة"، وبعدها اختر "مساعد الصيغة" من القائمة المنسدلة.

A screenshot of the Kutools Formula Helper option in Excel ribbon

3. في مربع الحوار مساعد الصيغة، يرجى التهيئة كما يلي:

  • حدد "البحث" في قسم "نوع الصيغة"؛
  • في قسم "حدد صيغة"، اختر "البحث عن البيانات في نطاق"؛
  • في قسم "إدخال الوسيط"، يرجى القيام بما يلي:
    • في مربع "نطاق الجدول"، حدد نطاق البيانات الذي يحتوي على قيمة البحث وقيمة النتيجة؛
    • في مربع "قيمة البحث"، حدد الخلية أو النطاق الذي يحتوي على القيمة التي تبحث عنها. يرجى ملاحظة أنه يجب أن يكون في العمود الأول من نطاق الجدول؛
    • في مربع "العمود"، حدد العمود الذي سيتم إرجاع القيمة المطابقة منه.

A screenshot of the Kutools Formula Helper setup with the Lookup for Value in List option

4. انقر على زر موافق للحصول على النتيجة.

A screenshot of the Kutools Formula Helper result

Kutools لـ Excel - قم بتعزيز Excel بأكثر من 300 أدوات أساسية. استمتع بميزات ذكاء اصطناعي مجانية بشكل دائم! احصل عليه الآن


المثال2: مطابقة تقريبية

تنفيذ مطابقة تقريبية باستخدام XLOOKUP

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

في هذا المثال، تحتاج إلى معرفة معدلات الضرائب على دخل موظفيك. على الجانب الأيسر من ورقة العمل توجد شرائح ضريبة الدخل الفيدرالية لعام2021. كيف يمكنك معرفة معدل الضريبة للموظف في العمود E؟ لا تقلق. يرجى اتباع الخطوات التالية:

1. اكتب الصيغة التالية في الخلية الفارغة E2، ثم اضغط على مفتاح Enter للحصول على النتيجة.
ثم قم بتغيير تنسيق النتيجة المرجعة حسب الحاجة.

=XLOOKUP(D2,B2:B8,A2:A8,,1)

A screenshot of the XLOOKUP formula in cell E2 with the tax bracket data in Excel >>> A screenshot of the tax rate lookup result in cell E2 using the XLOOKUP formula

√ ملاحظة: الوسيط الرابع [If_not_found] اختياري، لذا تم تجاهله.

2. الآن أصبحت تعرف معدل الضريبة للخلية D2. للحصول على بقية النتائج، تحتاج إلى تحويل مراجع الخلايا في lookup_array وreturn_array إلى مراجع مطلقة.

  • انقر مزدوجًا على الخلية E2 لعرض الصيغة =XLOOKUP(D2,B2:B8,A2:A8,,1);
  • حدد نطاق البحث B2:B8 في الصيغة، واضغط على مفتاح F4 لتحصل على $B$2:$B$8؛
  • حدد نطاق الإرجاع A2:A8 في الصيغة، واضغط على مفتاح F4 لتحصل على $A$2:$A$8؛
  • اضغط على زر Enter للحصول على نتيجة الخلية E2.
A screenshot of the updated XLOOKUP formula in cell E2 with absolute references in Excel >>> A screenshot of the tax rate lookup result in cell E2 using the XLOOKUP formula with absolute references

3. ثم اسحب مقبض التعبئة لأسفل للحصول على جميع النتائج.

A screenshot showing the formula results after dragging the fill handle down to cell E13

√ ملاحظة:

  • بالضغط على مفتاح F4 في لوحة المفاتيح يمكنك تحويل مرجع الخلية إلى مرجع مطلق بإضافة علامة الدولار قبل الصف والعمود.
  • بعد تطبيق المرجع المطلق على نطاق البحث ونطاق الإرجاع، قمنا بتغيير الصيغة في الخلية E2 إلى هذا الشكل:

=XLOOKUP(D2,$B$2:$B$8,$A$2:$A$8,,1)

  • عند سحب مقبض التعبئة لأسفل من الخلية E2، تتغير الصيغ في كل خلية من العمود E فقط في جزء lookup_value.
    على سبيل المثال، أصبحت الصيغة في E13 الآن كما يلي:

=XLOOKUP(D13,$B$2:$B$8,$A$2:$A$8,,1)

المثال3: مطابقة باستخدام الرموز النائبة

تنفيذ مطابقة باستخدام الرموز النائبة مع XLOOKUP

قبل أن نستعرض وظيفة المطابقة باستخدام الرموز النائبة في XLOOKUP، دعونا أولاً نعرف ما هي الرموز النائبة.

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

هناك ثلاثة أنواع من الرموز النائبة: النجمة (*)، علامة الاستفهام (؟)، والتلدة (~).

  • النجمة (*) تمثل أي عدد من الأحرف في النص؛
  • علامة الاستفهام (؟) ترمز لأي حرف واحد في النص؛
  • التلدة (~) تُستخدم لتحويل الرموز النائبة (*، ؟، ~) إلى أحرف فعلية. ضع التلدة (~) أمام الرمز النائب لتحقيق ذلك؛

في معظم الحالات، عند تنفيذ وظيفة المطابقة باستخدام الرموز النائبة في XLOOKUP، نستخدم رمز النجمة (*). الآن دعونا نرى كيف تعمل هذه المطابقة.

افترض أن لديك قائمة برؤوس أموال أكبر50 شركة أمريكية، وتريد معرفة القيمة السوقية لبعض الشركات ولكن أسماء الشركات مختصرة، هذا هو السيناريو المثالي لاستخدام المطابقة بالرموز النائبة. يرجى اتباع الخطوات معي خطوة بخطوة.

A screenshot of the stock market capitalization of large American companies

√ ملاحظة: لتنفيذ مطابقة باستخدام الرموز النائبة، أهم شيء هو ضبط الوسيط الخامس [match_mode] على2.

1. اكتب الصيغة التالية في الخلية الفارغة H3، ثم اضغط على مفتاح Enter للحصول على النتيجة.

=XLOOKUP("*"&G3&"*",B3:B52,D3:D52,,2)

A screenshot of the XLOOKUP formula for finding a market capitalization, with the formula visible in the cell >>> A screenshot of the XLOOKUP result of finding a market capitalization

2. الآن أصبحت تعرف نتيجة الخلية H3. للحصول على بقية النتائج، تحتاج إلى تثبيت lookup_array وreturn_array بوضع المؤشر داخل المصفوفة والضغط على مفتاح F4. بعد ذلك تصبح الصيغة في H3 كما يلي:

=XLOOKUP("*"&G3&"*",$B$3:$B$52,$D$3:$D$52,,2)

3. اسحب مقبض التعبئة لأسفل للحصول على جميع النتائج.

A screenshot of the Excel cell with the updated XLOOKUP formula where lookup and return arrays are fixed using F4

√ ملاحظة:

  • قيمة البحث في الصيغة في الخلية H3 هي "*"&G3&"*". قمنا بدمج رمز النجمة (*) مع قيمة G3 باستخدام علامة &.
  • الوسيط الرابع [If_not_found] اختياري، لذا تم تجاهله.
المثال4: البحث إلى اليسار

البحث من اليمين إلى اليسار باستخدام XLOOKUP

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

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

افترض أن لديك قائمة بالدول مع رموز الهاتف، وتريد البحث عن اسم الدولة بناءً على رمز الهاتف المعروف.

A screenshot of the country list with telephone codes, showing the setup for looking up a country name using XLOOKUP

نحتاج إلى البحث في العمود C وإرجاع القيمة من العمود A. يرجى اتباع الخطوات التالية:

1. اكتب الصيغة التالية في الخلية الفارغة G2.

=XLOOKUP(F2,C2:C11,A2:A11)

2. اضغط على مفتاح Enter للحصول على النتيجة.

A screenshot of the XLOOKUP formula used to look up country names based on telephone codes

√ ملاحظة: وظيفة البحث إلى اليسار في XLOOKUP يمكن أن تحل محل Index وMatch للبحث عن القيم في الأعمدة اليسرى.

ابحث عن القيمة من اليمين إلى اليسار ببضع نقرات

لمن لا يرغب في حفظ الصيغ، أوصي هنا بميزة مفيدة - "البحث من اليمين إلى اليسار". باستخدام هذه الميزة، يمكنك تنفيذ البحث من اليمين إلى اليسار خلال ثوانٍ معدودة.

Kutools لـ Excel يقدم أكثر من 300 ميزة متقدمة لتبسيط المهام المعقدة، مما يعزز الإبداع والكفاءة. بالتكامل مع قدرات الذكاء الاصطناعي، يقوم Kutools بأتمتة المهام بدقة، مما يجعل إدارة البيانات أمرًا سهلاً للغاية. معلومات تفصيلية عن Kutools لـ Excel... نسخة تجريبية مجانية...

1. انتقل إلى تبويب "Kutools" في Excel، وابحث عن "بحث متقدم"، ثم انقر على "البحث من اليمين إلى اليسار" في القائمة المنسدلة.

A screenshot of the 'LOOKUP from Right to Left' feature in Kutools tab in Excel

2. في مربع الحوار "البحث من اليمين إلى اليسار"، تحتاج إلى التهيئة كما يلي:

  • في قسم "نطاق القيم المراد البحث فيها ونطاق الإخراج"، حدد نطاق البحث ونطاق الإخراج؛
  • في قسم "نطاق البيانات"، أدخل نطاق البيانات، ثم حدد "العمود الرئيسي" و"عمود الإرجاع"؛

A screenshot of the 'LOOKUP from Right to Left' dialog box with input fields for lookup range, output range, key column, and return column

3. انقر على زر موافق للحصول على النتيجة.

A screenshot of the final result

Kutools لـ Excel - قم بتعزيز Excel بأكثر من 300 أدوات أساسية. استمتع بميزات ذكاء اصطناعي مجانية بشكل دائم! احصل عليه الآن


المثال5: البحث العمودي أو الأفقي

تنفيذ بحث عمودي أو أفقي باستخدام XLOOKUP

كمستخدمي Excel، قد تكونون معتادين على دالتي VLOOKUP وHLOOKUP. VLOOKUP للبحث عموديًا في عمود، وHLOOKUP للبحث أفقيًا في صف.

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

في المثال أدناه، سنوضح كيف يمكنك استخدام بنية واحدة فقط لـ XLOOKUP لتنفيذ البحث عموديًا أو أفقيًا.

لتنفيذ بحث عمودي، اكتب الصيغة التالية في الخلية الفارغة E2، واضغط على مفتاح Enter للحصول على النتيجة.

=XLOOKUP(E1,A2:A13,B2:B13)

A screenshot of the vertical lookup result using the formula

لتنفيذ بحث أفقي، اكتب الصيغة التالية في الخلية الفارغة P2، واضغط على مفتاح Enter للحصول على النتيجة.

=XLOOKUP(P1,B1:M1,B2:M2)

A screenshot of the horizontal lookup result using the formula

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

المثال6: البحث ثنائي الاتجاه

تنفيذ بحث ثنائي الاتجاه باستخدام XLOOKUP

هل ما زلت تستخدم دالتي INDEX وMATCH للبحث عن قيمة في جدول ثنائي الأبعاد؟ جرب الدالة المحسنة XLOOKUP لإنجاز المهمة بسهولة أكبر.

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

افترض أن لديك قائمة بدرجات الطلاب في مواد مختلفة، وتريد معرفة درجة كيم في مادة الكيمياء.

A screenshot of a table showing students' grades for various subjects

دعنا نرى كيف نستخدم XLOOKUP الرائعة لتحقيق ذلك.

    • نقوم بتشغيل XLOOKUP "الداخلية" لإرجاع قيم عمود كامل. XLOOKUP(H2,B1:E1,B2:E10) يمكنها جلب نطاق درجات الكيمياء.
    • نقوم بتضمين XLOOKUP "الداخلية" داخل XLOOKUP "الخارجية" باستخدام الدالة الداخلية كـ return_array في الصيغة الكاملة.
    • وهنا تأتي الصيغة النهائية:

=XLOOKUP(H1,A2:A10,XLOOKUP(H2,B1:E1,B2:E10))

  • اكتب الصيغة أعلاه في الخلية الفارغة H3، واضغط على زر Enter للحصول على النتيجة.

A screenshot of the XLOOKUP formula being used to perform a two-way lookup

أو يمكنك القيام بالعكس، استخدم XLOOKUP "الداخلية" لإرجاع قيم صف كامل، وهي جميع درجات المواد لكيم. ثم استخدم XLOOKUP "الخارجية" للبحث عن درجة الكيمياء بين جميع درجات كيم.

    • اكتب الصيغة أدناه في الخلية الفارغة H4، واضغط على زر Enter للحصول على النتيجة.

=XLOOKUP(H2,B1:E1,XLOOKUP(H1,A2:A10,B2:E10))

A screenshot of the XLOOKUP formula being used for a two-way lookup

تعد وظيفة البحث ثنائي الاتجاه في XLOOKUP أيضًا توضيحًا مثاليًا لوظيفة البحث العمودي والأفقي. جربها بنفسك!

المثال7: تخصيص رسالة عدم العثور

تخصيص رسالة عدم العثور باستخدام XLOOKUP

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

مع الوسيط المدمج [if_not_found]، يمكنك تحديد رسالة مخصصة لاستبدال نتيجة #N/A. اكتب النص الذي تريده في الوسيط الرابع الاختياري وضع النص بين علامتي اقتباس مزدوجتين ("").

على سبيل المثال، المدينة Denver غير موجودة، لذا تُرجع XLOOKUP رسالة الخطأ #N/A. ولكن بعد تخصيص الوسيط الرابع بالنص "لا توجد مطابقة"، ستعرض الصيغة نص "لا توجد مطابقة" بدلاً من رسالة الخطأ.

اكتب الصيغة أدناه في الخلية الفارغة F3، واضغط على زر Enter للحصول على النتيجة.

=XLOOKUP(E2,A2:A11,C2:C11,"No Match")

A screenshot showing the XLOOKUP formula to customize the error message when no match is found

تخصيص خطأ #N/A بميزة عملية

لتجاوز خطأ #N/A بسرعة برسالة مخصصة، Kutools لـ Excel هو أداة مثالية في Excel لمساعدتك. بفضل ميزة استبدال0 أو #N/A بفراغ أو قيمة محددة المدمجة، يمكنك تحديد رسالة عدم العثور دون الحاجة إلى صيغ معقدة أو الوصول إلى XLOOKUP.

Kutools لـ Excel يقدم أكثر من 300 ميزة متقدمة لتبسيط المهام المعقدة، مما يعزز الإبداع والكفاءة. بالتكامل مع قدرات الذكاء الاصطناعي، يقوم Kutools بأتمتة المهام بدقة، مما يجعل إدارة البيانات أمرًا سهلاً للغاية. معلومات تفصيلية عن Kutools لـ Excel... نسخة تجريبية مجانية...

1. انتقل إلى تبويب "Kutools" في Excel، وابحث عن "بحث متقدم"، ثم انقر على "استبدال0 أو #N/A بفراغ أو قيمة محددة" في القائمة المنسدلة.

A screenshot of the Kutools feature Replace 0 or #N/A with Blank or a Specific Value in Excel

2. في مربع الحوار "استبدال0 أو #N/A بفراغ أو قيمة محددة"، تحتاج إلى التهيئة كما يلي:

  • في قسم "نطاق القيم المراد البحث فيها ونطاق الإخراج"، حدد نطاق البحث ونطاق الإخراج؛
  • ثم حدد خيار "استبدال0 أو #N/A بقيمة محددة"، وأدخل النص الذي تريده؛
  • في قسم "نطاق البيانات"، حدد نطاق البيانات، ثم حدد "العمود الرئيسي" و"عمود الإرجاع".

A screenshot of the Kutools dialog box for replacing #N/A errors with a custom message in Excel

3. انقر على زر موافق للحصول على النتيجة. ستظهر الرسالة المخصصة عند عدم العثور على تطابق.

A screenshot showing the result after using Kutools to replace #N/A error with a custom message

Kutools لـ Excel - قم بتعزيز Excel بأكثر من 300 أدوات أساسية. استمتع بميزات ذكاء اصطناعي مجانية بشكل دائم! احصل عليه الآن


المثال8: قيم متعددة

إرجاع قيم متعددة باستخدام XLOOKUP

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

في المثال أدناه، تريد الحصول على جميع المعلومات حول رقم الطالب "FG9940005". الحيلة هي توفير نطاق كـ return_array في الصيغة بدلاً من عمود أو صف واحد. في هذه الحالة، نطاق الإرجاع هو B2:D9، ويشمل ثلاثة أعمدة.

اكتب الصيغة التالية في الخلية الفارغة G2، واضغط على مفتاح Enter للحصول على جميع النتائج.

=XLOOKUP(F2,A2:A9,B2:D9)

A screenshot of the XLOOKUP function formula in cell G2 returning multiple values

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

A screenshot of the formula bar showing grayed-out non-editable formula in Excel

بشكل عام، تعد وظيفة القيم المتعددة في XLOOKUP تحسينًا مفيدًا مقارنة بـ VLOOKUP. لم تعد بحاجة لتحديد رقم كل عمود على حدة لكل صيغة. 👍

المثال9: معايير متعددة

تنفيذ بحث بمعايير متعددة باستخدام XLOOKUP

ميزة جديدة مذهلة أخرى في XLOOKUP هي قدرتها على البحث باستخدام معايير متعددة. الحيلة هي دمج قيم البحث ومصفوفات البحث باستخدام عامل الربط "&" بشكل منفصل في الصيغة. دعونا نوضح ذلك من خلال المثال أدناه.

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

=XLOOKUP(F2&G2&H2,A2:A12&B2:B12&C2:C12,D2:D12)

A screenshot of the XLOOKUP function formula in cell I2 for multi-criteria lookup

√ ملاحظة: يمكن لـ XLOOKUP معالجة المصفوفات مباشرة. لا حاجة لتأكيد الصيغة باستخدام Control + Shift + Enter.

البحث بشروط متعددة بطريقة سريعة

هل هناك طريقة أسرع وأسهل لتنفيذ بحث بمعايير متعددة من XLOOKUP في Excel؟ Kutools لـ Excel يوفر ميزة مذهلة - "البحث بشروط متعددة". باستخدام هذه الميزة، يمكنك تنفيذ بحث بمعايير متعددة ببضع نقرات فقط!

Kutools لـ Excel يقدم أكثر من 300 ميزة متقدمة لتبسيط المهام المعقدة، مما يعزز الإبداع والكفاءة. بالتكامل مع قدرات الذكاء الاصطناعي، يقوم Kutools بأتمتة المهام بدقة، مما يجعل إدارة البيانات أمرًا سهلاً للغاية. معلومات تفصيلية عن Kutools لـ Excel... نسخة تجريبية مجانية...

1. انتقل إلى تبويب "Kutools" في Excel، وابحث عن "بحث متقدم"، ثم انقر على "البحث بشروط متعددة" في القائمة المنسدلة.

A screenshot of the Kutools Multi-condition Lookup option in Excel

2. في مربع الحوار "البحث بشروط متعددة"، يرجى القيام بما يلي:

  • في قسم "نطاق القيم المراد البحث فيها ونطاق الإخراج"، حدد نطاق قيم البحث ونطاق الإخراج؛
  • في قسم "نطاق البيانات"، يرجى تنفيذ العمليات التالية:
    • حدد الأعمدة الرئيسية المقابلة التي تحتوي على قيم البحث واحدة تلو الأخرى مع الضغط على مفتاح Ctrl في مربع "المفتاح"؛
    • حدد العمود الذي يحتوي على القيم المرجعة في مربع "العودة".

A screenshot of the Kutools Multi-condition Lookup dialog box in Excel

3. انقر على زر موافق للحصول على النتيجة.

A screenshot of the Multi-condition Lookup results in Excel

√ ملاحظة:

  • قسم استبدال القيم غير الموجودة (#N/A) بقيمة محددة اختياري في مربع الحوار، يمكنك تحديده أو تجاهله.
  • يجب أن يكون عدد الأعمدة المدخلة في مربع العمود الرئيسي مساويًا لعدد الأعمدة المدخلة في مربع قيم البحث، ويجب أن يتوافق ترتيب المعايير في كلا المربعين واحدًا لواحد.

Kutools لـ Excel - قم بتعزيز Excel بأكثر من 300 أدوات أساسية. استمتع بميزات ذكاء اصطناعي مجانية بشكل دائم! احصل عليه الآن


المثال10: البحث عن القيمة مع آخر تطابق

الحصول على آخر نتيجة مطابقة باستخدام XLOOKUP

للعثور على آخر قيمة مطابقة في Excel، قم بتعيين الوسيط السادس للبحث بترتيب عكسي.

بشكل افتراضي، وضع البحث في XLOOKUP مضبوط على1، أي البحث من الأول إلى الأخير. لكن الجميل في XLOOKUP أنه يمكنك تغيير اتجاه البحث. توفر XLOOKUP الوسيط الاختياري [search mode] للتحكم في ترتيب البحث. فقط قم بتعيين وضع البحث في الوسيط السادس إلى -1، وسيتغير اتجاه البحث ليبدأ من الأخير إلى الأول.

يرجى مراجعة المثال أدناه. نريد معرفة آخر عملية بيع لـ Emma في قاعدة البيانات.

اكتب الصيغة أدناه في الخلية الفارغة G2، ثم اضغط على مفتاح Enter للحصول على النتيجة.

=XLOOKUP(F2,B2:B11,D2:D11,,,-1)

A screenshot of the XLOOKUP formula in cell G2 for finding the last matching value

√ ملاحظة: الوسيطان الرابع والخامس اختياريان وتم تجاهلهما في هذا المثال. قمنا فقط بتعيين الوسيط السادس الاختياري إلى -1.

ابحث بسهولة عن آخر قيمة مطابقة باستخدام أداة رائعة

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

Kutools لـ Excel يقدم أكثر من 300 ميزة متقدمة لتبسيط المهام المعقدة، مما يعزز الإبداع والكفاءة. بالتكامل مع قدرات الذكاء الاصطناعي، يقوم Kutools بأتمتة المهام بدقة، مما يجعل إدارة البيانات أمرًا سهلاً للغاية. معلومات تفصيلية عن Kutools لـ Excel... نسخة تجريبية مجانية...

1. انتقل إلى تبويب "Kutools" في Excel، وابحث عن "بحث متقدم"، ثم انقر على "البحث من الأسفل إلى الأعلى" في القائمة المنسدلة.

A screenshot of the Lookup from Bottom to Top option in Kutools tab in Excel

2. في مربع الحوار "البحث من الأسفل إلى الأعلى"، تحتاج إلى التهيئة كما يلي:

  • في قسم "نطاق القيم المراد البحث فيها ونطاق الإخراج"، حدد نطاق البحث ونطاق الإخراج؛
  • في قسم "نطاق البيانات"، حدد نطاق البيانات، ثم حدد "العمود الرئيسي" و"عمود الإرجاع".

A screenshot of the Lookup from Bottom to Top dialog box in Excel

3. انقر على زر موافق للحصول على النتيجة.

A screenshot of the Lookup from Bottom to Top results

√ ملاحظة: قسم استبدال القيم غير الموجودة (#N/A) بقيمة محددة اختياري في مربع الحوار، يمكنك تحديده أو تجاهله.

Kutools لـ Excel - قم بتعزيز Excel بأكثر من 300 أدوات أساسية. استمتع بميزات ذكاء اصطناعي مجانية بشكل دائم! احصل عليه الآن


تحميل ملف عينة XLOOKUP

XLOOKUP Examples.xlsx

مقالات ذات صلة:

أفضل أدوات الإنتاجية لمكتب العمل

🤖 Kutools AI Aide: أحدث ثورة في تحليل البيانات من خلال: التنفيذ الذكي |توليد الشيفرة |إنشاء الصيغ المخصصة |تحليل البيانات وتوليد الرسوم البيانية |استدعاء الوظائف المحسنة
الميزات الشائعة: البحث، تمييز أو وضع علامة على المكررات | حذف الصفوف الفارغة | دمج الأعمدة أو العنوان دون فقدان البيانات | تقريب ...
بحث متقدم: بحث V متعدد المعايير | بحث V متعدد القيم | بحث في عدة ورقات | مطابقة غامضة ....
قائمة منسدلة متقدمة: إنشاء قائمة منسدلة بسرعة | قائمة منسدلة تابعة | قائمة منسدلة متعددة الاختيار ....
إدارة الأعمدة: إضافة عدد محدد من الأعمدة | نقل الأعمدة | تبديل حالة إظهار الأعمدة المخفية | مقارنة النطاق والأعمدة ...
ميزات مميزة: التركيز على الشبكة | عرض التصميم | شريط الصيغ المحسن | أدوات المصنف & أدوات الورقة العمل | مكتبة النص التلقائي (Auto Text) | منتقي التاريخ | دمج البيانات | تشفير/فك تشفير الخلايا | إرسال البريد الإلكتروني حسب القائمة | مرشح متقدم | تصفية خاصة (تصفية الخلايا التي تحتوي على خط عريض/مائل/يتوسطه خط...) ...
أفضل15 مجموعة أدوات:12 أداة نصية (إضافة نص، حذف الأحرف المحددة، ...) | أكثر من50 نوع من الرسوم البيانية (مخطط جانت، ...) | أكثر من40 صيغة عملية (حساب العمر بناءً على تاريخ الميلاد، ...) |19 أداة إدراج (إدراج رمز الاستجابة السريعة، إدراج صورة من المسار، ...) |12 أداة تحويل (تحويل إلى كلمات، تحويل العملة، ...) |7 أدوات دمج وتقسيم (دمج متقدم للصفوف، تقسيم الخلايا، ...) | ... والمزيد

عزز مهاراتك في Excel مع Kutools لـ Excel، واختبر الكفاءة كما لم يحدث من قبل. Kutools لـ Excel يقدم أكثر من300 ميزة متقدمة لزيادة الإنتاجية وتوفير وقت الحفظ. انقر هنا للحصول على الميزة التي تحتاجها أكثر...


Office Tab يقدم واجهة التبويب لـ Office، ويجعل عملك أسهل بكثير

  • تمكين تحرير وقراءة المستندات عبر التبويبات في Word وExcel وPowerPoint.
  • افتح وأنشئ عدة مستندات في تبويبات جديدة ضمن نفس النافذة، بدلاً من فتحها في نوافذ منفصلة.
  • يزيد إنتاجيتك بنسبة50%، ويقلل مئات النقرات بالماوس يومياً!