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

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

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

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

بناء الجملة

أمثلة

• مثال 1: مطابقة دقيقة
  • تنفيذ مطابقة دقيقة باستخدام XLOOKUP
  • بضع نقرات فقط للحصول على مطابقة دقيقة

• مثال 2: مطابقة تقريبية
  • تنفيذ مطابقة تقريبية باستخدام XLOOKUP

• مثال 3: مطابقة باستخدام البدائل
  • تنفيذ مطابقة باستخدام البدائل (Wildcard) باستخدام XLOOKUP

• مثال 4: البحث من اليمين إلى اليسار
  • البحث من اليمين إلى اليسار باستخدام XLOOKUP
  • البحث عن قيمة من اليمين إلى اليسار بنقرات قليلة

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

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

• مثال 7: تخصيص رسالة «غير موجود»
  • تخصيص رسالة «غير موجود» باستخدام XLOOKUP
  • تخصيص خطأ #N/A بميزة مفيدة

• مثال 8: قيم متعددة
  • إرجاع قيم متعددة باستخدام XLOOKUP

• مثال 9: معايير متعددة
  • تنفيذ بحث متعدد المعايير باستخدام XLOOKUP
  • البحث - البحث بشروط متعددة بطريقة سريعة

• مثال 10: العثور على القيمة مع آخر تطابق
  • الحصول على آخر نتيجة مطابقة باستخدام XLOOKUP
  • البحث بسهولة عن آخر قيمة مطابقة باستخدام أداة رائعة

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

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

بما أن دالة XLOOKUP متاحة فقط في Excel لـ Microsoft 365 وExcel 2021 والإصدارات الأحدث وExcel للويب، ففكّر في الترقية إذا كنت تستخدم Excel 2019 أو إصدارًا أقدم للوصول إلى هذه الدالة المتطورة.

بناء الجملة

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

الوسيطات:

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

للحصول على معلومات مفصلة حول الوسيطات، يُرجى اتباع الخطوات التالية:

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

2. اضغط Ctrl+A، فتظهر نافذة منبثقة تعرض وسيطات الدالة، ويُكمَل الجانب الآخر من الأقواس تلقائيًا.

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

>>>

أمثلة

أنا متأكد أنك قد أتقنت الآن المبادئ الأساسية لدالة XLOOKUP. دعنا نغوص مباشرةً في أمثلة عملية تُظهر كيفية استخدام XLOOKUP.

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

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

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

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

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

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

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

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

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

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

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

3. في مربع حوار «مساعد الصيغة»، يُرجى ضبط الإعدادات كما يلي:

• اختر «Lookup» في قسم «نوع الصيغة»؛
• في قسم «حدد صيغة»، اختر «البحث عن البيانات في نطاق»؛
• في قسم «إدخال الوسيط»، يُرجى القيام بما يلي:
  • في مربع «Table_array»، حدد نطاق البيانات الذي يحتوي على قيمة البحث والقيمة الناتجة؛
  • في مربع «Lookup_value»، حدد الخلية أو النطاق الذي يحتوي على القيمة التي تبحث عنها. يُرجى ملاحظة أنه يجب أن يكون موجودًا في العمود الأول من table_array.
  • في مربع «Column»، حدد العمود الذي تريد استرجاع القيمة المطابقة منه.

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

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

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

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

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

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

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

>>>

✓ ملاحظة: الوسيطة الرابعة [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.

>>>

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

√ ملاحظة:

• يتيح لك الضغط على مفتاح 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 match)

تنفيذ مطابقة باستخدام البدائل (Wildcard) باستخدام XLOOKUP

قبل أن نتعمق في وظيفة مطابقة البدائل في XLOOKUP، دعنا أولًا نتعرف على الرموز البديلة (Wildcards).

في Microsoft Excel، الرموز البديلة هي أحرف خاصة تُستخدم لاستبدال سلاسل نصية دفعةً واحدة، وتُعدّ مفيدة جدًّا عند تنفيذ عمليات بحث تعتمد على مطابقة جزئية.

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

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

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

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

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

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

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

>>>

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

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

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

√ ملاحظة:

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

المثال 4: البحث إلى اليسار

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

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

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

لديك قائمة بالدول مع رموزها الهاتفية، وترغب في العثور على اسم الدولة باستخدام رمز هاتفي معيّن.

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

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

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

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

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

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

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

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

2. في مربع حialog «البحث من اليمين إلى اليسار»، قم بالتهيئة على النحو التالي:

• في قسم «منطقة البحث والنتائج»، حدّد نطاق البحث ومنطقة وضع القائمة؛
• في قسم «نطاق البيانات»، أدخل نطاق البيانات، ثم حدّد «العمود الرئيسي» و«عمود الإرجاع»؛

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

المثال 5: البحث الرأسي أو الأفقي

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

بصفتك مستخدمًا لبرنامج Excel، فقد تكون على دراية بدالتي VLOOKUP وHLOOKUP؛ إذ تُستخدم VLOOKUP للبحث عموديًا ضمن عمود، بينما تُستخدم HLOOKUP للبحث أفقيًا عبر صف.

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

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

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

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

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

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

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

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

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

هل ما زلت تستخدم دالتيINDEX وMATCHللحصول على قيمة من جدول ثنائي الأبعاد؟ جرّب دالة XLOOKUP المحسّنة وأنهِ مهامك بسهولة أكبر!

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

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

دعنا نستعرض معًا كيفية استخدام الدالة السحرية XLOOKUP لتنفيذ هذه الخدعة!

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

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

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

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

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

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

تُعد ميزة البحث ثنائي الاتجاه (Two-way lookup) في دالة XLOOKUP مثالًا مثاليًا يبرز قدرتها على الجمع بين البحث الرأسي والأفقي في آنٍ واحد! جرّبها إذا رغبت!

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

تخصيص رسالة «غير موجود» باستخدام XLOOKUP

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

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

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

اكتب الصيغة التالية في الخلية الفارغة F3، ثم اضغط على زر الإدخال (Enter) للحصول على النتيجة.

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

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

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

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

2. في مربع حوار «استبدال 0 أو #N/A بخلية فارغة أو بقيمة محددة»، قم بالتهيئة على النحو التالي:

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

3. انقر على زر «موافق» (OK) للحصول على النتيجة. وستظهر الرسالة المخصصة إذا لم يُعثر على أي تطابق.

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

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

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

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

أدخل الصيغة التالية في الخلية الفارغة G2، ثم اضغط مفتاح الإدخال (Enter) للحصول على جميع النتائج.

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

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

بشكل عام، تُعد ميزة إرجاع القيم المتعددة في دالة XLOOKUP تحسينًا مفيدًا مقارنةً بدالة VLOOKUP، إذ لم تعد بحاجة إلى تحديد رقم العمود لكل صيغة على حدة. إبهارٌ رائع!

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

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

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

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

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

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

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

هل تبحث عن طريقة أسرع وأسهل لإجراء بحث متعدد المعايير في Excel مقارنةً بدالة XLOOKUP؟Kutools لـ Excelتوفر ميزة رائعة تُسمى «البحث – البحث بشروط متعددة». باستخدام هذه الميزة، يمكنك تنفيذ بحث متعدد المعايير بنقراتٍ قليلة فقط!

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

2. في مربع حوار «البحث - البحث بشروط متعددة»، يُرجى اتباع الخطوات التالية:

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

3. انقر على زر «موافق» (OK) للحصول على نتيجتك بسرعة!

√ ملاحظة:

• يُتيح لك قسم استبدال النتيجة المخرَجة التي لم يتم العثور عليها (والتي تُعيد ‹#N/A›) بإدخال قيمة مخصصة في مربع الحوار، ويمكنك اختيار تفعيل هذا الخيار أو تركه غير محدد.
• يجب أن يساوي عدد الأعمدة المدخلة في مربع العمود الرئيسي عدد الأعمدة المدخلة في مربع نطاق قيمة البحث، ويجب أن يتوافق ترتيب المعايير في كلا المربعين واحدًا تلو الآخر

مثال 10: العثور على القيمة باستخدام آخر تطابق

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

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

بشكل افتراضي، يُضبط وضع البحث في دالة XLOOKUP على القيمة 1، أي من الأول إلى الأخير. لكن الميزة الرائعة في XLOOKUP تكمن في إمكانية عكس اتجاه البحث بسهولة! فباستخدام المعامل الاختياري [search_mode]، يمكنك التحكّم الكامل في ترتيب البحث. كل ما عليك فعله هو تعيين القيمة -1 لهذا المعامل (وهو المعامل السادس)، ليتم البحث من الأخير إلى الأول.

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

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

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

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

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

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

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

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

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

3. انقر على زر «موافق» (OK) للحصول على نتيجتك.

√ ملاحظة: يتيح لك قسم «استبدال القيمة المخرَجة عند عدم العثور على نتيجة» — والتي تُعيد عادةً ‹#N/A› — بإدخال قيمة مخصصة في مربع الحوار، ويمكنك اختيار تعيينها أو تخطي هذه الخطوة.

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

أمثلة XLOOKUP.xlsx

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

• كيف تستخدم دالتي INDEX وMATCH معًا في Excel؟
• كيفية تطبيق مطابقة غامضة للعثور على نتيجة مطابقة تقريبية في Excel؟
• كيفية استخدام صيغة البحث ثنائي الاتجاه في Excel؟
• كيفية البحث عن قيمة باستخدام Vlookup مع معايير متعددة في Excel؟
• كيفية البحث عن قيم باستخدام Vlookup من اليمين إلى اليسار في Excel؟

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

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

استخدم Kutools باللغة التي تفضلها – يدعم الإنجليزية والإسبانية والألمانية والفرنسية والصينية و40+ لغات أخرى!

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

مزيد من التفاصيل حول Kutools لـ Excel...تنزيل مجاني...

---

يجلب Office Tab واجهة ذات علامات تبويب إلى Office، ويجعل عملك أسهل بكثير

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

مزيد من التفاصيل حول Office Tab...تنزيل مجاني...

---

جميع الإضافات من Kutools في برنامج تثبيت واحد!

Kutools for Office حزمةٌ تحتوي على إضافاتٍ مخصصة لتطبيقات Excel وWord وOutlook وPowerPoint، إلى جانب Office Tab Pro، مما يجعلها الخيار المثالي للفِرق التي تعمل عبر تطبيقات Office.

• حزمة شاملة واحدة— إضافات Excel وWord وOutlook وPowerPoint بالإضافة إلى Office Tab Pro
• برنامج تثبيت واحد، ترخيص واحد— الإعداد خلال دقائق (جاهز لـ MSI)
• يعمل بشكل أفضل معًا— إنتاجية ميسَّرة عبر تطبيقات Office
• تجربة مجانية لمدة 30 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
• أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل

مزيد من التفاصيل حول Kutools for Office...تنزيل مجاني...