Office-Blog

Einer meiner Kunden möchte seine Umsätze in fünf Preiskategorien – A bis E – auswerten. Die Umsatzdaten werden per Power Query aus einer SQL-Datenbank gezogen und aufbereitet. Doch wie lassen sich in Power Query die Verkaufspreise einer der fünf Kategorien zuordnen?

Bild 1: Die Umsätze sollen in Power Query nach Kategorien ausgewertet werden

Mein erster Gedanke: Das ließe sich doch mit einem Join in Power Query lösen. Doch das funktioniert nicht. Denn mit einem Join können nur Werte abgeglichen werden, die genau übereinstimmen. Das ist hier nicht der Fall. Folglich brauche ich etwas wie einen SVERWEIS mit ungefährer Übereinstimmung. Diese Lösung baue ich wie folgt auf.

Welche Lösungsansätze wären denkbar?

Wie kann ich die Verkaufspreise der Umsatztabelle (in Bild 1 links) mit den Preisspannen der Kategorientabelle (in Bild 1 Mitte) zusammenführen?

• In Excel würde ich einen SVERWEIS mit ungefährer Übereinstimmung einsetzen.
• Auch eine mehrfach geschachtelte WENN-Funktion oder die in Excel 365 und 2019 verfügbare Funktion WENNS könnte ich verwenden.

Doch all diese Lösungsansätze haben einen entscheidenden Nachteil: Die Datenquelle müsste um eine Zusatzspalte erweitert werden, in der die Funktionen SVERWEIS, WENN oder WENNS ausgeführt werden. Das wird jedoch nicht gewünscht. Denn beim Kunden soll der Import aus der SQL-Datenbank ohne irgendwelche Zwischenschritte erfolgen. Logisch, denn das reduziert auch das Risiko von Fehlern.

Ein weiterer Lösungsansatz wäre: Ich schreibe in Power Query eine M-Funktion, die den SVERWEIS mit ungefährer Übereinstimmung nachbildet. Im Internet gibt es dazu einige Vorschläge. Doch ich entscheide mich für die folgende Lösung in Power Query, die für die Anwender leichter nachvollziehbar ist.

5 Schritte: So funktioniert die Lösung

• Umsatztabelle und Kategorientabelle werden in Power Query eingelesen
• Die Kategorientabelle wird an die Umsatztabelle angefügt
• Zwei aufeinanderfolgende Sortiervorgänge sorgen für Umsatzzeilen mit vorangestellter Kategorienzeile
• Durch AutoAusfüllen werden die unterschiedlichen Kategorien auf die darunterliegenden Umsatzdatensätze übertragen
• Zum Schluss werden die angefügten Kategoriendatensätze per Filter wieder entfernt

Das sind die Schritte im Detail

• Über Daten > Daten abrufen > Aus Tabelle/Bereich wird die Umsatztabelle in Power Query eingelesen. Dabei werden alle wichtigen Datenbereinigungen wie Datentypen ändern durchgeführt. Die Abfrage wird mit Schließen & laden in … > Nur Verbindung gespeichert.
• Über Daten > Daten abrufen > Aus Tabelle/Bereich wird die Kategorientabelle eingelesen. Die abzugleichende Spalte »ab« benenne ich so um, dass der Name ebenso lautet wie in der Umsatztabelle (hier »Verkaufspreis«). Anschließend wird auch diese Abfrage mit Schließen & laden in … > Nur Verbindung gespeichert.
• Links im Abfragenbereich klicke ich mit der rechten Maustaste auf die Abfrage »Umsatz« und wähle Verweis.
• Es folgt Start > Kombinieren > Abfragen anfügen > Abfragen anfügen. Als Anzufügende Tabelle gebe ich die Tabelle »Kategorien« an.
• Die Spalte »Verkaufspreis« sortiere ich über den Spaltenkopf Aufsteigend.
• Anschließend sortiere ich die Spalte »Datum« über den Spaltenkopf ebenfalls Aufsteigend.

Bild 2: Nach dem Anfügen der Kategorientabelle an die Umsatztabelle sorgen zwei Sortiervorgänge für eine passende Kategorienzeile vor jeweiligen Umsatzzeilen

• In der Spalte »Kategorie« klicke ich mit der rechten Maustaste auf den Spaltenkopf und dann auf Ausfüllen > Nach unten.

Bild 3: Per AutoAusfüllen wird die Kategorie auf die passenden Umsatzdaten übertragen

• Über die Spalte »Datum« sorge ich per Klick auf das Filtersymbol und durch Entfernen des Häkchens bei Null-Werten dafür, dass die angefügten Kategorienzeilen gelöscht werden.

Bild 4: Die Umsatzdaten durch Wegfiltern von Nullwerten wieder bereinigen

Fazit

• Alle Umsatzzeilen sind nun mit einer passenden Kategorie versehen.
• Sollten sich die Grenzen für die Verkaufspreise ändern oder werden Kategorien hinzugefügt oder gelöscht, reicht eine entsprechende Änderung der Kategorientabelle aus.
• Den Rest erledigt Power Query automatisch im Hintergrund.
• Für die Anwender ist diese Lösung praktikabel und birgt keine unsichtbaren Risiken.