اكسل

INDEX و MATCH مع معايير متعددة

Index Match With Multiple Criteria

صيغة Excel: INDEX و MATCH بمعايير متعددةصيغة عامة
{= INDEX (range1, MATCH (1,(A1=range2)*(B1=range3)*(C1=range4),0))}
ملخص

للبحث عن القيم باستخدام INDEX و MATCH ، باستخدام معايير متعددة ، يمكنك استخدام صيغة صفيف. في المثال الموضح ، الصيغة في H8 هي:



 
{= INDEX (E5:E11, MATCH (1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0))}

ملاحظة: هذا ملف صيغة الصفيف ، ويجب إدخالها باستخدام control + shift + enter ، ما عدا في اكسل 365 .

تفسير

هذه صيغة أكثر تقدما. للأساسيات ، انظر كيفية استخدام INDEX و MATCH .





اختبار التفوق إذا كانت الخلية تحتوي على سلسلة

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

تعمل هذه الصيغة حول هذا القيد باستخدام المنطق البوليني لإنشاء ملف مجموعة مصفوفة من الآحاد والأصفار لتمثيل الصفوف المطابقة لجميع المعايير الثلاثة ، ثم استخدم MATCH لمطابقة أول 1 تم العثور عليه. يتم إنشاء المصفوفة المؤقتة من الآحاد والأصفار باستخدام هذا المقتطف:



 
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)

نقارن هنا العنصر الموجود في H5 مقابل جميع العناصر ، والحجم في H6 مقابل جميع الأحجام ، واللون في H7 مقابل جميع الألوان. النتيجة الأولية هي ثلاث مصفوفات من نتائج TRUE / FALSE مثل هذا:

 
{TRUETRUETRUEFALSEFALSEFALSETRUE}*{FALSEFALSETRUEFALSEFALSETRUEFALSE}*{TRUEFALSETRUEFALSEFALSEFALSETRUE}

نصيحة: استخدم F9 لرؤية هذه النتائج . ما عليك سوى تحديد تعبير في شريط الصيغة ، واضغط على F9.

تحوّل العملية الحسابية (الضرب) قيم TRUE FALSE إلى 1s و 0s:

كيف يشير Excel إلى أنه تم تحديد عمود في مرجع منظم
 
{1110001}*{0010010}*{1010001}

بعد الضرب ، لدينا مصفوفة واحدة مثل هذا:

 
{0010000}

التي يتم إدخالها في دالة MATCH كصفيف بحث ، بقيمة بحث 1:

 
 MATCH (1,{0010000})

في هذه المرحلة ، تكون الصيغة صيغة INDEX MATCH قياسية. ترجع الدالة MATCH 3 إلى INDEX:

 
= INDEX (E5:E11,3)

وترجع INDEX نتيجة نهائية قدرها 17.00 دولارًا.

تصور الصفيف

قد يكون من الصعب تصور المصفوفات الموضحة أعلاه. الصورة أدناه توضح الفكرة الأساسية. تتوافق الأعمدة B و C و D مع البيانات الموجودة في المثال. يتم إنشاء العمود F بضرب الأعمدة الثلاثة معًا. إنها المجموعة التي تم تسليمها إلى MATCH.

INDEX و MATCH مع معايير متعددة - تصور الصفيف

إصدار غير مصفوفة

من الممكن إضافة فهرس آخر إلى هذه الصيغة ، مع تجنب الحاجة إلى إدخالها كصيغة صفيف باستخدام control + shift + enter:

 
= INDEX (rng1, MATCH (1, INDEX ((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))

يمكن للدالة INDEX معالجة المصفوفات محليًا ، لذلك تتم إضافة INDEX الثاني فقط إلى 'التقاط' المصفوفة التي تم إنشاؤها باستخدام عملية المنطق المنطقي وإرجاع نفس المصفوفة مرة أخرى إلى MATCH. للقيام بذلك ، تم تكوين INDEX مع صفر صفوف وعمود واحد. تؤدي خدعة الصف الصفري إلى إرجاع INDEX للعمود 1 من الصفيف (وهو بالفعل عمود واحد على أي حال).

لماذا تريد إصدار غير مصفوفة؟ في بعض الأحيان ، ينسى الأشخاص إدخال صيغة صفيف باستخدام control + shift + enter ، وتعيد الصيغة نتيجة غير صحيحة. لذا ، فإن الصيغة غير المصفوفة هي أكثر 'مقاومة للرصاص'. ومع ذلك ، فإن المفاضلة هي صيغة أكثر تعقيدًا.

ملاحظة: في اكسل 365 ، ليس من الضروري إدخال صيغ الصفيف بطريقة خاصة.

كيفية حساب الشهور في Excel
المرفقات ملف INDEX و MATCH مع معايير متعددة. xlsx المؤلف ديف برونز


^