البحث بمعايير متعددة باستخدام INDEX و MATCH

عند التعامل مع قاعدة بيانات كبيرة في جدول Excel يحتوي على عدة أعمدة وصفوف، يكون من الصعب دائمًا العثور على شيء يلبي معايير متعددة. في هذه الحالة، يمكنك استخدام صيغة صفيف مع دالتي INDEX و MATCH.

كيفية إجراء بحث باستخدام معايير متعددة؟

لاكتشاف المنتج الذي هو أبيض ومتوسط الحجم بسعر 18 دولارًا كما هو موضح في الصورة أعلاه، يمكنك الاستفادة من المنطق البولياني لتوليد صفيف من القيم 1 و 0 لتحديد الصفوف التي تلبي المعايير. ستقوم دالة MATCH بعد ذلك بإيجاد موقع أول صف يلبي جميع المعايير. بعد ذلك، تقوم دالة INDEX بإيجاد معرف المنتج المقابل في نفس الصف.

الصيغة العامة

=INDEX(نطاق_الإرجاع,MATCH(1,(قيمة_المعيار1=نطاق_المعيار1*قيمة_المعيار2=نطاق_المعيار2*(…),0))

√ ملاحظة: هذه صيغة صفيف تتطلب منك الإدخال باستخدام Ctrl + Shift + Enter.

لايجاد المنتج الذي هو أبيض ومتوسط الحجم بسعر 18 دولارًا، يرجى نسخ أو إدخال الصيغة أدناه في الخلية H8، ثم الضغط على Ctrl + Shift + Enter للحصول على النتيجة:

=INDEX(B5:B10,MATCH(1,("White"=C5:C10)*("Medium"=D5:D10)*(18=E5:E10),0))

أو، استخدم مرجع خلية لجعل الصيغة ديناميكية:

=INDEX(B5:B10,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))

شرح الصيغة

=INDEX(B5:B10,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))

• (h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10): تقارن الصيغة اللون في الخلية h3 مع جميع الألوان في النطاق C5:C10؛ تقارن الحجم في H5 مع جميع الأحجام في D5:D10؛ تقارن السعر في H6 مع جميع الأسعار في E5:E10. النتيجة الأولية تكون كالتالي:
  {TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}*{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE}*{FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}.
  ستحول عملية الضرب القيم TRUE و FALSE إلى 1 و 0:
  {1;0;1;0;1;0}*{0;0;1;1;1;0}*{0;0;0;1;1;0}.
  بعد الضرب، سنحصل على صفيف واحد كالتالي:
  {0;0;0;0;1;0}.
• MATCH(1,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0),0) = MATCH(1,MATCH(1,{0;0;0;0;1;0},0),0): نوع التطابق 0 يطلب من دالة MATCH إيجاد التطابق الدقيق. ستقوم الدالة بإرجاع موقع القيمة 1 في الصفيف {0;0;0;0;1;0}، وهو 5.
• INDEX(B5:B10B5:B10,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)) = INDEX(B5:B10B5:B10,5): تقوم دالة INDEX بإرجاع القيمة الخامسة في نطاق معرف المنتج B5:B10، وهي 30005.

الدوال ذات الصلة

الصيغ ذات الصلة