Excel-TippGefilterte Zeilen mit einer Formel zählen
Wenn Sie in einer Excel-Tabelle mit Filtern arbeiten und Zeilen durch Filterkriterien ausblenden oder einblenden, dann passen sich einfache Formeln nicht auf die jeweilige Filterauswahl an. Beispiel: Sie möchten die Anzahl von gefilterten Zeilen in einer Tabelle mit einer Formel zählen?
Mit den klassischen Funktionen wie ZÄHLENWENN() oder auch ANZAHL2() kommen Sie hier nicht weiter, da diese zwar alle Zellen in einem bestimmten Bereich zählen, aber nicht berücksichtigen, wenn Zeilen über einen Filter ausgeblendet werden. Das Ergebnis würde daher alle Zeilen enthalten (sichtbare und ausgeblendete Zeilen).
Beispiel für das Filtern einer Tabelle
Angenommen Sie haben eine Tabelle mit Umsatzdaten, die Spalten für Monat, Jahr und Umsatz beinhaltet. Sie möchten diese Tabelle nach unterschiedlichen Kriterien filtern und es soll immer die Anzahl der sichtbaren Zeilen mit einer Formel gezählt werden, die gerade trotz Filter sichtbar sind.
Funktion TEILERGEBNIS() nutzen
Um diese Aufgabe zu lösen, können Sie in Excel die Funktion TEILERGEBNIS() einsetzen. Mit der TEILERGEBNIS-Funktion können Sie ein sogenanntes Aggregatergebnis aus einer gefilterten Liste Werte ermitteln.
TEILERGEBNIS() beherrscht viele Aggregationsfunktionen wie zum Beispiel SUMMME(), MITTELWERT(), ANZAHL(), MAX() etc. Das Besondere an der TEILERGEBNIS()-Funktion ist, dass diese in gefilterten Listen nur die sichtbaren Listeneinträge bei der Berechnung berücksichtigt. Des Weiteren kann sie Werte in ausgeblendete Zeilen einschließen oder ausschließen.
TEILERGEBNIS() besitzt die folgende Syntax:
=TEILERGEBNIS(Funktion;Bezug1;[Bezug2];…)
- Funktion: Eine Zahl, die angibt, welche Funktion oder Rechenoperation verwendet werden soll; siehe folgende Tabelle
- Bezug1: Ein Bereich oder Zellbezug, auf den die Rechenoperation angewendet werden soll
- Bezug2 [optional]: Ein weiterer Bereich oder Zellbezug, auf den die Rechenoperation angewendet wird
Da mehrere Aggregationsfunktionen (das Argument „Funktion“) für TEILERGEBNIS() zur Verfügung stehen, sind diese in der folgenden Tabelle aufgeführt.
Hinweis: „Ausgeblendet“ in den beiden Spalten dieser Übersicht bezieht sich auf die Zeilen, die Sie aktiv ausblenden: Menü START – Zellen – Format – Ausblenden und Einblenden … Wenn Sie die Tabelle filtern, haben beide Parameter (1 oder 101 etc.) die gleiche Wirkung.
Beispiel: Anzahl der nach Filterung angezeigten Zeilen berechnen
Die für die Aufgabe passende Aggregationsfunktion ist in diesem Fall die Funktion ANZAHL2. Mit ANZAHL2 können Sie die Anzahl der Zellen zu zählen, die Texte, Zahlen, Fehlerwerte und Wahrheitswerte enthalten. Leere Zellen werden aber nicht in die Berechnung einbezogen.
ANZAHL2 entspricht in der Funktion TEILERGEBNIS() dem Code 3 oder 103. Die beiden Funktionscodes unterscheiden sich nur bei der Berücksichtigung von manuell ausgeblendeten Zeilen:
- 3 = bezieht ausgeblendete Werte bei der Berechnung mit ein
- 103 = ignoriert ausgeblendete Werte bei der Berechnung
Erfassen Sie daher als Nächstes die folgende Formel in die Zelle, in der Sie die Anzahl der gefilterten Zeilen anzeigen lassen wollen:
=TEILERGEBNIS(3;B2:B13)
- 3 repräsentiert die Aggregationsfunktion ANZAHL2
- B2:B13 den Listenbereich ohne Kopfzeile, bei welchem die sichtbaren Zellen gezählt werden sollen.
Wichtig: Da die Funktion ANZAHL2 grundsätzlich keine leeren Zellen berücksichtigt, sollten Sie hier eine Spalte in der Liste auswählen, die keine Leerzellen enthält.
Als Ergebnis erhalten Sie den Wert 12 von der Formel geliefert. Dies ist korrekt, da alle Datensätze im Moment in der Liste angezeigt werden.
Die Wirkung der Funktion TEILERGEBNIS() zeigt sich dann, wenn die Liste gefiltert wird.
Dazu wird als Filter in Spalte C eingestellt: Es sollen nur die Datensätze angezeigt werden, deren Umsatz größer als 1.000.000 EUR ist.
Klicken Sie daher in der Spalte C = Umsatz auf das Filter-Symbol und wählen Sie die Befehlsfolge Zahlenfilter und Größer als aus.
Es öffnet sich das Dialogfeld Benutzerdefinierter Autofilter. Erfassen Sie hier neben ist größer als den Wert 1000000 und bestätigen Sie Ihre Einstellung, indem Sie das Dialogfeld durch Klick auf OK schließen.
Excel filtert daraufhin die Liste und zeigt nur die Datensätze an, deren Umsatz größer als 1.000.000 EUR ist. Die TEILERGEBNIS()-Funktion liefert jetzt den Wert 7 als Ergebnis, da nur noch die sichtbaren Listeneinträge durch die Funktion TEILERGEBNIS() berücksichtigt werden.
