Excel-TippGefilterte Zeilen mit einer Formel zählen

Beim Einsatz von Filtern in Excel-Tabellen passen sich Formeln und ihre Ergebnisse nicht einfach an die gewählten Filterkriterien an. Wenn Sie die Funktion TEILERGEBNIS() mit den bekannten Rechenoperationen kombinieren, erhalten Sie das gewünschte Ergebnis. So gehen Sie vor.

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.

Tabelle mit Filterfunktion

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.

Mögliche Funktionen, die mit TEILERGEBNIS() eingesetzt werden können

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.

Beispiel für die Kombination von TEILERGEBNIS() mit ANZAHL2()

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.

Tabelle filtern mit Größer als …

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.

Einstellungen für den benutzerdefinierten Autofilter einer Excel-Tabelle

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.

Ergebnis der Funktion ANZAHL2 mit TEILERGEBNIS() und gefilterter Tabelle

Dazu im Management-Handbuch

Ähnliche Artikel

Excel-Tipps