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

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

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

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

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

=INDEX()return_range,MATCH(1,())criteria_value1=criteria_range1*criteria_value2=criteria_range2*(…),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,)(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)=MATCH(1,)يطلب معامل نوع المطابقة0 من دالةMATCH العثور على تطابق تام. ثم تُرجع الدالة موضع القيمة1 في المصفوفة{0;0;0;0;1;0}، وهو5.
• INDEX()B5:B10,MATCH(1,)(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)) = INDEX(B5:B10تقوم دالة INDEX بإرجاع القيمة الخامسة في النطاقB5:B10، وهي30005.

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

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