Beschreibung von Formeln zum Zählen der Instanzen von Text, Zeichen und Wörtern in Excel

• Gilt für::

  Excel 2016, Excel 2013

Zusammenfassung

Dieser Artikel enthält und beschreibt Formeln, die Folgendes berechnen:

• Die Anzahl der Instanzen einer Textzeichenfolge in einem Zellbereich.
• Die Anzahl der Instanzen eines Zeichens in einer Zelle.
• Die Anzahl der Instanzen eines Zeichens in einem Zellbereich.
• Die Anzahl der Wörter (oder Textzeichenfolgen), die durch ein Zeichen in einer Zelle getrennt sind.

Weitere Informationen

Formel zum Zählen der Anzahl der Instanzen einer Textzeichenfolge in einem Bereich

=SUM(LEN(range)-LEN(SUBSTITUTE(range,"text","")))/LEN("text")

Hierbei ist range der betreffende Zellbereich, und „text“ wird durch die spezifische Textzeichenfolge ersetzt, für die Sie eine Zählung durchführen möchten.

Hinweis

Die obige Formel muss als Arrayformel eingegeben werden. Um eine Formel als Array in Excel für Windows einzugeben, drücken Sie STRG+UMSCHALT+EINGABE. Um eine Formel als Array in Excel für Macintosh einzugeben, drücken Sie COMMAND+RETURN.

Die Formel muss durch die Länge der Textzeichenfolge geteilt werden, da die Summe der Zeichenlänge des Bereichs um ein Vielfaches jeder Instanz der Textzeichenfolge reduziert wird. Diese Formel kann alle späteren Formeln in diesem Artikel ersetzen, mit Ausnahme der Formel, mit der die Anzahl der Wörter in einer Zelle gezählt wird.

Beispiel 1: Zählen der Anzahl der Vorkommen einer Textzeichenfolge in einem Bereich

1. Starten Sie Excel, und öffnen Sie eine neue Arbeitsmappe.

2. Geben Sie in Mappe1 Folgendes ein:

   A1: Fruit
   A2: apple,apple
   A3: orange
   A4: apple,orange
   A5: grape
   A6: orange,grape
   A7: grape, apple
   A8: =SUM(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,"apple","")))/LEN("apple")
   

   Der Wert von Zelle A8 ist 4, da der Text „apple“ viermal im Bereich angezeigt wird.

Formel zum Zählen der Anzahl der Vorkommen eines einzelnen Zeichens in einer Zelle

=LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,"a",""))

Hierbei ist cell_ref der Zellbezug, und „a“ wird durch das Zeichen ersetzt, das Sie zählen möchten.

Hinweis

Diese Formel muss nicht als Arrayformel eingegeben werden.

Beispiel 2: Zählen der Anzahl der Instanzen eines Zeichens in einer Zelle

Verwenden Sie dieselben Daten aus dem vorherigen Beispiel; angenommen, Sie möchten die Anzahl der Instanzen des Zeichens „p“ in A7 zählen. Geben Sie die folgende Formel in Zelle A9 ein:

A9: =LEN(A7)-LEN(SUBSTITUTE(A7,"p",""))

Der Wert von Zelle A9 ist 3, da das Zeichen „p“ dreimal in A7 angezeigt wird.

Formel zum Zählen der Anzahl der Instanzen eines einzelnen Zeichens in einem Bereich

=SUM(LEN(range)-LEN(SUBSTITUTE(range,"a","")))

Hierbei ist range der betreffende Zellbereich, und „a“ wird durch das Zeichen ersetzt, das Sie zählen möchten.

Hinweis

Die obige Formel muss als Arrayformel eingegeben werden. Um eine Formel als Arrayformel in Excel einzugeben, drücken Sie STRG+UMSCHALT+ENTER.

Beispiel 3: Zählen der Anzahl der Instanzen eines Zeichens in einem Bereich

Verwenden Sie dieselben Daten aus dem vorherigen Beispiel. Angenommen, Sie möchten die Anzahl der Instanzen des Zeichens „p“ in A2:A7 zählen. Geben Sie die folgende Formel in Zelle A10 ein:

A10: =SUM(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,"p","")))

Hinweis

Die obige Formel muss als Arrayformel eingegeben werden. Um eine Formel als Arrayformel in Excel einzugeben, drücken Sie STRG+UMSCHALT+ENTER.

Der Wert von Zelle A10 ist 11, da das Zeichen „p“ insgesamt 11 Mal in A2:A7 angezeigt wird.

Formel zum Zählen der Anzahl der Wörter, die durch ein Zeichen in einer Zelle getrennt sind

=IF(LEN(TRIM(cell_ref))=0,0,LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,char,""))+1)

Hierbei ist cell_ref der Zellbezug und char das Zeichen, das die Wörter trennt.

Hinweis

Die obige Formel enthält keine Leerzeichen. Mehrfache Zeilen wurden nur verwendet, damit die Formel in dieses Dokument passt. Schließen Sie keine Leerzeichen ein, wenn Sie sie in die Zelle eingeben. Diese Formel muss nicht als Arrayformel eingegeben werden.

Beispiel 4: Zählen der Anzahl der Wörter, die durch ein Leerzeichen in einer Zelle getrennt sind

Führen Sie die folgenden Schritte aus, um die Anzahl der Wörter in einer Zelle zu zählen, in der die Wörter durch ein Leerzeichen getrennt sind:

1. Starten Sie Excel, und öffnen Sie eine neue Arbeitsmappe.

2. Geben Sie in Mappe1 Folgendes ein:

   A1: The car drove fast
   A2: =IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1)
   

Die Formel in Zelle A2 gibt den Wert 4 zurück, um widerzuspiegeln, dass die Zeichenfolge vier Wörter enthält, die durch Leerzeichen getrennt sind. Wenn Wörter durch mehrere Leerzeichen getrennt sind oder Leerzeichen am Anfang oder Ende stehen, spielt dies keine Rolle. Die TRIM-Funktion entfernt zusätzliche Leerzeichen und Start- und Endleerzeichen im Text der Zelle.

In Excel können Sie auch ein Makro verwenden, um die Instanzen eines bestimmten Zeichens in einer Zelle oder einem Zellbereich zu zählen.

Informationsquellen

Weitere Informationen zum Zählen der Instanzen von Text finden Sie im folgenden Artikel der Microsoft Knowledge Base:

89794 Verwenden von Visual Basic für Anwendungen zum Zählen der Instanzen eines Zeichens in einer Auswahl in Excel