Formuła ogólna
=COUNTIFS(range,"*"&$item1&"*",range,"*"&item2&"*")
Podsumowanie
Aby zbudować tabelę podsumowującą z liczbą sparowanych elementów, które pojawiają się na liście istniejących kombinacji, możesz użyć kolumny pomocniczej i formuły opartej na funkcji LICZ.WARUNKI. W przykładzie pokazanym formuła w komórce H5 to:
=IF($G5=H$4,"-",COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*"))
gdzie „pomocnik” to nazwany zakres E5: E24.
Uwaga: ten wzór zakłada, że pozycje nie powtarzają się w danej kombinacji (tj. AAB, EFE nie są prawidłowymi kombinacjami).
Wyjaśnienie
Chcemy policzyć, jak często elementy w kolumnach B, C i D pojawiają się razem. Na przykład, jak często A pojawia się z C, B z F, G z D i tak dalej. Wydawałoby się, że jest to idealne użycie LICZ.WARUNKÓW, ale jeśli spróbujemy dodać kryteria szukające 2 elementów w 3 kolumnach, to nie zadziała.
Prostym obejściem jest połączenie wszystkich elementów w jednej komórce, a następnie użycie funkcji LICZ.WARUNKI z symbolem wieloznacznym do zliczania elementów. Robimy to za pomocą kolumny pomocniczej (E), która łączy elementy w kolumnach B, C i D za pomocą funkcji CONCAT w następujący sposób:
=CONCAT(B5:D5)
W starszych wersjach programu Excel możesz użyć następującej formuły:
=B5&C5&D5
Ponieważ elementy powtarzane nie są dozwolone w kombinacji, pierwsza część formuły wyklucza pasujące elementy. Jeśli te dwa elementy są takie same, formuła zwraca łącznik lub myślnik jako tekst:
=IF($G5=H$4,"-"
Jeśli pozycje są różne, uruchamiana jest funkcja LICZ.WARUNKI:
COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*")
Funkcja COUNTIFS jest skonfigurowana do liczenia „par” elementów. Tylko wtedy, gdy pozycja w kolumnie G i odpowiadająca jej pozycja z wiersza 4 pojawiają się razem w komórce, para jest liczona. Symbol wieloznaczny (*) jest łączony z obiema stronami elementu, aby zapewnić, że dopasowanie będzie liczone bez względu na to, gdzie pojawi się w komórce.
