Zählen Sie bestimmte Wörter in einem Bereich

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. Im gezeigten Beispiel lautet die Formel in C11:

= SUMPRODUCT (( LEN (rng)- LEN ( SUBSTITUTE (rng,txt,'')))/ LEN (txt))

Hinweis: Die Formel auf dieser Seite zählt i Instanzen eines Wortes in einem Bereich. Wenn eine Zelle beispielsweise zwei Instanzen eines Wortes enthält, trägt sie 2 zur Gesamtzahl bei. Wenn du nur zählen willst Zellen, die ein bestimmtes Wort enthalten , Sehen Sie sich diese einfache Formel an, die auf der ZÄHLENWENN-Funktion basiert .

In der generischen Version der Formel rng stellt den zu überprüfenden Bereich dar und TXT ist das zu zählende Wort oder die Teilzeichenfolge.

Die Zelle für bedingte Excel-Formatierung enthält Text

Im 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 die Teilzeichenfolge aus dem Originaltext und LEN berechnet die Länge des Textes ohne die Teilzeichenfolge. 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 der Teilzeichenfolge geteilt. Wenn also eine Teilzeichenfolge oder ein Wort 5 Zeichen lang ist und 10 Zeichen fehlen, nachdem es aus dem Originaltext entfernt wurde, wissen wir, dass die Teilzeichenfolge/das Wort zweimal im Originaltext vorkam.

wie man arithmetische Operatoren in Excel verwendet

Da die obige Berechnung in die SUMPRODUCT-Funktion eingeschlossen ist, ist das Ergebnis ein Array, das eine Zahl für jede Zelle im Bereich enthält. Diese Zahlen stellen die Anzahl der Vorkommen der Teilzeichenfolge in jeder Zelle dar. Für dieses Beispiel sieht das Array so aus: {1101}

wie man die ersten 3 Zeichen in Excel löscht

Schließlich summiert SUMPRODUCT alle Elemente im Array, um das Gesamtvorkommen der Teilzeichenfolge im Zellbereich zu erhalten.

Fall ignorieren

SUBSTITUTE ist eine Funktion, bei der die Groß-/Kleinschreibung beachtet wird, daher wird die Groß-/Kleinschreibung beim Ausführen einer Substitution berücksichtigt. Wenn Sie sowohl Groß- als auch Kleinbuchstaben eines Wortes oder einer Teilzeichenfolge zählen müssen, verwenden Sie die Funktion UPPER in SUBSTITUTE, um den Text in Großbuchstaben umzuwandeln, bevor Sie die Ersetzung ausführen:

 
= SUMPRODUCT (( LEN (B5:B8)- LEN ( SUBSTITUTE (B5:B8,C2,'')))/ LEN (C2))