Generische Formel
=COUNTIFS(range,"*"&$item1&"*",range,"*"&item2&"*")
Zusammenfassung
Um eine Übersichtstabelle mit einer Anzahl gepaarter Elemente zu erstellen, die in einer Liste vorhandener Kombinationen angezeigt werden, können Sie eine Hilfsspalte und eine Formel verwenden, die auf der COUNTIFS-Funktion basiert. In dem gezeigten Beispiel lautet die Formel in Zelle H5:
=IF($G5=H$4,"-",COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*"))
Dabei ist "Helfer" der benannte Bereich E5: E24.
Hinweis: Bei dieser Formel wird davon ausgegangen, dass Elemente in einer bestimmten Kombination nicht wiederholt werden (dh AAB, EFE sind keine gültigen Kombinationen).
Erläuterung
Wir möchten zählen, wie oft Elemente in den Spalten B, C und D zusammen angezeigt werden. Zum Beispiel, wie oft A mit C erscheint, B mit F erscheint, G mit D erscheint und so weiter. Dies scheint eine perfekte Verwendung von COUNTIFS zu sein, aber wenn wir versuchen, Kriterien hinzuzufügen, die nach 2 Elementen in 3 Spalten suchen, wird dies nicht funktionieren.
Eine einfache Problemumgehung besteht darin, alle Elemente in einer einzigen Zelle zusammenzufügen und dann COUNTIFS mit einem Platzhalter zu verwenden, um Elemente zu zählen. Wir tun dies mit einer Hilfsspalte (E), die Elemente in den Spalten B, C und D mithilfe der CONCAT-Funktion wie folgt verbindet:
=CONCAT(B5:D5)
In älteren Versionen von Excel können Sie eine Formel wie die folgende verwenden:
=B5&C5&D5
Da wiederholte Elemente in einer Kombination nicht zulässig sind, schließt der erste Teil der Formel übereinstimmende Elemente aus. Wenn die beiden Elemente identisch sind, gibt die Formel einen Bindestrich oder Bindestrich als Text zurück:
=IF($G5=H$4,"-"
Wenn sich die Elemente unterscheiden, wird eine COUNTIFS-Funktion ausgeführt:
COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*")
COUNTIFS ist so konfiguriert, dass "Artikelpaare" gezählt werden. Nur wenn das Element in Spalte G und das entsprechende Element aus Zeile 4 zusammen in einer Zelle erscheinen, wird das Paar gezählt. Ein Platzhalter (*) wird auf beiden Seiten des Elements verkettet, um sicherzustellen, dass eine Übereinstimmung gezählt wird, unabhängig davon, wo sie in der Zelle angezeigt wird.
