PQQ: SVERWEIS() nach beiden Seiten

SVERWEIS nach rechts und nach links (PQ)

Es existiert eine Kunden-Datei (DACH-Kunden.xlsx) mit einigen wenigen Kunden aus Deutschland (DE), Österreich (AT) und der Schweiz (CH). Die Daten habe ich aus der Northwind-Datenbank (MS Access) entnommen, einige Spalten entfernt, deutsche Überschriften eingesetzt und eine eindeutige Kundennummer eingefügt. Diesen Daten-Extrakt können Sie hier für Ihre Tests herunterladen.

In einen weiteren Excel Workbook (also in einer anderen Mappe) sollen nun -je nach Situation- Daten per Verweis aus der Datei mit den DACH-Kunden eingebunden werden. Exemplarisch seien hier 2 Fälle genannt:

  1. Die Kundennummer wird in ein Feld eingegeben oder es existiert dort schon eine Liste aller erdenklichen Kundennummern, die anderen Spalten sollen automatisch aus- bzw. aufgefüllt werden.
  2. Es ist (nur) die Telefonnummer bekannt, auf der Basis sollen die restlichen Felder des Kunden gefüllt werden; auch hier kann natürlich eine ganze Reihe (exakt: Spalte) von Telefonnummern bereits eingegeben sein.

Und das Ganze soll (natürlich) mit Power Query erledigt werden. Auch wenn PQ in gerade diesem Fall nicht unbedingt die erste bzw. sinnvollste Wahl ist, des Prinzips und der Übersichtlichkeit und des besseren Lerneffekts wegen verwende ich diese kleine Datei.

Sie haben die zweite Datei mit den auszufüllenden Zellen geöffnet:

Die Ziel-Datei mit einigen ausgefüllten Kundennummern

Die Ziel-Datei mit einigen ausgefüllten Kundennummern

… das File mit den kompletten Kundendaten kann geöffnet sein oder geschlossen bleiben.

1. Vorarbeiten (gilt für beide Beispiele)

  • Die Datei DACH-Kunden.xlsx kann geschlossen bleiben oder (auch durch einen anderen User) geöffnet sein.
  • Füllen Sie in die jeweils einzige Spalte eines Blattes (KdNr oder Telefon) einige „echte“ (sprich: korrekte) Einträge aber vielleicht auch einen fehlerhaften Wert ein, damit Sie anschließend den Effekt erkennen. Da ja je nach Tabelle alle anderen Felder noch ausgefüllt werden sollen, bleiben diese leer. – Hinweis: Sie werden später erkennen, dass sie eigentlich überflüssig sind …
  • Alternative: Laden Die diese Datei herunter, dort sind schon einige wenige Werte als Muster eingefügt (siehe auch obige Abbildung).
  • Klicken Sie in ein nicht leeres Feld (Zelle) und StrgT oder StrgL, um aus den eingegebenen Daten eine Intelligente Tabelle/Liste zu machen. (Das ist zwar nicht unbedingt erforderlich, kann aber gleich zu Beginn mehr Sicherheit bringen.)

Denken Sie daran, dass dieses nur die Vorarbeiten sind und Sie an dieser Stelle noch keinerlei Effekt erwarten können.

▲ nach oben …

2. Kundennummer → restliche Felder füllen

Das File, wo anhand der Eingaben die restlichen Daten eingefügt werden sollen ist geöffnet und auf dem Schirm. Die Datei mit ausschließlich den Überschriften wäre diese:  PQQ_Sverweis_Kunden_DACH_leer.xlsx; oder PQQ_Sverweis_Kunden_DACH.xlsx mit einigen Muster-Einträgen.  Das Blatt KdNr bekannt ist aktiv und es sind von mir oder von Ihnen einige Kundennummern eingetragen. Die aktive Zelle ist irgendwo im Datenbereich dieses Worksheets. Bei den Excel-Versionen 2010 und 2013 wählen Sie (bei installiertem Power Query) den Menüpunkt Power Query, ab Excel 2016 ein Klick auf das Register Daten. Anschließend das Ribbon Von bzw. Aus Tabelle. Der Abfrage-Editor wird sich nach Bestätigung des Datenbereichs und dem gesetzten Häkchen für die Überschriften automatisch öffnen. Gleich auf Schließen & laden klicken, um den Weg für das Erstellen einer weiteren Abfrage zu ebnen. Hinweis: Mit zunehmender Erfahrung in Power Query werden Sie den Weg über Schließen & laden in… gehen.

Hinweis: Ab hier beziehen sich die Anweisungen auf Excel 2016, in den früheren Versionen sind diese aber sehr ähnlich. Im Menü-Register Start Gruppe Neue Abfrage, Auswahl Neue Quelle klicken Sie Datei | Aus Arbeitsmappe (Excel-Symbol), eventueller Wechsel zum entsprechenden Verzeichnis und wählen dann das File DACH-Kunden.xlsx. Im Navigator erkennen Sie als oberen Eintrag die Tabelle Kunden_DACH (erkennbar am blauen Streifen oberhalb des Zell-Rasters) und das Arbeitsblatt Kunden – DACH (mit den symbolischen Registerkarten unten links); Sie wählen die Tabelle nach einem kontrollierenden Blick in die Vorschau im rechten Seitenfenster zum Bearbeiten aus. Die komplette Kunden-Liste wird sofort als zweite Abfrage in das Portfolio eingegliedert. Idealerweise werden Sie am linken Rand auf Abfragen klicken, um dort das Seitenfenster zu öffnen (falls es nicht schon ohne Ihr Zutun erschienen ist).

Wählen Sie jetzt die zuerst erstellte Abfrage, wo ausschließlich die Kundennummern enthalten sind. Wiederum im Menü Start finden Sie in der rechten Hälfte die Schaltfläche Kombinieren. Nach einem Klick darauf wählen Sie im DropDown die obere Auswahl Abfragen zusammenführen. Im Dialogfenster Zusammenführen sind die ersten Zeilen der Tabelle bereits in der oberen Hälfte des Fensters eingefügt. Ein Klick auf das leere Listenfeld und dort wählen Sie die Abfrage Kunden_DACH aus. Klicken Sie nun in beiden Fenstern in das Feld mit der Kundennummer, verinnerlichen sich gerne den Punkt Join-Art und bestätigen dann mit OK. Der Effekt ist, dass sie im Editor eine hinzugefügte Spalte erkennen, wo jede einzelne Zeile den Eintrag Table hat:

Neu erstellte Spalte Kunden_DACH

Neu erstellte Spalte Kunden_DACH

In der Überschrift der neuen Spalte ein Klick auf das Erweitern-Symbol und im Dialog entfernen Sie zu Beginn das Häkchen bei KdNr und ganz unten bei Ursprünglichen Spaltennamen als Präfix verwenden. Nach einem OK werden alle passenden Werte übernommen. Sie erkennen aber auch, dass keineswegs die bereits existierenden Spalten mit den Daten gefüllt werden sondern dass die Spalten der importierten Daten „angehängt“ wurden und die Überschriften durch einen Index erweitert hat, weil jede Überschrift ein Unikat sein muss. Der Übersicht halber löschen Sie die „alten“ Spalten KD-ID bis Fax, eventuell ändern Sie in den verbleibenden Spalten die Überschrift dahingehend, dass der Punkt mit dem angefügten Index entfernt wird. – Und wenn Sie später einmal solch eine Prozedur durchführen werden Sie es so machen, wie weiter unten bei der Telefonnummer beschrieben.  😎 

Sollten Sie eine fehlerhafte, also nicht existierende Kundennummer eingetragen haben, finden Sie anschließend im Editor in den weiteren Spalten den Wert null, welcher später in der Excel-Tabelle als leeres Feld definiert ist. Sollten Sie diese falsch eingegebenen Kundennummern bereits im Abfrage-Editor eliminieren wollen, dann filtern sie beispielsweise die Spalte Firmen-Name dergestalt, dass sie beim Wert (NULL) das Häkchen entfernen. – Schließen & laden und die neue Tabelle wird als neues Blatt in die Mappe eingefügt.

Wenn Sie meine Muster-Mappe mit den bereits ausgefüllten Kundennummern verwendet haben, dann sind in der Abfrage in jedem Fall die beiden letzten Zeilen als null dargestellt bekommen, weil beide eingegebenen Kundennummern nicht existierten. Wechseln Sie zum Blatt KdNr bekannt und ändern Sie die von mir fehlerhaft eingegebene Kundennummer 1203 auf den Wert 1202. Wechseln Sie zur ausgefüllten Tabelle mit den 2 Leerzeilen, Menü Tabellentools | Entwurf und ein Klick auf die Schaltfläche Aktualisieren. Sofort werden die Werte der nun korrekten und somit passenden Kundennummer in die Spalten eingefügt. Natürlich können Sie auch der Ursprungstabelle KdNr bekannt eine weitere Kundennummer eingeben und die Ziel-Tabelle wird nach einer Aktualisierung entsprechend aktualisiert.

▲ nach oben …

3. Telefonnummer → restliche Felder füllen

Die eben durchgeführte Prozedur hätten Sie natürlich ganz normal mit einem SVERWEIS durchführen können, denn der auszufüllende Begriff stand ja jeweils rechts vom Suchbegriff. Ist aber die Telefonnummer bekannt, dann wäre dieses ein Verweis nach links, der in dieser Form in Excel nicht (ohne Umwege) durchführbar ist.

Wechseln Sie zum Tabellenblatt Telefon bekannt und geben Sie die Spalte die eine oder andere Telefonnummer in exakt der Schreibweise ein, wie sie in der zu vergleichenden Datei (die DACH-Kunden) vorhanden ist.Alternativ haben Sie meine bereits ausgefüllte Datei geladen.

Sie haben bei den Kundennummern erkannt, dass in diesem Stadium prinzipiell nur die Spalte mit demSuchwert benötigt wird, alle restlichen Spalten werden ja durch das Zusammenfügen sowieso erneut geschrieben. Darum ein Rechtsklick auf die Überschrift Telefon und Andere Spalten entfernen. Anschließend geht es ähnlich weiter, wie bei den Kundennummern.

Menü Kombinieren, Abfragen zusammenführen. Als zu kombinierende zweite Abfrage wählen Sie Kunden_DACH. In beiden teil-Fenstern markieren Sie die Spalte Telefon, anschließend OK. Die neue Spalte Kunden_Dack erweitern Sie wiederum durch den Klick auf Doppelpfeil. Sie entfernen die Markierung bei Telefon und bei Ursprünglichen Spaltennamen… danach OK. Klick in die Übeschrift Telefon und ziehen Sie die Spalte nun noch an die gewünschte, ursprüngliche Position. – Alternative: Sie löschen nach dem Klick auf das Erweitern-Symbol ausschließlich Ursprünglichen Spaltennamen… und dann gleich OK. Und da die Spalte Telefon nun einmal ganz am Anfang und auch in der richtigen Position ziemlich am Ende steht, werden sie die 1. Spalte durch Rechtsklick auf den Spaltenkopf/Überschrift und dann Entfernen löschen. Damit auch diese Aufgabe erledigt.

Epilog

Dieses war ein Schnelldurchgang, der Gruppe „Quickie“ entsprechend. Eine wesentlich ausführlichere Anleitung zu diesem Thema finden Sie beispielsweise hier im Blog.

▲ nach oben …

Dieser Beitrag wurde unter Allgemein veröffentlicht. Setze ein Lesezeichen auf den Permalink.

Schreibe einen Kommentar