Obecný vzorec
=COUNTIFS(range,"*"&$item1&"*",range,"*"&item2&"*")
souhrn
Chcete-li vytvořit souhrnnou tabulku s počtem spárovaných položek, které se objeví v seznamu existujících kombinací, můžete použít pomocný sloupec a vzorec založený na funkci COUNTIFS. V zobrazeném příkladu je vzorec v buňce H5:
=IF($G5=H$4,"-",COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*"))
kde „pomocník“ je pojmenovaný rozsah E5: E24.
Poznámka: tento vzorec předpokládá, že se položky v dané kombinaci neopakují (tj. AAB, EFE nejsou platné kombinace).
Vysvětlení
Chceme spočítat, jak často se položky ve sloupcích B, C a D objevují společně. Například to, jak často se A objeví s C, B se objeví s F, G se objeví s D atd. Zdálo by se to jako perfektní využití COUNTIFS, ale pokud se pokusíme přidat kritéria hledající 2 položky ve 3 sloupcích, nebude to fungovat.
Jednoduchým řešením je spojit všechny položky dohromady v jedné buňce a poté pomocí COUNTIFS se zástupným znakem počítat položky. Děláme to pomocí pomocného sloupce (E), který spojuje položky ve sloupcích B, C a D pomocí funkce CONCAT takto:
=CONCAT(B5:D5)
Ve starších verzích aplikace Excel můžete použít následující vzorec:
=B5&C5&D5
Protože opakované položky nejsou povoleny v kombinaci, první část vzorce vylučuje odpovídající položky. Pokud jsou dvě položky stejné, vrátí vzorec pomlčku nebo pomlčku jako text:
=IF($G5=H$4,"-"
Pokud jsou položky odlišné, je spuštěna funkce COUNTIFS:
COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*")
COUNTIFS je nakonfigurován tak, aby počítal „páry“ položek. Pouze když se položka ve sloupci G a odpovídající položka z řádku 4 objeví společně v buňce, počítá se pár. Zástupný znak (*) je zřetězen na obě strany položky, aby bylo zajištěno, že shoda bude započítána bez ohledu na to, kde se v buňce objeví.
