Laden Sie die Beispielarbeitsmappe herunter
In diesem Tutorial wird gezeigt, wie Sie fehlende Zahlen in einem Bereich auflisten.
Übersprungene Nummern der Reihe nach auflisten
Um fehlende Zahlen in einer Liste zu finden, können wir die folgende Formel verwenden:
| 1 | =KLEIN(WENN(ZÄHLENWENN($B$3:$B$8,REIHE($1:$6))=0,REIHE($1:$6),""),REIHE(B1)) |
Anmerkungen:
- Dies ist eine Array-Formel! Wenn Sie Excel 2022 und früher verwenden, müssen Sie die Formel eingeben, indem Sie STRG + UMSCHALT + EINGABETASTE drücken. Sie wissen, dass die Formel richtig eingegeben wurde, wenn Sie die Formel in geschweiften Klammern sehen.
- Diese Formel funktioniert nur mit positiven ganzen Zahlen (Ganzzahlen).
Wie funktioniert die Formel?
Zuerst wird die ROW-Funktion verwendet, um ein Array von Zahlen zu generieren:
| 1 | {1;2;3;4;5;6} |
Tatsächlich könnten Sie diese Zahlenreihe sogar manuell eingeben:
| 1 | =KLEIN(WENN(ZÄHLENWENN($B$3:$B$8,{1;2;3;4;5;6})=0,{1;2;3;4;5;6},""), REIHE(B1)) |
Als nächstes zählt die COUNTIF-Funktion die Werte, „wenn“ sie mit einem im Array gefundenen Wert übereinstimmen.
Um dies zu veranschaulichen, konvertieren wir zuerst den Bereich $B$3:$B$8 in sein Array von Werten {3;2;0;6;0;5}:
| 1 | =KLEIN(WENN(ZÄHLENWENN({3;2;0;6;0;5},{1;2;3;4;5;6})=0,{1;2;3;4;5;6 },""),ZEILE(B1)) |
Als nächstes führen wir die COUNTIF-Operation aus:
| 1 | =KLEIN(WENN({0;1;1;0;1;1}=0,{1;2;3;4;5;6},""),REIHE(B1)) |
Wird kein Wert gefunden, wird 0 ausgegeben. Die IF-Funktion prüft, ob der Wert NICHT gefunden wurde und gibt in diesem Fall den Wert aus dem Array zurück, andernfalls gibt sie leer aus:
| 1 | =KLEIN({1;"";"";4;"";""},REIHE(B1)) |
Zuletzt gibt die Funktion SMALL den kleinsten fehlenden Wert in der ausgewählten Zelle zurück.
| 1 | =KLEIN({1;"";"";4;"";""},{1}) |
| 1 | ={1} |
Spitze:
Um die #NUM zu vermeiden! error, umschließen Sie Ihre Formel mit der IFERROR-Funktion:
| 1 | =WENNFEHLER(KLEIN(WENN(ZÄHLWENN(B3:B8,REIHE(1:6))=0,REIHE(1:6),""),REIHE(B1)),"") |
Listen Sie übersprungene Zahlen nacheinander in Google Tabellen auf
Alle oben erläuterten Beispiele funktionieren in Google-Tabellen genauso wie in Excel.
