Dieses Tutorial zeigt, wie Sie dasExzel ZÄHLENWENN und ZÄHLENWENN Funcgen in Excel, um Daten zu zählen, die bestimmte Kriterien erfüllen.
ZÄHLENWENN Funktionsübersicht
Sie können die ZÄHLENWENN-Funktion in Excel verwenden, um Zellen zu zählen, die einen bestimmten Wert enthalten, Zellen zu zählen, die größer oder gleich einem Wert sind usw.
(Beachten Sie, wie die Formeleingaben angezeigt werden)
COUNTIF-Funktionssyntax und Argumente:
=COUNTIF (Bereich, Kriterien)
Bereich - Der Bereich der zu zählenden Zellen.
Kriterien - Die Kriterien, die steuern, welche Zellen gezählt werden sollen.
Was ist die ZÄHLENWENN-Funktion?
Die ZÄHLENWENN-Funktion ist eine der älteren Funktionen, die in Tabellenkalkulationen verwendet werden. Einfach ausgedrückt, es ist großartig, einen Bereich zu scannen und Ihnen mitzuteilen, wie viele der Zellen diese Bedingung erfüllen. Wir werden uns ansehen, wie die Funktion mit Text, Zahlen und Datumsangaben funktioniert. sowie einige der anderen Situationen, die auftreten können.
Einfaches Beispiel
Beginnen wir mit einem Blick auf diese Liste zufälliger Elemente. Wir haben einige Zahlen, leere Zellen und einige Textzeichenfolgen.
Wenn Sie wissen möchten, wie viele Elemente genau den Kriterien entsprechen, können Sie als zweites Argument angeben, wonach Sie suchen möchten. Ein Beispiel für diese Formel könnte so aussehen
=ZÄHLENWENN(A2:A9, "Apfel")
Diese Formel würde die Zahl 3 zurückgeben, da es 3 Zellen in unserem Bereich gibt, die diese Kriterien erfüllen. Alternativ können wir eine Zellreferenz verwenden, anstatt einen Wert fest zu codieren. Wenn wir "Apple" in Zelle G2 schreiben, könnten wir die Formel ändern in
=ZÄHLENWENN(A2:A9, G2)
Beim Umgang mit Zahlen ist es wichtig, zwischen Zahlen und Zahlen zu unterscheiden, die als Text gespeichert sind. Im Allgemeinen setzen Sie beim Schreiben von Formeln keine Anführungszeichen um Zahlen. Um also eine Formel zu schreiben, die nach der Zahl 5 sucht, würdest du schreiben:
=ZÄHLENWENN(A2:A9, 5)
Schließlich könnten wir auch mit einer Zeichenfolge der Länge Null nach leeren Zellen suchen. Wir würden diese Formel schreiben als
=ZÄHLENWENN(A2:A9, "")
Notiz: Diese Formel zählt sowohl Zellen, die wirklich leer sind, als auch Zellen, die als Ergebnis einer Formel, wie einer IF-Funktion, leer sind.
Teilweise Übereinstimmungen
Die ZÄHLENWENN-Funktion unterstützt die Verwendung von Platzhaltern „*“ oder „?“ in den Kriterien. Schauen wir uns diese Liste leckerer Backwaren an:
Um alle Elemente zu finden, die mit Apple beginnen, könnten wir „Apple*“ schreiben. Um eine Antwort von 3 zu erhalten, lautet unsere Formel in D2
=ZÄHLENWENN(A2:A5, "Apfel*")
Notiz: Bei der Funktion ZÄHLENWENN wird die Groß-/Kleinschreibung nicht beachtet, Sie können also auch „Apfel*“ schreiben, wenn Sie möchten.
Zurück zu unseren Backwaren, möchten wir vielleicht auch herausfinden, wie viele Torten wir in unserer Liste haben. Wir finden das, indem wir den Platzhalter an den Anfang unseres Suchbegriffs setzen und schreiben
=ZÄHLENWENN(A2:A5, "*Kuchen")
Diese Formel liefert das Ergebnis 2.
Wir können auch Platzhalter verwenden, um nach Zellen mit Text zu suchen. Kehren wir zu unserer ursprünglichen Datenliste zurück.
Um die Anzahl der Zellen zu zählen, die mindestens etwas Text enthalten, also keine Zahlen oder leere Zellen zählen, können wir schreiben
=ZÄHLENWENN(A2:A9, "*")
Sie können sehen, dass unsere Formel ein Ergebnis von 4 korrekt zurückgibt.
Vergleichsoperatoren in COUNTIF
Beim Schreiben der Kriterien haben wir bisher impliziert, dass unser Vergleichsoperator „=“ ist. Eigentlich hätten wir das schreiben können:
=ZÄHLENWENN(A2:A9, "=Apfel")
Es ist jedoch ein zusätzliches Zeichen zum Ausschreiben, daher wird es normalerweise weggelassen. Dies bedeutet jedoch, dass Sie die anderen Operatoren wie größer als, kleiner als oder ungleich verwenden können. Schauen wir uns diese Liste der aufgezeichneten Altersgruppen an:
Wenn wir wissen wollten, wie viele Kinder mindestens 5 Jahre alt sind, können wir einen „größer oder gleich“-Vergleich wie folgt aufschreiben:
=ZÄHLENWENN(A2:A8, ">=5")
Notiz: Der Vergleichsoperator wird immer als Textzeichenfolge angegeben und muss daher in Anführungszeichen stehen.
Auf ähnliche Weise können Sie auch nach Elementen suchen, die kleiner als ein bestimmter Wert sind. Wenn wir herausfinden müssen, wie viele weniger als 8 sind, können wir ausschreiben
=ZÄHLENWENN(A2:A8, "<8")
Damit erhalten wir das gewünschte Ergebnis von 5. Stellen wir uns nun vor, dass alle 6-jährigen Kinder einen Ausflug machen. Wie viele Kinder werden bleiben? Wir können dies herausfinden, indem wir einen "nicht gleich"-Vergleich wie diesen verwenden:
=ZÄHLENWENN(A2:A8, "6")
Jetzt können wir schnell sehen, dass wir 6 Kinder haben, die noch nicht 6 Jahre alt sind.
In diesen bisherigen Vergleichsbeispielen haben wir die gewünschten Werte hart codiert. Sie können auch einen Zellbezug verwenden. Der Trick besteht darin, dass Sie den Vergleichsoperator mit dem Zellbezug verketten müssen. Nehmen wir an, wir geben die Zahl 7 in Zelle C2 ein und unsere Formel in D2 soll zeigen, wie viele Kinder jünger als 7 Jahre sind.
Unsere Formel in D2 muss so aussehen:
=ZÄHLENWENN(A2:A8, "<"&C2)
Notiz: Achten Sie beim Schreiben dieser Formeln besonders darauf, ob Sie ein Element in Anführungszeichen oder außerhalb setzen müssen. Die Operatoren stehen immer innerhalb von Anführungszeichen, Zellbezüge stehen immer außerhalb von Anführungszeichen. Zahlen sind außen, wenn Sie eine genaue Übereinstimmung durchführen, aber innen, wenn Sie einen Vergleichsoperator verwenden.
Mit Daten arbeiten
Wir haben gesehen, wie Sie einen Text oder eine Zahl als Kriterium angeben können, aber was ist, wenn wir mit Datumsangaben arbeiten müssen? Hier ist eine kurze Beispielliste, mit der wir arbeiten können:
Um zu zählen, wie viele Daten nach dem 4. Mai liegen, müssen wir etwas Vorsicht walten lassen. Computer speichern Datumsangaben als Zahlen, daher müssen wir sicherstellen, dass der Computer die richtige Zahl verwendet. Wenn wir diese Formel schreiben würden, würden wir das richtige Ergebnis erhalten?
=ZÄHLENWENN(A2:A9, "
Die Antwort ist „möglicherweise“. Da wir das Jahr in unseren Kriterien weggelassen haben, geht der Computer davon aus, dass wir das aktuelle Jahr meinen. Wenn alle Daten, mit denen wir arbeiten, für das laufende Jahr gelten, erhalten wir die richtige Antwort. Wenn es jedoch einige Termine gibt, die in der Zukunft liegen, erhalten wir die falsche Antwort. Sobald das nächste Jahr beginnt, liefert diese Formel ein anderes Ergebnis. Daher sollte diese Syntax wahrscheinlich vermieden werden.
Da es schwierig sein kann, Datumsangaben in einer Formel korrekt zu schreiben, empfiehlt es sich, das Datum, das Sie verwenden möchten, in eine Zelle zu schreiben und dann diesen Zellbezug in Ihrer COUNTIF-Formel zu verwenden. Schreiben wir also das Datum 7. Mai 2020 in Zelle C2, und dann können wir unsere Formel in C4 eingeben.
Die Formel in C4 ist
=ZÄHLENWENN(A2:A9, "<"&C2)
Jetzt wissen wir, dass das Ergebnis von 7 richtig ist und sich die Antwort nicht unerwartet ändern wird, wenn wir diese Tabelle irgendwann in der Zukunft öffnen.
Bevor wir diesen Abschnitt verlassen, ist es üblich, beim Arbeiten mit Datumsangaben die Funktion HEUTE zu verwenden. Wir können das genauso verwenden wie eine Zellreferenz. Zum Beispiel könnten wir die vorherige Formel wie folgt ändern:
=ZÄHLENWENN(A2:A9, "<"&HEUTE())
Jetzt wird unsere Formel immer noch in Echtzeit aktualisiert, und wir werden eine Anzahl von Artikeln haben, die weniger als heute sind.
Mehrere Kriterien und ZÄHLENWENN
Die ursprüngliche ZÄHLENWENN-Funktion wurde 2007 verbessert, als ZÄHLENWENN herauskam. Die Syntax der beiden ist sehr ähnlich, wobei letztere es Ihnen ermöglicht, zusätzliche Bereiche und Kriterien anzugeben. Sie können COUNTIFS problemlos in jeder Situation verwenden, in der COUNTIF vorhanden ist. Es ist nur eine gute Idee zu wissen, dass beide Funktionen existieren.
Schauen wir uns diese Datentabelle an:
Um herauszufinden, wie viele Personen die Lohnstufen 1 bis 2 haben, könnten Sie eine Summation der ZÄHLENWENN-Funktionen wie folgt schreiben:
=ZÄHLENWENN(B2:B7, ">=1")-ZÄHLENWENN(B2:B7, ">2")
Diese Formel funktioniert, da Sie alles finden, was über 1 liegt, aber dann die Anzahl der Datensätze subtrahiert, die über Ihrem Grenzwert liegen. Alternativ können Sie COUNTIFS wie folgt verwenden:
=ZÄHLENWENN(B2:B7, ">=1", B2:B7, "<=2")
Letzteres ist intuitiver zu lesen, daher sollten Sie diese Route verwenden. Außerdem ist COUNTIFS leistungsfähiger, wenn Sie mehrere Spalten berücksichtigen müssen. Nehmen wir an, wir möchten wissen, wie viele Personen im Management und in der Gehaltsstufe 1 beschäftigt sind. Das geht nicht nur mit einem ZÄHLENWENN; du müsstest ausschreiben
=ZÄHLENWENN(A2:A7, "Verwaltung", B2:B7, 1)
Diese Formel würde Ihnen das korrekte Ergebnis von 2 liefern. Bevor wir diesen Abschnitt verlassen, betrachten wir eine Logik vom Typ Oder. Was wäre, wenn wir herausfinden wollten, wie viele Leute im Management sind oder? Sie müssten einige ZÄHLENIFS zusammenzählen, aber es gibt zwei Möglichkeiten, dies zu tun. Der einfachere Weg ist, es so zu schreiben:
=ZÄHLENWENN(A2:A7, "HR")+ZÄHLENWENN(A2:A7, "Verwaltung")
Sie könnten auch ein Array verwenden und diese Array-Formel schreiben:
=SUMME(ZÄHLENWENN(A2:A7, {"HR", "Management"}))
Notiz: Array-Formeln müssen mit `Strg+Shift+Enter` und nicht nur mit `Enter` bestätigt werden.
Wie diese Formel funktioniert, ist, dass Sie ein Array als Eingabe angegeben haben. Es berechnet also das Ergebnis zu zwei verschiedenen ZÄHLENWENN-Funktionen und speichert sie in einem Array. Die SUM-Funktion addiert dann alle Ergebnisse in unserem Array zu einer einzigen Ausgabe. Somit wird unsere Formel wie folgt ausgewertet:
=SUMME(ZÄHLENWENN(A2:A7, {"HR", "Management"})) =SUMME({2, 3}) =5
Zähle einzigartige Werte
Nachdem wir nun gesehen haben, wie ein Array mit der COUNTIF-Funktion verwendet wird, können wir noch einen Schritt weitergehen, um zu zählen, wie viele eindeutige Werte sich in einem Bereich befinden. Schauen wir uns zunächst noch einmal unsere Liste der Abteilungen an.
=SUMME(1/ZÄHLWENN(A2:A7,A2:A7))
Wir können sehen, dass es 6 Zellen mit Daten gibt, aber es gibt nur 3 verschiedene Elemente. Damit die Mathematik funktioniert, müssten wir jedes Element einen Wert von 1/N haben, wobei N die Anzahl der Wiederholungen eines Elements ist. Wenn beispielsweise jede HR nur 1/2 wert wäre, würdest du bei der Addition eine Zahl von 1 für 1 einzigartigen Wert erhalten.
Zurück zu unserem ZÄHLENWENN, mit dem ermittelt werden soll, wie oft ein Artikel in einem Bereich vorkommt. In D2 schreiben wir die Arrayformel
=SUMME(1/ZÄHLENWENN(A2:A7, A2:A7))
Wie diese Formel funktioniert, ist, dass für jede Zelle im Bereich A2:A7 überprüft wird, wie oft sie vorkommt. Mit unserem Beispiel wird dies eine Reihe von
{2, 2, 3, 3, 3, 1}
Dann wandeln wir alle diese Zahlen in Brüche um, indem wir etwas dividieren. Jetzt sieht unser Array so aus
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
Wenn wir diese alle zusammenzählen, erhalten wir unser gewünschtes Ergebnis von 3.
Countif mit zwei oder mehreren Bedingungen - Die Countifs-Funktion
Bisher haben wir nur mit der COUNTIF-Funktion gearbeitet. Die ZÄHLENWENN-Funktion kann jeweils nur ein Kriterium verarbeiten. Um ZÄHLENWENN mit mehreren Kriterien zu verwenden, müssen Sie die ZÄHLENWENN-Funktion verwenden. ZÄHLENWENN verhält sich genau wie ZÄHLENWENN. Fügen Sie einfach zusätzliche Kriterien hinzu. Schauen wir uns das folgende Beispiel an.
=ZÄHLENWENN(B2:B7,"=130")
ZÄHLENWENN & ZÄHLENWENN in Google Tabellen
Die Funktion ZÄHLENWENN & ZÄHLENWENN funktioniert in Google Tabellen genauso wie in Excel:
