Xtract: Kunden sollen aufgrund der PLZ einem betreuenden Mitarbeiter zugeordnet werden. Die PLZ liegen dabei sehr selten einzeln sondern meist in Nummernblöcken „von … bis” vor. Per Power Query werden die PLZ-Blöcke in die einzelnen PLZ gesplittet.
Wissensstand: Level 2 ⇒ Solides Basiswissen in Excel, etwas Erfahrung in PQ
Einen Kunden aufgrund Postleitzahlbereich dem zugehörigen Mitarbeiter zuordnen
In den unterschiedlichen Excel-Foren wird immer wieder einmal die Frage gestellt, wie ein Kunde aufgrund der Postleitzahl dem entsprechenden Mitarbeiter zugeordnet werden kann. Das gleiche Prinzip kann auch angewendet werden, wenn beispielsweise Kommunen einem Landkreis oder Bundesland zugeordnet werden sollen. Der Schlüsselbegriff ist dabei immer die Postleitzahl, die im gewissen Rahmen eindeutig ist. „Im gewissen Rahmen“, weil in manchen Fällen die PLZ ja auch für mehrere Orte mit unterschiedlichen Namen vergeben werden kann. Allerdings gilt, dass eine bestimmte PLZ immer einem definierten Mitarbeiter zugeordnet wird.
In dieser Musterdatei sind mehrere hundert Kunden erfasst. Zu jedem Kunden gibt es naturgemäß eine eindeutige Postleitzahl. In einer weiteren Tabelle sind die PLZ-Bereiche (die auch schon einmal aus einer einzelnen PLZ bestehen können) erfasst und in der Nebenspalte wird der entsprechende Mitarbeiter, welcher für diesen PLZ-Bereich zuständig ist, ausgewiesen. Im Tabellenblatt Kundenzuweisung soll nun jedem Kunden der entsprechende Mitarbeiter zugeordnet werden.
Ich räume ein, dass die Zuordnung keiner wirklichen Logik entspricht. Ich habe die Postleitzahl bzw. Postleitzahlbereiche nach dem Zufallsprinzip sortiert und die Spalten dann nebeneinander angeordnet. In der vor-PQ-Zeit wurde solch ein Problem gewiss mit einer Datenbank oder über den Weg der Programmierung mit VBA gelöst. Dank Power Query ist das aber recht gut ohne Programmierung mit einigen Funktionen der Sprache M lösbar.
Postleitzahlen-Bereiche „auseinzeln”
Aktivieren Sie zu Beginn das Arbeitsblatt PLZ Bereiche. Und da diese Auflistung noch nicht als Intelligente Tabelle vorliegt, formatieren sie die Daten entsprechend. Idealerweise vergeben sie schon hier einen „sprechenden“ Namen, beispielsweise PLZ_Bereiche. Der Vorteil dabei ist, dass dieser Tabellenname nach dem Import in den Power Query-Editor automatisch auch als Name für die Abfrage verwendet wird. Anschließend importieren sie die Tabelle. ¿ Der Daten-Bereich stellt sich nun so dar:
Die Abfrage direkt nach dem Import
Zwei Dinge werden (sollten) Ihnen auffallen: In der Spalte Name ist die numerische Bezeichnung des Mitarbeiters nicht sichtbar und in den beiden PLZ-Spalten sind die ersten 16 Zeilen nur vierstellig, also ohne die führende Null. Das mit dem Namen ist recht simpel zu organisieren: Ziehen Sie die Spalte etwas breiter und schon ist die Nummer des Mitarbeiters klar erkennbar. Bei den Spalten mit den Postleitzahlen sollte Ihnen klar sein, dass Power Query hier eine Typumwandlung vorgenommen hat was Sie auch erkennen, wenn Sie eine oder beide Spalten markieren und dann einen Blick in das Menüband, Gruppe Transformieren auf den Eintrag Datentyp: werfen. Da die führende Null wegen der 5‑stelligen PLZ in Deutschland ja zwingend erforderlich ist, markieren Sie jetzt diese beiden Spalten und ändern Sie den Datentyp auf Text.
Und wie Sie sehen, sehen Sie nichts. Zu mindestens was eine Änderung der Darstellung betrifft. Ich gebe zu, dass die Ursachenforschung auf der einen Seite gar nicht einmal so leicht, die eigentliche Ursache jedoch prinzipiell ganz banal ist. Ich schlage ihnen vor, bereits an dieser Stelle einmal auf das Erweitern-Symbol bei Schließen & laden zu Klicken, Schließen & laden in… auszuwählen und dann Nur Verbindung erstellen zu markieren. Die Abfrage wird geschlossen und sie befinden sich wieder im Excel-Arbeitsblatt. Klicken Sie nun in beispielsweise A2 und schauen Sie sich dann den Wert in der Eingabezeile an… Und sie werden erkennen, dass hier bei der Dateneingabe einfach gepfuscht worden ist (ja ich habe es extra so gemacht). 😉 Damit wäre die Ursache geklärt.
Prinzipiell gibt es nun natürlich 2 Wege, dieses Problem zu beseitigen. Sie können die Excel-Tabelle anpassen, damit dort auch beim Zeilenformat Text eine führende Null gegeben ist oder sie lassen Power Query die Arbeit erledigen. Auch wenn es den Anschein hat dass es in diesem Fall deutlich einfacher wäre, den Weg über Plain Excel zu beschreiten, sollten Sie nicht nur des Lerneffekts wegen das Problem per PQ lösen. Es ist prinzipiell sogar erforderlich, wie Sie auch gleich erkennen werden.
Löschen Sie im rechten Seitenfenster des PQ-Editors bei Angewendete Schritte die zweite, also die letzte Zeile. Sie erkennen, dass jetzt nur noch der Eintrag Quelle existiert. Wenn Sie nun den Datentyp der einzelnen Spalten überprüfen werden sie erkennen, dass es durchgehend Beliebig ist.
Im nächsten Schritt sollen die PLZ-Bereiche so erweitert werden, dass jede der Postleitzahlen im angegebenen Bereich in einer einzelnen Zeile aufgeführt, also expandiert wird. Und natürlich soll neben jeder einzelnen PLZ auch der Name des Mitarbeiters bzw. der Mitarbeiterin angezeigt werden. Um das zu erreichen wechseln Sie zum Menü Spalte hinzufügen und wählen dort im Menüband das Symbol Benutzerdefinierte Spalte. Im Dialog geben Sie bei Neuer Spaltenname beispielsweise PLZ ein und in das große Feld Benutzerdefinierte Spaltenformel diese Formel:
= {[PLZ von]..[PLZ bis]}
Die Spaltenüberschriften übernehmen Sie dabei am besten per Doppelklick auf die entsprechende Position im rechten Kasten.
Nach einem OK werden Sie sehen, dass Power Query eine neue Spalte generiert hat. In jeder einzelnen Zeile dieser Spalte steht der Eintrag List, der engliche Begriff für Liste. Und werfen Sie nun einmal einen Blick in die Statuszeile unten links. Dort steht, dass diese Abfrage 4 Spalten und 493 Zeilen hat. Erweitern Sie die einzelnen Zellen durch einen Klick auf den Doppelpfeil 
in der Überschrift und anschließend Auf neue Zeilen ausweiten. Sie werden rasch erkennen, dass nun für jede einzelne PLZ des entsprechenden Bereichs eine einzelne Zeile erstellt worden ist und alle anderen Spalten-Einträge per Duplikat mit übernommen wurden. Und ein Blick in die Statuszeile ist der endgültige Beweis…
Löschen Sie nun noch die beiden Spalten PLZ von und PLZ bis und diese Query ist im gewünschten Status. Darum Schließen & laden und Sie gelangen wieder in das Excel-Arbeitsbaltt. Im rechten Seitenfenster des Arbeitsblatts erkennen Sie, dass 1 Abfrage existiert und unterhalb des Namens sehen Sie auch, dass die stolze Anzahl von 98.767 Zeilen geladen worden ist.
Erklärung zu {..}
Eine kurze Erklärung zu der Formel bin ich Ihnen gewiss noch schuldig. Die beiden Überschriften in den eckigen Klammern sollten insbesondere im Hinblick auf die „sprechenden” Überschriften selbsterklärend sein. Die zwei direkt aufeinanderfolgenden Punkte .. sind hier das Synonym für „bis”. Durch die einfassenden geschweiften Klammern wird aus dem Auswertungsergebnis eine Liste generiert. Das wird ja auch dadurch deutlich, dass als Ergebnis in jedem Feld der Begriff List steht. Damit das Ganze aber funktioniert, müssen die Felder der Zeile einen numerischen Wert enthalten, welcher von Startwert bis Endwert jeweils um den Wert 1 inkrementiert wird. Ein Datenformat wie beispielsweise Text würde naturgemäß zu einem Fehler führen.
Kunden den Mitarbeitern zuweisen
Wechseln Sie nun zum Arbeitsblatt Kundenzuweisung. Wie schon aus den Spaltennamen ersichtlich geht es darum, jedem Kunden anhand der Postleitzahl den zuständigen Mitarbeiter zuzuweisen. Und da jetzt ja schon eine Liste (Abfrage) aller denkbaren Postleitzahlen existiert, könnten sie theoretischer Weise auch mit einem SVERWEIS() die Spalte C füllen. Aber wenn sie schon Power Query nutzen, dann sollten Sie diese Funktionalität auch hier einsetzen. Übrigens würde das unter diesen Umständen sowieso nicht funktionieren, da die Abfrage ja nur als Verbindung und nicht als physische Tabell in einem Arbeitsblatt existiert. Also Klicken Sie irgendwo in die Spalten A|B und importieren Sie diese Tabelle in den Power Query-Editor.
Das Grundprinzip bzw. eine detaillierte Beschreibung des PQ-Ersatzes für den SVERWEIS() können Sie hier in unserem Blog oder auch hier nachlesen. Darum werde ich an dieser Stelle die einzelnen Schritte nur kurz beschreiben. Nach dem Import sollte Ihnen aufgefallen sein, dass die Spalte PLZ automatisch in den Datentyp: Ganze Zahl umgewandelt worden ist. Wäre das nicht der Fall gewesen müssten sie diesen Schritt von Hand durchführen, denn wie auch beim SVERWEIS() in Excel können nicht Zahlen und Texte „sauber” verglichen werden.
Erweitern Sie im Menü Start Gruppe Kombinieren das Symbol Abfragen zusammenführen und wählen Sie im DropDown Abfragen zusammenführen. Wählen Sie dann im kleinen, mittleren Textfeld die Abfrage PLZ_Bereiche und im unteren großen Kasten werden sofort die ersten Datensätze dieser Abfrage angezeigt:
Zusammenfügen der Daten, um die Mitarbeiter zu verlinken
Markieren Sie in beiden Abfragen (im oberen und unteren Kasten) die Spalte PLZ, belassen sie Join-Art bei der vorgegebenen Einstellung und OK. Automatisch wurde eine neue Spalte PLZ_Bereiche erstellt und der Inhalt jeder einzelnen Zelle der 970 Zeilen ist Table. Erweitern Sie die Überschrift durch einen Klick auf den Doppelpfeil und entfernen Sie im Dialog alle Häkchen mit Ausnahme der Zeile Name. Denken Sie auch daran, das Häkchen bei Ursprünglichen Spaltennamen als Präfix verwenden zu entfernen. Und nach einem Klick auf OK sind ruckzuck alle Mitarbeiter-Namen eingetragen und die Spalte hat auch automatisch die Überschrift Name bekommen; bei Bedarf können Sie diese natürlich Ihren Wünschen gemäß anpassen. Schließen & laden oder Schließen und laden in… und die Daten werden entweder in ein neues Tabellenblatt oder an Ihrer Wunschposition gespeichert.
Das war’s auch schon. Die Aufgabe ist gelöst. Mit einer einzigen und aus meiner Sicht auch recht überschaubaren Formel haben Sie die die Aufgabe gelöst. – Oder doch nicht? Da war doch noch was…
Richtg, die PLZ müssen ja alle 5‑stellig sein und nicht teilweise (nur) 4‑stellig. Wechseln Sie dazu in das Menü Spalte hinzufügen | Benutzerdefinierte Spalte und vergeben als Neuer Spaltenneme beispielsweise PLZ. (also mit einem angehängten Punkt, da in einer Abfrage keine Überschriften mehrfach vorkommen können). Als Formel geben Sie ein:
= Number.ToText([PLZ], "00000")
und umgehend wird die neue PLZ-Spalte wunschgemäß formatiert. Dass der Datentyp hier Beliebig ist, spielt prinzipiell keine Rolle. Sie können ihn aber der des Prinzips wegen auf Text ändern. Ich lösche nun noch die (alte) Spalte PLZ, verschiebe PLZ. an die Position 2 und ändere den Namen der Überschrift wieder auf PLZ (ohne Punkt). Noch einmal Schließen & laden und Sie können das Ergebnis zufrieden betrachten. 😎
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 3,00 € freuen …