Xtract: Power Query für Einsteiger, Vergleich von Monatsumsätzen des aktuellen und des Vorjahres mit Berechnung der prozentualen Differenzen.
Wissensstand: Level 2 ⇒ Power Query für Einsteiger – Kaum Vorkenntnisse in PQ
Monatsweiser Umsatzvergleich, prozentuale Differenz
(absolut und relativ)
Prolog
In einem Forum wurde die Frage aufgeworfen, wie die monatlichen Umsätze unterschiedlicher Jahre so ausgewertet werden können, dass die Zahlen des aktuellen Jahres mit denen des Vorjahres verglichen werden und die prozentuale Differenz ausgegeben wird. Der Fragesteller gab an, dass er die Excel-Version 2016 hätte und wollte eine Auswertung auf Pivot-Basis haben. Da ich der Überzeugung bin, dass eine Lösung mit Power Query deutlich besser zu realisieren ist, habe ich auf der PQ-Basis einen Weg zum Ziel erarbeitet.
Um die Aufgabe nachvollziehen zu können laden Sie erst einmal diese Datei von unserem Server herunter. Es handelt sich dabei um die Kopie des Original-Files. Der einzige Unterschied: Ich habe die beiden Blatt-Register Ergebnis und Datenbasis in der Reihenfolge ausgetauscht, da mir die logische Reihenfolge von links nach rechts lieber und gewohnter ist. Mir ist klar, dass Excel neu eingefügte Blätter standardmäßig immer vor dem aktuellen Blatt einfügt, daher diese ursprüngliche Anordnung.
Betrachten Sie gerne einmal das Blatt Ergebnis. Sie werden feststellen, dass die Übersicht so oder so gewiss nicht optimal ist. Und das Ganze mit einer PivotTable zu realisieren, bedarf es doch schon einiges an Aufwand. Außerdem finde ich es deutlich übersichtlicher, wenn in der ersten Spalte die Monatsnamen vermerkt sind, die Spalten der beiden Jahresergebnisse danach nebeneinander dargestellt werden und eine 4. Spalte (gerne auch in einer 5. Spalte) die prozentualen Differenzen aufgeführt werden.
Die Ausführung
Wechseln Sie zum Blatt Datenbasis und markieren Sie eine beliebige Zelle innerhalb der Daten. Um daraus eine Intelligente Tabelle zu machen, gehen Sie entweder über das Menü oder StrgL bzw. StrgT. Achten Sie darauf, dass im Dialog das Häkchen bei Tabelle hat Überschriften gesetzt ist. Falls oberhalb der Tabelle jetzt die Warnung auftaucht, dass Externe Datenverbindungen deaktiviert worden sind, können Sie diesen Hinweis entweder durch Aktivieren bestätigen oder aber die Zeile durch einen Klick auf das X ganz rechts zum Verschwinden bringen. Bevor Sie den nächsten Schritt machen, Klicken Sie auf den Inhalt des Feldes Tabellenname, dort hat Excel automatisch Tabelle1 eingetragen.
Wechseln Sie zum Menü Daten und Klicken Sie auf das Symbol Aus Tabelle/Bereich. Sofort öffnet sich (eventuell nach der Warnung, dass eine Verbindung zu einer externen Datenquelle hergestellt wird) der Power Query-Editor und die Abfrage hat automatisch den Namen Tabelle1 bekommen; diese Bezeichnung wurde aus dem Tabellennamen in Excel übernommen. Die Daten stellen sich hier so dar:
Die Daten aus dem Arbeitsblatt ‘Datenbasis’ direkt nach dem Import
Ihnen wird gewiss sofort auffallen, dass das Feld Datum Rechnung nicht nur das Datum sondern durchgängig auch die Uhrzeit 00:00:00 enthält. Da Sie ja grundsätzlich nur das reine Datum brauchen, werden Sie das auch gleich ändern. Dazu Klicken Sie zuerst einmal in die Überschrift Datum Rechnung. Die komplette Spalte wird hellgrün hinterlegt, die Überschrift in einem dunkleren Grünton. Im Menü Start Klicken Sie auf den Menüleisten-Eintrag Datentyp: Datum/Uhrzeit und im Dropdown wählen Sie Datum. Den Dialog Spaltentyp ändern quittieren Sie mit Aktuelle ersetzen. Sofort wird jede einzelne Zelle dieser Spalte auf den neuen Datentyp angepasst. Damit ist eine vernünftige Datenbasis geschaffen.
Im kommenden Schritt sollen ja irgendeiner Form die kalendarischen Daten 2019 und 2020 in getrennte Spalten übertragen werden. Und in Vorausschau auf die Bedingung, dass ja in den Spalten später auch die Jahreszahl als Überschrift gezeigt werden soll und natürlich auch die Monate in einer getrennten Spalte dargestellt werden, fügen Sie nun erst einmal je eine Spalte für den Monat und das Jahr ein. Gehen Sie dazu so vor:
- Markieren Sie die Spalte Datum Rechnung durch einen Klick in die Überschrift.
- Wechseln Sie zum Menü-Register Spalte hinzufügen.
- Klicken Sie im Menüband auf das Symbol Datum | Monat | Name des Monats. Sofort wird eine Spalte Monatsname mit dem ausgeschriebenen Monatsnamen des Datums dieser Zeile erstellt.
- Die Spalte mit den kalendarischen Daten ist noch markiert, wiederum ein Klick auf das Symbol Datum und wählen Sie dieses Mal Jahr | Jahr.
- Markieren Sie nun zuerst die Spalte Monatsname, Shift und dann Jahr.
- Immer noch im Menü Spalte hinzufügen wählen Sie im Menüband das Symbol für Spalten zusammenführen.
- Im Dialogfenster Spalten zusammenführen wählen Sie bei Trennzeichen das Leerzeichen und bei Neuer Spaltenname (optional) schreiben Sie statt Zusammengeführt den Text Monat Jahr.
- Ich räume ein, dass dieser letzte Schritt (beginnend mit ‘Monatsname und Jahr markieren) nicht unbedingt erforderlich ist, aber übungshalber finde ich das ganze nützlich. 😎
- Im nächsten Schritt werden Sie diese Abfrage duplizieren. Klicken Sie dazu in den Bereichen links der Abfrage entweder auf den Text Abfragen oder das Größer-Symbol >:
Durch einen Klick aud den Text erweitern Sie den Seitenstreifen zum Seitenfenster
- Der Seitenstreifen wird zu einem linken Seitenfenster; Rechtsklick auf den einzigen Eintrag Tabelle1 und wählen Sie im Kontextmenü Duplizieren.
- Der neue Eintrag Tabelle1 (2) ist markiert. F2 (alternativ Rechtsklick und dann Umbenennen) und vergeben sie den Namen Jahr 2019 für diese Abfrage.
- Anschließend werden sie die Abfrage Tabelle1 noch einmal duplizieren und dem Ergebnis dann den Namen Jahr 2020 geben.
- Erweitern Sie hier nun die Spalte Jahr durch einen Klick auf
und im Dialog müsste eigentlich auch das Jahr 2020 zur Auswahl stehen, denn dieses sollte das Jahr sein, welches sie gleich als einziges mit einem Häkchen versehen. Normalerweise wird sich Ihnen aber dieses Bild darstellen:
Das Aufrufungszeichen im gelben Dreieck ist beachtenswert!
- Klicken Sie auf den hierüber markierten Hinweis und sofort haben Sie auch den Eintrag für das Jahr 2020 zur Verfügung. Löschen Sie das Häkchen bei 2019 und anschließend OK.
- Wechseln Sie zur Abfrage Jahr 2019 und gehen Sie gleichermaßen vor, nur dass Sie hier das Häkchen bei 2020 entfernen. Um jeweils eine andere Abfrage auszuwählen, Klicken Sie im linken Seitenfenster einfach auf den entsprechenden Eintrag.
- Rechtsklick in die Überschrift der Spalte Monatsname und wählen Sie im Kontextmenü den Punkt Gruppieren nach…
So stellt sich der Dialog ‘Gruppieren nach’ anfangs dar
- Im Textfeld Neuer Spaltenname tragen Sie Monatsumsatz 2019 ein und bei Vorgang Klicken Sie auf Summe. Bei Spalte wird automatisch der Wert Betrag angezeigt, die Auswahl ist auch absolut korrekt. Bestätigen Sie mit OK. Das Ergebnis sollte überzeugen:
Die kumulierten Jahresumsätze des Jahres 2019 sind in der neuen Spalte eingefügt
- Es wird Sie nicht verwundern, dass Sie für die Abfragejahr 2020 gleichermaßen vorgehen. Selbstredend werden Sie als Überschrift Monatsumsatz 2020 wählen.
- Machen Sie sich klar, warum in der Abfrage Jahr 2019 12 Zeilen und in der Abfrage Jahr 2020 nur 4 Zeilen enthalten sind. Dass jeweils die Daten des einzelnen Monats zusammengefasst (kumuliert) worden sind, dürfte selbsterklärend sein.
Im Grunde genommen haben Sie jetzt alle Daten parat, die Sie zum Vergleich der Monate der beiden Jahre benötigen. Sie müssen „nur” noch dafür sorgen, dass die beiden Abfragen so zusammengefasst werden, dass die Monatsspalten nebeneinander stehen und anschließend die prozentuale Differenz berechnet wird. Dazu wechseln Sie erst einmal zur Abfrage Jahr 2019. Aktivieren Sie das Menü Start. Ein Klick auf die Schaltfläche Kombinieren und anschließend Abfragen zusammenführen. Es öffnet sich dieses Dialog-Fenster:
Der erste Schritt im Dialog ‘Zusammenführen’
Klicken Sie in das kleine Textfeld unterhalb der Tabelle Jahr 2019 und wählen Sie im Dropdown Jahr 2020. Im unteren größeren Textfeld wird nun eine Tabelle mit den Daten für das Jahr 2020 angezeigt.
Wenn Sie noch nie mit Datenbanken zu tun hatten, wird der folgende Teil vielleicht schwer zu verstehen sein. Sie müssen eine Verknüpfung, einen Link zwischen den Feldern der beiden Tabellen schaffen, wo die Inhalte gleichartig sind. Power Query wird dann vergleichen, ob die jeweils andere Tabelle eine Zelle mit identischem (also nicht nur gleichen) Inhalt hat. Identisch bedeutet: Gleiche Groß- Kleinschreibung und natürlich keine zusätzlichen (angehängten) Leerzeichen in eine der Zellen. Der Name der Überschrift kann sogar komplett anders sein. Klicken Sie im oberen als auch im unteren großen Fenster irgendwo in die Spalte Monatsname, damit diese beide markiert sind. Dadurch wird die gewünschte Verknüpfung hergestellt.
Den Eintrag ganz unten bei Join-Art lesen Sie bitte einmal durch und Sie werden feststellen, dass Sie tatsächlich alle Datensätze aus der ersten (oberen) Tabelle und jene Zeilen (Datensätze) aus der unteren Tabelle darstellen wollen, wo der Inhalt der verknüpften Spalte übereinstimmt. Das stellt sich nun so dar:
In diesem Staduium können Sie das Dialog-Fensters schließen
Überlegen Sie gerne noch einmal, ob der Satz „die Auswahl stimmt mit 4 von 12 Zeilen in der ersten Tabelle überein.” dem entsprechen könnte, was sie wünschen. Ja, das kommt hin, denn sie wollen Jahr alle 12 Datensätze aus dem Jahr 2019 und alle 4 existierenden und übereinstimmenden Datensätze aus dem Jahr 2020 zu einer neuen Tabelle vereinen. Also ein Klick auf OK. Das Ergebnis wird sich vielleicht überraschen:
Der durchgehende Eintrag ‘Table’ hat durchaus seine Richtigkeit
Es wurde zwar eine neue Spalte eingefügt, welche die Überschrift Jahr 2020 hat. Aber der Inhalt jeder einzelnen Zelle ist hier Table, also der US-Ausdruck für Tabelle. Das Erweitern-Symbol dieser Spalte ist ein Doppelpfeil 
, Klicken Sie darauf und es öffnet sich dieser Dialog:
Es soll nur ein einziges Häkchen erhalten bleiben
Entfernen Sie alle Häkchen (einschließlich Ursprünglichen Spaltennamen …) mit der Ausnahme von Monatsumsatz 2020. Schließen Sie das Fenster und das Ergebnis wird Sie überzeugen. Alle 12 Monatsnamen in der korrekten Reihenfolge, danach die Monatsumsätze der Jahre 2019 und 2020. Und das ist nun die ideale Voraussetzung, um die prozentuale Berechnung der Unterschiede vorzunehmen. Bei der Gelegenheit: Der Zellinhalt null bedeutet, dass die Zelle nach der Übertragung in eine Excel-Tabelle wirklich leer, ohne Inhalt ist.
Sie werden es ahnen, Sie brauchen wiederum eine neue Spalte. 😉 Also Spalte hinzufügen | Benutzerdefinierte Spalte und geben Sie bei Neuer Spaltenname beispielsweise den Text Abweichung ein. Im ersten Anlauf werden sie in den absoluten Prozentwert berechnen, also beispielsweise 110,00 %, wenn im Jahr 2020 ein 10% höherer Umsatz erzielt worden ist. Entsprechend natürlich 90%, wenn der Umsatz 10% niedriger war. Tragen Sie im großen Feld Benutzerdefinierte Spaltenformel nach dem Gleichheitszeichen diese Formel ein:
= [Monatsumsatz 2020]/[Monatsumsatz 2019]
Am einfachsten übernehmen Sie die beiden Spaltenbezeichnungen (Überschriften einschließlich der eckigen Klammern) durch einen Doppelklick im rechten Kasten Verfügbare Spalten. Schließen Sie das Fenster und sie werden erkennen, dass das Ergebnis stimmt. Sie können sogar bereits hier in Power Query dafür sorgen, dass die Formatierung als Prozent erfolgt. Dafür ein Rechtsklick in die Überschrift, Typ ändern | Prozentsatz und das sieht dann auch richtig gut aus. Hinweis: Wahrscheinlich wird dieses Format aber nicht automatisch in die später generierte Excel-Tabelle übernommen. Dort werden Sie dieses %-Format erneut einrichten.
Ich hatte schon angedeutet, dass eine weitere Spalte erstellt werden kann, wo die eigentliche Differenz in Prozent dargestellt wird. Dazu erstellen Sie wiederum eine Benutzerdefinierte Spalte, verwenden als Neuer Spaltenname beispielsweise Differenz Prozent und tragen diese Formel ein:
[Monatsumsatz 2020]/[Monatsumsatz 2019]-1
Auch hier wieder den Datentyp auf Prozentsatz ändern und das Ergebnis stimmt. – Die Aufgabe ist hiermit gelöst. Wenn Sie Ihr Ergebnis mit meinem vergleichen wollen, laden Sie die Datei hier von unserem Server herunter.
Ich hoffe, Sie konnten alles gut nachvollziehen. Falls ich mich an irgendeinem Punkt nicht deutlich genug, nicht eindeutig ausgedrückt habe, bitte ich um eine Nachricht. Es ist auch für mich schwer, mich immer wieder in jene Zeiten zu erinnern, wo ich Power Query gelernt habe und oft auch als unwissender Noob dagestanden habe. Immer wieder daran denken, dass Sie ja absolute Einsteiger in Sachen Power Query sind ist schon eine Herausforderung …
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,00 € freuen …