INDEX و MATCH مع مصفوفات متعددة
لنفترض أن لديك عدة جداول بنفس التسميات التوضيحية كما هو موضح أدناه ، فقد يكون البحث عن القيم التي تطابق معايير إعطاء من هذه الجداول مهمة صعبة بالنسبة لك. في هذا البرنامج التعليمي ، سنتحدث عن كيفية البحث عن قيمة عبر مصفوفات أو نطاقات أو مجموعات متعددة من خلال مطابقة معايير محددة مع INDEX, MATCH و اختار الوظائف.
كيف تبحث عن قيمة عبر مصفوفات متعددة؟
لمعرفة قادة المجموعات المختلفة التي تنتمي إلى أقسام مختلفة، يمكنك أولاً استخدام وظيفة CHOOSE لاستهداف الجدول لإرجاع اسم القائد منه. ستكتشف وظيفة MATCH بعد ذلك موقع القائد في الجدول الذي ينتمي إليه / هي. أخيرًا ، ستقوم وظيفة INDEX باسترداد القائد بناءً على معلومات الموقع بالإضافة إلى العمود المحدد حيث يتم سرد أسماء القادة.
بناء جملة عام
=INDEX(CHOOSE(array_num,array1,array2,…),MATCH(lookup_value,lookup_array,0),column_num)
- صفيف_رقم: الرقم CHOOSE المستخدم للإشارة إلى مصفوفة من القائمة المصفوفة 1 ، المصفوفة 2 ، ... لإرجاع النتيجة من.
- المصفوفة 1 ، المصفوفة 2 ، ...: المصفوفات المطلوب إرجاع النتيجة منها. هنا يشير إلى الجداول الثلاثة.
- ابحث عن القيمة: القيمة المستخدمة في الصيغة المركبة للعثور على موضع قائدها المقابل. هنا يشير إلى مجموعة معينة.
- lookup_array: نطاق الخلايا حيث يكون ملف ابحث عن القيمة مدون. هنا يشير إلى نطاق المجموعة. ملاحظة: يمكنك استخدام نطاق المجموعة من أي قسم حيث أنهم جميعًا متماثلون ونحتاج فقط للحصول على رقم الوظيفة.
- العمود: العمود الذي تحدده والذي تريد استرداد البيانات منه.
لمعرفة قائد المجموعة د التي تنتمي إلى القسم أ، الرجاء نسخ أو إدخال الصيغة أدناه في الخلية G5 ، واضغط أدخل للحصول على النتيجة:
= فهرس (اختر (1,5 دولارات أمريكية: 8 دولارات كندية,11 دولارات أمريكية: 14 دولارات كندية,17 دولارات أمريكية: 20 دولارات كندية)،تطابق(F5,5 دولارات أمريكية: 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 ملف 1صفيف st من المصفوفات الثلاثة المدرجة في الصيغة. لذلك سوف يعود 5 دولارات أمريكية: 8 دولارات كندية، على سبيل المثال ، نطاق بيانات القسم أ.
- المباراة (F5، B $ 5: $ 8,0،XNUMX B $): نوع match_type 0 يفرض على دالة MATCH إرجاع موضع المباراة الأولى لـ المجموعة د، القيمة الموجودة في الخلية F5، في المصفوفة 5 دولارات أمريكية: 8 مليار دولار أمريكي، والذي هو 4.
- فهرس(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),المباراة (F5، B $ 5: $ 8,0،XNUMX B $),2) = الفهرس (5 دولارات أمريكية: 8 دولارات كندية,4,2): تسترد الدالة INDEX القيمة عند تقاطع ملف 4الصف العاشر و 2العمود الثاني من النطاق 5 دولارات أمريكية: 8 دولارات كندية، والذي هو إميلي.
لتجنب التغيير array_num في الصيغة في كل مرة تقوم فيها بنسخها ، يمكنك استخدام العمود المساعد ، العمود D. ستكون الصيغة كما يلي:
= فهرس (اختر (D5,5 دولارات أمريكية: 8 دولارات كندية,11 دولارات أمريكية: 14 دولارات كندية,17 دولارات أمريكية: 20 دولارات كندية)،تطابق(F5,5 دولارات أمريكية: 8 مليار دولار أمريكي، 0) ،2)
√ ملاحظة: الأرقام 1, 2, 3 في العمود المساعد تشير إلى array1, array2, array3 داخل وظيفة الاختيار.
وظائف ذات الصلة
ترجع الدالة Excel INDEX القيمة المعروضة بناءً على موضع معين من نطاق أو صفيف.
تبحث دالة Excel MATCH عن قيمة معينة في نطاق من الخلايا ، وتُرجع الموضع النسبي للقيمة.
ترجع الدالة CHOOSE قيمة من قائمة وسيطة القيمة بناءً على رقم الفهرس المحدد. على سبيل المثال ، تقوم CHOOSE (3 ، "Apple" ، "Peach" ، "Orange") بإرجاع Orange ، ورقم الفهرس هو 3 ، و Orange هي القيمة الثالثة بعد رقم الفهرس في الوظيفة.
الصيغ ذات الصلة
قيم البحث من ورقة عمل أو مصنف آخر
إذا كنت تعرف كيفية استخدام وظيفة VLOOKUP للبحث عن القيم في ورقة عمل ، فلن تمثل قيم vlookup من ورقة عمل أو مصنف آخر مشكلة بالنسبة لك.
في كثير من الحالات ، قد تحتاج إلى جمع البيانات عبر أوراق عمل متعددة للحصول على ملخص. باستخدام تركيبة دالة VLOOKUP والدالة INDIRECT ، يمكنك إنشاء صيغة للبحث عن قيم محددة عبر أوراق العمل باستخدام اسم الورقة الديناميكي.
بحث متعدد المعايير باستخدام INDEX و MATCH
عند التعامل مع قاعدة بيانات كبيرة في جدول بيانات Excel مع العديد من الأعمدة والتعليقات التوضيحية للصفوف ، من الصعب دائمًا العثور على شيء يلبي معايير متعددة. في هذه الحالة ، يمكنك استخدام صيغة صفيف مع الدالتين INDEX و MATCH.
أفضل أدوات إنتاجية المكتب
كوتولس ل إكسيل - يساعدك على التميز من بين الحشود
Kutools for Excel يضم أكثر من 300 ميزة، التأكد من أن ما تحتاجه هو مجرد نقرة واحدة...
علامة تبويب Office - تمكين القراءة والتحرير المبوب في Microsoft Office (بما في ذلك Excel)
- ثانية واحدة للتبديل بين عشرات المستندات المفتوحة!
- قلل مئات النقرات بالماوس كل يوم ، وداعًا ليد الماوس.
- يزيد من إنتاجيتك بنسبة 50٪ عند عرض مستندات متعددة وتحريرها.
- يجلب علامات التبويب الفعالة إلى Office (بما في ذلك Excel)، تمامًا مثل Chrome وEdge وFirefox.