عمليات البحث متعددة المعايير باستخدام INDEX-MATCH

لا يحدد العمود الواحد صفًا بشكل فريد أحيانًا. فقد تحتاج إلى سعر منتج بحجم محدد، أو راتب موظف في قسم معين.

وهنا نحتاج إلى بحث متعدد المعايير: أي المطابقة في عمودين أو أكثر في الوقت نفسه لتحديد صف واحد بعينه.

يتعامل INDEX-MATCH مع ذلك بأناقة من خلال دمج الشروط في اختبار تطابق واحد، من دون الحاجة إلى أعمدة مساعدة إضافية.

أبسط نموذج ذهني هو دمج أعمدة المفاتيح في مفتاح واحد. أضف عمودًا مساعدًا يضم المنتج والحجم معًا، ثم نفّذ بحثًا عاديًا فيه.

على سبيل المثال، قد تحتوي خلية مساعدة على =A2&"|"&B2، فتنتج "Shirt|Large". بعد ذلك تطابق "Shirt|Large" مع ذلك العمود المدمج باستخدام MATCH.

ينجح هذا الأسلوب، لكنه يزيد من ازدحام ورقة العمل. وتوضح المشاهد التالية كيفية الاستغناء عن العمود المساعد تمامًا.

الخدعة الأساسية هي ضرب اختباري الشرطين معًا داخل MATCH.

ينتج (A2:A10=G1) مصفوفة من TRUE/FALSE للمعيار الأول. وينتج (B2:B10=G2) الشيء نفسه للمعيار الثاني. ويؤدي ضربهما، (A2:A10=G1)*(B2:B10=G2)، إلى إنتاج 1 فقط عندما يكون الشرطان كلاهما TRUE، و0 في بقية الحالات.

بعد ذلك يبحث MATCH عن القيمة 1 للعثور على الصف الذي يستوفي الشرطين.

تتعامل جداول البيانات مع TRUE باعتبارها 1، ومع FALSE باعتبارها 0. وي模拟 ضرب هاتين القيمتين المنطقي AND:

• 1 في 1 = 1 (تحقق الشرطان)
• 1 في 0 = 0
• 0 في 1 = 0
• 0 في 0 = 0

لذلك لا تنتج القيمة 1 إلا الصفوف التي يتحقق فيها كلا المعيارين. وتتحول كل الصفوف الأخرى إلى 0. وتحدد تلك القيمة 1 المنفردة الصف المطلوب.

والآن ضع المصفوفة المضروبة داخل MATCH، وابحث عن القيمة الدقيقة 1.

تُرجع MATCH(1, (A2:A10=G1)*(B2:B10=G2), 0) موضع أول صف تكون فيه الشروط TRUE معًا.

إذا كان الصف المطابق هو صف البيانات الرابع، فستُرجع MATCH القيمة 4. وهذا هو الموضع الذي يحتاج إليه INDEX لجلب الإجابة.

مرّر نتيجة MATCH إلى INDEX على العمود الذي تريده فعليًا، وليكن السعر في C2:C10.

تكون الصيغة الكاملة كالتالي: من C2:C10، أرجع القيمة الموجودة في الصف الذي يساوي فيه المنتج G1 والحجم G2.

هذا بحث حقيقي متعدد المعايير، من دون عمود مساعد أو إعادة ترتيب بياناتك.

تقيّم هذه الصيغة مصفوفات من الشروط. في Excel الحديث وGoogle Sheets، ما عليك سوى الضغط على Enter وستعمل.

أما في Excel الأقدم (قبل ظهور المصفوفات الديناميكية)، فيجب تأكيدها كصيغة مصفوفة باستخدام Ctrl+Shift+Enter، ما يضيف أقواس معقوفة. وإذا كانت النتيجة خاطئة أو ظهر خطأ في Excel القديم، فعادةً ما تكون خطوة التأكيد هذه هي الجزء المفقود.

هل تحتاج إلى ثلاثة معايير؟ ما عليك سوى ضرب اختبار آخر. لنفترض أنك تريد أيضًا مطابقة لون في العمود D مع المُدخل G3.

يضيّق كل عامل إضافي من الشكل (range=criterion) النتيجة أكثر. ولا تحتفظ حاصل ضرب القيم 1 إلا بالصفوف التي تكون فيها كل الشروط TRUE؛ إذ تحوّل أي قيمة FALSE الحاصل كله إلى 0.

يمكن توسيع هذا النمط ليشمل أي عدد تحتاج إليه من الأعمدة.

البيانات: A = المنتج، B = الحجم، C = السعر. تريد سعر "Shirt" بحجم "Large".

• G1 = "Shirt"، وG2 = "Large".
• تنتج مصفوفات الشروط القيمة 1 فقط في صف Shirt+Large، ولنفترض أنه الصف 4.
• تُرجع MATCH(1, ..., 0) القيمة 4.
• تُرجع INDEX(C2:C10, 4) سعر ذلك الصف.

غيّر أيًا من المُدخلين، وستعيد الصيغة العثور على الصف الصحيح فورًا.

ضع الأمور التالية في اعتبارك:

• تساوي النطاقات: يجب أن يكون لكل نطاق شرط وعمود INDEX الارتفاع نفسه.
• عدم وجود تطابق: إذا لم يستوفِ أي صف جميع المعايير، فسيُرجع MATCH الخطأ #N/A. غلّف الصيغة كاملة داخل IFERROR.
• التكرارات: إذا تطابق أكثر من صف، فسيُرجع MATCH الصف الأول فقط. اجعل معاييرك محددة بما يكفي لضمان التفرد.

إذا كان من الممكن أن تتطابق عدة صفوف، وكنت تفضل جمع قيمها بدلًا من جلب قيمة واحدة، فإن SUMPRODUCT يمثل بديلًا واضحًا عن INDEX-MATCH المُدخل كصيغة مصفوفة.

يضرب مصفوفات الشروط في عمود القيم ثم يجمع النتائج، بحيث لا تسهم إلا الصفوف التي تستوفي الشرطين. ولا حاجة إلى Ctrl+Shift+Enter، لأن SUMPRODUCT يتعامل مع المصفوفات تلقائيًا.

استخدم INDEX-MATCH لجلب قيمة مطابقة واحدة، واستخدم SUMPRODUCT لتجميع قيم جميع التطابقات.

اختبر معرفتك بالبحث متعدد المعايير.

لعمليات البحث متعددة المعايير باستخدام INDEX-MATCH:

• اضرب مصفوفات الشروط معًا: ‏(A=G1)*(B=G2) تعطي 1 فقط في المواضع التي تتحقق فيها جميع الشروط (أي AND منطقي).
• تبحث MATCH(1, ..., 0) عن موضع الصف.
• تعيد INDEX(returnCol, position) القيمة.

أضف عوامل *(range=criterion) أخرى للشروط الإضافية، وحافظ على تساوي أطوال النطاقات، وأكّد باستخدام Ctrl+Shift+Enter في إصدارات Excel القديمة، وحصّن الصيغة باستخدام IFERROR.