انتقل إلى المحتوى الرئيسي

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

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

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

كيف تحصل على XLOOKUP؟

بناء جملة الدالة XLOOKUP

أمثلة على وظائف XLOOKUP

قم بتنزيل نموذج ملف XLOOKUP

كيف تحصل على XLOOKUP؟

منذ دالة XLOOKUP is الشاغرة فقط in Excel لـ Microsoft 365, التفوق 2021و Excel للويب، يمكنك ترقية Excel إلى الإصدار المتاح للحصول على XLOOKUP.

بناء جملة الدالة XLOOKUP

وظيفة XLOOKUP يبحث عن نطاق أو مصفوفة ثم يعرض قيمة أول نتيجة مطابقة. بناء الجملة من وظيفة XLOOKUP كما يلي:

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

xlookup وظيفة 1

الحجج:

  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 مرتبة بترتيب تنازلي. إذا لم يتم الفرز ، ستكون نتيجة الإرجاع غير صالحة.

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

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

=XLOOKUP(

xlookup وظيفة 2

2. صحافة CTRL + A، ثم مربع موجه الملوثات العضوية الثابتة التي تظهر الحجج الوظيفية. ويتم إنهاء الجانب الآخر من الحامل تلقائيًا.

xlookup وظيفة 3

3. اسحب لوحة البيانات، ثم يمكنك رؤية كل شيء ست وظائف الحجج من XLOOKUP.

xlookup وظيفة 4 >>> xlookup وظيفة 5

أمثلة على وظائف XLOOKUP

أنا متأكد من أنك قد أتقنت المبادئ الأساسية لوظيفة XLOOKUP الآن. دعنا نتعمق في ملف أمثلة عملية من XLOOKUP.

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

قم بإجراء تطابق تام مع XLOOKUP

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

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

xlookup وظيفة 6

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

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

xlookup وظيفة 7

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

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

ربما تستخدم إصدارًا أقل من Excel وليس لديك خطة للترقية إلى Excel 2021 أو Microsoft 365 حتى الآن. في هذه الحالة ، سأوصي ميزة يدوية - ابحث عن قيمة في صيغة القائمة of كوتولس ل إكسيل. باستخدام هذه الميزة ، يمكنك الحصول على النتيجة بدون صيغ معقدة أو الوصول إلى XLOOKUP.

مع سur وظيفة Excel الإضافية معاهدalled ، يرجى القيام بما يلي:

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

2. انتقل إلى كوتولس ، انقر فوق مساعد الصيغة، ثم انقر فوق مساعد الصيغة في القائمة المنسدلة.

xlookup وظيفة 8

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

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

xlookup وظيفة 9

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

xlookup وظيفة 10

انقر لتنزيل Kutools for Excel للحصول على نسخة تجريبية مجانية مدتها 30 يومًا.


مثال 2. تطابق تقريبي

قم بإجراء تطابق تقريبي مع XLOOKUP

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

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

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

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

xlookup وظيفة 11 >>> xlookup وظيفة 12

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

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

  • انقر نقرًا مزدوجًا فوق الخلية E2 لإظهار الصيغة = XLOOKUP (D2، B2: B8، A2: A8،، 1) ؛
  • حدد نطاق البحث B2: B8 في الصيغة ، اضغط على مفتاح F4 لتحصل على 2 دولار: 8 دولارات أمريكية ؛
  • حدد نطاق الإرجاع A2: A8 في الصيغة ، اضغط على مفتاح F4 لتحصل على 2 دولار أسترالي: 8 دولارات أسترالية ؛
  • اضغط على أدخل زر للحصول على نتيجة الخلية E2.
xlookup وظيفة 13 >>> xlookup وظيفة 14

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

xlookup وظيفة 15

√ ملاحظة:

  • الضغط على مفتاح 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: تطابق حرف البدل

قم بإجراء مباراة Wildcard مع XLOOKUP

قبل أن ننظر في دالة مطابقة أحرف البدل في XLOOKUP، دعنا نرى أولاً ما هي أحرف البدل.

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

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

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

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

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

xlookup وظيفة 16

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

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

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

xlookup وظيفة 17 >>> xlookup وظيفة 18

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

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

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

xlookup وظيفة 19

√ ملاحظة:

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

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

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

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

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

xlookup وظيفة 20

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

1. اكتب ملف الصيغة أدناه في الخلية الفارغة G2.

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

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

xlookup وظيفة 21

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

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

بالنسبة لأولئك الذين لا يريدون تذكر الصيغ ، سأوصي هنا ميزة مفيدة - ابحث من اليمين إلى اليسار of كوتولس ل إكسيل. باستخدام هذه الميزة ، يمكنك إجراء بحث من اليمين إلى اليسار في غضون ثوانٍ قليلة.

مع سur وظيفة Excel الإضافية معاهدalled ، يرجى القيام بما يلي:

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

xlookup وظيفة 22

2. في ال LOOKUP من مربع الحوار من اليمين إلى اليسار، تحتاج إلى التكوين على النحو التالي:

  • في مجلة قيم البحث وقسم نطاق الإخراج، حدد نطاق البحث و مجموعة الانتاج;
  • في مجلة قسم نطاق البيانات، إدخال نطاق البيانات، ثم حدد عمود المفتاح و عمود العودة;

xlookup وظيفة 23

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

xlookup وظيفة 24

انقر لتنزيل Kutools for Excel للحصول على نسخة تجريبية مجانية مدتها 30 يومًا.


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

قم بإجراء بحث عمودي أو أفقي باستخدام XLOOKUP

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

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

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

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

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

xlookup وظيفة 25

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

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

xlookup وظيفة 26

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

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

قم بإجراء بحث ثنائي الاتجاه باستخدام XLOOKUP

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

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

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

xlookup وظيفة 43

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

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

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

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

xlookup وظيفة 27

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

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

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

xlookup وظيفة 28

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

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

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

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

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

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

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

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

xlookup وظيفة 29

تخصيص خطأ # N / A بميزة يدوية

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

مع شركائنا وظيفة Excel الإضافية مثبت ، يرجى القيام بما يلي:

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

xlookup وظيفة 30

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

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

xlookup وظيفة 31

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

xlookup وظيفة 32

انقر لتنزيل Kutools for Excel للحصول على نسخة تجريبية مجانية مدتها 30 يومًا.


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

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

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

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

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

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

xlookup وظيفة 33

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

xlookup وظيفة 34

الكل في الكل ، دالة القيم المتعددة في XLOOKUP هي ملف تحسين مفيد مقارنة بـ VLOOKUP. لا يمكنك تحديد رقم كل عمود بشكل منفصل لكل صيغة. ممتاز!

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

قم بإجراء بحث متعدد المعايير باستخدام XLOOKUP

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

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

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

xlookup وظيفة 35

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

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

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

مع شركائنا وظيفة Excel الإضافية مثبت ، يرجى القيام بما يلي:

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

xlookup وظيفة 36

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

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

xlookup وظيفة 37

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

xlookup وظيفة 38

√ ملاحظة:

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

انقر لتنزيل Kutools for Excel للحصول على نسخة تجريبية مجانية مدتها 30 يومًا.


مثال 10. أوجد قيمة مع آخر تطابق

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

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

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

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

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

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

xlookup وظيفة 39

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

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

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

مع شركائنا وظيفة Excel الإضافية مثبت ، يرجى القيام بما يلي:

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

xlookup وظيفة 40

2. في ال LOOKUP من مربع الحوار السفلي إلى الأعلى، تحتاج إلى التكوين على النحو التالي:

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

xlookup وظيفة 41

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

xlookup وظيفة 42

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

انقر لتنزيل Kutools for Excel للحصول على نسخة تجريبية مجانية مدتها 30 يومًا.


قم بتنزيل نموذج ملف XLOOKUP

أمثلة XLOOKUP.xlsx

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


  • سوبر فورميولا بار (بسهولة تحرير أسطر متعددة من النص والصيغة) ؛ تخطيط القراءة (قراءة وتحرير أعداد كبيرة من الخلايا بسهولة) ؛ لصق في النطاق المصفى
  • دمج الخلايا / الصفوف / الأعمدة وحفظ البيانات. تقسيم محتوى الخلايا ؛ اجمع الصفوف المكررة والمجموع / المتوسط... منع تكرار الخلايا؛ قارن النطاقات
  • حدد مكرر أو فريد صفوف حدد صفوف فارغة (جميع الخلايا فارغة) ؛ البحث الفائق والبحث الغامض في العديد من المصنفات. تحديد عشوائي ...
  • نسخة طبق الأصل خلايا متعددة بدون تغيير مرجع الصيغة ؛ إنشاء المراجع تلقائيًا إلى أوراق متعددة أدخل الرموز النقطية، مربعات الاختيار والمزيد ...
  • المفضلة وإدراج الصيغ بسرعةوالنطاقات والمخططات والصور ؛ تشفير الخلايا مع كلمة السر إنشاء قائمة بريدية وإرسال رسائل البريد الإلكتروني ...
  • استخراج النص، إضافة نص ، إزالة حسب الموضع ، إزالة الفضاء؛ إنشاء وطباعة المجاميع الفرعية لترحيل الصفحات ؛ التحويل بين محتوى الخلايا والتعليقات
  • سوبر تصفية (حفظ وتطبيق مخططات التصفية على أوراق أخرى) ؛ فرز متقدم حسب الشهر / الأسبوع / اليوم ، التكرار والمزيد ؛ مرشح خاص بواسطة bold، italic ...
  • اجمع بين المصنفات وأوراق العمل؛ دمج الجداول على أساس الأعمدة الرئيسية ؛ تقسيم البيانات إلى أوراق متعددة; تحويل دفعة xls و xlsx و PDF
  • تجميع الجدول المحوري حسب رقم الأسبوع واليوم من الأسبوع والمزيد ... إظهار الخلايا غير المؤمنة والمغلقة بألوان مختلفة قم بتمييز الخلايا التي لها صيغة / اسم
علامة تبويب kte 201905
  • تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
  • فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
  • يزيد من إنتاجيتك بنسبة 50٪ ، ويقلل مئات النقرات بالماوس كل يوم!
أوفيسيتاب القاع
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations