Es gibt sie überall: Listen und Tabellen mit fortlaufenden Zahlen. Messwerte, Zeitreihen, Losnummern, Rechnungen, Personalnummern und vieles mehr. Und eigentlich sollten solche Reihen vollständig sein.
Eigentlich.
Aber manchmal fehlen halt zwischendrin einzelne Zahlen. Wie man solche fehlenden Werte aufspüren und auflisten kann, zeige ich in diesem Artikel.
Und so geht’s:
Beispieldatei herunterladen
Beispiel 1: Freie Sitzplätze
Folgendes Szenario: Du bist Vorstand eines kleinen Theaters und der Kartenverkauf für das nächste Stück läuft auf Hochtouren. Allerdings sind noch einige Sitzplätze frei und du hättest gerne eine Liste mit allen freien Platznummern.
Sitzplatznummern im Theater
Wenn du eine halbwegs aktuelle Excel-Version im Einsatz hast (Excel 2021, Excel 2024 oder Microsoft 365), dann ist diese Aufgabe mit ein paar dynamischen Array-Funktionen schnell erledigt.
Hast du noch eine Excel-Version 2019 oder älter im Einsatz? Auch kein Problem. Ich habe in diesem älteren Artikel andere Lösungsmöglichkeiten vorgestellt.
Bleiben wir aber bei den dynamischen Array-Funktionen. Eine mögliche Lösung könnte so aussehen:
=FILTER(SEQUENZ(100);ZÄHLENWENN(B10:K19;SEQUENZ(100))=0)
Freie Sitzplätze per Formel ermitteln
Was genau macht diese Formel?
Die SEQUENZ-Funktion erstellt einfach eine Liste mit fortlaufenden Zahlen von 1 bis 100, was in unserem Beispiel die einzelnen Sitzplatznummern repräsentiert.
SEQUENZ(100)
Die FILTER-Funktion filtert diese Liste dann. Dabei kommt als Filterkriterium die ZÄHLENWENN-Funktion zum Einsatz:
ZÄHLENWENN(B10:K19;SEQUENZ(100))
ZÄHLENWENN liefert die Basis für die FILTER-Funktion
Die Funktion nimmt den Bereich mit den vergebenen Sitzplätzen (B10:K19) und zählt, wie oft jeder einzelne Wert in der Liste von 1 bis 100 enthalten ist. Damit erhält man eine Liste nur mit Einsen und Nullen.
Und diese Liste wird in der FILTER-Funktion auf die 0-Werte gefiltet und liefert somit alle noch nicht verkauften Sitzplatznummern:
=FILTER(SEQUENZ(100);ZÄHLENWENN(B10:K19;SEQUENZ(100))=0)
Online-Kurs „Dynamische Array-Funktionen“
Beispiel 2: Fehlende Rechnungsnummern
In diesem Szenario soll geprüft werden, welche Rechnungsnummern in einer fortlaufenden Liste noch fehlen. Zum Beispiel weil die Buchhaltung dafür noch keinen Beleg vorliegen hat.
Dafür habe ich eine kleine formatierte („intelligente“) Tabelle mit dem Namen „tRechnungen“ vorbereitet, die alle vorhandenen Rechnungsnummern aufsteigend sortiert enthält:
Unvollständige Liste mit Rechnungsnummern
Alle fehlenden Nummern sollen in einer separaten Liste ausgegeben werden. Im Grunde also fast das gleiche Szenario wie in Beispiel 1, nur mit zwei wesentlichen Unterschieden:
- es gibt keine fixe Anzahl von Rechnungsnummern
- die erste und auch die letzte Nummer kann sich ändern
Ich werde dir im Folgenden zwei verschiedene Lösungsvarianten vorstellen.
Variante 1
Unsere Formellösung wird grundsätzlich ähnlich wie im ersten Beispiel sein, muss aber etwas flexibler angelegt werden.
=FILTER(SEQUENZ(...);ZÄHLENWENN(...)=0)
Die SEQUENZ-Funktion soll eine fortlaufende Liste aller Zahlen erstellen, die zwischen der kleinsten und der größten Rechnungsnummer liegen. Die allgemeine Syntax sieht ja wie folgt aus:
=SEQUENZ(Zeilen;Spalten;Anfang)
Für die Anzahl der Zeilen wird die Differenz von größter und kleinster Rechnungsnummer berechnet und zum Schluss noch der Wert 1 addiert:
MAX(tRechnungen[Re-Nr.])-MIN(tRechnungen[Re-Nr.])+1
Als Anfangswert der Liste wird die kleinste Rechnungsnummer benötigt:
MIN(tRechnungen[Re-Nr.])
Damit sieht die vollständige SEQUENZ-Funktion folgendermaßen aus:
SEQUENZ(MAX(tRechnungen[Re-Nr.])-MIN(tRechnungen[Re-Nr.])+1;;MIN(tRechnungen[Re-Nr.]))
Bei der ZÄHLENWENN-Funktion ist es dann ähnlich. Als erstes Argument wird die Tabelle mit den Rechnungsnummern benötigt, das zweite Argument ist dann wieder die SEQUENZ-Funktion, wie wir gerade erstellt haben:
ZÄHLENWENN(tRechnungen[Re-Nr.];SEQUENZ(MAX(tRechnungen[Re-Nr.])-MIN(tRechnungen[Re-Nr.])+1;;MIN(tRechnungen[Re-Nr.])))
Und somit können wir die vollständige FILTER-Formel zusammensetzen:
=FILTER(SEQUENZ(MAX(tRechnungen[Re-Nr.])-MIN(tRechnungen[Re-Nr.])+1;;MIN(tRechnungen[Re-Nr.]));ZÄHLENWENN(tRechnungen[Re-Nr.];SEQUENZ(MAX(tRechnungen[Re-Nr.])-MIN(tRechnungen[Re-Nr.])+1;;MIN(tRechnungen[Re-Nr.])))=0)
Die FILTER-Funktion wird deutlich länger und unübersichtlicher
Ja, das sieht nach einem ziemlich langen Formelmonster aus. Aber man darf dabei nicht vergessen, dass die Schreibweise im Zusammenhang mit intelligenten Tabellen meistens deutlich länger ist – dafür sind die Bezüge aber leichter verständlich (Stichwort „strukturierte Verweise„).
Außerdem kommt in der Formel die SEQUENZ-Funktion zweimal vor, was das Ganze nicht gerade übersichtlicher macht. Daher zeige ich dir nun eine andere Lösungsmöglichkeit.
Variante 2
Immer wenn bestimmte Teile in einer Formel mehrmals vorkommen, sollte man über die LET-Funktion nachdenken.
Mit dieser Funktion kann man Berechnungsergebnissen einen Namen zuweisen und mit diesen Namen dann innerhalb der Formel weiterarbeiten. Wie könnte das in unserem Beispiel mit den Rechnungsnummern aussehen?
=LET(
liste;tRechnungen[Re-Nr.];
soll;SEQUENZ(MAX(liste)-MIN(liste)+1;;MIN(liste));
FILTER(soll;ZÄHLENWENN(liste;soll)=0)
)
Die LET-Lösung ist deutlich übersichtlicher
Ich definiere den Namen „liste“ und weise diesem Namen die Spalte mit den Rechnungsnummern zu.
Danach wird ein zweiter Name „soll“ festgelegt, welcher mit Hilfe der SEQUENZ-Funktion die Soll-Liste aller Rechnungsnummern bestimmt (die Namen sind dabei völlig willkürlich gewählt).
Aber wie man sieht, verwende ich innerhalb der SEQUENZ-Funktion bereits den zuvor festgelegten Namen „liste“. Das macht die Formel schon deutlich lesbarer:
soll;SEQUENZ(MAX(liste)-MIN(liste)+1;;MIN(liste));
Als letztes Argument wird dann mit der FILTER-Funktion die eigentliche Ausgabeliste erzeugt, wobei auch hier wieder die zuvor definierten Namen „liste“ und „soll“ zum Einsatz kommen:
FILTER(soll;ZÄHLENWENN(liste;soll)=0)
Man kann in der LET-Funktion beliebig viele solcher Namens-/Werte-Paare definieren (genau genommen bis zu 126 Paare). Das letzte Argument ist dann immer die finale Berechnung oder Ausgabe.
Fazit
Die beiden vorgestellten Varianten zeigen: Mit den dynamischen Array-Funktionen ist es gar nicht so schwer, fehlende Werte in einer Liste zu identifizieren.
Wenn man die beiden Formeln aus Variante 1 und 2 direkt vergleicht, dann wird der Nutzen von LET noch sehr viel deutlicher.
Vergleich der beiden Varianten
Der FILTER-Teil in der LET-Funktion enthält die eigentlich maßgebliche Berechnung, die auch völlig identisch mit der Variante 1 ist. Nur dass davor eben noch 2 Namen definiert wurden, um die Formel einfacher und besser lesbar zu machen.
Und wer noch keine dynamischen Array-Funktionen nutzen kann, für den gibt es noch alternative Lösungen.
Wie schaut’s aus:
Hast du andere konkrete Anwendungsfälle aus deiner Praxis im Kopf? Dann lass es uns in den Kommentaren wissen!
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,
ein tolles und nützliches Beispiel, welches sich prima auf ähnliche Fälle übertragen lässt.
Vielen Dank insgesamt für deine tollen Beiträge und Hilfen für die alltägliche Arbeit.