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

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

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

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

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

=INDEX(النطاق_المرجعي,(MATCH(1,MMULT(--(مصفوفة_البحث=قيمة_البحث),TRANSPOSE(COLUMN(مصفوفة_البحث)^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(TRANSPOSE(COLUMN($C$5:$E$7)^0)^0) = TRANSPOSE(TRANSPOSE({3,4,5}^0)^0): بعد رفع القوة إلى 0، سيتم تحويل جميع الأرقام في الصفيف {3,4,5} إلى 1: {1,1,1}. ثم تقوم دالة TRANSPOSE بتحويل صفيف العمود إلى صفيف صف كالتالي: {1;1;1}.
• MMULT(MMULT(--($C$5:$E$7=G5),,TRANSPOSE(COLUMN($C$5:$E$7)^0))) = MMULT(MMULT({1,0,0;0,0,0;0,0,0},,{1;1;1})): تقوم دالة MMULT بإرجاع حاصل ضرب المصفوفتين كالتالي: {1;0;0}.
• MATCH(1,MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0),0) = MATCH(1,MATCH(1,{1;0;0},0),0): يجبر نوع_المطابقة 0 دالة MATCH على إرجاع موقع أول تطابق لـ 1 في الصفيف {1;0;0}، وهو 1.
• INDEX($B$5:$B$7$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$B$5:$B$7,,,1): تقوم دالة INDEX بإرجاع القيمة الأولى في نطاق الفئة $B$5:$B$7، وهي A.

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

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

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