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
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
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
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
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
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
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
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
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
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
Die Spalte „Dann-Wert“ gibt im Fall von 0 die jeweilige Zeilennummer aus, welche gleichzeitig der fehlenden Rechnungsnummer entspricht:
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
Und zu guter Letzt ermittelt die KKLEINSTE-Funktion noch den jeweils Rang-kleinsten Wert:
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
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
Ich weiß, das war ein ziemlich harter Brocken und ich gratuliere, wenn du bis hierher durchgehalten hast. Du kannst stolz auf dich sein!
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.
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
Hallo Gerhard,
vielen Dank für das nette Feedback. Und die besten Aufgabenstellungen sind die aus dem richtigen Leben.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für die ausführliche Erläuterung mit der guten Grafik! So etwas macht man nicht nebenbei.
Weiter so!
Detlev
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
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
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
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
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
Pingback: Die Summe der Top-3-Werte | Der Tabellen-Experte
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
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
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
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
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.
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:
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
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.
Freut mich zu hören!
Schöne Grüße,
Martin
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
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
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
Hallo Adrian,
vielen Dank für die Anregung, das ist auch eine sehr clevere Idee!
Schöne Grüße,
Martin
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.
Hallo Andreas,
ich stehe irgendwie auf der Leitung, aber ich habe die Anforderung noch nicht verstanden…
Schöne Grüße,
Martin
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?
Hallo Andrea,
wenn es keine fortlaufenden Nummern gibt, wie soll Excel dann erkennen, ob tatsächlich eine fehlt?
Schöne Grüße,
Martin
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.
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
Ehrenmann, sehr korrekt!
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…
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
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!
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
Hallo Martin,
fast gleiche Fragestellung, allerdings sind es bei meinem Problem Schlüssel an einem großen Wandbrett mit gut 500 Plätzen.
Da viele dieser Schlüssel ständig wechseln, sind „willkürlich“ viele Nr.-Felder frei und ich muss ständig umständlich die freien Nr. austesten oder am Brett nach freien ÜPlätzen schauen. Dummerweise kiege ich die Schlüssel vorne am Empfang ausgehändigt, muss die sofort mit einer „freien“ Nr. beschriften und kann später erst an die Wand gehen.
Und:
Die Tabelle ist leider nicht einfach nach Nr. sortierbar, da sie zuerst einmal nach Alphabet aufgebaut und dann, je nach Buchstaben mit Nr. versehen wurde (und jetzt kommt noch die hohe Fluktuationsrate/Neuvergabe der Nr. hinzu).
Die Tabelle einfach umstellen geht auch nicht, da verschiedene Abteilungen in weiteren Spalten noch viele Datensätze einfügen.
Ich versuche, die Tabelle einmal grob darzustellen es als Beispieltabelle zu erklären (Bild einfügen klappt leider nicht einfügen):
Zeilenname: Bootsname B C D F G
Buchstabe A
Eigner … ….da blabla Bemerkungen
22 Anna Müller ja …. …
(Platzhalter) Ante Gretel Nein
157 Ara Schulz ja … …
….
Buchstabe B
175 Berta Krause ja …………………
398 Bunter Fisch Hentze ja ………..
…
Buchstabe T
498 Talas Hotte ………….
(Platzhalter) Tata Jan ………….
11 Teilchenbeschleuniger Schulte …………..
3 Twister Meier ………..
……
usw.
Die Doppelten Ziffern bekomme ich schon als Warnung angezeigt, trotzdem ist es müßig, freie Ziffern durchzuprobieren.
Wie bekomme ich diese „freien Ziffern“ zwischen z.B. Zeile 3-500 jetzt separat angezeigt, um diese dann leicher zu belegen?
Danke schon mal für Deine Hilfe.
Gruß
Lars
Ohje, das Kommentarfeld hat leider die Tabellenformatierung zerlegt…
Hallo Lars,
tut mir leid, die Kommentarfunktion lässt leider keine vernünftige Darstellung von Tabellen zu. Daher fällt es mir gerade etwas schwer, das Muster und die Anforderungen zu erkennen. Du kannst mir aber deine Beispieldatei an info@tabellenexperte.de schicken. Dann werde ich gerne mal einen Blick darauf werfen.
Schöne Grüße,
Martin
Moin Martin,
ja, hab ich ja auch schon gemerkt. 🙂
Mach ich wie empfohlen.
Danke schon mal vorab.
Beste Grüße
Lars