Eine Liste ist mit 1, 2, 3 durchnummeriert. Sobald Sie filtern, entstehen Lücken: Sichtbar sind etwa nur noch 2, 3, 5 und 9. Mit einer Formel zählt die Nummer nur die sichtbaren Zeilen und beginnt nach jedem Filter wieder lückenlos bei 1.
Die Kurzlösung
Stehen die Artikel in B2:B100, schreiben Sie in A2 diese Formel und kopieren sie nach unten:
=AGGREGAT(3;5;B$2:B2)
AGGREGAT mit der Funktion 3 (ANZAHL2) und der Option 5 (ausgeblendete Zeilen ignorieren) zählt die gefüllten, sichtbaren Zellen von B2 bis zur aktuellen Zeile. Das Dollarzeichen hält den Anfang fest, das Ende wandert beim Kopieren mit.
Beispiel
Gefiltert nach Preisen von 80 bis 130 €:
| Zeile | Artikel | Preis | Nr. ohne Formel | Nr. mit AGGREGAT |
|---|---|---|---|---|
| 3 | Artikel 2 | 120 € | 2 | 1 |
| 4 | Artikel 3 | 80 € | 3 | 2 |
| 6 | Artikel 5 | 95 € | 5 | 3 |
| 7 | Artikel 6 | 130 € | 6 | 4 |
| 9 | Artikel 8 | 110 € | 8 | 5 |
| 11 | Artikel 10 | 85 € | 10 | 6 |
Heben Sie den Filter auf, zählt die Nummer wieder von 1 bis 10.
Warum AGGREGAT statt TEILERGEBNIS?
Häufig wird =TEILERGEBNIS(103;B$2:B2) empfohlen. Die Formel zählt ebenfalls nur sichtbare Zellen und liefert dieselben Nummern. Sie hat aber einen Haken:
Enthält eine Spalte TEILERGEBNIS-Formeln, hält der Autofilter die letzte Zeile der Liste für eine Ergebniszeile und blendet sie nicht aus. Im Beispiel bleibt ein Artikel mit 300 € sichtbar, obwohl nur Preise bis 130 € angezeigt werden sollen. Mit AGGREGAT tritt das nicht auf, die Zeile wird korrekt ausgeblendet.
AGGREGAT gibt es ab Excel 2010. Für ältere Versionen bleibt TEILERGEBNIS. Dann sollte unter der Liste eine Zeile stehen, die nicht gefiltert werden muss, etwa eine echte Summenzeile.
Ohne Hilfsspalte
Ältere Anleitungen nummerieren zuerst eine Hilfsspalte mit festen Zahlen und zählen dann diese Spalte. Das ist nicht nötig: Die Formel kann jede Spalte zählen, die in jeder Zeile gefüllt ist, etwa die Artikelbezeichnung. Wählen Sie eine Spalte, in der keine Lücken vorkommen, sonst zählt die Nummer bei leeren Zellen nicht weiter.
In einer Tabelle (Strg+T)
In einer als Tabelle formatierten Liste schreiben Sie die Formel mit strukturierten Verweisen:
=AGGREGAT(3;5;INDEX([Artikel];1):[@Artikel])
INDEX([Artikel];1) ist die erste Zelle der Spalte Artikel, [@Artikel] die Zelle der aktuellen Zeile. Excel übernimmt die Formel automatisch für neue Zeilen.
Die ganze Nummerierung mit einer Formel (Microsoft 365)
In Microsoft 365 geht es auch ohne Kopieren nach unten. Diese Formel in A2 nummeriert alle Zeilen von B2 bis B11 und berücksichtigt dabei den Filter:
=SCAN(0;NACHZEILE(B2:B11;LAMBDA(z;TEILERGEBNIS(103;z)));LAMBDA(a;x;a+x))
NACHZEILE prüft für jede Zeile mit TEILERGEBNIS, ob sie sichtbar und gefüllt ist, und liefert 1 oder 0. SCAN zählt diese Werte von oben nach unten auf, wie eine laufende Summe. Mit dem Filter aus dem Beispiel ergibt das für die sichtbaren Zeilen wieder 1 bis 6. Die Formel steht in einer einzigen Zelle, neue Zeilen erfassen Sie, indem Sie den Bereich vergrößern.
In einer intelligenten Tabelle (Strg+T) funktioniert das nicht, denn Formeln mit Überlaufbereich sind dort nicht erlaubt. Dort bleibt die Formel mit AGGREGAT von oben.
Für eine Liste ohne Filter genügt =SEQUENZ(ANZAHL2(B2:.B1000)). Der gekürzte Bezug mit Punkt zählt die gefüllten Zellen, SEQUENZ erzeugt die Zahlen von 1 bis zu dieser Anzahl.
Nummerierung je Gruppe
Oft soll die Nummer für jeden Kunden, jedes Projekt oder jede Kostenstelle neu bei 1 beginnen. Stehen die Kunden in B2:B100, zählt diese Formel in A2, wie oft der Kunde bis zur aktuellen Zeile schon vorkam:
=ZÄHLENWENN(B$2:B2;B2)
| Kunde | Nr. je Kunde |
|---|---|
| Meier | 1 |
| Schulz | 1 |
| Meier | 2 |
| Meier | 3 |
| Schulz | 2 |
Wie bei AGGREGAT hält das Dollarzeichen den Anfang fest, das Ende wandert beim Kopieren mit. In Microsoft 365 erzeugt =MAP(B2:B6;LAMBDA(x;ZÄHLENWENN(B2:x;x))) dieselben Nummern mit einer Formel. Zusammen mit dem Kunden ergibt sich daraus eine eindeutige Kennung wie „Meier-3“, etwa mit =B2&"-"&A2.
Typische Fehler
- Dollarzeichen vergessen: Ohne
B$2verschiebt sich der Anfang beim Kopieren mit, und jede Zeile zählt nur sich selbst. - Lücken in der gezählten Spalte: Leere Zellen zählen nicht mit. Die Nummer bleibt dort stehen und wiederholt sich in der nächsten Zeile.
- Sortieren: Beim Sortieren rechnet die Formel neu, die Nummer bleibt also immer 1, 2, 3 von oben nach unten. Wer eine feste Nummer für jeden Datensatz braucht, etwa eine Belegnummer, trägt sie als Wert ein und verwendet die Formel nur für die Anzeige.
- Manuell ausgeblendete Zeilen: AGGREGAT mit Option 5 und TEILERGEBNIS mit 103 ignorieren auch von Hand ausgeblendete Zeilen. TEILERGEBNIS mit 3 statt 103 zählt sie dagegen mit.
- ZEILE()-1 als Nummer: Die schnelle Lösung
=ZEILE()-1zählt die Zeilennummer des Blatts. Nach dem Filtern bleiben die Lücken, und eine neue Zeile oberhalb der Liste verschiebt alle Nummern.
Mit KI schneller zum Ziel
Bitten Sie ChatGPT, Claude oder Copilot um eine laufende Nummer, kommt oft =ZEILE()-1 oder die TEILERGEBNIS-Variante mit 103. Die erste versagt beim Filtern, die zweite bringt das Problem mit der letzten Zeile mit, das dieser Beitrag beschreibt. Beides fällt im ersten Test nicht auf, weil man meist ohne Filter testet. Prüfen Sie KI-Vorschläge deshalb mit gesetztem Filter und achten Sie darauf, ob die letzte Zeile korrekt ausgeblendet wird.
Mehr zu Stärken und Grenzen der Werkzeuge im Beitrag ChatGPT und Copilot in Excel: Möglichkeiten und Grenzen. Wie wir KI in Projekten einsetzen, um Aufwand und Kosten zu senken, steht unter KI und Automatisierung.
Aus der Praxis
Eine Nummerierung, die beim Filtern mitzählt, ist eine Kleinigkeit, die Listen deutlich angenehmer macht. Gleichzeitig zeigt sie, wie viel in Details wie dem Unterschied zwischen AGGREGAT und TEILERGEBNIS steckt. Solche Kniffe vermitteln wir in unseren Excel-Schulungen an Ihren eigenen Listen, damit sie im Alltag sitzen und nicht beim nächsten Mal wieder gesucht werden müssen.
Passende Beiträge
Lernen oder machen lassen?
Excel-Formeln, die im Alltag sitzen.
In einer Schulung arbeiten wir mit Ihren eigenen Dateien, damit Sie das Gelernte am nächsten Tag nutzen. Oder wir bauen die Lösung direkt für Sie.
