Všeobecný vzorec
=COUNTIFS(range,"*"&$item1&"*",range,"*"&item2&"*")
Zhrnutie
Ak chcete zostaviť súhrnnú tabuľku s počtom spárovaných položiek, ktoré sa zobrazia v zozname existujúcich kombinácií, môžete použiť pomocný stĺpec a vzorec založený na funkcii COUNTIFS. V zobrazenom príklade je vzorec v bunke H5:
=IF($G5=H$4,"-",COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*"))
kde „pomocník“ je pomenovaný rozsah E5: E24.
Poznámka: Tento vzorec predpokladá, že sa položky v danej kombinácii neopakujú (tj. AAB, EFE nie sú platné kombinácie).
Vysvetlenie
Chceme spočítať, ako často sa položky v stĺpcoch B, C a D objavujú spolu. Napríklad to, ako často sa A objavuje pri C, B pri F, G pri D, atď. Zdá sa to ako dokonalé využitie COUNTIFS, ale ak sa pokúsime pridať kritériá hľadajúce 2 položky v 3 stĺpcoch, nebude to fungovať.
Jednoduché riešenie je spojiť všetky položky dohromady v jednej bunke a potom pomocou počítať položky použiť COUNTIFS so zástupným znakom. Robíme to pomocou pomocného stĺpca (E), ktorý spája položky v stĺpcoch B, C a D pomocou funkcie CONCAT takto:
=CONCAT(B5:D5)
V starších verziách Excelu môžete použiť nasledujúci vzorec:
=B5&C5&D5
Pretože opakované položky nie sú povolené v kombinácii, prvá časť vzorca vylučuje zhodné položky. Ak sú dve položky rovnaké, vzorec vráti pomlčku alebo pomlčku ako text:
=IF($G5=H$4,"-"
Ak sú položky odlišné, spustí sa funkcia COUNTIFS:
COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*")
COUNTIFS je nakonfigurovaný na počítanie „párov“ položiek. Pár sa počíta, až keď sa položka v stĺpci G a zodpovedajúca položka z riadku 4 objavia spolu v bunke. Zástupný znak (*) je zreťazený na obidve strany položky, aby sa zabezpečilo, že zhoda sa bude počítať bez ohľadu na to, kde sa v bunke objaví.
