Umsatz-Liste zu Kreuztabelle mit Power Query (1)

Foren-Q&A: Umsatz-Analyse mit Power Query und transponieren der Daten (Teil 1)


Video auf YouTubeHinweis:
Die Arbeitsschritte dieses Beitrages sind in einem kleinen Video dokumentiert. Das unterstützende Begleit-Video finden Sie auf YouTube an dieser Stelle.

Beachten Sie bitte: Dieses Begleit-Video enthält zusätzliche Informationen, die im folgenden Beitrag nicht dokumentiert sind!


Eine Aufgabe, die mir anfangs recht simpel schien, erweiterte sich rasch zu einem kleinen Projekt. Gegeben waren verschiedene Produkte, die an unterschiedlichen kalendarischen Daten verkauft worden sind. Diese Liste ist formgerecht zeilenweise aufgebaut:

Die Roh-Daten für die Analyse

Die Roh-Daten für die Analyse

Sie erkennen, dass in Spalte A die unterschiedlichen Produkte, in Spalte B ein Datum und in Spalte C der jeweils passenden Monat mit einem Kürzel dargestellt ist. Allerdings im Gegensatz zu der typischen Schreibweise des Excel-Zahlenformats hier in Großbuchstaben. – Im Tabellenblatt Auswertung sehen Sie die Wunschvorstellung des Fragestellers:

Die endgültige Ziel-Vorstellung des Fragestellers

Die endgültige Ziel-Vorstellung des Fragestellers im Forum

Da exakt diese Darstellung doch einen höheren Aufwand bedeutet, habe ich das Projekt in 3 kleinere Teile gesplittet. Im 1. Teil (diesem Beitrag) werden nur die in der Liste aufgeführten Monate mit den vorgegebenen Überschriften (gekürzten Monatsnamen) berücksichtigt, der 2. Teil zeigt dann zwar alle Monate aber mit den ausgeschriebenen Namen der Monate sowie einer kleinen Hilfstabelle in Excel und im letzten Teil*) kommen sie ohne diese Hilfstabelle aus und können bei der Spaltenbezeichnung zwischen den 3-stelligen und den langen Monatsnamen wählen.

*) Diesen Teil werde ich nicht offen im Blog anbieten. Die Basis-Funktionalität habe ich ihnen in den 1. beiden Beiträgen dieses Themas dargelegt und prinzipiell sollte das auch zielführend sein. Lesen Sie hier mehr zu der Begründung, warum manche aufwendig recherchierten und verarbeiteten Beiträge auch einen Beitrag von ihrer Seite erfordern. Ein weiterer Beitrag zum Thema: hier; der Beitrag ist allerdings (zwecks Prävention vor emotionalen Angriffen) auch Passwort-geschützt, Sie bekommen den Zugang aber ohne „dumme Nachfragen“ auf Ihre Anfrage. 😎 

▲ nach oben …

Kurz, knapp, (relativ) schnell und effektiv

Einer der Grundsätze in Excel heißt: „Form follows function“ was prinzipiell bedeutet, dass die Funktionalität und natürlich das Ergebnis selber höher zu bewerten ist als das Aussehen. Dem stimme ich voll und ganz zu. Darum wird auch in dieser ersten Übung nicht alles berücksichtigt, was als Wunsch vorgetragen worden ist. Die hauptsächliche Einschränkung: Es werden nur die Monate dargestellt, die auch in der Liste des Blattes Produkte aufgeführt sind.

Beginnen Sie damit, dass sie den Cursor irgendwo in die Liste des Blattes Produkte bewegen und anschließend StrgL oder StrgT betätigen, um aus den Daten eine Formatierte Tabelle zu erstellen. Je nach Excel-Version werden sie nun diese Liste entweder über den Menüpunkt Power Query oder Daten nach Power Query importieren, indem sie auf Von bzw. Aus Tabelle anwählen. Im Abfrage-Editor stellen sich die Daten nun so dar:

Direkt nach dem Import: Die Daten

Direkt nach dem Import: Die Daten

Um möglichst effektiv zu arbeiten, können Sie die Überschriften so belassen wie sie sind. Klicken Sie nun zuerst in die Überschrift Produkt, Strg und dann ein Klick in die Überschrift Spalte1. Da sie sich ja immer noch im Menü Start befinden, direkt ein Klick auf die Schaltfläche Gruppieren nach. Übernehmen Sie alle vorgegebenen Einstellungen und nach einem Klick auf OK stellt sich das ganze so dar:

Die gruppierten Daten mit der Anzahl der verkauften Einheiten

Die gruppierten Daten mit der Anzahl der verkauften Einheiten

Markieren Sie die Spalte mit den Monatskürzeln, indem sie in die Überschrift Spalte1 klicken. Wechseln Sie nun zum Menü Transformieren. In der Gruppe Beliebige Spalte klicken Sie auf das Symbol für Pivotieren:

Etwas versteckt: Der Menüpunkt Pivotieren

Etwas versteckt: Der Menüpunkt Pivotieren

Der folgende Dialog sieht so aus:

Der Dialog für das Pivotieren der Daten

Der Dialog für das Pivotieren der Daten

Ich habe mir angewöhnt, den Text immer wieder genau durchzulesen, um zum gewünschten Ergebnis zu kommen. Hier steht unscheinbar aber ganz klar: Verwenden Sie die Namen in Spalte "Spalte1" zum Erstellen neuer Spalten. Also: Die Spalte mit dem Namen Spalte1 war markiert und diese Inhalte werden nun als Überschrift für die Kreuztabelle verwendet. Die Bezeichnung „Wertespalte“ ist vielleicht nicht ganz so eingängig, aber wenn sie einem Moment auf das (i) neben Wertespalte zeigen, wird ein erklärender Text eingeblendet. – In den reinen Datenbereich sollen ja jeweils die Werte der Spalte Anzahl eingetragen werden, darum wählen Sie im Kombinationsfeld die Zeile mit Anzahl aus. Nun ein Klick auf Erweiterte Optionen und es wird ein Kombinationsfeld für die Aggregatwertfunktion eingeblendet. Hier wählen Sie statt Summe die unterste Auswahl Nicht aggregieren. Nach einem Klick auf OK ist prinzipiell das Wunschergebnis schon erreicht:

Vorschau des Ergebnisses im Editor

Vorschau des Ergebnisses im Editor

Wenn Sie an dieser Stelle über Start | Schließen & laden gehen, haben Sie ein solides Ergebnis der gegebenen Daten als Kreuztabelle. Dass die Felder mit dem Inhalt null leer sind, werden sie wahrscheinlich aus anderen Übungen mit Power Query kennen. Wenn Sie möchten, dass die Felder nicht leer sind, sondern dass dort die Zahl 0 drin steht, dann öffnen Sie noch einmal den Abfrage-Editor, markieren die Spalten mit den Monaten und (beispielsweise über Rechtsklick) Werte ersetzen…null durch 0 und das Ergebnis für die Kreuztabelle mit den Kurznamen für die Monate ist optimal:

Das Endergebnis für diese Kurzdarstellung

Das Endergebnis für diese Kurzdarstellung

▲ nach oben …

2. Teil Alle Monate (mit Hilfs-Tabelle)
3. Teil Alle Monate, Monatsdarstellung nach Auswahl, ohne Hilfs-Tabelle

Reference: #6292

Dieser Beitrag wurde unter Allgemein, Verschiedenes, Datum & Zeit, Foren-Q&A, Power Query, Spalten bearbeiten, Transponieren abgelegt und mit , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.