Fehlende Zahlen in einer Liste finden 8

Lücken in einer Werte finden: Eine einfache und eine nicht ganz so einfache Variante!
 

Neulich erhielt ich eine Excel-Anfrage zu einem Thema, über das ich mir bisher noch nie Gedanken gemacht hatte. Da ich mir aber vorstellen kann, dass der eine oder die andere auch schon an einer ähnlichen Fragestellung verzweifelt ist, habe ich diesen Artikel geschrieben.

Worum es geht?

In einer langen Liste fortlaufender Nummern (sagen wir z.B. Rechnungsnummern) sind immer wieder Lücken enthalten. Das heißt, zwischendurch fehlen einfach Nummern.

Da die Liste jedoch wirklich seeehr lang ist und wir seeehr bequem sind, soll Excel uns bei der Suche nach den fehlenden Zahlen helfen.

Und so geht’s:

Wie fast immer in Excel kommen je nach konkreter Aufgabenstellung und den vorhandenen Rahmenbedingungen unterschiedliche Lösungswege in Betracht. Dazu habe ich wieder eine kleine Beispieltabelle mit fortlaufenden Rechnungsnummern vorbereitet:

Beispiel: Lückenhafte Rechnungsnummern

Beispiel: Lückenhafte Rechnungsnummern

Die Datei kannst Du Dir bei Bedarf hier herunterladen.

Bedingte Formatierung

Wenn es reicht, die Stellen optisch hervorzuheben, an denen eine Nummer fehlt, dann bietet sich dafür die bedingte Formatierung an. Dazu markieren wir alle Rechnungsnummern und legen eine neue Formatierungsregel an:

Neue Formatierungsregel anlegen

Neue Formatierungsregel anlegen

Wichtig: Ich gehe hier davon aus, dass die Werteliste aufsteigend sortiert ist.

Mit unserer Formatierungsregel prüfen wir einfach, ob die Zahl in der aktuellen Zelle um 1 größer ist, als die der vorhergehenden Zelle. Wenn nicht, soll die Zelle farbig hervorgehoben werden:

Formatierungsregel

Formatierungsregel

Markierung, wenn Nummern fehlen

Markierung, wenn Nummern fehlen

Wer es etwas dezenter möchte, kann auch einfach eine rote Linie an der Stelle ziehen lassen, an der eine Zahl fehlt. Die Regel bleibt gleich, lediglich die Formatierung ist anders:

Variante 2

Variante 2

Array-Formel

Will man jedoch genau wissen, welche Nummern in der Liste fehlen, kommt man um eine auf den ersten Blick etwas undurchschaubare Formel nicht herum. Schreiben wir also in die Spalte neben unseren Rechnungsnummern folgende Formel:

{=KKLEINSTE(WENN(ZÄHLENWENN($A$2:$A$31;ZEILE($100:$133))=0;ZEILE($100:$133));ZEILE(A2)-1)}

Wichtig:
Hierbei handelt es sich um eine sogenannte Array-Formel. Die geschweiften Klammern am Anfang und Ende dürfen dabei nicht von Hand eingetippt werden. Stattdessen muss die Eingabe mit der Tastenkombination Strg+Umschalt+Enter abgeschlossen werden.

Damit wird uns die erste fehlende Rechnungsnummer angezeigt:

Die Array-Funktion

Die Array-Funktion

Kopieren wir diese Formel jetzt bis ans Ende unserer Liste, erhalten wir sämtliche fehlenden Nummern. Nach der höchsten Ziffer wird dann nur noch der Fehlerwert “#ZAHL!” ausgegeben.

Die Liste der fehlenden Nummern

Die Liste der fehlenden Nummern

Array-Formeln sind immer etwas schwer zu verstehen, denn mit ihrer Hilfe werden viele einzelne Rechenschritte vorgenommen und nur die zusammengefassten Ergebnisse ausgegeben. Um diese Schritte etwas leichter nachzuvollziehen, splitten wir diese Array-Formel in ihre Bestandteile auf.

Es handelt sich also um eine etwas verschachtelte KKLEINSTE-Funktion. Damit wird der k-kleinste Wert aus einer Liste bestimmt, wobei k den jeweiligen gewünschten Rang angibt: Also z.B. den 2.-kleinsten, 3.-kleinsten usw. Wert.

Im folgenden Schaubild habe ich versucht, die einzelnen Elemente dieser Funktion etwas transparenter zu machen und ich hoffe, dich nicht zu sehr damit zu verwirren:

Zusammensetzung der Funktion

Zusammensetzung der Funktion

Damit es noch etwas deutlicher wird, habe ich in unserem Tabellenblatt die Array-Funktion auf die Spalten D:H aufgeteilt und dort jeweils nur ein einzelnes Element berechnet:

Aufsplittung der Array-Funktion

Aufsplittung der Array-Funktion

Die ZEILE-Funktion wird benötigt, um eine fortlaufende Nummer zu generieren. Da unsere Rechnungsnummern mit 100 beginnen, müssen wir die entsprechende Zeilennummer verwenden. Wäre unsere erste Rechnungsnummer z.B. 97345, dann lautete die entsprechende Funktion =ZEILE(97345:97345)

Teil 1: ZEILE-Funktion

Teil 1: ZEILE-Funktion

Die ZÄHLENWENN-Funktion soll herausfinden, ob in der Liste der Rechnungsnummer die jeweils berechnete Zeilennummer enthalten ist. Wenn ja, lautet der Wert 1, ansonsten 0 (Null):

Teil 2: ZÄHLENWENN-Funktion

Teil 2: ZÄHLENWENN-Funktion

Die Spalte “Dann-Wert” gibt im Fall von 0 die jeweilige Zeilennummer aus, welche gleichzeitig der fehlenden Rechnungsnummer entspricht:

Teil 3: WENN-Funktion

Teil 3: WENN-Funktion

Die Spalte “k-Wert” benötigen wir, um die fortlaufende Rangfolge zu bestimmen, die wir in der KKLEINSTE-Funktion benötigen. Auch dafür verwenden wir einfach die Zeilennummer der jeweiligen Rechnungsnummer. Da unsere Rechnungsnummern erst in Zeile 2 beginnen, müssen wir vom Ergebnis noch den Wert 1 abziehen:

Teil 4: Die Rangzahl

Teil 4: Die Rangzahl

Und zu guter Letzt ermittelt die KKLEINSTE-Funktion noch den jeweils Rang-kleinsten Wert:

Teil 5: Die KKLEINSTE-Funktion

Teil 5: Die KKLEINSTE-Funktion

Und so kommen wir auf fast wundersame Weise am Ende der Tabelle auf die gleichen Ergebnisse wie unsere ursprüngliche Array-Funktion:

Die Ergebnisse stimmen überein

Die Ergebnisse stimmen überein

Um die noch etwas unschönen #ZAHL!-Fehler zu beseitigen, schließen wir das ganze Konstrukt noch in eine WENNFEHLER-Formel ein. Auch hier wieder nicht vergessen, die Eingabe mit Strg+Umschalt+Enter abzuschließen:

Noch etwas Kosmetik

Noch etwas Kosmetik

Ich weiß, das war ein ziemlich harter Brocken und ich gratuliere, wenn du bis hierher durchgehalten hast. Du kannst stolz auf dich sein!
 

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Kommentar erstellen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

8 Gedanken zu “Fehlende Zahlen in einer Liste finden

  • Gerhard Pundt

    Hallo Martin,

    das ist in der Tat eine interessante Aufgabenstellung. Und du hast wieder gute Loesungen gefunden. Die erste Loesung mit der bedingten Formatierung finde ich vollkommen ausreichend, die Array-Losung ist natuerlich komfortabler.
    Danke fuer die Tipps.
    Viele Grueße
    Gerhard

    • Martin Weiß Beitragsautor

      Hallo Gerhard,

      vielen Dank für das nette Feedback. Und die besten Aufgabenstellungen sind die aus dem richtigen Leben.

      Schöne Grüße,
      Martin

    • Martin Weiß Beitragsautor

      Hallo Detlev,

      dankeschön, die Grafik war in der Tat ein ziemliches Gebastel 🙂 Umso schöner, wenn sie gefällt.

      Schöne Grüße,
      Martin

  • Christina Becker

    Hallo Martin!

    Vielen Dank für deine Lösung! Leider funktioniert sie für Rechnungen mit langen Nummern, wie 1 600 000, nicht.
    Das liegt vermutlich an der “Zeile” – Funktion (Da Excel nur 1.048.576 Zeilen hat)

    Könntest Du mir bitte einen Tipp geben, womit ich diese Formel ersetzten kann?

    Vielen Dank im Voraus!

    Beste Grüße
    Christina

    • Martin Weiß Beitragsautor

      Hallo Christina,

      ja, es liegt an der ZEILE-Funktion und damit an der von Dir genannten Beschränkung in Excel. Aber auch solche sehr langen Rechnungsnummern liegen üblicherweise in einem bestimmten Nummernkreis. Das heißt, es gibt einen fixen Anteil und einen fortlaufenden. Damit könnte man die Nummer aufteilen und den fixen Teil ignorieren. Und somit sollte die gezeigte Lösung auch wieder funktionieren.

      Schöne Grüße,
      Martin

  • Johannes Boye

    Moin Martin,

    danke für die Lösung. Meine Aufgabenstellung ist recht ähnlich aber doch abweichend: “meine” fortlaufende Nummerierung ist vorerst nicht aufsteigend sortiert (das lässt sich natürlich ändern) nd ist im Format “P00001” bis ca. “P14000”, also alphanumerischer Natur. Inwieweit muss ich die Array-Lösung anpassen?

    Danke für Deine Hilfe.
    Lg
    Johannes

    • Martin Weiß Beitragsautor

      Hallo Johannes,

      eine Möglichkeit wäre, das führende “P” abzutrennen, so dass es sich wieder ausschließlich um numerische Werte handelt. Oder die Array-Formel um das “P” erweitern.

      Also statt
      {=WENNFEHLER(KKLEINSTE(WENN(ZÄHLENWENN($A$2:$A$31;ZEILE($100:$133))=0;ZEILE($100:$133));ZEILE(A2)-1);””)}

      …heißt es dann:
      {=WENNFEHLER(“P”&KKLEINSTE(WENN(ZÄHLENWENN($A$2:$A$31;”P”&ZEILE($100:$133))=0;ZEILE($100:$133));ZEILE(A2)-1);””)}

      Schöne Grüße,
      Martin