Nach diesem Lernprogramm können Sie die Gesamtzahl der Zeichen in einer Zelle oder einem Bereich sowie das Auftreten bestimmter Zeichen oder Zeichenkombinationen in einer Zelle oder einem Bereich zählen, wobei beide Groß- und Kleinschreibung beachten und unempfindliche Alternativen. Die in diesem Lernprogramm erworbenen Kenntnisse sind eine gute Grundlage für die weitere kreative Lösung von Excel-Problemen.

Zählen von Zeichen in Excel

Wenn Sie lernen möchten, wie Zeichen in Excel gezählt werden, Sie müssen die Funktion LEN verwenden, indem Sie die Formel = LEN (Zelle) zum Zählen der Gesamtzeichen in einer Zelle verwenden, oder die Kombination der Funktionen SUMPRODUCT und LEN zum Zählen der Gesamtzeichen in einem Bereich mit der Formel = SUMPRODUCT (LEN (Bereich)) verwenden. Zusätzlich wird das Zählen eines bestimmten Zeichens in einer Zelle oder eines Bereichs und eine bestimmte Kombination von Zeichen in einer Zelle oder einem Bereich ausführlich erläutert.

1. Gesamtzahl der Zeichen in einer Zelle zählen
2. Anzahl der Zeichen in einem Bereich zählen
3. Anzahl spezifischer Zeichen in einer Zelle zählen
4. Anzahl Spezifisches Zeichen in einem Bereich
5. Spezifische Kombination von Zeichen in einer Zelle oder einem Bereich zählen

Gesamtzahl der Zeichen in einer Zelle zählen

Gesamtzahl der Zeichen in a Zelle kann leicht mit Excel-Funktion LEN gefunden werden. Diese Funktion hat nur ein Argument, eine Zellreferenz oder einen Text, bei dem die Gesamtzeichennummer gezählt werden soll:

=LEN(text)

=LEN(B3)

Diese Funktion zählt die Gesamtzahl der Zeichen in einer Zelle, einschließlich Leerzeichen, Satzzeichen und Symbole, unabhängig davon, wie oft sie in einer Zeichenfolge vorkommen.

Wenn wir die Gesamtzahl der Zeichen in einer Zelle ohne Leerzeichen zählen möchten, ist die Kombination der Formeln LEN und SUBSTITUTE erforderlich. Schauen wir uns das folgende Beispiel an. Die Formel, die Leerzeichen ausschließt, sieht folgendermaßen aus:

=LEN(SUBSTITUTE(B3;" ";""))

Die SUBSTITUTE-Funktion ändert in einer definierten Zelle ein Zeichen / einen Text mit einem anderen Zeichen / Text. Wenn wir Leerzeichen mit dieser Funktion ausschließen möchten, werden sie entfernt und alle Vorkommen von Leerzeichen durch eine leere Zeichenfolge ersetzt. Nach SUBSTITUTE würde der Funktionstext in einer Zelle folgendermaßen aussehen:

= LEN („Sunisshining“)

Danach ist es einfach, die Gesamtzahl der Zeichen mit der LEN-Funktion mit dem Finale zu zählen Ergebnis von 12 Zeichen.

Gesamtzahl der Zeichen in einem Bereich zählen

Zum Zählen der Gesamtzahl der Zeichen in einem definierten Bereich wird die Kombination von zwei Funktionen benötigt, SUMPRODUCT und LEN. Verwendung der SUMPRODUCT-Funktion ist eine elegante Lösung, wenn es sich um mehrere Zellen oder Arrays handelt. Sehen Sie sich das folgende Beispiel und eine Kombination der Formeln an:

=SUMPRODUCT(LEN(range))

=SUMPRODUCT(LEN(B3:B6))

Die LEN-Funktion bezieht sich auf die Zelle, aber für Die Verwendung von summierten LEN-Funktionen (= LEN (B3) + LEN (B4) + LEN (B5) + LEN (B6)) in Zellbereichen ist nicht die beste Lösung, da es sich um große Bereiche handeln könnte. Stattdessen fasst die Funktion SUMPRODUCT zusammen Ergebnisse der LEN-Funktion im definierten Bereich. Bei der Formelauswertung sieht das Ergebnis folgendermaßen aus:

Zahlen aus dem Array sind LEN-Funktionsergebnisse aus jeder Zelle im definierten Bereich B3: B6, was endgültige Zeichenergebnisse von 75 ergibt.

Die alternative Lösung für den Umgang mit Bereichen ist die Verwendung der SUM- und LEN-Funktionskombination. Die Syntax ist fast dieselbe, mit dem einzigen Unterschied in der Verwendung von geschweiften Klammern, um die SUMME-Funktion in die Array-Formel zu konvertieren (Verknüpfung zum Erstellen eines Arrays ist STRG + UMSCHALT + EINGABETASTE). In der folgenden Formel ist die genaue Syntax für die SUM / LEN-Funktionskombination angegeben:

={SUM(LEN(B3:B6))}

Bestimmte Zeichen in einer Zelle zählen

Neben der Gesamtzeichennummer gibt es in Excel auch die Option, die Anzahl der Vorkommen bestimmter Zeichen zu zählen. Lassen Sie uns das Beispiel zum Zählen der Anzahl einzelner Zeichen in einer bestimmten Zelle durchgehen. Zu diesem Zweck wird eine Kombination aus LEN- und SUBSTITUTE-Funktion benötigt, wie im ähnlichen Beispiel zum Zählen der Anzahl der Zeichen in der Zelle ohne Leerzeichen. Die Formelsyntax sieht folgendermaßen aus:

=LEN(cell)-LEN(SUBSTITUTE(cell;character;""))

In einem bestimmten Beispiel, wenn die Anzahl der Zeichen in einem definierten Zeichen gezählt werden soll In Zelle B3 sieht die Formel folgendermaßen aus:

=LEN(B3)-LEN(SUBSTITUTE(B3;"s";""))

Lassen Sie uns kurz die Logik der Funktionskombination erklären. Die Gesamtzeichennummer in einer Zelle B3 wird von der Zeichennummer in derselben Zelle abgezogen, jedoch ohne ein bestimmtes Zeichen, das gezählt werden soll. Wie in Tutorial Punkt 1 erwähnt, wird die SUBSTITUTE-Funktion verwendet, um eine Zeichenfolge in einer definierten Zelle in Form ohne bestimmtes Zeichen zu ändern und dieses Zeichen durch eine leere Zeichenfolge zu ersetzen.

=SUBSTITUTE(cell;"character";"")

Im Endergebnis kann festgestellt werden, dass die Funktion keine Großbuchstaben zählt, da bei der LEN-Funktion zwischen Groß- und Kleinschreibung unterschieden wird. Die Lösung zum Zählen von Zeichen ohne Groß- und Kleinschreibung ist die Verwendung der Funktion UPPER / LOWER, bei der alle Zeichen in Groß- / Kleinschreibung übersetzt werden und die Funktion die Groß- und Kleinschreibung nicht mehr berücksichtigt.

Im folgenden Beispiel die Funktion LOWER ist in der SUBSTITUTE-Funktion verschachtelt und ändert alle Zeichenfolgen in Zelle B3 in Kleinbuchstaben, da Kriterien als Kleinbuchstaben „s“ definiert sind:

=LEN(cell)-LEN(SUBSTITUTE(LOWER(cell);"lowercase character";""))

=LEN(B3)-LEN(SUBSTITUTE(LOWER(B3);"s";""))

Eine andere Lösung für die Erstellung einer Funktion ohne Berücksichtigung der Groß- und Kleinschreibung ist Die Verwendung der doppelt verschachtelten SUBSTITUTE-Funktion in Kombination mit der LEN-Funktion. Im weiteren Beispiel befindet sich das Zählen von Zeichen in einer bestimmten Zelle, da es manchmal nicht praktisch ist, jedes Mal zu schreiben, um ein Zeichen in der Formel zu zählen, insbesondere wenn Sie es tun mit komplexen Bereichen und Formeln.

Formel mit doppelt verschachtelter SUBSTITUTE-Funktion:

=LEN(cell)-LEN(SUBSTITUTE(SUBSTITUTE(cell;LOWER(character);"");UPPER(character);""))

=LEN(B3)-LEN(SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);"");UPPER(B6);""))

Die Formel sieht möglicherweise komplex aus, aber nach der Erklärung wird alles klar. Lassen Sie uns die Formel Schritt für Schritt auswerten.

Zuerst möchten wir das Kleinbuchstabenzeichen aus dem Text entfernen:

=SUBSTITUTE(B3;LOWER(B6);"")

=SUBSTITUTE("Sun is shining ”;LOWER("S”);"")

Die Funktion LOWER wird gesetzt, um Zählzeichen in Kleinbuchstaben zu übersetzen. Anschließend ersetzt die Funktion + SUBSTITUTE Kleinbuchstaben in eine leere Zeichenfolge. Nach diesem Schritt sieht das Ergebnis der Formel wie folgt aus: „Sun i hining“.

Im nächsten Formelschritt besteht das Ziel darin, das Zählzeichen in Großbuchstaben aus dem definierten Text / der definierten Zelle zu entfernen. Dies wird durch die verschachtelte SUBSTITUTE-Funktion gelöst :

=SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);"");UPPER(B6);"")

Wir haben bereits im Ergebnis der SUBSTITUTE-Funktion „Sun i hining“ erklärt und werden es in die folgende Funktion einfügen Machen Sie die Situation klarer. In der ersten SUBSTITUTE-Funktion wurde das Kleinbuchstabenzeichen durch eine leere Zeichenfolge ersetzt, und in der zweiten SUBSTITUTE-Funktion wurde das Großbuchstabenzählzeichen durch eine leere Zeichenfolge ersetzt, was dazu führte, dass der Text die Zeichen „s“ und „S“ nicht zählte: „un i hining“

=SUBSTITUTE("Sun i hining”;UPPER("S”);"")

Die LEN-Funktion zählt dann nur die Anzahl der Zeichen aus einem geänderten Text:

=LEN(SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);"");UPPER(B6);""))

=LEN("un i hining”)

Im letzten Schritt wird das Ergebnis der ausgewerteten Formel als Text ohne „s“ ausgewertet. und „S“ wird von der Gesamtzahl der Zeichen in einer definierten Zelle subtrahiert:

=LEN(B3)-LEN(SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);"");UPPER(B6);""))

=LEN("Sun is shining”) - LEN("un i hining”)

Spezifisches Zeichen in einem Bereich zählen

Immer wenn es sich um Zellbereiche und Arrays handelt, wird die Funktion SUMPRODUCT in Kombination mit anderen Funktionen benötigt. Zum Zählen des spezifischen Zeichens Zeichen in einem Bereich, für Groß- und Kleinschreibung, wird die Kombination von drei Funktionen verwendet: SUMPRODUCT, LEN und SUBSTITUTE. Für die Zählung ohne Berücksichtigung der Groß- und Kleinschreibung sollten zusätzliche Formeln hinzugefügt werden: UPPER / LOWER.

Zunächst wird das Zählen der Häufigkeit des Auftretens des Zeichens „S“ in einem definierten Bereich (Version ohne Berücksichtigung der Groß- und Kleinschreibung) erläutert. Eine Formel ähnelt fast der Formel, die das Zählen von Zeichen erklärt. Der Unterschied ergibt sich aus der LEN-Funktion, die in der SUMPRODUCT-Funktion und im Zählbereich verschachtelt ist, anstelle der Zelle wird ein Zellbereich definiert:

=SUMPRODUCT(LEN(B3:B6))-SUMPRODUCT(LEN(SUBSTITUTE(B3:B6;B9;"")))

Für die Zeichenzählung ohne Berücksichtigung der Groß- und Kleinschreibung in einem definierten Bereich können wir die ausführlich erläuterte Funktion für Groß- und Kleinschreibung verwenden -unempfindliche Zeichenzählung in einer bestimmten Zelle in Lernprogrammpunkt 3 mit zwei Änderungen: Verschachteln der LEN-Funktion in der SUMPRODUCT-Funktion und Ersetzen der Zelle durch den Zellbereich:

=SUMPRODUCT(LEN(B3:B6))-SUMPRODUCT(LEN(SUBSTITUTE(SUBSTITUTE(B3:B6;LOWER(B9);"");UPPER(B9);"")))

Denken Sie daran, dass Sie immer die Funktion SUMPRODUCT verwenden müssen, wenn Sie mit Zellbereichen arbeiten in ra zählen Eine Kombination von Funktionen ist erforderlich: SUMPRODUCT, LEN und SUBSTITUTE, und für die Zählung ohne Berücksichtigung der Groß- und Kleinschreibung: SUMPRODUCT-, LEN-, SUBSTITUTE- und UPPER / LOWER-Funktionen.

Spezifische Kombination von Zeichen in einer Zelle oder einem Bereich

Es besteht auch die Möglichkeit, eine bestimmte Zeichenkombination in einer definierten Zelle oder einem definierten Bereich zu zählen. In früheren Tutorial-Punkten haben wir die Einzelzeichenzählung in einer Zelle oder einem Bereich mit Alternativen behandelt, bei denen zwischen Groß- und Kleinschreibung unterschieden wird. Die Formel zum Zählen der Zeichenkombination ist dieselbe, nur müssen wir sie durch die Anzahl der Zeichen in der Zeichenkombination teilen.

Schauen wir uns das folgende Beispiel an, um eine bestimmte Kombination von Zeichen in einer Zelle zu zählen (Version ohne Berücksichtigung der Groß- und Kleinschreibung):

=(LEN(B3)-LEN(SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);"");UPPER(B6);"")))/LEN(B6)

Die Logik ist dieselbe wie beim Zählen einzelner Zeichen, nur mussten wir die reguläre Formel durch die Anzahl der spezifischen Zeichen teilen, die Wir zählen, einfach unter Verwendung der Formel: LEN („in“). Ohne Teilen der Formel mit LEN („in“) würde das Ergebnis mit der Anzahl der Zeichen in der Zeichenkombination multipliziert (in unserem Beispiel mit 2, da „in“ ”Hat zwei Zeichen)

Zum Zählen einer bestimmten Kombination von Zeichen in einer Zelle (Groß- und Kleinschreibung beachten) sieht die Formel folgendermaßen aus:

=(LEN(B3)-LEN(SUBSTITUTE(B3;B6;"")))/LEN(B6)

Das Zählen der Zeichenkombination in einem Bereich hat dieselbe Logik wie das Zählen einzelner Zeichen. In diesem Fall sieht die Formel für vertrauliche Versionen folgendermaßen aus:

=(SUMPRODUCT(LEN(B3:B6))-SUMPRODUCT(LEN(SUBSTITUTE(SUBSTITUTE(B3:B6;LOWER(B9);"");UPPER(B9);""))))/LEN(B9)

Regelmäßige Formel, erklärt in tu Das Thema 4 wird durch die Anzahl der spezifischen Zeichen, die wir zählen, mithilfe der Funktion LEN unterteilt.

Wenn die Version ohne Berücksichtigung der Groß- und Kleinschreibung verwendet werden soll, sieht die Formelsyntax folgendermaßen aus:

=(SUMPRODUCT(LEN(B3:B6))-SUMPRODUCT(LEN(SUBSTITUTE(B3:B6;B9;""))))/LEN(B9)

Benötigen Sie noch Hilfe bei der Excel-Formatierung oder haben Sie weitere Fragen zu Excel? Wenden Sie sich hier an einen Live-Excel-Experten, um Hilfe zu erhalten. Ihre erste Sitzung ist immer kostenlos.