Общая формула
=COUNTIFS(range,"*"&$item1&"*",range,"*"&item2&"*")
Резюме
Чтобы создать сводную таблицу с подсчетом парных элементов, которые появляются в списке существующих комбинаций, вы можете использовать вспомогательный столбец и формулу, основанную на функции СЧЁТЕСЛИМН. В показанном примере формула в ячейке H5:
=IF($G5=H$4,"-",COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*"))
где «helper» - именованный диапазон E5: E24.
Примечание: эта формула предполагает, что элементы не повторяются в данной комбинации (т. Е. AAB, EFE не являются допустимыми комбинациями).
Объяснение
Мы хотим подсчитать, как часто элементы в столбцах B, C и D появляются вместе. Например, как часто A появляется с C, B появляется с F, G появляется с D и так далее. Это может показаться идеальным использованием СЧЁТЕСЛИМН, но если мы попытаемся добавить критерии поиска 2 элементов в 3 столбца, это не сработает.
Простой обходной путь - объединить все элементы в одну ячейку, а затем использовать СЧЁТЕСЛИМН с подстановочным знаком для подсчета элементов. Мы делаем это с помощью вспомогательного столбца (E), который объединяет элементы в столбцах B, C и D, используя функцию CONCAT следующим образом:
=CONCAT(B5:D5)
В более старых версиях Excel вы можете использовать такую формулу:
=B5&C5&D5
Поскольку повторяющиеся элементы не допускаются в комбинации, первая часть формулы исключает совпадающие элементы. Если два элемента совпадают, формула возвращает дефис или тире в виде текста:
=IF($G5=H$4,"-"
Если элементы отличаются, запускается функция СЧЁТЕСЛИМН:
COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*")
СЧЕТЕСЛИМН настроен на подсчет «пар» элементов. Только тогда, когда элемент в столбце G и соответствующий элемент из строки 4 появляются вместе в ячейке, считается пара. Подстановочный знак (*) присоединяется к обеим сторонам элемента, чтобы гарантировать, что совпадение будет засчитано независимо от того, где оно появляется в ячейке.
