الوظيفتان INDEX وMATCH مع مصفوفات متعددة

لنفترض أن لديك عدة جداول بنفس العناوين كما هو موضح أدناه، البحث عن القيم التي تطابق المعايير المعطاة من هذه الجداول قد يكون مهمة صعبة بالنسبة لك. في هذا الدرس، سنتحدث عن كيفية البحث عن قيمة عبر مصفوفات أو نطاقات أو مجموعات متعددة عن طريق مطابقة معايير محددة باستخدام الوظائف INDEX وMATCH وCHOOSE.

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

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

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

=INDEX(CHOOSE(array_num,array1,array2,…),MATCH(lookup_value,lookup_array,0),column_num)

لمعرفة قائد مجموعة D التي تنتمي إلى القسم A، يرجى نسخ أو إدخال الصيغة أدناه في الخلية G5، ثم اضغط على Enter للحصول على النتيجة:

=INDEX(CHOOSE(1,$B$5:$C$8,,$B$11:$C$14,,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)

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

شرح الصيغة

=INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)

• CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20): تقوم وظيفة CHOOSE بإرجاع المصفوفة الأولى من بين المصفوفات الثلاثة المدرجة في الصيغة. وبالتالي، ستعيد $B$5:$C$8، أي نطاق بيانات القسم A.
• MATCH(F5,$B$5:$B$8,0): يجبر match_type 0 وظيفة MATCH على إرجاع موقع أول تطابق لمجموعة D، وهي القيمة الموجودة في الخلية F5، في المصفوفة $B$5:$B$8، وهو 4.
• INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),22) = INDEX($B$5:$C$8,4,22): تقوم وظيفة INDEX باسترداد القيمة عند تقاطع الصف الرابع والعمود الثاني من النطاق $B$5:$C$8، وهي Emily.

لتجنب تغيير array_num في الصيغة كلما نسختها، يمكنك استخدام عمود المساعدة، وهو العمود D. ستكون الصيغة كالتالي:

=INDEX(CHOOSE(D5,$B$5:$C$8,,$B$11:$C$14,,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)

√ ملاحظة: الأرقام 1، 2، 3 في عمود المساعدة تشير إلى array1، array2، array3 داخل وظيفة CHOOSE.

الوظائف ذات الصلة

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