INDEX وMATCH عبر أعمدة متعددة

للحصول على قيمة بالبحث عبر أعمدة متعددة، ستفيدك كثيرًا صيغة مصفوفة تعتمد على دالتيINDEX وMATCH، والتي تتضمن دالاتMMULT،TRANSPOSE، وCOLUMN.

كيف تبحث عن قيمة بالبحث عبر أعمدة متعددة؟

لملءفئة كل طالب المقابلةكما هو موضح في الجدول أعلاه، حيث تكونالمعلومات موزعة عبر أعمدة متعددة، يمكنك أولًا استخدام خدعة الدوال MMULT وTRANSPOSE وCOLUMN لإنشاء مصفوفة. بعد ذلك، ستُعطيك دالة MATCH موضع قيمة البحث الخاصة بك، والذي سيتم تمريره إلى دالة INDEX لاسترداد القيمة التي تبحث عنها من المصفوفة.

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

=INDEX()return_range,(MATCH(1,MMULT(--())))lookup_array=lookup_value),TRANSPOSE(COLUMN()lookup_array)^0)),0)))

√ ملاحظة: هذه صيغة مصفوفة تتطلب الضغط علىCtrl+Shift+Enter.

للعثور علىفئة جيمي، يُرجى نسخ الصيغة أدناه أو إدخالها في الخلية H5، ثم الضغط علىCtrl+Shift+Enterللحصول على النتيجة:

=INDEX()$B$5:$B$7،(MATCH(1،MMULT(--())))$C$5:$E$7=G5)،TRANSPOSE(COLUMN()$C$5:$E$7)^0))،0)))

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

شرح الصيغة

=INDEX()$B$5:$B$7,(MATCH(1,))MMULT()--($C$5:$E$7=G5),TRANSPOSE()COLUMN($C$5:$E$7)^0)),0)))

• --($C$5:$E$7=G5):يقوم هذا الجزء بالتحقق من كل قيمة في النطاق$C$5:$E$7 لمعرفة ما إذا كانت تساوي القيمة الموجودة في الخلية G5، ويُنشئ مصفوفة من القيم TRUE وFALSE على النحو التالي:
  {TRUE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE}.
  ثم يحوّل العامل السالب المضاعف قيم TRUE وFALSE إلى 1 و0 لإنتاج مصفوفة كالتالي:
  {1,0,0;0,0,0;0,0,0}.
• COLUMN($C$5:$E$7):تُرجع دالة COLUMN أرقام الأعمدة المقابلة للنطاق$C$5:$E$7 على هيئة مصفوفة كالتالي: {3,4,5}.
• TRANSPOSE()COLUMN($C$5:$E$7)^0)=TRANSPOSE(){3,4,5}^0):بعد رفع الأرقام إلى الأس 0، تصبح جميع القيم في المصفوفة {3،4،5} مساوية لـ 1، أي {1،1،1}. بعد ذلك، تقوم دالة TRANSPOSE بتحويل هذه المصفوفة الأفقية إلى مصفوفة عمودية على النحو التالي:{1;1;1}.
• MMULT()--($C$5:$E$7=G5),TRANSPOSE()COLUMN($C$5:$E$7)^0))=MMULT(){1,0,0;0,0,0;0,0,0},{1;1;1}):تُرجع دالة MMULT ناتج ضرب المصفوفتين على النحو التالي: {1;0;0}.
• MATCH(1,)MMULT()--($C$5:$E$7=G5),TRANSPOSE()COLUMN($C$5:$E$7)^0)),0)=MATCH(1,){1;0;0},0):يُجبر نوع_المطابقة0 دالة MATCH على إرجاع موضع أول تطابق لـ1 في المصفوفة{1;0;0}، وهو1.
• INDEX()$B$5:$B$7,(MATCH(1,))MMULT()--($C$5:$E$7=G5),TRANSPOSE()COLUMN($C$5:$E$7)^0)),0))) = INDEX($B$5:$B$7تقوم دالة INDEX بإرجاع القيمة الأولى في نطاق الفئات$B$5:$B$7، وهيA.

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

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

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