Es existiert eine Kun­den-Datei (DACH-Kunden.xlsx) mit eini­gen weni­gen Kun­den aus Deutsch­land (DE), Öster­re­ich (AT) und der Schweiz (CH). Die Dat­en habe ich aus der North­wind-Daten­bank (MS Access) ent­nom­men, einige Spal­ten ent­fer­nt, deutsche Über­schriften einge­set­zt und eine ein­deutige Kun­den­num­mer einge­fügt. Diesen Dat­en-Extrakt kön­nen Sie hier für Ihre Tests herun­ter­laden.

In einen weit­eren Excel Work­book (also in ein­er anderen Mappe) sollen nun ‑je nach Sit­u­a­tion- Dat­en per Ver­weis aus der Datei mit den DACH-Kun­den einge­bun­den wer­den. Exem­plar­isch seien hier 2 Fälle genan­nt:

1. Die Kun­den­num­mer wird in ein Feld eingegeben oder es existiert dort schon eine Liste aller erden­klichen Kun­den­num­mern, die anderen Spal­ten sollen automa­tisch aus- bzw. aufge­füllt wer­den.
2. Es ist (nur) die Tele­fon­num­mer bekan­nt, auf der Basis sollen die restlichen Felder des Kun­den gefüllt wer­den; auch hier kann natür­lich eine ganze Rei­he (exakt: Spalte) von Tele­fon­num­mern bere­its eingegeben sein.

Und das Ganze soll (natür­lich) mit Pow­er Query erledigt wer­den. Auch wenn PQ in ger­ade diesem Fall nicht unbe­d­ingt die erste bzw. sin­nvoll­ste Wahl ist, des Prinzips und der Über­sichtlichkeit und des besseren Lern­ef­fek­ts wegen ver­wende ich diese kleine Datei.

Sie haben die zweite Datei mit den auszufül­len­den Zellen geöffnet:

Die Ziel-Datei mit eini­gen aus­ge­füll­ten Kun­den­num­mern

… das File mit den kom­plet­ten Kun­den­dat­en 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 jew­eils einzige Spalte eines Blattes (KdNr oder Tele­fon) einige „echte” (sprich: kor­rek­te) Ein­träge aber vielle­icht auch einen fehler­haften Wert ein, damit Sie anschließend den Effekt erken­nen. Da ja je nach Tabelle alle anderen Felder noch aus­ge­füllt wer­den sollen, bleiben diese leer. – Hin­weis: Sie wer­den später erken­nen, dass sie eigentlich über­flüs­sig sind …
• Alter­na­tive: Laden Die diese Datei herunter, dort sind schon einige wenige Werte als Muster einge­fügt (siehe auch obige Abbil­dung).
• Klick­en Sie in ein nicht leeres Feld (Zelle) und StrgT oder StrgL, um aus den eingegebe­nen Dat­en eine Intel­li­gente Tabelle/Liste zu machen. (Das ist zwar nicht unbe­d­ingt erforder­lich, kann aber gle­ich zu Beginn mehr Sicher­heit brin­gen.)

Denken Sie daran, dass dieses nur die Vorar­beit­en sind und Sie an dieser Stelle noch kein­er­lei Effekt erwarten kön­nen.

▲ nach oben …

2. Kundennummer → restliche Felder füllen

Das File, wo anhand der Eingaben die restlichen Dat­en einge­fügt wer­den sollen ist geöffnet und auf dem Schirm. Die Datei mit auss­chließlich den Über­schriften wäre diese:  PQQ_Sverweis_Kunden_DACH_leer.xlsx; oder PQQ_Sverweis_Kunden_DACH.xlsx mit eini­gen Muster-Ein­trä­gen.  Das Blatt KdNr bekan­nt ist aktiv und es sind von mir oder von Ihnen einige Kun­den­num­mern einge­tra­gen. Die aktive Zelle ist irgend­wo im Daten­bere­ich dieses Work­sheets. Bei den Excel-Ver­sio­nen 2010 und 2013 wählen Sie (bei instal­liertem Pow­er Query) den Menüpunkt Pow­er Query, ab Excel 2016 ein Klick auf das Reg­is­ter Dat­en. Anschließend das Rib­bon Von bzw. Aus Tabelle. Der Abfrage-Edi­tor wird sich nach Bestä­ti­gung des Daten­bere­ichs und dem geset­zten Häkchen für die Über­schriften automa­tisch öff­nen. Gle­ich auf Schließen & laden Klick­en, um den Weg für das Erstellen ein­er weit­eren Abfrage zu ebnen. Hin­weis: Mit zunehmender Erfahrung in Pow­er Query wer­den Sie den Weg über Schließen & laden in… gehen.

Hin­weis: Ab hier beziehen sich die Anweisun­gen auf Excel 2016, in den früheren Ver­sio­nen sind diese aber sehr ähn­lich. Im Menü-Reg­is­ter Start Gruppe Neue Abfrage, Auswahl Neue Quelle Klick­en Sie Datei | Aus Arbeitsmappe (Excel-Sym­bol), eventueller Wech­sel zum entsprechen­den Verze­ich­nis und wählen dann das File DACH-Kunden.xlsx. Im Nav­i­ga­tor erken­nen Sie als oberen Ein­trag die Tabelle Kunden_DACH (erkennbar am blauen Streifen ober­halb des Zell-Rasters) und das Arbeits­blatt Kun­den – DACH (mit den sym­bol­is­chen Reg­is­terkarten unten links); Sie wählen die Tabelle nach einem kon­trol­lieren­den Blick in die Vorschau im recht­en Seit­en­fen­ster zum Bear­beit­en aus. Die kom­plette Kun­den-Liste wird sofort als zweite Abfrage in das Port­fo­lio eingegliedert. Ide­al­er­weise wer­den Sie am linken Rand auf Abfra­gen Klick­en, um dort das Seit­en­fen­ster zu öff­nen (falls es nicht schon ohne Ihr Zutun erschienen ist).

Wählen Sie jet­zt die zuerst erstellte Abfrage, wo auss­chließlich die Kun­den­num­mern enthal­ten sind. Wiederum im Menü Start find­en Sie in der recht­en Hälfte die Schalt­fläche Kom­binieren. Nach einem Klick darauf wählen Sie im Drop­Down die obere Auswahl Abfra­gen zusam­men­führen. Im Dialogfen­ster Zusam­men­führen sind die ersten Zeilen der Tabelle bere­its in der oberen Hälfte des Fen­sters einge­fügt. Ein Klick auf das leere Lis­ten­feld und dort wählen Sie die Abfrage Kunden_DACH aus. Klick­en Sie nun in bei­den Fen­stern in das Feld mit der Kun­den­num­mer, verin­ner­lichen sich gerne den Punkt Join-Art und bestäti­gen dann mit OK. Der Effekt ist, dass sie im Edi­tor eine hinzuge­fügte Spalte erken­nen, wo jede einzelne Zeile den Ein­trag Table hat:

Neu erstellte Spalte Kunden_DACH

In der Über­schrift der neuen Spalte ein Klick auf das Erweit­ern-Sym­bol und im Dia­log ent­fer­nen Sie zu Beginn das Häkchen bei KdNr und ganz unten bei Ursprünglichen Spal­tenna­men als Prä­fix ver­wen­den. Nach einem OK wer­den alle passenden Werte über­nom­men. Sie erken­nen aber auch, dass keineswegs die bere­its existieren­den Spal­ten mit den Dat­en gefüllt wer­den son­dern dass die Spal­ten der importierten Dat­en „ange­hängt” wur­den und die Über­schriften durch einen Index erweit­ert hat, weil jede Über­schrift ein Unikat sein muss. Der Über­sicht hal­ber löschen Sie die „alten” Spal­ten KD-ID bis Fax, eventuell ändern Sie in den verbleiben­den Spal­ten die Über­schrift dahinge­hend, dass der Punkt mit dem ange­fügten Index ent­fer­nt wird. – Und wenn Sie später ein­mal solch eine Proze­dur durch­führen wer­den Sie es so machen, wie weit­er unten bei der Tele­fon­num­mer beschrieben.  😎 

Soll­ten Sie eine fehler­hafte, also nicht existierende Kun­den­num­mer einge­tra­gen haben, find­en Sie anschließend im Edi­tor in den weit­eren Spal­ten den Wert null, welch­er später in der Excel-Tabelle als leeres Feld definiert ist. Soll­ten Sie diese falsch eingegebe­nen Kun­den­num­mern bere­its im Abfrage-Edi­tor eli­m­inieren wollen, dann fil­tern sie beispiel­sweise die Spalte Fir­men-Name dergestalt, dass sie beim Wert (NULL) das Häkchen ent­fer­nen. – Schließen & laden und die neue Tabelle wird als neues Blatt in die Mappe einge­fügt.

Wenn Sie meine Muster-Mappe mit den bere­its aus­ge­füll­ten Kun­den­num­mern ver­wen­det haben, dann sind in der Abfrage in jedem Fall die bei­den let­zten Zeilen als null dargestellt bekom­men, weil bei­de eingegebe­nen Kun­den­num­mern nicht existierten. Wech­seln Sie zum Blatt KdNr bekan­nt und ändern Sie die von mir fehler­haft eingegebene Kun­den­num­mer 1203 auf den Wert 1202. Wech­seln Sie zur aus­ge­füll­ten Tabelle mit den 2 Leerzeilen, Menü Tabel­len­tools | Entwurf und ein Klick auf die Schalt­fläche Aktu­al­isieren. Sofort wer­den die Werte der nun kor­rek­ten und somit passenden Kun­den­num­mer in die Spal­ten einge­fügt. Natür­lich kön­nen Sie auch der Ursprungsta­belle KdNr bekan­nt eine weit­ere Kun­den­num­mer eingeben und die Ziel-Tabelle wird nach ein­er Aktu­al­isierung entsprechend aktu­al­isiert.

▲ nach oben …

3. Telefonnummer → restliche Felder füllen

Die eben durchge­führte Proze­dur hät­ten Sie natür­lich ganz nor­mal mit einem SVERWEIS durch­führen kön­nen, denn der auszufül­lende Begriff stand ja jew­eils rechts vom Such­be­griff. Ist aber die Tele­fon­num­mer bekan­nt, dann wäre dieses ein Ver­weis nach links, der in dieser Form in Excel nicht (ohne Umwege) durch­führbar ist.

Wech­seln Sie zum Tabel­len­blatt Tele­fon bekan­nt und geben Sie die Spalte die eine oder andere Tele­fon­num­mer in exakt der Schreib­weise ein, wie sie in der zu ver­gle­ichen­den Datei (die DACH-Kun­den) vorhan­den ist.Alternativ haben Sie meine bere­its aus­ge­füllte Datei geladen.

Sie haben bei den Kun­den­num­mern erkan­nt, dass in diesem Sta­di­um prinzip­iell nur die Spalte mit dem­Such­w­ert benötigt wird, alle restlichen Spal­ten wer­den ja durch das Zusam­men­fü­gen sowieso erneut geschrieben. Darum ein Recht­sklick auf die Über­schrift Tele­fon und Andere Spal­ten ent­fer­nen. Anschließend geht es ähn­lich weit­er, wie bei den Kun­den­num­mern.

Menü Kom­binieren, Abfra­gen zusam­men­führen. Als zu kom­binierende zweite Abfrage wählen Sie Kunden_DACH. In bei­den Teil-Fen­stern markieren Sie die Spalte Tele­fon, anschließend OK. Die neue Spalte Kunden_Dach erweit­ern Sie wiederum durch den Klick auf . Sie ent­fer­nen die Markierung bei Tele­fon und bei Ursprünglichen Spal­tenna­men… danach OK. Klick in die Übeschrift Tele­fon und ziehen Sie die Spalte nun noch an die gewün­schte, ursprüngliche Posi­tion. – Alter­na­tive: Sie löschen nach dem Klick auf das Erweit­ern-Sym­bol auss­chließlich Ursprünglichen Spal­tenna­men… und dann gle­ich OK. Und da die Spalte Tele­fon nun ein­mal ganz am Anfang und auch in der richti­gen Posi­tion ziem­lich am Ende ste­ht, wer­den sie die 1. Spalte durch Recht­sklick auf den Spaltenkopf/Überschrift und dann Ent­fer­nen löschen. Damit auch diese Auf­gabe erledigt.

Epilog

Dieses war ein Schnell­durch­gang, der Gruppe „Quick­ie” entsprechend. Eine wesentlich aus­führlichere Anleitung zu diesem The­ma find­en Sie beispiel­sweise hier im Blog.

▲ nach oben …