Auf den ersten Blick erschien mir die „Auf­gabe” des Fragestellers im Forum ganz ein­fach und ganz klar. Auf den 2. Blick merk­te ich dann aber rasch, dass die Lösung zwar neben kon­ven­tionellen Lösun­gen (per Formel) dur­chaus mit Pow­er Query erstellt wer­den kann, jedoch etwas mehr an Über­legung bedarf als es anfangs schien. Laden Sie sich erst mal hier die Muster-Datei herunter und analysieren dann die gegebe­nen Dat­en nach der hier als Orig­i­nal-Zitat (Forum) vor­ge­tra­ge­nen Zielset­zung:

Hal­lo an alle Excel­f­reaks,
ich bräuchte mal eure Hil­fe bei ein­er Auswer­tung.
Wie kann man in der ange­hängten Tabelle die Gesamt­summe der Ton­nage nach Mitar­beit­ern getren­nt auswerten.
Die Mitar­beit­er sind ein­mal in der ersten, zweit­en oder der drit­ten Schicht zu find­en.
Ich würde gerne die Gesamt­summe pro Monat und Mitar­beit­er ermit­teln.
Am lieb­sten per ADO oder zur Not über eine Formel

Eine gut nachvollziehbare PQ-Lösung

Wie fast immer im Excel-Leben führen mehrere Wege zum Ziel. Ich stelle Ihnen hier einen etwas aufwändi­geren dafür aber gewiss gut ver­ständlichen Weg vor und biete Ihnen im Anschluss auch noch eine deut­lich schlankere aber auch anspruchsvollere Lösung an. Genug der Vorrede, importieren sie erst ein­mal die vorhan­de­nen Dat­en in den Pow­er Query-Edi­tor. Das stellt sich dann so dar:

Die Dat­en direkt nach dem Import

Im ersten Schritt wer­den Sie über­legen, welche Spal­ten Sie (wirk­lich) brauchen und welche nicht. Ich habe die Spal­ten Datum, Monat, die 3 Namens-Spal­ten für die jew­eilige Schicht (Mitarbeiter_…) und die 3 Ton­nage-Spal­ten (Tonnage_…) markiert und die restlichen Spal­ten über Andere Spal­ten ent­fer­nen eli­m­iniert. Das geht übri­gens ganz gut, wenn sie die Spalte Datum in der Über­schrift markieren, Shift und in die Über­schrift der let­zten Spalte Klick­en; dann mit Strg in die Über­schriften KW, Jahr und Produkt_1Schicht Klick­en, damit die Markierung dieser Spal­ten aufge­hoben wird. Anschließend Recht­sklick in eine der markierten Über­schriften und Andere Spal­ten ent­fer­nen.

Im näch­sten Schritt ändere ich den Daten­typ der Spalte Datum zu (nur) Datum, denn derzeit ist ja noch die Zeit mit einge­tra­gen. Wählen Sie dazu den Weg, der ihnen am lieb­sten ist. Ich selb­st denke schon einen Schritt voraus, markiere die Spalte Datum und wech­se­le zum Reg­is­ter Trans­formieren. Im Menüband dann Datum | Monat | Monats­be­ginn was dazu führt, dass anschließend in jed­er Zeile der Spalte der 1. Tag des entsprechen­den Monats als Datum einge­tra­gen ist. Das erle­ichtert später die Sortierung und da ja nur der Monat aus­gew­ertet wer­den soll, ist dieses ein gut gang­bar­er Weg.

Der näch­ste Schritt ist eher für mein eigenes Wohlbefind­en gedacht als eine Notwendigkeit. Ich gebe dieser Abfrage einen neuen Namen: Raw­Da­ta und erstelle davon 3 Dup­likate. Nun benenne ich die Dup­likate um in Schicht 1, Schicht 2 und Schicht 3. Jet­zt markiere ich die Abfrage Raw­Da­ta, Reg­is­ter Datei | Schließen & laden in… | Nur Verbindung erstellen und dann Laden. Im recht­en Seit­en­fen­ster sind nun alle 4 Abfra­gen aufge­führt, jew­eils mit dem Ver­merk Nur Verbindung.

Öff­nen Sie die Abfrage Schicht 1. Markieren Sie die ersten 4 Spal­ten und löschen Sie die nicht markierten Spal­ten. In der Abfrage Schicht 2 belassen Sie die ersten bei­den Spal­ten sowie die Dat­en der Schicht 2; in der Abfrage Schicht 3 verbleiben neben Datum und Monat die bei­den let­zten Spal­ten. In jed­er dieser 3 Schicht-Abfra­gen sind jet­zt nur noch die rel­e­van­ten Dat­en enthal­ten.

Im kom­menden Schritt sollen diese Abfra­gen zusam­menge­führt, aneinan­derge­hängt wer­den. Dazu ist es wichtig, dass die Über­schriften der Spal­ten iden­tisch sind. Löschen Sie also in jed­er dieser Abfra­gen in der Über­schrift alles ab dem Unter­strich _, damit jew­eils nur Mitar­beit­er und Ton­nage ste­hen bleibt. Am Beispiel der Abfrage Schicht 1 stellt sich das nun so dar:

Beispiel der separi­erten Abfrage

Jet­zt fügen Sie die einzel­nen Abfra­gen zu ein­er einzi­gen Query zusam­men, welche alle Dat­en aller Schicht­en enthält. Dazu im Reg­is­ter Start ein Klick auf Kom­binieren, erweit­ern Sie die Auswahl Abfra­gen anfü­gen  und wählen dort Abfra­gen als neu anfü­gen aus. Aktivieren Sie die Option 3 oder mehr Tabellen, markieren im linken Kas­ten nacheinan­der Schicht 2 und Schicht 3 und entwed­er ein Dop­pelk­lick oder aber ein Klick auf Hinzuf… Anschließend OK. Automa­tisch wird eine neue Abfrage mit dem Namen Append1 erstellt und eine Kon­trolle wird Ihnen zeigen, dass dort alle Werte enthal­ten sind. Fil­tern Sie die Spalte Mitar­beit­er nun so, dass die Zeilen mit dem Wert (NULL) ent­fer­nt wer­den.

Sortieren Sie nun nach Datum auf­steigend und anschließend nach Mitar­beit­er eben­falls auf­steigend. Wenn Sie möcht­en, kön­nen Sie nun über Datei | Schließen & laden oder durch einen Klick auf das entsprechende Sym­bol im Reg­is­ter Start in einem neuen Arbeits­blatt eine Excel-Tabelle erstellen. Diese Tabelle kann nun ganz bequem als Daten­ba­sis für eine Piv­ot­Table dienen. Das Feld Datum wer­den sie dabei naturgemäß nicht mit anzeigen lassen. Das sähe dann beispiel­sweise so aus:

Beispiel­hafte Piv­ot­Table

▲ nach oben …

Für Übungszwecke wer­den sie nun eine son­st nicht ger­ade geliebte Redun­danz schaf­fen. Erstellen Sie ein Dup­likat der Abfrage Append1. Sie kön­nen auch direkt in Pow­er Query ohne den Umweg über die Piv­ot­ta­belle eine Auswer­tung nach den geforderten Kri­te­rien erstellen. Markieren Sie dazu die ersten 3 Spal­ten der Abfrage Append1 (2) und im Menüband wählen Sie Grup­pieren nach. Als Neuer Spal­tenname bietet sich Ton­nage an, bei Vor­gang wählen Sie Summe und die sollen naturgemäß aus der Spalte Ton­nage gebildet wer­den. Ihr Dia­log stellt sich nun so dar:

Der aus­ge­füllte Dia­log für die Grup­pierung

Eigentlich sollte die Sortierung nun so sein, wie es meinen Vorstel­lun­gen entspricht. Aber wenn sie genau hin­se­hen wer­den Sie in Zeile 13 und Zeile 14 einen Aus­reißer erken­nen und die Mitar­beit­er sind auch nicht wirk­lich nach Alpha­bet sortiert. Darum sortieren sie nun nach Datum und anschließend nach Mitar­beit­er jew­eils Auf­steigend. Das sieht schon ein­mal sehr gut aus. Löschen Sie jet­zt noch die Spalte Datum, sie wird nicht mehr gebraucht und würde das Bild durch den jew­eili­gen Monat­ser­sten nur ver­fälschen bzw. irri­tieren. Wenn Ihnen diese Darstel­lungsweise reicht, kön­nen Sie jet­zt auf Schließen & laden Klick­en und der Job wäre been­det.

▲ nach oben …

Direkt zur Kreuztabelle

Etwas „schick­er” geht es auf diese Weise:

• Markieren Sie die Spalte Monat
• Wech­seln Sie zum Reg­is­ter Trans­formieren
• Wählen Sie im Menüband den Punkt Piv­otieren   (nicht ent­piv­otieren)
• Bei Wertes­palte wählen Sie Ton­nage
• Klick­en Sie auf Erweit­erte Optio­nen und acht­en Sie darauf, dass dort Summe ste­ht.
• Been­den Sie den Dia­log mit OK.

Diese Darstel­lungsweise gefällt mir um einiges bess­er und ist auch über­sichtlich­er, wenn weit­ere Monate dazu kom­men. Schließen & laden und sie wer­den wahrschein­lich noch die Spal­ten mit den Ton­nage-Werten so for­matieren, dass stets 1 oder 2 Nachkom­mas­tellen angezeigt wer­den und vielle­icht auch als Benutzerdefiniertes Zahlen­for­mat ein ” to” anhän­gen:

Die fer­tig erstellte Tabelle in Excel

Die fer­tige Datei kön­nen Sie hier von unserem Serv­er herun­ter­laden. Ver­gle­ichen Sie Ihr eigenes Werk mit dem, was ich erstellt habe. Und damit ist diese Auf­gabe ‑was das Stich­wort Pow­er Query bet­rifft- erledigt.

Elegant und professionell

Ich habe zu Beginn dargelegt, dass es weit­ere Lösungswege gibt. Der eben dargelegte Weg ist durch die Redun­danzen vielle­icht nicht der schnell­ste und erscheint gewiss auch etwas umständlich, holperig. Dafür aber ist er auch für Ein­steiger gut nachvol­lziehbar und glänzt dadurch, dass keine einzige Formel zum Ein­satz gekom­men ist. 😎 

Ein dur­chaus ele­gan­ter Weg arbeit­et mit Formeln der Sprache M und ist aus mein­er Sicht auch wegen der fehlen­den Redun­danzen ein­fach „sauber­er”. Wenn Sie daran Inter­esse haben, schreiben Sie mir eine Mail über diesen Link und Sie erhal­ten für eine kleine Spende die Datei mit dem eben ange­sproch­enen Weg sowie auf Wun­sch ein­er Beschrei­bung, die ähn­lich wie dieser Beitrag hier aufge­baut ist. Und um Such­maschi­nen nicht zu „irri­tieren” brauchen Sie ein Pass­wort für den ver­Link­ten Beitrag zum The­ma „Spende”: $-loe­sung. Hin­weis: Auch wenn Sie nicht inner­halb 24 Stun­den eine Antwort haben, ich antworte bes­timmt!

▲ nach oben …

Ref­er­ence: #0592