PQQ: PQ-Verweis statt SVERWEIS

  Wissensstand: Level 1 ⇒ Einsteiger in PQ, keine/kaum Erfahrung   

Power Query Quickie: Ersatz für SVERWEIS()

Der folgende Satz wird sie vielleicht in einem Power Query-Thema etwas verwundern: In vielen Fällen des täglichen Lebens mit Excel und vorhandenem Power Query ist der ganz normale, der klassische SVERWEIS() ausreichend und manchmal sogar die bessere Lösung. Der Vorteil dabei: Jede Änderung der Quelldaten wird sofort berücksichtigt. Und wenn die Daten des Suchbereichs sortiert sind, kann die Suche auch einigermaßen schnell sein. Und nicht zu vergessen: Es werden auch „ungenaue“ Werte gefunden, wenn der 4. Parameter des SVERWEIS() nicht angegeben wird oder 1 bzw. WAHR ist. Unter PQ ist das nicht möglich.

Warum sollten Sie dann Power Query als Ersatz für den SVERWEIS() einsetzen? Es gibt einige Situationen, wo es sich wirklich lohnt bzw. mehr als sinnvoll ist. Als erstes fällt mir dabei in der SVERWEIS nach links ein, welcher hier und auch hier detailliert beschrieben ist. Auch bei großen Datenmengen hat Power Query wegen der Geschwindigkeit sowieso deutliche Vorteile. Und wenn der Suchwert auch noch in verschiedenen Tabellen, Arbeitsblättern oder sogar Mappen steht, dann kann sich jeder glücklich schätzen, der PQ verwenden und einsetzen kann. 😉 

In dieser als *.ZIP gepackten Musterdatei sind verschiedene Beispiele aufgeführt. Beginnend mit ganz einfachen Beispielen bis hin zu komplexen Such-Routinen. Aber gemäß dem Obertitel Power Query Quickie werden die einzelnen Beispiele recht komprimiert beschrieben. Das betrifft zumindest die beiden ersten Kapitel.

Schulnoten (1)

Öffnen Sie zu Beginn die Datei Notenfehler.xlsx und betrachten Sie sich das 1. Arbeitsblatt Notenfehler. Ihnen wird auffallen, dass ab Zeile 5 nur noch Fehlermeldungen zu sehen sind. Machen Sie sich bitte erst einmal auf die Suche nach dem Fehler, der auf jeden Fall in der Formel in Spalte C zu finden ist. Kleiner Hinweis: Absolute vs. relative Adressierung… 😉 

Mithilfe von Power Query soll nun dieses Mini-Projekt auf andere Weise gelöst werden. Grundsätzlich werden Sie zu importierende Listen vor dem eigentlichen Import in eine Formatierte Liste umwandeln, auch wenn es Power Query für Sie in den meisten Fällen gut erledigt. Erstellen Sie nun aus den Spalten Name, Note und Wertung gemeinsam eine Tabelle und eine zweite aus Note/Wertung. Diese beiden Tabellen importieren Sie nun nacheinander in Power Query. Sie sollten nach jedem einzelnen Import die Tabelle über Datei | Schließen und laden in… | Nur Verbindung erstellen speichern. Der Vorteil dabei ist, dass sie Redundanzen der erstellten Ergebnis-Tabellen vermeiden.

Ihnen liegen nach dem Import 2 Abfragen vor: Tabelle1 und Tabelle2. Da ja beide Abfragen geschlossen sind, öffnen Sie die Abfrage Tabelle1 auf beliebigem Wege. Sie können (und sollten für diese Übung) in dieser Abfrage-Tabelle die Spalte Wertung für Vergleichszwecke bestehen lassen; bei Bedarf kann diese immer noch direkt vor dem endgültigen speichern gelöscht werden. – Idealerweise nun ein Klick auf den linken Seitenstreifen (sofern dort nur der Text Abfragen und das Pfeilsymbol stehen) und dort sind dann beide Abfragen für den direkten Zugriff sichtbar. Prüfen Sie noch einmal kurz, ob Tabelle2 die korrekten Daten enthält.

Aktivieren Sie nun die Abfrage Tabelle1, Menü Start | Kombinieren | Abfragen zusammenführen   (erweitern) | Abfragen als neue Abfrage zusammenführen. Die Daten für Tabelle1 sind im oberen Bereich eingetragen. In der unteren Hälfte erweitern Sie das DropDown und wählen dort Tabelle2 aus. Nun jeweils ein Klick in die zu verknüpfenden Daten, was in diesem Fall jeweils die Einträge bei Note sind. Anschließend sind beide Spalten grün hinterlegt:

Zusammenführen der beiden Abfragen

Zusammenführen der beiden Abfragen

Die Auswahl bei Join-Art kann so bleiben, denn es sollen ja alle Einträge der Tabelle1 erhalten bleiben und mit den passenden Daten aus Tabelle2 ergänzt werden. Nach einem OK gibt es eine weitere Abfrage mit dem Namen Merge1 und einer neuen Spalte mit dem Namen Tabelle2 sowie dem durchgängigen Inhalt Table.

Ein Klick auf den Doppelpfeil Doppelpfeil dieser  Spalte und entfernen Sie die Häkchen bei Note als auch bei Ursprünglichen Spaltennamen als Präfix verwenden. Ein anschließender Vergleich wird zeigen, dass diese Werte hier nun korrekt sind. Idealerweise löschen Sie die zu Vergleichszwecken erstellte und hier übernommene Spalte Wertung (falls noch nicht geschehen) und benennen die neu erstellte Spalte Wertung.1 in (nur) Wertung um. Schließen & laden und der Erfolg ist gegeben. Power Query schreibt in ein neues Tabellenblatt das Ergebnis der Abfrage. – Gerade bei riesigen Datenmengen, beispielsweise mehr als 10.000 Zeilen ist diese Methode erheblich effizienter als der normale Excel – SVERWEIS().

▲ nach oben …

Schulnoten (2)

Wechseln Sie nun einmal zum Arbeitsblatt Notenfehler (2). Und Ihnen fällt sofort auf, dass hier in Spalte C ausschließlich Fehlerwerte stehen. Das ist auch ganz klar, denn beim SVERWEIS() muss der Suchbegriff ja in der ersten Spalte des Verweis-Bereichs stehen und das wäre dann Spalte F. Wie bereits weiter oben erwähnt gibt es dafür unterschiedliche Formellösungen, die ich hier aber nicht diskutiere. Hier ist das Spielfeld für Power Query. 😎 

Importieren Sie wiederum die beiden hier bereits formatierten Tabellen dieses Arbeitsblatts in den PQ-Editor. Die Namen der beiden Abfragen werden Tabelle3 und Tabelle4 sein. Wie gehabt beide Abfragen erst einmal als Nur Verbindung laden. Löschen Sie nach dem öffnen der Abfrage Tabelle3 die Spalte Wertung, sie beinhaltet ja nun wirklich nichts, was einen Vergleich lohnt. 

Und ab hier ist das Vorgehen praktisch identisch. In der Abfrage Merge2 die beiden Queries Tabelle3 und Tabelle4 als Neue Abfrage zusammenführen, das Feld Tabelle4 erweitern und das Häkchen bei Wertung stehen lassen. Das war’s auch schon. Schließen & laden in… und an gewünschter Position in ein Tabellenblatt schreiben.

▲ nach oben …

Lager und Rechnung (mehrere Spalten)

Aller guten Dinge sind ja bekanntlich drei. Und darum schließen Sie nun die Datei mit den Schulnoten und öffnen das File Lager&Rechnung.xlsx. Alternativ können Sie auch ein wenig experimentieren und die beiden Arbeitsblätter dieser Datei per Power Query in eine neue, leere Arbeitsmappe importieren.

Importieren Sie nun bitte die bereits als Liste formatierte Tabelle aus dem Tabellenblatt Lager. Der Name der Tabelle ist übrigens auch Lager. Und anschließend erstellen Sie eine weitere Abfrage aus dem Blatt Rechnung, wo die Datenlage gewiss noch etwas „dünn“ ist. Hier soll nach Eingabe der Produkt-Nummer und der Stückzahl (hier für dieses Training bereits erfolgt) nicht nur der Brutto-Preis errechnet werden sondern natürlich auch die Mehrwertsteuer an sich. Und die richtet sich bekanntlich nach dem MwSt-Satz, welcher für das Kürzel 1 (derzeit) 19% beträgt und für die 2 der ermäßigte Satz von 7%.

Sie werden sich vorstellen können, dass die Rechnung in jeder auszufüllenden Spalte mit einer eigenen Formel befüllt werden muss, wenn Sie in Excel mit dem SVERWEIS() arbeiten. Und da in der Tabelle Lager die Produktnummer in der 1. Spalte steht, sind auch keine „Klimmzüge“ erforderlich. Allerdings einen Schritt weiter gedacht, dass aus der Tabelle Rechnung ja auch eine Packliste und ein Lieferschein generiert werden könnte bzw. sollte, wird das ganze schon etwas aufwendiger. Hier kommt Power Query ganz klar in die Pole Position und zeigt seine Stärken. Und darum geht es natürlich weiter mit den Anleitungen für die Anwendung in Power Query.

Zu Beginn eine Anmerkung und der Hinweis auf einmalige Vorarbeiten, die so oder so erforderlich sind. Hinweis: Sie werden die Spalten Anzahl und Produkt-Nr. im Excel-Arbeitsblatt Rechnung ausfüllen und die Berechnung der weiteren Spalten nicht vollautomatisch sondern erst in Power Query durchführen lassen. Und das, was ich als Vorarbeit deklariert habe wird sie vielleicht überraschen: Schreiben Sie sich (in Excel) die Spaltennamen beginnend in Spalte C bis zur Spalte I auf oder drucken alternativ dieses Arbeitsblatt aus; löschen Sie nun in Excel diese Spalten komplett. Anschließend wechseln Sie in den Abfrage-Editor und aktualisieren Sie die Query. Natürlich werden jetzt nur diese beiden ersten Spalten sichtbar sein und das ist gewollt so. Sollte Ihnen das löschen der Spalten in der Excel-Tabelle nicht wirklich behagen, dann lassen Sie diese erst einmal dort stehen, importieren bzw. aktualisieren die ursprüngliche Tabelle und löschen erst im Power Query-Editor die entsprechenden Spalten zu einem beliebigen Zeitpunkt. Und wenn sie auch das nicht so optimal finden, dann wird PQ beim einfügen bzw. Erstellen der Spalten mit den verknüpften Werten den entsprechenden Spaltennamen mit einem .1 als „Anhängsel“ versehen und sie können schlussendlich entscheiden, wie sie damit umgehen wollen.

Im ersten Schritt werden Sie nun nacheinander die beiden erstellten Abfragen für Lager und Rechnung per Schließen & laden in… nacheinander als Nur Verbindung sichern. Je nach Version ist es denkbar, dass bereits nach dem ersten S&L-Vorgang beide Abfragen auf diese Weise geschlossen werden. Dass Sie nach dem ersten Vorgang wie eventuell auch nach dem zweiten Power Query bzw. die entsprechende Abfrage wieder öffnen müssen, ist Standard.

Falls im linken Seitenbereich nur das Wort Abfragen und der Pfeil zu sehen sind, öffnen Sie mit einem Doppelklick darauf das Seitenfenster, um dort alle existierenden Abfragen anzuzeigen. Wählen Sie dort die Abfrage Rechnung. Start | Kombinieren | Abfragen zusammenführen ▼  erweitern | Abfragen als neue Abfrage zusammenführen. Es öffnet sich der Zusammenführen-Dialog wo im oberen (großen) Kasten die ersten Werte der Abfrage Rechnung eingetragen sind. Im Auswahlfenster darunter wählen Sie die Abfrage Lager. Klicken Sie nun im oberen als auch im unteren Kasten in die Spalte Produkt-Nr., weil dieses Feld ja das verbindende Element zwischen beiden Abfragen ist. Beim Punkt Join-Art belassen Sie es bei der Vorgabe, denn es sollen ja alle Zeilen der Abfrage Rechnung verwendet werden und jene mit der gleichen Produktnummer aus der Abfrage Lager eingefügt werden:

So soll sich der Dialog darstellen

Bestätigen Sie mit OK und es wird eine neue Abfrage Merge1 erzeugt, die Sie vielleicht etwas irritiert:

Vielleicht nicht erwartet, aber korrekt…

Hier steht Table für Tabelle, nicht für Tisch! 😉 Klicken Sie in der Überschrift Lager auf den Doppelpfeil Doppelpfeil und es öffnet sich dieser Dialog:

Wählen Sie die anzuzeigenden Felder aus

Entfernen Sie zuerst einmal das allerunterste Häkchen bei Ursprünglichen Spaltennamen als… und überlegen Sie dann, welche Felder aus der Abfrage Lager übernommen werden sollen. Sie werden erkennen, dass es außer Produkt-Nr. jedes Feld sein soll. Darum entfernen Sie ausschließlich das Häkchen bei Produkt-Nr. und ein schließen Sie den Dialog per OK. Und das Ergebnis sieht schon ganz vielversprechend aus:

Die gewünschten Felder sind in die Abfrage eingefügt

Dass hier für eine Rechnung überflüssige Informationen (Spalten) enthalten sind, spielt derzeit keine Rolle. Es fehlen dennoch 2 Spalten, wo der entsprechende Wert berechnet werden soll. Auch wenn Ihnen Excel als erste Wahl einfällt, nein, auch hier ist Power Query gefragt. Dazu wechseln Sie zum Register Spalte hinzufügen und wählen dort das Symbol Bedingte Spalte. Sollte dieser Punkt ausgegraut sein dann achten Sie darauf, dass nur eine einzelne Spalte markiert ist und wiederholen den Vorgang. Im Dialog Bedingte Spalte hinzufügen schreiben Sie bei Neuer Spaltenname MwSt, denn die 1. neu zu erstellende Spalte soll ja diese Überschrift haben. Anschließend werden sie in den Feldern darunter diese Werte eingeben:

  • Neuer Spaltenname → MwSt
  • Spaltenname | WennMwSt-Satz (auswählen)
  • Operator | ist gleich (belassen)
  • Wert | ABC/1231
  • Ausgabe | ABC/12319% (oder 0,19)
  • Andernfalls | ABC/1237% (oder 0,07)

…Und danach das Fenster per OK schließen. An dieser Stelle ist mir eine deutliche Unregelmäßigkeit, aus meiner Sicht ein Bug (Fehler) aufgefallen: Wenn Sie noch einmal auf dieses Feld über das Bearbeiten-Symbol zugreifen, dann wird der Wert nach Power Query’s Ermessen geändert; statt der Prozentwerte wird der reine Zahlenwert also 19 bzw. 7 statt 0,19 (19%) oder 0,07 (7%) in jeder Zelle eingetragen bzw. ersetzt. Das ist natürlich ein dramatischer Unterschied, weil eine Multiplikation mit 100 vorgenommen wird. Ich bin erst einmal den Weg gegangen, diese Spalte sofort als Prozentsatz zu formatieren, was (hoffentlich) diesen Mangel beseitigt. Wenn Sie im Nachhinein die Überschrift ändern möchten, sollten Sie aber die Änderung direkt aber nicht über den Dialog vornehmen. 😥 

Wenn Sie auf der ganz sicheren Seite sein wollen, dann gehen Sie einen vielleicht etwas umständlicheren dafür aber garantiert sicheren Weg. Nach dem Wechsel zum Menüpunkt Spalte hinzufügen wählen Sie nicht Bedingte Spalte sondern Benutzerdefinierte Spalte. Tragen Sie bei Neuer Spaltenname wie gehabt den Wert MwSt ein und bei Benutzerdefinierte Spaltenformel eine Formel in exakt dieser Schreibweise:
= if [#"MwSt-Satz"]=1 then 0.19 else 0.07
und schließen dann das Fenster. Hinweis: Den Feldnamen [#”MwSt-Satz”] übernehmen Sie idealerweise durch einen Doppelklick auf den entsprechenden Namen rechts im Kasten Verfügbarer Spalten, wobei die eckigen Klammern sowie das hier erforderliche #-Zeichen automatisch korrekt eingefügt werden. Und die Dezimaltrenner (Punkt) sind hier im Code korrekt, in der Abfrage wird die Darstellung automatisch mit einem Komma verwendet. Je nach Belieben können Sie das Format dieser Spalte so belassen oder als Prozentsatz darstellen.

Last but not least soll bzw. muss noch der Brutto-Preis berechnet werden. Hier sehe ich prinzipiell wiederum 2 Wege, die je nach Wissensstand des Anwenders zum Ziel führen. Einsteiger können beispielsweise so vorgehen: Markieren Sie die Spalten Preis (netto), Strg und ein Klick in die Überschrift MwSt. Immer noch im Register Spalte hinzufügen wählen Sie Standard | Multiplizieren. Es wird automatisch eine Spalte mit der Überschrift Multiplikation erstellt. Wenn die Spalte MwSt als Prozent formatiert ist, dann wird das Ergebnis der Multiplikation gleichermaßen formatiert sein und auf den ersten Blick (und auch in der Realität) ein 100-fach zu hohes Ergebnis zeigen. Formatieren Sie in dem Fall in diese Spalte (beispielsweise über Typ ändern) als Dezimalzahl oder als Währung.

Damit ist allerdings erst einmal nur der Preis für ein Stück berechnet. Erstellen Sie also nun eine weitere Benutzerdefinierte Spalte und Multiplizieren Sie Anzahl mit dem Feld Multiplikation, wo ja der reine Mehrwertsteuer-Betrag ausgerechnet worden ist. Die Berechnung des endgültigen Preises (Brutto-Preis) beschreibe ich weiter unten, nach dem Intermezzo für „mutige“ Anwender. 😉 

Ich will Ihnen natürlich auch nicht den etwas fortgeschrittenen Weg vorenthalten. Hier erstellen Sie auch im Register Spalte hinzufügen einer Benutzerdefinierte Spalte und geben gleich als Überschrift/Neuer Spaltenname Brutto-Preis ein. Die Spaltenformel greift naturgemäß auf die Spaltennamen im rechten Kasten zu, welche sie dann idealerweise durch einen Doppelklick übernehmen. Die Formel sollte dann so aussehen:
= [Anzahl]*[#"Preis (netto)"]*(1+[MwSt])

Ich finde diesen Weg nicht nur transparenter sondern auch eleganter und im Endeffekt sicherer. Da wird PQ nicht eigenmächtig irgendwelche Änderungen vornehmen. In jedem Fall sollten Sie natürlich stichprobenmäßig berechnen, ob das Ergebnis korrekt ist.

Wenn Sie den Weg für Einsteiger gegangen sind, dann fehlt ja noch die Berechnung des Brutto-Preises. Die erforderlichen Daten existieren ja bereits in den Spalten Preis (netto) und Multiplikation. Darum markieren Sie genau diese beiden Überschriften, Spalte hinzufügen | Standard | Addieren. In der neu geschaffenen Spalte Addition steht nun der Preis für ein Stück des jeweiligen Produkts. Wenn Sie nun in diesem Register bleiben, Addition und Anzahl markieren (mittels Strg) und dann Standard | Multiplizieren wählen, dann wird als letzte Spalte Multiplikation.1 mit dem korrekten Brutto-Preis angelegt. Da Sie diese Spalte in jedem Falle weiter nutzen werden ist es natürlich sinnvoll, um den Spaltennamen auf Brutto-Preis zu ändern. 😎 

Damit ist Ursprungsform der Abfrage Rechnung prinzipiell komplett. Die eine oder andere Spalte werden Sie an dieser Stelle idealerweise löschen. In dieser Version sollten folgende Spalten erhalten bleiben: Anzahl, Produkt-Nr., Bezeichnung, Regal, Platz, Preis (netto), MwSt-Satz und Brutto-Preis. Diese Abfrage (Merge1) sollten Sie auch per Schließen & laden in… als Nur Verbindung sichern.

Natürlich haben die Felder für den Lagerplatz nichts auf einer Rechnung oder einem Lieferschein zu suchen. Aber es war zu Beginn ihr schon angesprochen, dass das Lager für die Zusammenstellung der Ware eine Ausfertigung braucht und auf einem zu erstellenden Lieferschein sind standardmäßig auch keine Preise ausgewiesen. – Um die Transparenz etwas zu erhöhen, benennen Sie die Abfrage Merge1 um, in der neue Name kann beispielsweise RawData Rechnung sein. Das geht idealerweise per Rechtsklick im linken Seitenfenster.

Öffnen Sie nun die Abfrage RawData Rechnung. Start | Verwalten | Verweis und es wird eine Kopie, genauer gesagt ein Verweis auf die gewählte Abfrage erstellt. Im rechten Seitenfenster werden Sie erkennen, dass nur ein einziger Schritt dargestellt wird, die Abfrage jedoch komplett ist. Näheres zum Thema Verweis können Sie hier nachlesen.

Benennen Sie die aktuelle Abfrage RawData Rechnung (2) in beispielsweise Exemplar für das Lager um. Löschen Sie nun -beginnend mit Preis (netto)– alle rechts davon stehenden Spalten. Falls gewünscht können Sie jetzt noch über Spalte hinzufügen | Benutzerdefinierte Spalte eine Spalte mit der Überschrift Stück gepackt erstellen und als Formel = null eingeben. Das führt dazu, dass eine Spalte mit ausschließlich leeren Zellen generiert wird. Dort kann dann das Lagerpersonal in Excel die gepackte Menge eintragen. Und natürlich kann dann auch noch eine weitere Spalte erstellt werden, wo die Spalten Anzahl und Stück verglichen werden, wo dann entweder OK oder Mindermenge ausgegeben wird.

Noch einmal RawData Rechnung im linken Seitenfenster markieren, Rechtsklick und wiederum Verweis auswählen. Diese Abfrage sollte den Namen Lieferschein bekommen. Hier werden sie nur die ersten 3 Spalten bestehen lassen. Wenn Sie das Optimum erreichen wollen, dann werden Sie über den Menüpunkt Kombinieren die Spalte Stück mit der geänderten Überschrift Geliefert einbinden, falls in dieser Spalte der numerische Wert der gepackten Stückzahlen steht.

Es bleibt noch die Abfrage Rechnung auf dem gleichen Wege wie eben zu erstellen. Hier werden Sie in jedem Fall Regal und Platz löschen, die Namen der Überschriften jener Spalten, die sie in dieser Abfrage übernehmen anpassen und die „überflüssigen“ Spalten löschen.

Die 3 zuletzt erstellten Abfragen können Sie nicht ad hoc in ein neues Tabellenblatt schreiben. Sie werden seitens Power Query automatisch auch als Nur Verbindung gesichert. Wie sie das bequem ändern können, lesen Sie hier in unserem Blog nach.

Sie haben jetzt gewiss erkannt, dass Power Query doch erhebliche Erleichterungen bieten kann. Denn es reicht, die Daten der Ursprungsabfrage (Rechnung) den neuen Gegebenheiten anzupassen, auf Aktualisieren zu klicken und schon sind alle weiteren Abfragen auf dem neuesten Stand.

▲ nach oben …

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag (0,50€ – 2,00€) Ihrerseits freuen …

Dieser Beitrag wurde unter Basics-PQQ, Daten zusammenführen, Power Query, PQ-Basics abgelegt und mit , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.