Загальна формула
=COUNTIFS(range,"*"&$item1&"*",range,"*"&item2&"*")
Резюме
Щоб побудувати підсумкову таблицю з кількістю спарених елементів, які відображаються у списку існуючих комбінацій, ви можете використовувати допоміжний стовпець та формулу на основі функції COUNTIFS. У наведеному прикладі формула в комірці Н5 має вигляд:
=IF($G5=H$4,"-",COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*"))
де "помічник" - названий діапазон E5: E24.
Примітка: ця формула передбачає, що елементи не повторюються в даній комбінації (тобто AAB, EFE не є дійсними комбінаціями).
Пояснення
Ми хочемо підрахувати, як часто елементи у стовпцях B, C та D з’являються разом. Наприклад, як часто A з’являється з C, B - з F, G - з D тощо. Це здається ідеальним використанням COUNTIFS, але якщо ми спробуємо додати критерії для пошуку 2 елементів у 3 стовпцях, це не буде працювати.
Просте обхідне рішення - об’єднати всі елементи разом в одній комірці, а потім використовувати COUNTIFS із символом підстановки для підрахунку елементів. Ми робимо це за допомогою допоміжного стовпця (E), який об’єднує елементи у стовпцях B, C та D, використовуючи функцію CONCAT так:
=CONCAT(B5:D5)
У старих версіях Excel ви можете використовувати таку формулу:
=B5&C5&D5
Оскільки повторювані елементи не дозволяються в поєднанні, перша частина формули виключає відповідні елементи. Якщо два елементи однакові, формула повертає дефіс або тире як текст:
=IF($G5=H$4,"-"
Якщо елементи різні, запускається функція COUNTIFS:
COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*")
COUNTIFS налаштовано на підрахунок "пар" елементів. Тільки тоді, коли елемент у стовпці G та відповідний елемент із рядка 4 з’являються разом у комірці, пара враховується. Підстановочний знак (*) поєднується з обох сторін елемента, щоб забезпечити зарахування збігу незалежно від того, де він відображається в клітинці.
