Excel -Formeln, um Zellen mit Text zu zählen: spezifische oder filtrierte Zellen

Wie zähle ich Zellen, die Text in Excel enthalten?

Excel wurde ursprünglich so konzipiert, dass sie Zahlen verarbeiten, aber heute verwenden wir es auch oft, um Text zu speichern und zu verarbeiten. Möchten Sie wissen, wie viele Zellen im Arbeitsblatt enthalten sind, das Text enthält? Microsoft Excel bietet mehrere Funktionen, um dies zu erreichen. Welche Funktion sollte verwendet werden? Es hängt von der Situation ab. In diesem Tutorial finden Sie verschiedene Formeln und die beste Zeit, um jede Formel zu verwenden.

• Statistikzellen, die Text enthalten
• Ignorieren Sie leere Saiten und Räume und zählen Sie Zellen, die Text enthalten
• Statistiken von Zellen, die spezifischer Text enthalten
• Statistiken filtrierten Zellen, die Text enthalten

So zählen Sie die Anzahl der Zellen, die Text in Excel enthalten

Es gibt zwei grundlegende Formeln, um die Anzahl der Zellen zu finden, die eine Textzeichenfolge oder einen bestimmten Zeichen innerhalb eines bestimmten Bereichs enthalten.

Verwenden Sie die Countif -Formel, um alle Zellen mit Text zu zählen

Wenn Sie die Anzahl der Zellen in Excel finden möchten, die Text mit einem Sternchen in einem bedingten Parameter enthalten, ist die beste und einfachste Lösung:

COUNTIF(*范围*, "\*") Da das Sternchen (*) eine Wildcard ist, die jeder Zeichensequenz entspricht, zählt die Formel alle Zellen, die einen beliebigen Text enthalten.

Statistikzellen, die einen beliebigen Text mit der Sumproduktformel enthalten

Eine andere Möglichkeit, die Anzahl der Textzellen zu erhalten, die Text enthalten

SUMPRODUCT(--ISTEXT(*范围*)) oder

SUMPRODUCT(ISTEXT(*范围*)*1) Die ISText -Funktion prüft, ob jede Zelle im angegebenen Bereich alle Textzeichen enthält, und gibt ein Array von True (Zellen, die Text enthalten) und falsche (andere Zellen) zurückgeben. Der Double Unary Operator (-) oder die Multiplikationsoperation legt wahr und false auf 1 bzw. 0, wodurch ein Array von 1 und 0 erzeugt wird. Die Summenproduktfunktion setzt alle Elemente des Arrays zusammen und gibt die Anzahl 1 zurück, dh die Anzahl der Zellen, die Text enthalten.

Um besser zu verstehen, wie diese Formeln funktionieren, sehen Sie, welche Werte gezählt werden und welche nicht gezählt werden:

Inhalt zählen	Unzähliger Inhalt
- Zellen, die einen beliebigen Text enthalten - Sonderzeichen - als Text formatierte Zahlen - Zellen, die als leer angesehen werden können, aber leere Zeichenfolgen (""), Apostrophes ('), Räume oder unsichtbare Zeichen enthalten	- Nummer - Datum - Logische Werte von wahr und falsch - Fehler - leere Zellen

Zum Beispiel können Sie eine der folgenden Formeln verwenden, um Zellen zu zählen, die Text im A2: A10 -Bereich (mit Ausnahme von Zahlen, Daten, logischen Werten, Fehlern und leeren Zellen) enthalten:

=COUNTIF(A2:A10, "*")

=SUMPRODUCT(--ISTEXT(A2:A10))

=SUMPRODUCT(ISTEXT(A2:A10)*1)

Die folgende Abbildung zeigt die Ergebnisse:

Ignorieren Sie Leerzeichen und leere Saiten und zählen Sie Zellen, die Text enthalten

Die oben diskutierte Formel zählt alle Zellen, die Textzeichen enthalten. In einigen Fällen kann dies jedoch verwirrend sein, da einige Zellen leer aussehen, aber tatsächlich Zeichen enthalten, die das menschliche Auge nicht sehen kann, wie leere Zeichenfolgen, Apostrophen, Räume, Linienbrüche usw. Infolgedessen werden Zellen, die als leer angesehen werden, von Formeln gezählt werden, wodurch der Benutzer sich Sorgen macht, den Grund herauszufinden.

Verwenden Sie die CountIFs -Funktion und verwenden Sie das Zeichen "ausschließen" in der zweiten Bedingung, um "falsch positive" leere Zellen aus der Anzahl auszuschließen.

Verwenden Sie beispielsweise die folgende Formel:

=COUNTIFS(A2:A7,"*", A2:A7, " ")

Wenn der Zielbereich irgendwelche formelgesteuerten Zahlen enthält, können einige Formeln zu einer leeren Zeichenfolge ("") führen. Um Zellen zu ignorieren, die leere Saiten enthalten, ersetzen Sie "*" durch "*?*" Im Parameter von Kriterien1 :

=COUNTIFS(A2:A9,"*?*", A2:A9, " ")

Ein Fragezeichen, das mit einem Sternchen eingeschlossen ist, zeigt an, dass es in der Zelle mindestens einen Textcharakter geben sollte. Da eine leere Zeichenfolge keine Zeichen hat, erfüllt sie nicht die Kriterien und wird nicht gezählt. Leere Zellen, die mit dem Apostroph (') beginnen, werden ebenfalls nicht gezählt.

In der folgenden Abbildung gibt es einen Raum in A7, ein Apostroph in A8 und eine leere Zeichenfolge in A9 (= ""). Unsere Formel ignoriert alle diese Zellen und gibt eine Textzellzahl von 3 zurück:

Wie man Zellen zählt, die einen spezifischen Text in Excel enthalten

Um die Anzahl der Zellen zu erhalten, die einen bestimmten Text oder Zeichen enthalten, geben Sie den Text einfach in den bedingten Parametern der CountIF -Funktion an. Das folgende Beispiel erklärt die Feinheiten.

Um den Beispieltext genau zu entsprechen, geben Sie den vollständigen Text in Zitate ein:

COUNTIF(*范围*, "*文本*") Um teilweise übereinstimmende Zellen zu zählen, platzieren Sie den Text zwischen zwei Sternchen, die eine beliebige Anzahl von Zeichen vor und nach dem Text darstellen:

COUNTIF(*范围*, "\**文本*\*") Zum Beispiel, um herauszufinden, wie viele Zellen im A2: A7 -Bereich das Wort "Bananen" enthalten, verwenden Sie die folgende Formel:

=COUNTIF(A2:A7, "bananas")

Verwenden Sie die folgende Formel:

=COUNTIF(A2:A7, "*bananas*")

Um die Formel zu erleichtern, können Sie die Erkrankung in einer vordefinierten Zelle wie D2 platzieren und die Zellreferenz in den zweiten Parameter platzieren:

=COUNTIF(A2:A7, D2)

Basierend auf der Eingabe in D2 kann die Formel mit dem Beispieltext vollständig oder teilweise übereinstimmen:

• Geben Sie für eine genaue Übereinstimmung das gleiche Wort oder die gleiche Phrase wie das in der Quelltabelle angezeigte vollständige Wort oder die vollständige Phrase ein.
• Für teilweise Übereinstimmungen typen Beispieltext, umgeben von Platzhalterzeichen wie * Bananas * .

Da die Formel nicht unterempfindlich ist , müssen Sie sich keine Sorgen um Briefkoffer machen, was bedeutet, dass * Bananen * auch in Ordnung sind.

Alternativ, um teilweise übereinstimmende Zellen zu zählen, Zellreferenzen und Wildcard -Charaktere beispielsweise beizutragen:

=COUNTIF(A2:A7, "*"&D2&"*")

Weitere Informationen finden Sie unter der Anzahl der Zellen, die einen spezifischen Text in Excel enthalten.

Wie man gefilterte Zellen zählt, die Text in Excel enthalten

Wenn Sie Excel -Filter verwenden, um nur relevante Daten für einen bestimmten Moment anzuzeigen, müssen Sie manchmal sichtbare Zellen mit Text zählen. Leider gibt es für diese Aufgabe keine Ein-Klick-Lösung, aber das folgende Beispiel führt Sie durch die Schritte.

Angenommen, Sie haben eine Tabelle, wie in der folgenden Abbildung gezeigt. Einige Einträge wurden aus einer größeren Datenbank unter Verwendung von Formeln extrahiert, und einige Fehler traten dabei auf. Sie möchten die Gesamtzahl der Elemente in Spalte A ermitteln. Die COUNTIF -Formel, mit der wir Zellen mit Text enthalten, ist sehr effektiv, wenn alle Zeilen sichtbar sind:

=COUNTIF(A2:A10, "*")

Jetzt schränken Sie die Liste anhand bestimmter Kriterien ein, z. B. die Filterung von Elementen mit einer Reihe von mehr als 10. Die Frage ist: Wie viele Projekte sind noch übrig?

Um die gefilterten Zellen zu zählen, die Text enthalten , müssen Sie Folgendes tun:

1. Machen Sie in der Quelltabelle alle Zeilen sichtbar. Löschen Sie dazu alle Filter und verborgene Zeilen.

2. Fügen Sie eine Helferspalte hinzu, die die subtotale Formel enthält, die angibt, ob die Zeile gefiltert wurde. Verwenden Sie 3 für den Parameter function_num , um gefilterte Zellen zu verarbeiten: =SUBTOTAL(3, A2)

   Um alle versteckten Zellen zu identifizieren (filtriert und manuell versteckt), geben Sie 103 in function_num ein:

   =SUBTOTAL(103, A2)

   In diesem Beispiel möchten wir sichtbare Zellen, die Text enthalten , zählen, unabhängig davon, wie andere Zellen versteckt sind. Daher geben wir die zweite Formel in A2 ein und kopieren sie in A10.

   Für sichtbare Zellen kehrt die Formel zurück. 1. Sobald Sie bestimmte Zeilen manuell herausgefiltert oder verbergen, gibt die Formel 0 für sie zurück. (Sie werden diese Nullen nicht sehen, weil sie für versteckte Zeilen zurückgegeben werden. Um sicherzustellen, dass sie auf diese Weise funktioniert, kopieren Sie einfach den Inhalt der versteckten Zelle, die die subtotale Formel enthält, in eine sichtbare Zelle, z. B. = D2, vorausgesetzt, Zeile 2 ist versteckt.)

   

3. Verwenden Sie die CountIFs -Funktion und zwei verschiedene Kriterien_Range / Kriterienpaare , um sichtbare Zellen zu zählen, die Text enthalten:

   • Bedingung 1 - Suche nach Zellen, die einen beliebigen Text ("*") im Bereich A2: A10 enthalten.
   • Bedingung 2 - Suchen Sie 1 im Bereich D2: D10, um sichtbare Zellen zu erkennen.

   =COUNTIFS(A2:A10, "*", D2:D10, 1)

Jetzt können Sie die Daten, wie Sie möchten, filtern, und die Formel zeigt Ihnen die Anzahl der gefilterten Zellen, die in Spalte A (in diesem Fall) Text enthalten:

Wenn Sie keine zusätzlichen Spalten in Ihr Arbeitsblatt einfügen möchten, benötigen Sie eine längere Formel, um die Aufgabe zu erledigen. Wählen Sie einfach die aus, die Sie bevorzugen:

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

=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A10, ROW(A2:A10) - MIN(ROW(A2:A10)),,1)), -- (ISTEXT(A2:A10)))

Multiplikationsbetreiber können auch sein:

=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A10))) * (ISTEXT(A2:A10)))

=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A10, ROW(A2:A10)-MIN(ROW(A2:A10)),,1)) * (ISTEXT(A2:A10)))

Welche Formel zu verwenden ist, hängt von Ihren persönlichen Vorlieben ab - die Ergebnisse sind sowieso gleich:

Wie diese Formeln funktionieren

Die erste Formel verwendet die indirekte Funktion, um eine einzelne Referenz auf Subtotal für alle Zellen im angegebenen Bereich zu "providieren". Die zweite Formel verwendet eine Kombination aus Offset-, Reihe- und MIN -Funktionen, um den gleichen Zweck zu erfüllen.

Die subtotale Funktion gibt ein Array von 1 und 0 zurück, wobei 1 sichtbare Zellen und 0 versteckte Zellen repräsentiert (z. B. die Hilfsspalte oben).

Die ISText -Funktion überprüft jede Zelle in A2: A10 und gibt true zurück, wenn die Zelle Text enthält, andernfalls gibt es false zurück. Der Double Unary Operator (-) leitet wahre und falsche Werte auf 1 und 0. An diesem Punkt lautet die Formel wie folgt:

=SUMPRODUCT({0;1;1;1;0;1;1;0;0}, {1;1;1;0;1;1;0;1;1})

Die Sumproduktfunktion multipliziert zunächst Elemente in zwei Arrays an derselben Position und fasst dann das resultierende Array zusammen.

Da das Ergebnis der Multiplikation mit Null Null ist, haben nur Zellen, die in beiden Arrays durch 1 dargestellt werden, 1 im endgültigen Array.

=SUMPRODUCT({0;1;1;0;0;1;0;0;0})

Die Anzahl von 1 im obigen Array ist die Anzahl der sichtbaren Zellen, die Text enthalten.

So zählt Zellen, die Text in Excel enthalten. Vielen Dank für das Lesen und hoffe, Sie nächste Woche in unserem Blog zu sehen!

Herunterladbare Dateien

Formeln zum Zählen von Zellen, die Text in Excel enthalten

Das obige ist der detaillierte Inhalt vonExcel -Formeln, um Zellen mit Text zu zählen: spezifische oder filtrierte Zellen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!