Wie man eindeutige Werte in kommagetrennten Listen effizient zählt

Das Problem

Wir haben eine Excel-Liste mit Einträgen, bei denen manche Zellen mehrere Werte enthalten, die durch Kommas getrennt sind. Zum Beispiel:

A
A, B
B
C, A
D, A
E
A

Nun möchte ich wissen, wie oft jeder einzelne Wert vorkommt (A=5, B=2, C=1, D=1, E=1). Die Standardfunktionen wie ZÄHLENWENN stoßen hier schnell an ihre Grenzen, da sie nicht ohne Weiteres mehrere Werte innerhalb einer Zelle erkennen können.

Manuelle Lösungen wie das Zählen per Auge werden bei größeren Datensätzen schnell unpraktisch, und bisherige Formeln erfordern oft, dass bereits im Voraus bekannt ist, welche Werte gezählt werden sollen.

Die Lösung

Mit modernen Excel-Funktionen lässt sich dieses Problem elegant in einer einzigen Formel lösen. Hier ist die komplette Lösung:

=LET(
    bereich; A2:A8;
    eindeutigeWerte; EINDEUTIG(MTRANS(TEXTTEILEN(TEXTVERKETTEN(", ";WAHR;bereich);", ")));
    zählen; WENNFEHLER(MAP(eindeutigeWerte; LAMBDA(wert;
        SUMME(LÄNGE(bereich)-LÄNGE(WECHSELN(bereich;wert;"")))/LÄNGE(wert)
    ));"Fehler");
    HSTAPELN(eindeutigeWerte; zählen)
)

Diese Formel gibt eine zweispaltige Tabelle zurück: In der ersten Spalte stehen alle eindeutigen Werte aus Ihrer Liste, und in der zweiten Spalte die entsprechende Anzahl der Vorkommen.

So funktioniert die Formel im Detail

1. Daten vorbereiten:
   Die Funktion TEXTVERKETTEN verbindet alle Zellen zu einem großen Text und TEXTTEILEN spaltet diesen wieder in einzelne Werte auf.
   
2. Eindeutige Werte finden:
   Mit EINDEUTIG und MTRANS werden alle eindeutigen Werte aus der kommagetrennten Liste extrahiert.
   
3. Häufigkeit zählen:
   Für jeden eindeutigen Wert wird mit einer cleveren Kombination aus LÄNGE und WECHSELN gezählt, wie oft dieser im ursprünglichen Bereich vorkommt.
   
4. Ergebnis darstellen:
   HSTAPELN fügt die Liste der eindeutigen Werte und ihre Häufigkeiten zu einer übersichtlichen Tabelle zusammen.
   

Mit dieser Formel spart man Zeit und erhält sofort einen Überblick über die Verteilung der Daten.