Office-Blog

In einer intelligenten Tabelle genügen zwei Mausklicks in der Ergebniszeile, um die Anzahl der gefilterten Datensätze zu ermitteln. Wenn ich meine Filterergebnisse aber mit einer laufenden Nummer versehen möchte, muss ich tiefer in die Trickkiste greifen und Excel überlisten.

„Flüchtige“ Informationen in der Statusleiste

Nachdem ich in einer Tabelle einen oder mehrere Filter gesetzt habe, zeigt mir Excel unten links in der Statusleiste die Anzahl der gefundenen Datensätze sowie aller Datensätze an.

Klicke ich jedoch auf eine Zelle außerhalb der intelligenten Tabelle oder wechsele ich das Arbeitsblatt, verschwindet die Anzeige in der Statusleiste. Um sie erneut zu sehen, müsste ich noch mal filtern.

Damit die Anzahl der gefilterten Datensätze dauerhaft angezeigt wird, blende ich mir die Ergebniszeile der intelligenten Tabelle ein und wähle dort für die gewünschte Spalte die Funktion Anzahl (bei alphanumerischen Inhalten) bzw. Anzahl Zahlen (bei numerischen Inhalten).

Die Gesamtzahl aller Datensätze erhalte ich, wenn ich in der Ergebniszeile anstatt TEILERGEBNIS die Funktion ANZAHL bzw. ANZAHL2 verwende.

Laufende Nummer für meine Filterergebnisse

Für eine fortlaufende Nummerierung der Filterergebnisse füge ich meiner Tabelle eine neue Spalte hinzu und entferne alle Filter aus der Tabelle.

• Da ich nur die sichtbaren Zeilen durchnummerieren möchte, verwende ich zum Zählen der Zellen die Funktion TEILERGEBNIS.
• Damit die Zählung immer bei der ersten Tabellenzeile beginnt, verwende ich für die Startzelle einen absoluten Tabellenbezug, damit die Zelladresse beim Kopieren der Funktion unverändert bleibt.

Excel erkennt, dass es sich um eine berechnete Spalte handelt und kopiert für mich die Funktion automatisch in jede Zeile. Toll! Oder doch nicht?

Zu früh gefreut

Setze ich nun einen Filter, werden zwar alle gefilterten Ergebnisse fortlaufend nummeriert, aber die Anzahl der gefundenen Datensätze in der Statuszeile stimmt nicht mit meiner laufenden Nummer überein. Bei genauem Hinsehen fallen noch zwei weitere Dinge auf:

• Zum einen passt der letzte Datensatz nicht zu meinem gewählten Filter (Geburtsjahr 1967, 1971 oder 1974).
• Zum anderen ist die Zeilennummer dieses Datensatzes nicht blau.

Es handelt sich bei dem Datensatz um die letzte Zeile meiner ungefilterten Liste. Oder anders ausgedrückt: Die letzte Zeile der Datenbasis wird (unabhängig vom gewählten Filter) immer angezeigt. Aber warum?

Die TEILERGEBNIS-Funktion „unsichtbar“ machen

• Des Rätsels Lösung liegt darin, wie Excel die Verwendung der Funktion TEILERGEBNIS interpretiert: Befindet sich in der letzten Zeile einer Tabelle oder eines zu filternden Zellbereichs in irgendeiner Spalte ein TEILERGEBNIS, geht Excel davon aus, dass es sich um eine „Ergebniszeile“ handelt.
• Es blendet diese Zeile nie aus!

Mit einem Trick lässt sich dieses „intelligente“ Verhalten von Excel überlisten:

• Ich multipliziere das Ergebnis der Funktion TEILERGEBNIS mit 1.
• Dadurch  „verstecke“ ich die TEILERGEBNIS-Funktion.
• Excel „sieht“ nun nur noch eine Formel, in der TEILERGEBNIS verwendet wird, anstatt die Zeile (aufgrund der TEILERGEBNIS-Funktion) als Ergebniszeile zu interpretieren.

Da ich „1*“ erst nachträglich hinzugefügt habe, muss ich den Filter mit der Schaltfläche Filtern im Register Daten einmal aus- und wieder einschalten. Erst dann wird das korrekte Filterergebnis angezeigt.

Eindeutige Nummerierung der Datensätze

Wie in einer Excel-Tabelle jedem Datensatz automatisch eine eindeutige Nummer zugewiesen werden kann, beschreibt übrigens meine Kollegin Hildegard Hügemann sehr gut in ihrem Blog: https://www.huegemann-informatik.de/blog/excel/automatische-nummerierung-in-excel-tabellen