Generische Formel
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Zusammenfassung
Um zu zählen, wie oft ein bestimmtes Wort (oder eine beliebige Teilzeichenfolge) in einem Zellbereich vorkommt, können Sie eine Formel verwenden, die auf den Funktionen SUBSTITUTE, LEN und SUMPRODUCT basiert. In dem gezeigten Beispiel lautet die Formel in C11:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Hinweis: Die Formel auf dieser Seite zählt die Bestandteile eines Wortes in einem Bereich. Wenn eine Zelle beispielsweise zwei Instanzen eines Wortes enthält, trägt sie 2 zur Gesamtzahl bei. Wenn Sie nur Zellen zählen möchten, die ein bestimmtes Wort enthalten, lesen Sie diese einfache Formel, die auf der COUNTIF-Funktion basiert.
Erläuterung
In der generischen Version der Formel steht rng für den zu überprüfenden Bereich und txt für das zu zählende Wort oder die zu zählende Teilzeichenfolge .
In dem gezeigten Beispiel ist B5: B8 der zu überprüfende Bereich, und C2 enthält den zu zählenden Text (Wort oder Teilzeichenfolge).
Für jede Zelle im Bereich entfernt SUBSTITUTE den Teilstring aus dem Originaltext und LEN berechnet die Länge des Textes ohne den Teilstring. Diese Zahl wird dann von der Länge des Originaltextes abgezogen. Das Ergebnis ist die Anzahl der Zeichen, die von SUBSTITUTE entfernt wurden.
Dann wird die Anzahl der entfernten Zeichen durch die Länge des Teilstrings geteilt. Wenn also ein Teilstring oder ein Wort 5 Zeichen lang ist und 10 Zeichen fehlen, nachdem es aus dem Originaltext entfernt wurde, wissen wir, dass der Teilstring / das Wort im Originaltext zweimal vorkommt.
Da die obige Berechnung in die Funktion SUMPRODUCT eingeschlossen ist, ist das Ergebnis ein Array, das eine Zahl für jede Zelle im Bereich enthält. Diese Zahlen geben die Anzahl der Vorkommen des Teilstrings in jeder Zelle an. In diesem Beispiel sieht das Array folgendermaßen aus: (1; 1; 0; 1)
Schließlich summiert SUMPRODUCT alle Elemente im Array, um das gesamte Vorkommen von Teilzeichenfolgen im Zellbereich zu ermitteln.
Fall ignorieren
SUBSTITUTE unterscheidet zwischen Groß- und Kleinschreibung, sodass beim Ausführen einer Substitution die Groß- und Kleinschreibung berücksichtigt wird. Wenn Sie sowohl Groß- als auch Kleinbuchstaben eines Wortes oder einer Teilzeichenfolge zählen müssen, konvertieren Sie den Text mit der UPPER-Funktion in SUBSTITUTE in Großbuchstaben, bevor Sie die Ersetzung ausführen:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
