Fehlende Zahlen in einer Liste finden 32

Artikelbild-146
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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

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

32 Gedanken zu “Fehlende Zahlen in einer Liste finden

  • Avatar-Foto
    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

    • Avatar-Foto
      Martin Weiß

      Hallo Gerhard,

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

      Schöne Grüße,
      Martin

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Michael Müller

    Hallo Martin,
    da glaubt man, fit in Excel zu sein und steht dann vor dem von Dir beschriebenen Problem. Und dann finde ich Deine Array-Formel und möchte Dir danken, mich wieder auf den Boden der Tatsachen geführt zu haben.
    Zwar habe ich mit solchen Formeln auch schon früher gearbeitet aber jetzt „auf dem Altenteil“ sind sie wohl in Vergessenheit geraten.

    Übrigens auch beeindruckend, wie Du das Thema beschrieben, abgebildet und dann auch noch gesplittet hast. Das nenne ich ein Tutorial!

    Herzlichen Dank also für die Hilfe.
    Michael

    • Avatar-Foto
      Martin Weiß

      Hallo Michael,

      vielen Dank für dieses schöne Feedback. Und ich kann Dich beruhigen: Auch bei mir gerät die eine oder andere Formel und Funktion im Laufe der Zeit in Vergessenheit. Wir werden alle nicht jünger..

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Noam Chomsky

    Super Arbeit, vielen Dank!
    Kann man das jetzt auch noch automatisieren? Also dass ich jederzeit neue Listen mit Nummern in die erste Spalte kopieren kann ohne, dass ich in der Formel per Hand den ersten und den Letzten Wert eintragen muss? Dann wäre ich am Ziel.
    VG
    Mike

    • Avatar-Foto
      Martin Weiß

      Hallo Noam,

      gern geschehen! Eine Lösung, wie man um die Anpassung der Formel herumkommt, habe ich leider auch nicht. Vielleicht hat ja ein anderer Leser eine gute Idee.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Michael

    Ich habe gerade ein anderes Problem:

    Ich möchte:

    060.07.2018
    061.07.2018
    062.07.2018
    usw.
    Wie kann ich das automatisieren?

    bei mir sieht das immer so aus:

    060.07.2018
    060.07.2019
    060.07.2020
    usw.

    Keine Ahnung……….mache es von Hand.

    • Avatar-Foto
      Martin Weiß

      Hallo Michael,

      Du könntest die fortlaufenden Nummern (60, 61, 62…) in einer Spalte eingeben und die gewünschte Ausgabe über eine Formel in der Spalte daneben berechnen. Beispielsweise so:
      Beispiel

      Jetzt kannst Du beide Spalten einfach mit der Maus über das kleine Ausfüllkästchen in der rechten unteren Zellecke nach unten ziehen und die Daten werden automatisch fortgeschrieben.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Uwe H.

    Toller Lösungsansatz – Auf solche Gedanken wäre ich nie gekommen. Ich bin begeistert und wende jetzt noch mehr EXCEL für meine Problemlösungen ein als zuvor.

    Gerne möchte ich in Kontakt bleiben und mehr erfahren, über weitere Problemlösungen und Anwendungen.

  • Avatar-Foto
    Heinz Helm

    Hallo Martin,
    durch Zufall bin ich auf Deine Seite gestossen – und habe seit vielen Jahren ein Problem. Kurz, mein Wissen stammt noch aus der Excel 2003 Zeit, aber hauptsächlich mit normalen Formeln und Listen. Vor Jahren erstellte ich eine spezielle Liste mit Matrix-Formeln und hab mit sehr Glück die Liste zum Laufen bekommen – aber mit einem Fehler, der mich schon seit Anfang ärgert. Am Ende jeder Liste erscheint immer die Fehlermeldung #ZAHL! in den einzelnen Zellen. Habe mich wieder mal nach langer Zeit an das Problem gewagt. Ich komme nicht weiter, den Fehler zu beseitigen.. Ich könnte Dir einzelne Screenshots senden. Kannst Du mir helfen?
    MFG
    Heinz

    • Avatar-Foto
      Martin Weiß

      Hallo Heinz,

      solche Meldungen lassen sich in der Regel über eine WENN-Abfrage in Kombination mit der ISTFEHLER-Funktion unterdrücken, in aktuellen Excel-Versionen noch einfacher mit der WENNFEHLER-Funktion. Allerdings kann ich nicht beantworten, ob es die ISTFEHLER-Funktion in Excel 2003 schon gab. Die Formel müsste dann sinngemäß so aussehen:

      =WENN(ISTFEHLER(deine_formel);““;deine_formel)
      Bzw.
      =WENNFEHLER(deine_formel;““)

      Damit wird im Fehlerfall einfach eine leere Zelle angezeigt. Vielleicht hilft das ja weiter.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Adrian Stöckli

    Hallo Martin

    Und wieder eine neue Formel entdeckt, die bei grossen Tabellen sehr nützlich sein kann!

    Nebenbei: man kann auch fehlende Datumswerten ermitteln.
    Dabei muss man in der Formel im Teil ZEILE($100:$133) die Zahlen mit der Zeilennummer für die zu prüfenden Daten einsetzen. Also für 01.01.2018:31.12.2019 heisst es ZEILE($43101.$43830).und die Formel dann auf Datum formatieren…

    Gruss
    Adrian

    • Avatar-Foto
      Martin Weiß

      Hallo Adrian,

      vielen Dank für die Anregung, das ist auch eine sehr clevere Idee!

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Andreas

    Super Formel hat mir sehr geholfen. Aber kann man die so Modifizieren das er nur unter einen Abstand von 25 Nummern mir die Fehlende Anzeigt?
    ZB 1.2.4.26. also fehlt nur die 3 und nicht noch 5.6.7.usw

    danke.

    • Avatar-Foto
      Martin Weiß

      Hallo Andreas,

      ich stehe irgendwie auf der Leitung, aber ich habe die Anforderung noch nicht verstanden…

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Andrea

    Hallo,
    ich habe eine Frage dazu.
    Ich habe 926 ID’s in meiner Mastertabelle. Täglich bekomme ich hierfür Daten geliefert. Es kommt aber vor, dass nicht für alles ID’s Daten geliefert werden. Ich möchte mir einen Überblick verschaffen, welche ID’s an einzelnen Tagen fehlen.
    Funktioniert dies auch wenn ich keine fortlaufenden Nummern habe?

    • Avatar-Foto
      Martin Weiß

      Hallo Andrea,

      wenn es keine fortlaufenden Nummern gibt, wie soll Excel dann erkennen, ob tatsächlich eine fehlt?

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Tom

    Hallo Martin,

    nach ewiger Suche und langem Probieren Deine Formel gefunden. Herzlichen Dank, das erspart mir einen Haufen Arbeit und macht die ganze Arbeit übersichtlicher. In einen Punkt komme ich jedoch mit der Formel nicht weiter, was aber extrem nützlich wäre.

    Für Tabellenblatt 1 kann ich sie nutzen. Aber gibt es auch einen Weg Daten aus einem zweiten Tabellenblatt und dritten Tabellenblatt mit in die Formelauswertung zu nehmen, für ein einzelnes Ergebnis? – Hintergrund: Ich habe fortlaufend nummerierte Datensätze, welche der Übersichtlichkeit halber nach Typ je auf ein Tabellenblatt (3 Blätter insg.) aufgeteilt sind.

    Ich habe bereits verschiedenes ausprobiert, aber ich komme da einfach nicht weiter. Knoten im Hirn … oder Excel mag mich nicht. 😉 Vielleicht kannst Du mir bei der Lösung helfen?
    Vielen Dank vorab.
    Mfg, Tom.

    • Avatar-Foto
      Martin Weiß

      Hallo Tom,

      ich fürchte, das wird mit einer Formellösung schwierig bis gar nicht umzusetzen sein. Mein Tipp: Alle Tabellen per Power Query zu einer gemeinsamen Tabelle zusammenfassen und dann entweder in dieser Tabelle per Formel auswerten oder es direkt in Power Query zu versuchen.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Silvia

    Hallo, ich habe genau so eine Tabelle mit Rechnungsnummern, bei mir werden aber alle Nummern markiert, nicht nur die fehlenden und ich komm nicht dahinter, wo mein Fehler liegen könnte…

    • Avatar-Foto
      Martin Weiß

      Hallo Silvia,

      ich vermute, du hast noch irgendeinen Fehler in der Formatierungsregel. Es kommt bei den bedingten Formatierungen immer ganz genau darauf an, wo sich die aktive Zelle befindet, wenn man eine neue Regel anlegt. Am besten löscht du die Regel nochmal und legst eine neue an. Achte dabei genau darauf, dass die verwendeten Zellbezüge auch genau zu deiner Tabelle und zur Position der aktiven Zelle passen. Im zweiten Screenshot oben siehst du zum Beispiel, dass meine aktive Zelle in A2 steht. Und darauf beziehen sich dann auch die Angaben in der Formatierungsregel.

      Und die Liste mit den Rechnungsnummern muss natürlich aufsteigend sortiert sein.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Richard

    Hallo Martin,

    vielen Dank für die Hilfe!
    Ich bin begeistert von der guten Erklärung!

    Hat mir eben sehr geholfen und deine Seite wurde direkt als Favorit gespeichert.

    Viele Grüße!

    • Avatar-Foto
      Martin Weiß

      Hallo Richard,

      dankeschön, das freut mich sehr. Dann hoffe ich, dass du zukünftig noch mehr Tipps hier findest.

      Schöne Grüße,
      Martin