Office-Blog

Laufende Nummerierung in gefilterten Listen

15.04.2013   Dominik Petri

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.

Das flüchtige Filterergebnis in der Statusleiste

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.

Erster Entwurf einer Teilergebnis-Formel

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.

Problem: Die laufende Nummer und Anzeige in der Statuszeile stimmen nicht überein

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.

Die fertige Teilergebnis-Formel

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

Über den Autor

Dominik Petri

- Dipl.-Betriebswirt mit 20 Jahren Berufserfahrung in Großbanken
- Chartered Financial Analyst® und Spezialist für Business Intelligence (BI)
- Zertifizierter Office-Trainer mit den Schwerpunkten Excel und VBA
- Spezialist der ersten Stunde für Power BI mit Excel
- Gründer der offiziellen Microsoft Power BI User Group Frankfurt
- Unterstützt Firmen beim Einführen und Verwenden der BI-Tools von Microsoft
- Projekterfahrener Excel-Berater, VBA-Programmierer für Analysetools
- Autor für Microsoft Press, dpunkt.verlag und »Modernes Reporting mit Excel«

3 Comments so far

KevinPosted on  10:56 am - Dez 18, 2015

Hallo,
vielen Dank für den Tipp! Mit Imanuels Hinweis habe ichs hinbekommen, mit [@Vorname] kam immer ein Fehler. Könntest du die Syntax [@Vorname] erklären?
Dankeschön und Grüsse

    Dominik PetriPosted on  4:40 pm - Jan 5, 2016

    Hallo Kevin,
    die Syntax [@Vorname] bedeutet: Die Zelle in der Spalte Vorname, die sich in der gleichen Zeile wie die Formel befindet.
    Für Zeile 11 wäre es beispielsweise $B11
    Viele Grüße
    Dominik Petri

ImanuelPosted on  3:11 am - Sep 14, 2014

Grüß Gott. Den ersten Teil hatte ich nicht verstanden, mit dem Bezug auf @Vorname; das fand ich dann auf einer anderen (Seite. $B$2:$B2)
Aber der Trick mit „1*TEILERGEBNIS“ war hilfreich. Dankeschön!