In einer Spalte stehen Einträge mit Lücken dazwischen, etwa weil nicht jede Zeile ausgefüllt ist. Für eine Auswertung, eine Auswahlliste oder ein Diagramm brauchen Sie dieselben Einträge lückenlos untereinander. Eine Formel erledigt das und aktualisiert sich bei jeder Änderung.
Die Kurzlösung
Stehen die Daten in B5:B25, schreiben Sie in Excel für Microsoft 365 und Excel 2021 in D5:
=FILTER(B5:B25;B5:B25<>"";"")
Excel gibt alle Einträge ohne Lücken ab D5 nach unten aus. Kommen neue Einträge dazu, wächst die Liste automatisch mit. Das dritte Argument "" sorgt dafür, dass bei einer leeren Quelle nichts erscheint statt des Fehlers #KALK!.
Beispiel
| Zeile | B (Quelle) | D (Ergebnis) |
|---|---|---|
| 5 | Anna | Anna |
| 6 | Ben | |
| 7 | Ben | Clara |
| 8 | David | |
| 9 | ||
| 10 | Clara | |
| 11 | ||
| 12 | David |
Für ältere Excel-Versionen
Ohne FILTER hilft AGGREGAT (ab Excel 2010). Schreiben Sie in D5 diese Formel und kopieren Sie sie so weit nach unten, wie die Quelle lang ist:
=WENNFEHLER(INDEX($B$5:$B$25;AGGREGAT(15;6;(ZEILE($B$5:$B$25)-ZEILE($B$5)+1)/($B$5:$B$25<>"");ZEILEN($D$5:D5)));"")
Die Formel braucht keine Eingabe mit Strg+Umschalt+Eingabe. So arbeitet sie:
(ZEILE(...)-ZEILE($B$5)+1)nummeriert die Quellzeilen mit 1, 2, 3 …- Die Division durch
($B$5:$B$25<>"")lässt die Nummern gefüllter Zellen stehen und macht aus leeren Zellen den Fehler #DIV/0!. AGGREGAT(15;6;…;k)ist die Funktion KKLEINSTE (15), die Fehlerwerte ignoriert (6). Sie liefert die k-kleinste Nummer.ZEILEN($D$5:D5)zählt beim Kopieren mit: 1 in D5, 2 in D6 und so weiter.- INDEX holt den Eintrag, WENNFEHLER lässt die Zellen unterhalb der Liste leer.
Varianten
| Ziel | Formel (Excel 365) |
|---|---|
| ohne Leerzellen | =FILTER(B5:B25;B5:B25<>"";"") |
| ohne Leerzellen und ohne Nullen | =FILTER(B5:B25;(B5:B25<>"")*(B5:B25<>0);"") |
| ohne Leerzellen, jeder Eintrag einmal, sortiert | =SORTIEREN(EINDEUTIG(FILTER(B5:B25;B5:B25<>"";""))) |
| Anzahl der Einträge | =SUMMENPRODUKT(--(B5:B25<>"")) |
Typische Fehler
- Formeln, die „“ liefern: ANZAHL2 zählt auch Zellen mit einer Formel wie
="". Ältere Lösungen, die die Länge der Liste mit ANZAHL2 begrenzen, zeigen dann am Ende den Fehler #ZAHL!. Die Formeln oben prüfen mit<>""und behandeln solche Zellen wie leere. - #ÜBERLAUF! bei FILTER: Unterhalb von D5 stehen bereits Werte. FILTER braucht freien Platz für die ganze Ergebnisliste. Leeren Sie die Zellen darunter.
- Bezüge beim Kopieren: In der AGGREGAT-Formel müssen die Quellbereiche fest sein (
$B$5:$B$25), nur das zweite D inZEILEN($D$5:D5)darf mitlaufen. - Zahlen 0 erscheinen: Eine 0 ist ein Eintrag und wird mit aufgelistet. Soll sie fehlen, nehmen Sie die Variante ohne Nullen.
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.
