Excel-TippFehlende Zahlen in einer Liste ermitteln
Mit Power Query lassen sich fehlende Werte in einer Liste schnell und einfach ermitteln. Diese Methode kann in vielen Szenarien eingesetzt werden. Zum Beispiel, um fehlende Rechnungsnummern oder unvollständige Datensätze zu identifizieren.
Power Query spart dabei viel manuelle Arbeit und stellt sicher, dass keine Werte übersehen werden.
Die Ausgangsdaten: eine lange Liste mit Kundennummern
Angenommen, Sie haben eine Tabelle mit einer Spalte für die Kundennummer. Die Liste ist aber nicht vollständig, da einige Nummern in der fortlaufenden Nummerierung fehlen.
Hinweis: Zur Anschaulichkeit zeigt die folgende Abbildung Nummern von 1 bis 10. Das folgende Verfahren hat seine Stärke besonders dann, wenn die Liste mit den Nummern besonders lang ist.
In der Beispielliste fehlen die Nummern 4 und 7. Im anschließenden, nicht sichtbaren Teil der Liste fehlen noch weitere Kundennummern. Es sollen nun die fehlenden Kundennummern ermittelt und an eine andere Stelle in der Arbeitsmappe ausgegeben werden.
Dafür nutzen Sie die Funktionen von Power Query.
Liste der Kundennummern in Power Query importieren
Laden Sie die Tabelle mit den Kundennummern in Power Query. Markieren Sie hierzu eine Zelle in der Liste und aktivieren Sie im Menüband die Befehlsfolge Registerkarte Daten > Befehlsgruppe Daten abrufen und transformieren > Befehl Aus Tabelle.
Da es sich bei der Kundenliste um einen normalen Bereich handelt, wird der Bereich erst in eine intelligente Tabelle umgewandelt, damit die Daten in Power Query geladen werden können.
Es öffnet sich das Dialogfeld Tabelle erstellen. Der Bereich wird richtig ausgewählt und Excel erkennt, dass die intelligente Tabelle eine Überschriftenzeile hat: Das Kontrollkästchen Tabelle hat Überschriften ist aktiviert.
Würde die Liste keine Überschriftenzeile besitzen, dann muss das Kontrollkästchen deaktiviert sein.
Bestätigen Sie die Einstellungen, indem Sie das Dialogfeld durch Klick auf OK schließen.
Die Kundenliste wird daraufhin in den Power-Query-Editor geladen. Excel erkennt das Zahlenformat automatisch und weist den Kundennummern den Datentyp Ganze Zahl zu. Dieses Datenformat passt für die Aufgabenstellung.
Liste mit allen Kundennummern in Power Query erzeugen
Um die fehlenden Werte zu ermitteln, benötigen Sie zunächst eine vollständige Liste aller möglichen Kundennummern.
Aktivieren Sie im Menüband von Power Query die Befehlsfolge Start > Neue Quelle > Andere Quellen > Leere Abfrage.
Es öffnet sich eine leere Abfrage. Erstelle Sie jetzt eine Liste aller Kundennummern, indem Sie in der Bearbeitungsleiste den folgenden M-Code erfassen und anschließend die Enter-Taste drücken:
= List.Numbers(1, 1000)
Mit der Formel = List.Numbers(1, 1000) haben Sie eine Liste mit den Zahlen (Nummern) von 1 bis 1000 erzeugt.
Klicken Sie jetzt im Menüband auf den Befehl Zu Tabelle konvertieren, um die Liste in eine Tabelle umzuwandeln.
Es öffnet sich das Dialogfeld Zu Tabelle. Da Sie nur eine Aufzählung der Zahlen von 1 bis 1000 haben, lassen Sie die Einstellungen unverändert und schließen das Dialogfeld durch Klick auf die Schaltfläche OK.
Die Liste wird daraufhin in eine Tabelle umgewandelt. Die Spalte bekommt automatisch die Spaltenüberschrift Column1 zugewiesen.
Aus Gründen der Übersichtlichkeit sollten Sie die Spalte sinnvoll umbenennen. Doppelklicken Sie mit der linken Maustaste auf die Spaltenüberschrift und benennen Sie die Spalte in Kundennummer um.
Wenn Ihre Kundennummern oder Nummernlisten, die Sie überprüfen wollen, ein besonderes Format haben, müssen Sie diese vollständige Liste auf andere Weise erzeugen oder in Power Query importieren.
Beispiel: Die Liste der vollständigen Kundennummern ist: KU-0001, KU-0002 … KU-1000.
Nachdem Sie in Power Query – wie oben beschrieben – aus der Liste mit den Zahlen 1 bis 1000 eine Tabelle erzeugt haben, fügen Sie diese Funktion ein:
= Table.AddColumn(#"In Tabelle konvertiert", "Kundennummer", each Text.Combine({"KU-", Text.PadStart(Text.From([Column1], "de-DE"), 4, "0")}), type text)
Sie erhalten eine Liste mit Kundennummern im Format des genannten Beispiels.
Auch mit solchen Kundennummern im Textformat können Sie eine vollständige und eine unvollständige Liste miteinander vergleichen und die fehlenden Werte der unvollständigen Liste alle eindeutig ermitteln.
Nach fehlenden Nummern in der Liste suchen
Zunächst verbinden Sie die vollständige Liste der Kundennummern mit der lückenhaften Liste, die Sie zuvor in Power Query importiert haben.
Lassen Sie die Abfrage1 (vollständige Kundennummernliste) markiert und aktivieren Sie im Menüband die Befehlsfolge Start > Kombinieren > Abfragen zusammenführen.
Es öffnet sich das Dialogfeld Zusammenführen.
Als erste Tabelle ist die Abfrage1 ausgewählt. Wählen Sie nun als zweite Tabelle die Tabelle1 aus. Dies ist die Abfrage mit den lückenhaften Kundennummern.
Markieren Sie bei beiden Tabellen die Spalte Kundennummer, indem Sie mit der linken Maustaste auf den jeweiligen Namen klicken. Dies ist die Spalte, mit der die beiden Tabellen gematcht werden können.
Bei der Join-Art wählen Sie bitte den Eintrag Linker Anti-Join (Zeilen nur in erster) aus.
Mit dieser Join-Art, werden nur die Einträge in der ersten Tabelle ausgegeben, die sich nicht in der zweiten Tabelle befinden. Dies entspricht den fehlenden Kundennummern in der Ausgangsliste.
Bestätigen Sie die Einstellung, indem Sie das Dialogfeld durch Klick auf OK schließen.
Als Ergebnis erhalten Sie eine Liste, die die fehlenden Kundennummern auflistet.
Da Sie die Abfrage mit der ersten Abfrage zusammengeführt haben, erhalten Sie eine weitere Spalte mit den Daten (Table) der anderen Tabelle.
Diese Spalte wird nicht benötigt. Sie können diese löschen. Klicken Sie mit der rechten Maustaste auf den Spaltennamen Tabelle1 und wählen Sie im Menüband den Befehl Entfernen aus.
Sie können die Aufstellung mit den fehlenden Kundennummern jetzt in Ihr Excel-Arbeitsblatt übertragen, indem Sie im Menüband die Befehlsfolge Start > Schließen & laden > Schließen & laden aktivieren.
Die Liste wird daraufhin als intelligente Tabelle in Excel ausgegeben.
