Excel: Zählen Sie Zellen mit spezifischem Text (genaue und teilweise Übereinstimmung)

Das Tutorial zeigt, wie die Anzahl von Zellen mit bestimmtem Text in Excel zählt. Sie finden Formelbeispiele für genaue Übereinstimmung, teilweise Übereinstimmung und gefilterte Zellen.

Letzte Woche haben wir uns angesehen, wie Zellen mit Text in Excel zählt, was bedeutet, dass alle Zellen mit jedem Text. Bei der Analyse großer Informationsbrocken möchten Sie möglicherweise auch wissen, wie viele Zellen einen spezifischen Text enthalten. In diesem Tutorial wird erläutert, wie man es auf einfache Weise macht.

• Anzahl der Zellen mit spezifischem Text
• Zählen Sie Zellen, die einen bestimmten Text enthalten (teilweise Übereinstimmung)
• Fallempfindliche Formel zum Zählen von Zellen mit gegebenem Text
• Zählen Sie gefilterte Zellen mit spezifischem Text

Wie man Zellen mit spezifischem Text in Excel zählt

Microsoft Excel hat eine spezielle Funktion, um Zellen, die Countif -Funktion, konditionell zu zählen. Alles, was Sie tun müssen, ist, die Zieltextzeichenfolge im Kriterienargument zu liefern.

Hier ist eine generische Excel -Formel, um die Anzahl der Zellen mit spezifischem Text zu zählen:

Countif (Bereich, " Text ")

Das folgende Beispiel zeigt es in Aktion. Angenommen, Sie haben eine Liste von Element-IDs in A2: A10 und möchten die Anzahl der Zellen mit einer bestimmten ID zählen, sagen "AA-01". Geben Sie diese Zeichenfolge in das zweite Argument ein und Sie erhalten diese einfache Formel:

=COUNTIF(A2:A10, "AA-01")

Damit Ihre Benutzer Zellen mit einem bestimmten Text zählen können, ohne die Formel zu ändern, geben Sie den Text in eine vordefinierte Zelle ein, z. B. D1, und liefern Sie die Zellreferenz:

=COUNTIF(A2:A10, D1)

Notiz. Die Excel Countif-Funktion ist von Fall unempfindlich , was bedeutet, dass sie keinen Buchstabenfall unterscheidet. Verwenden Sie diese Fall-sensitive Formel, um Groß- und Kleinbuchstaben unterschiedlich zu behandeln.

Wie man Zellen mit einem bestimmten Text zählt (teilweise Übereinstimmung)

Die im vorherige Beispiel diskutierte Formel entspricht genau den Kriterien. Wenn es in einer Zelle mindestens einen anderen Charakter gibt, zum Beispiel ein zusätzlicher Raum am Ende, wird dies nicht genau übereinstimmen und eine solche Zelle wird nicht gezählt.

Um die Anzahl der Zellen zu finden, die einen bestimmten Text als Teil ihres Inhalts enthalten, verwenden Sie Wildcard -Zeichen in Ihren Kriterien, nämlich einen Sternchen (*), der jede Sequenz oder Zeichen darstellt. Abhängig von Ihrem Ziel kann eine Formel wie eine der folgenden Aussagen aussehen.

Zählen Sie Zellen, die zu Beginn spezifischer Text enthalten:

Countif (Bereich, " Text *")

Zählen Sie Zellen, die einen bestimmten Text in jeder Position enthalten:

Countif (Bereich, " * Text *")

Um beispielsweise zu finden, wie viele Zellen im Bereich A2: A10 mit "AA" beginnen, verwenden Sie diese Formel:

=COUNTIF(A2:A10, "AA*")

Verwenden Sie diese, um die Anzahl von Zellen, die "AA" in jeder Position enthalten, diese zu erhalten:

=COUNTIF(A2:A10, "*AA*")

Um die Formeln dynamischer zu gestalten, ersetzen Sie die hartcodierten Zeichenfolgen durch Zellreferenzen.

Zählen von Zellen, die mit einem bestimmten Text beginnen:

=COUNTIF(A2:A10, D1&"*")

Zellen mit einem bestimmten Text überall in ihnen zu zählen:

=COUNTIF(A2:A10, "*"&D1&"*")

Der folgende Screenshot zeigt die Ergebnisse:

Zählen Sie Zellen, die einen spezifischen Text enthalten (Fallempfindlichkeit)

In Situation, wenn Sie Groß- und Kleinbuchstaben unterscheiden müssen, funktioniert die Countif -Funktion nicht. Abhängig davon, ob Sie nach einer genauen oder teilweisen Übereinstimmung suchen, müssen Sie eine andere Formel erstellen.

Fallempfindliche Formel zum Zählen von Zellen mit spezifischem Text (exakte Übereinstimmung)

Um die Anzahl der Zellen zu zählen, in denen bestimmter Text den Textfall erkennt, werden wir eine Kombination aus Summenprodukt und genauen Funktionen verwenden:

Sumprodukt (-exakt (" Text ", Bereich ))

Wie diese Formel funktioniert:

• Genau vergleicht jede Zelle im Bereich mit dem Beispieltext und gibt ein Array von wahren und falschen Werten zurück, die genaue Übereinstimmungen darstellen und alle anderen Zellen falsch sind. Ein doppelter Bindestrich ( doppelt unärtbar ) zwingt wahr und falsch in 1 und 0.
• Sumproduct fasst alle Elemente des Arrays zusammen. Diese Summe ist die Anzahl der 1, die Anzahl der Übereinstimmungen.

Um beispielsweise die Anzahl der Zellen in A2: A10 zu erhalten, die den Text in D1 enthalten und Groß- und Kleinbuchstaben als verschiedene Zeichen verarbeiten, verwenden Sie diese Formel:

=SUMPRODUCT(--EXACT(D1, A2:A10))

Fallempfindliche Formel zum Zählen von Zellen mit spezifischem Text (partielle Übereinstimmung)

Um eine fälschliche Formel zu erstellen, die überall in einer Zelle eine Textzeichenfolge finden kann, verwenden wir 3 verschiedene Funktionen:

Sumproduct (-(isNumber (find (" text ", range ))))

Wie diese Formel funktioniert:

• Die fallempfindliche Funktionsfunktion sucht nach dem Zieltext in jeder Zelle des Bereichs. Wenn es erfolgreich ist, gibt die Funktion die Position des ersten Zeichens zurück, sonst der #Value! Fehler. Aus Gründen der Klarheit müssen wir die genaue Position nicht kennen, eine beliebige Zahl (im Gegensatz zum Irrtum) bedeutet, dass die Zelle den Zieltext enthält.
• Die ISNumber -Funktion übernimmt das Array der Nummern und Fehler, die durch Finding zurückgegeben wurden, und konvertiert die Zahlen in wahr und alles andere in false. Eine doppelte Unäre (-) zwingt die logischen Werte in eine und Nullen.
• Sumproduct fasst das Array von 1 und 0 zusammen und gibt die Anzahl von Zellen zurück, die den angegebenen Text als Teil ihres Inhalts enthalten.

Um die Formel für reale Daten zu testen, finden wir fest, wie viele Zellen in A2: A10 den Substring-Eingang in D1 enthalten:

=SUMPRODUCT(--(ISNUMBER(FIND(D1, A2:A10))))

Und dies gibt eine Anzahl von 3 zurück (Zellen A2, A3 und A6):

Wie man gefilterte Zellen mit spezifischem Text zählt

Um sichtbare Elemente in einer gefilterten Liste zu zählen, müssen Sie eine Kombination aus 4 oder mehr Funktionen verwenden, je nachdem, ob Sie eine genaue oder teilweise Übereinstimmung wünschen. Um die Beispiele zu erleichtern, schauen wir uns zuerst einen kurzen Blick auf die Quelldaten.

Angenommen, Sie haben eine Tabelle mit Auftrags -IDs in Spalte B und Menge in Spalte C, wie im Bild unten gezeigt. Im Moment interessieren Sie sich nur für Mengen von mehr als 1 und haben Ihre Tabelle entsprechend gefiltert. Die Frage ist: Wie zählen Sie gefilterte Zellen mit einer bestimmten ID?

Formel zum Zählen gefilterter Zellen mit spezifischem Text (exakte Übereinstimmung)

Verwenden Sie eine der folgenden Formeln, um gefilterte Zellen zu zählen, deren Inhalt genau mit der Textzeichenfolge der Proben übereinstimmt:

=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A10))), --(B2:B10=F1))

=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A10, ROW(A2:A10) - MIN(ROW(A2:A10)),,1)), --(B2:B10=F1))

Wobei F1 der Probentext ist und B2: B10 die Zellen sind zu zählen.

Wie diese Formeln funktionieren:

Im Kern beider Formeln führen Sie 2 Überprüfungen durch:

1. Identifizieren Sie sichtbare und versteckte Reihen. Zu diesem Zweck verwenden Sie die subtotale Funktion mit dem auf 103 festgelegten Function_num -Argument. Um alle einzelnen Zellreferenzen auf subtotale zu versorgen, verwenden Sie entweder indirekt (in der ersten Formel) oder eine Kombination aus Offset, Zeile und min (in der zweiten Formel). Da wir uns selbst sichtbare und versteckte Zeilen lokalisieren wollen, spielt es keine Rolle, welche Spalte (A in unserem Beispiel) referenziert werden soll. Das Ergebnis dieser Operation ist eine Reihe von 1 und 0, bei denen sichtbare Reihen und Nullen - versteckte Zeilen darstellen.
2. Finden Sie Zellen, die gegebenen Text enthalten. Vergleichen Sie dazu den Probentext (F1) mit dem Zellbereich (B2: B10). Das Ergebnis dieser Operation ist eine Reihe von wahren und falschen Werten, die mit Hilfe des Doppel -Unary -Operators auf 1 und 0 gezwungen werden.

Schließlich multipliziert die Sumproduktfunktion die Elemente der beiden Arrays in denselben Positionen und fasst dann das resultierende Array zusammen. Da sich multiplizieren mit Null Null ergibt, haben nur die Zellen, die in beiden Arrays 1 im endgültigen Array haben. Die Summe von 1 ist die Anzahl der gefilterten Zellen, die den angegebenen Text enthalten.

Formel zum Zählen gefilterter Zellen mit spezifischem Text (partielle Übereinstimmung)

Um gefilterte Zellen zu zählen, die einen bestimmten Text als Teil des Zellinhalts enthalten, ändern Sie die obigen Formeln auf folgende Weise. Anstatt den Beispieltext mit dem Zellbereich zu vergleichen, suchen Sie nach dem Zieltext mit ISNumber und finden Sie wie in einem der vorherigen Beispiele erläutert:

=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A10))), --(ISNUMBER(FIND(F1, B2:B10))))

=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A10, ROW(A2:A10) - MIN(ROW(A2:A10)),,1)), --(ISNUMBER(FIND(F1, B2:B10))))

Als Ergebnis lokalisieren die Formeln eine bestimmte Textzeichenfolge in einer beliebigen Position in einer Zelle:

Notiz. Die subtotale Funktion mit 103 im Function_Num -Argument identifiziert alle versteckten Zellen, die manuell herausgefiltert und versteckt haben. Infolgedessen zählen die obigen Formeln nur sichtbare Zellen, unabhängig davon, wie unsichtbare Zellen versteckt waren. Verwenden Sie 3 für Function_num , um nur Zellen auszuschließen, aber die manuell versteckten, die manuell versteckt sind.

So zählt man die Anzahl der Zellen mit einem bestimmten Text in Excel. Ich danke Ihnen für das Lesen und hoffe, Sie nächste Woche in unserem Blog zu sehen!

Verfügbare Downloads

Excel -Formeln, um Zellen mit einem bestimmten Text zu zählen

Das obige ist der detaillierte Inhalt vonExcel: Zählen Sie Zellen mit spezifischem Text (genaue und teilweise Übereinstimmung). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!