Hin­weis:
Die Arbeitss­chritte dieses Beitrages sind in einem kleinen Video doku­men­tiert. Das unter­stützende Begleit-Video find­en Sie auf YouTube an dieser Stelle.

Beacht­en Sie bitte: Dieses Begleit-Video enthält zusät­zliche Infor­ma­tio­nen, die im fol­gen­den Beitrag nicht doku­men­tiert sind!

Auch wenn jed­er dieser 3 Teile in sich abgeschlossen ist, werde ich mich in den ersten Zeilen dieses 2. Teils etwas knap­per fassen; zumal hier auch die Basis­dat­en schon etwas auf­bere­it­et sind. Den­noch … Es sind die gle­ichen Dat­en; teils gekürzt, teils ergänzt. Laden Sie diese ein­fach ein­mal hier herunter. Was in diesem File kom­plett fehlt ist das Arbeits­blatt mit dem Muster, wie es anschließend nach der Fer­tig­stel­lung sein sollte. Da kön­nten Sie zum Ver­gle­ich den vorheri­gen Beitrag zum The­ma öff­nen oder Sie glauben mir ein­fach, dass das Ergeb­nis stimmt. 😉

Im Blatt-Reg­is­ter Roh-Dat­en fällt Ihnen gewiss auf, dass hier im Ver­gle­ich zur vorheri­gen Ver­sion nur 2 Spal­ten vorhan­den sind, es fehlen also die Monat­sna­men in der Kurz­schreib­weise. Diese Änderung habe ich bewusst vorgenom­men.

Und im 2. Arbeits­blatt, welch­es hier den Namen Monate hat, habe ich bere­its 2 Tabellen vor­bere­it­et. Bei­de enthal­ten die Monat­sna­men, ein­mal in Lang- und ein­mal in Kurz­schreib­weise. Und jede dieser bei­den Tabellen hat auch eine sin­nvolle, eine „sprechende” Bezeichnung/Überschrift.

Apro­pos Monat­sna­men: In dieser Übung habe ich nur die Kurz­form mit den 3 Zeichen ver­wen­det. Für Ihre per­sön­lichen Bedürfnisse kön­nen Sie natür­lich prob­lem­los auch die andere Tabelle mit den aus­geschriebe­nen Namen ver­wen­den.

Zu Beginn wer­den sie naturgemäß die Tabelle mit den Roh-Dat­en in den Abfrage-Edi­tor importieren. Je nach Ver­sion geht das über den Menüpunkt Pow­er Query oder Dat­en. Und natür­lich holen sie sich die Dat­en aus ein­er Tabelle. Das Ergeb­nis stellt sich anschließend so dar:

Die Dat­en im Edi­tor nach dem Import

Gle­ich zu Beginn wech­seln Sie in das Menü Spalte hinzufü­gen und erstellen eine Benutzerdefinierte Spalte. Als Neuer Spal­tenname ver­wen­den Sie vorzugsweise (nur) M, weil in dieser Spalte die Monat­szahl numerisch ohne führende Null dargestellt wer­den wird. Per Tab oder Mausklick kom­men sie in den Bere­ich Benutzerdefinierte Spal­tenformel und geben Sie dort nach dem fest einge­fügten = fol­gende Formel ein: Date.Month([Datum]) wobei sie auf die exak­te Groß- Klein­schrei­bung acht­en müssen. Beim Feld­na­men [Datum] kön­nen Sie sich die Arbeit etwas erle­ichtern, indem sie in der recht­en Box einen Dop­pelk­lick auf genau dieses Ele­ment durch­führen, der Wert wird dann automa­tisch mit den eck­i­gen Klam­mern in die Formel einge­fügt.

Bestäti­gen Sie mit OK und umge­hend wird die numerische Beze­ich­nung des Monats aus der Spalte Datum in die Spalte M einge­tra­gen.

Sie erin­nern sich vielle­icht, dass in der zu erzeu­gen­den Kreuzta­belle in jede Monats-Spalte die Anzahl der Aufträge für den entsprechen­den Monat einge­tra­gen wer­den soll. Da in der Vor­lage (den Orig­i­nal­dat­en) kein­er­lei Zahl für das jew­eilige Pro­dukt ver­merkt war, habe ich ein­fach fest­gelegt, dass es immer genau ein Stück ist. Schließlich stand auch in der Ergeb­nis-Vor­lage immer nur die 1 bzw. 0. Gener­ieren Sie nun eine weit­ere Benutzerdefinierte Spalte, ver­wen­den als Über­schrift beispiel­sweise Zäh­ler und als Benutzerdefinierte Formel schreiben Sie nur die Zahl 1 in das Feld. Damit bekommt jed­er Auf­trag, jede Zeile in dieser Spalte automa­tisch den Wert 1.

Damit ist die erste Abfrage prinzip­iell fer­tig gestal­tet. Wech­seln Sie zum Menü Datei | Schließen & laden in… und im Dia­log Klick­en Sie auf Nur Verbindung erstellen. Anschließend Laden. Damit erre­ichen Sie, dass diese Abfrage nicht noch ein­mal in einem getren­nten Excel-Tabel­len­blatt als Tabelle geschrieben wird und damit unnötige Redun­danzen erzeugt wer­den.

Wech­seln Sie nun in das Arbeits­blatt Monate und importieren Sie die Tabelle mit den Kurzbeze­ich­nun­gen der Monate. Im Abfrage-Edi­tor sind nun alle 12 Monate in der kor­rek­ten Rei­hen­folge untere­inan­der aufge­führt. Um sie später auch Pow­er Query kor­rekt sortieren zu kön­nen, bedarf es ein­er kleinen „Krücke”: Spalte hinzufü­gen und erweit­ern Sie in der Gruppe All­ge­mein den Punkt Indexs­palte durch einen Klick auf das kleine Dreieck ▼. Wählen Sie nun im Drop­down-Menü den Punkt Von 1, damit wird jedem Monat auf die ein­fache Art und Weise der kor­rek­te numerische Werte zugewiesen.

Was mich an dieser Stelle noch stört ist der durch Excel vergebene Name der Tabelle und damit auch der Name der Abfrage. Ändern Sie ihn von Tabelle1 auf beispiel­sweise Monate. Danach sich­ern Sie den Stand der Dinge und nach Schließen & laden sind sie erst ein­mal wieder im Arbeits­blatt Monate. 

Zugegeben, das spe­ich­ern hätte eben und an der Stelle noch nicht sein müssen. Aber eine kleine Sicher­heits­maß­nahme kann nie schaden. 💡 Öff­nen Sie nun wiederum die Abfrage Monate und Klick­en Sie in der Menüleiste auf das Sym­bol Kom­binieren. Danach bitte gle­ich Abfra­gen zusam­men­führen auswählen. Es öffnet sich ein Dia­log, der sich so darstellt:

Klick­en Sie in das noch leere Kom­bi­na­tions­feld ober­halb der Box Es ist keine Vorschau ver­füg­bar und wählen Sie im Drop­Down des Kom­bi­na­tions­feldes anschließend die Abfrage Raw­Da­ta. Sie sehen, dass sofort in der unteren Hälfte des Dialogs die ersten Zeilen dieser Query sicht­bar sind.

Der näch­ste Schritt ist für Ein­steiger immer etwas abstrakt, muss aber sehr genau durchge­führt wer­den. In bei­den Abfra­gen gibt es eine Spalte, die als Verknüp­fung des jew­eili­gen Ele­ments fungieren muss. Es müssen die gleich(artig)en Dat­en sein, welche sie per Klick in die Dat­en ein­er Spalte markieren. Beispiel­sweise immer die Kun­den­num­mern, wobei die Beze­ich­nung die Spalte (Über­schrift) dur­chaus unter­schiedlich sein kann.

Ich weiß, dass hört sich ziem­lich ver­schroben an. 😳 Wenn Sie ein­mal die Spal­tenna­men außer Acht lassen, dann hil­ft Ihnen vielle­icht dieser Hin­weis: In der Abfrage Raw­Da­ta ste­ht in der Zeile Produkt1 eine 3 in der Spalte M, weil dieser Wert ja durch Pow­er Query aus dem Datum der Spalte Datum berech­net wor­den ist. Wenn Sie nun den Wert 3 in der oberen Hälfte des Dialogs suchen wer­den Sie fest­stellen, dass dort Mrz in der Spalte links daneben ste­ht. Und das passt dann logis­cher­weise auch. Darum ein Klick in die Dat­en bei Index und ein Klick in die Dat­en der Spalte M, weil in jed­er dieser Spal­ten der numerische Monatswert ste­ht. Bei­de sind dann entsprechend markiert und im Fußbere­ich des Dialogs erken­nen sie den Hin­weis, dass die Auswahl mit 7 der ersten 12 Zeilen übere­in­stimmt:

Schließen Sie nun diesen Dia­log und wieder im Abfrage-Edi­tor erweit­ern Sie die Spalte Raw­Da­ta durch einen Klick auf den Dop­pelpfeil . Im Dia­log ent­fer­nen Sie erst ein­mal das Häkchen bei (Alle Spal­ten auswählen) und auch bei Ursprünglichen Spal­tenna­men … Danach set­zen Sie jew­eils das Häkchen bei Pro­dukt, M und bei Zäh­ler. Auf den ersten Blick sieht das Ergeb­nis vielle­icht etwas unko­or­diniert aus:

Alle Monate in schein­bar unge­ord­neter Rei­hen­folge

Sie erin­nern sich, in der Spalte Index ste­ht ja eigentlich der numerische Wert des Monats. Darum wer­den sie als erstes diese Spalte auf­steigend sortieren. Und schon sind die Monate in der 1. Spalte nach ihrer logis­chen Rei­hen­folge sortiert. Warum dieser Umweg? Excel selb­st ist zwar in der Lage, die Monatskürzel kor­rekt zu sortieren, Pow­er Query ist dazu jedoch nicht fähig. – Die Spal­ten Index und M wer­den jet­zt nicht mehr gebraucht, löschen Sie diese.

▲ nach oben …

Jet­zt kommt die 100.000 € Frage: Welche Ein­träge sollen in der Kreuzta­belle als Über­schrift erscheinen? Diese Spalte markieren Sie durch einen Klick in die entsprechende Über­schrift. Und ich denke, sie haben bei MMM gek­lickt. 🙂  Gut so, denn jet­zt wech­seln Sie zum Menü Trans­formieren und suchen in der Gruppe Beliebige Spalte das Sym­bol für Piv­otieren (Achtung: nicht Ent­piv­otieren):

Etwas ver­steckt: der Menüpunkt Piv­otieren

Umge­hend tut sich ein neuer Dia­log auf, wo sie doch die eine oder andere Verän­derung vornehmen müssen:

Ger­ade in solchen nicht so oft angewen­de­ten Funk­tion­al­itäten schaue ich schon ein­mal genau hin und habe mir auch angewöh­nt, Texte immer wieder genau durchzule­sen, um zum gewün­scht­en Ergeb­nis zu kom­men. Hier ste­ht unschein­bar aber ganz klar: Ver­wen­den Sie die Namen in Spalte "MMM" zum Erstellen neuer Spal­ten. Also: Die Spalte mit dem Namen MMM hat­ten Sie ja bere­its markiert und diese Inhalte wer­den nun als Über­schrift für die Kreuzta­belle ver­wen­det. Die Beze­ich­nung Wertes­palte ist vielle­icht nicht ganz so eingängig, aber wenn sie einem Moment auf das (i) neben Wertes­palte zeigen, wird ein erk­lären­der Text einge­blendet.

In den reinen Daten­bere­ich sollen ja jew­eils die Werte der Spalte Zäh­ler einge­tra­gen wer­den, darum wählen Sie im Kom­bi­na­tions­feld die Zeile mit Zäh­ler aus. Nun noch ein Klick auf Erweit­erte Optio­nen und es wird ein Kom­bi­na­tions­feld für die Aggre­gatwert­funk­tion einge­blendet. Hier wählen Sie statt Summe die unter­ste Auswahl Nicht aggregieren. Nach einem Klick auf OK ist prinzip­iell das Wun­schergeb­nis schon erre­icht:

Das sieht schon fast fer­tig aus. Allerd­ings stört noch etwas …

Zunächst bin ich froh, dass die Abfrage und damit auch die kün­ftige Tabelle ein vernün­ftiges Ausse­hen hat und auch in der Form ein­er Kreuzta­belle vor­liegt. Beim genauen hin­se­hen fällt mir aber auf, dass die Zeile 1 abso­lut leer ist. Meine erste Idee: Das ist die Zeile für Produkt6, wo ganz offen­sichtlich nichts verkauft wor­den ist. Als ich aber in den Roh-Dat­en nachge­se­hen habe merk­te ich, dass es Produkt6 über­haupt nicht gibt. Also war das ein Holzweg. Wie auch immer, das muss kor­rigiert wer­den. Der vielle­icht beste Weg: Start | Zeilen ver­ringern | Zeilen ent­fer­nen | Erste Zeilen ent­fer­nen und im Dia­log dann die 1 für die eine zu ent­fer­nende Zeile ein­tra­gen. Alter­na­tiv hät­ten sie natür­lich auch die Spalte Pro­dukt so fil­tern kön­nen, dass der Wert (NULL) demarkiert wird. Der Effekt ist der gle­iche. Und das Ergeb­nis sieht doch wirk­lich gut aus:

Der Lohn der Mühe 😉

Ich empfinde es als über­sichtlich­er, wenn die derzeit leeren Zellen auch leer bleiben. Denn der Fragesteller hat­te in seinem Beispiel in diese Zellen immer eine 0 einge­fügt; das zu real­isieren wäre aber auch kein Prob­lem, ein­fach in der Abfrage lösen (siehe Teil 1 oder im Video).