PQ: Auswertung pro Monat je Mitarbeiter

Foren-Q&A: Tonnage pro Monat je Mitarbeiter

Auf den ersten Blick erschien mir die „Aufgabe“ des Fragestellers im Forum ganz einfach und ganz klar. Auf den 2. Blick merkte ich dann aber rasch, dass die Lösung zwar neben konventionellen Lösungen (per Formel) durchaus mit Power Query erstellt werden kann, jedoch etwas mehr an Überlegung bedarf als es anfangs schien. Laden Sie sich erst mal hier die Muster-Datei herunter und analysieren dann die gegebenen Daten nach der hier als Original-Zitat (Forum) vorgetragenen Zielsetzung:

Hallo an alle Excelfreaks,
ich bräuchte mal eure Hilfe bei einer Auswertung.
Wie kann man in der angehängten Tabelle die Gesamtsumme der Tonnage nach Mitarbeitern getrennt auswerten.
Die Mitarbeiter sind einmal in der ersten, zweiten oder der dritten Schicht zu finden.
Ich würde gerne die Gesamtsumme pro Monat und Mitarbeiter ermitteln.
Am liebsten 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ändigeren dafür aber gewiss gut verständlichen Weg vor und biete Ihnen im Anschluss auch noch eine deutlich schlankere aber auch anspruchsvollere Lösung an. Genug der Vorrede, importieren sie erst einmal die vorhandenen Daten in den Power Query-Editor. Das stellt sich dann so dar:

Die Daten direkt nach dem Import

Die Daten direkt nach dem Import

Im ersten Schritt werden Sie überlegen, welche Spalten Sie (wirklich) brauchen und welche nicht. Ich habe die Spalten Datum, Monat, die 3 Namens-Spalten für die jeweilige Schicht (Mitarbeiter_…) und die 3 Tonnage-Spalten (Tonnage_…) markiert und die restlichen Spalten über Andere Spalten entfernen eliminiert. Das geht übrigens ganz gut, wenn sie die Spalte Datum in der Überschrift markieren, Shift und in die Überschrift der letzten Spalte klicken; dann mit Strg in die Überschriften KW, Jahr und Produkt_1Schicht klicken, damit die Markierung dieser Spalten aufgehoben wird. Anschließend Rechtsklick in eine der markierten Überschriften und Andere Spalten entfernen.

Im nächsten Schritt ändere ich den Datentyp der Spalte Datum zu (nur) Datum, denn derzeit ist ja noch die Zeit mit eingetragen. Wählen Sie dazu den Weg, der ihnen am liebsten ist. Ich selbst denke schon einen Schritt voraus, markiere die Spalte Datum und wechsele zum Register Transformieren. Im Menüband dann Datum | Monat | Monatsbeginn was dazu führt, dass anschließend in jeder Zeile der Spalte der 1. Tag des entsprechenden Monats als Datum eingetragen ist. Das erleichtert später die Sortierung und da ja nur der Monat ausgewertet werden soll, ist dieses ein gut gangbarer Weg.

Der nächste Schritt ist eher für mein eigenes Wohlbefinden gedacht als eine Notwendigkeit. Ich gebe dieser Abfrage einen neuen Namen: RawData und erstelle davon 3 Duplikate. Nun benenne ich die Duplikate um in Schicht 1, Schicht 2 und Schicht 3. Jetzt markiere ich die Abfrage RawData, Register Datei | Schließen & laden in… | Nur Verbindung erstellen und dann Laden. Im rechten Seitenfenster sind nun alle 4 Abfragen aufgeführt, jeweils mit dem Vermerk Nur Verbindung.

Öffnen Sie die Abfrage Schicht 1. Markieren Sie die ersten 4 Spalten und löschen Sie die nicht markierten Spalten. In der Abfrage Schicht 2 belassen Sie die ersten beiden Spalten sowie die Daten der Schicht 2; in der Abfrage Schicht 3 verbleiben neben Datum und Monat die beiden letzten Spalten. In jeder dieser 3 Schicht-Abfragen sind jetzt nur noch die relevanten Daten enthalten.

Im kommenden Schritt sollen diese Abfragen zusammengeführt, aneinandergehängt werden. Dazu ist es wichtig, dass die Überschriften der Spalten identisch sind. Löschen Sie also in jeder dieser Abfragen in der Überschrift alles ab dem Unterstrich _, damit jeweils nur Mitarbeiter und Tonnage stehen bleibt. Am Beispiel der Abfrage Schicht 1 stellt sich das nun so dar:

Beispiel der separierten Abfrage

Beispiel der separierten Abfrage

Jetzt fügen Sie die einzelnen Abfragen zu einer einzigen Query zusammen, welche alle Daten aller Schichten enthält. Dazu im Register Start ein Klick auf Kombinieren, erweitern Sie die Auswahl Abfragen anfügen  und wählen dort Abfragen als neu anfügen aus. Aktivieren Sie die Option 3 oder mehr Tabellen, markieren im linken Kasten nacheinander Schicht 2 und Schicht 3 und entweder ein Doppelklick oder aber ein Klick auf Hinzuf… Anschließend OK. Automatisch wird eine neue Abfrage mit dem Namen Append1 erstellt und eine Kontrolle wird Ihnen zeigen, dass dort alle Werte enthalten sind. Filtern Sie die Spalte Mitarbeiter nun so, dass die Zeilen mit dem Wert (NULL) entfernt werden.

Sortieren Sie nun nach Datum aufsteigend und anschließend nach Mitarbeiter ebenfalls aufsteigend. Wenn Sie möchten, können Sie nun über Datei | Schließen & laden oder durch einen Klick auf das entsprechende Symbol im Register Start in einem neuen Arbeitsblatt eine Excel-Tabelle erstellen. Diese Tabelle kann nun ganz bequem als Datenbasis für eine PivotTable dienen. Das Feld Datum werden sie dabei naturgemäß nicht mit anzeigen lassen. Das sähe dann beispielsweise so aus:

Beispielhafte PivotTable

Beispielhafte PivotTable


▲ nach oben …

Für Übungszwecke werden sie nun eine sonst nicht gerade geliebte Redundanz schaffen. Erstellen Sie ein Duplikat der Abfrage Append1. Sie können auch direkt in Power Query ohne den Umweg über die Pivottabelle eine Auswertung nach den geforderten Kriterien erstellen. Markieren Sie dazu die ersten 3 Spalten der Abfrage Append1 (2) und im Menüband wählen Sie Gruppieren nach. Als Neuer Spaltenname bietet sich Tonnage an, bei Vorgang wählen Sie Summe und die sollen naturgemäß aus der Spalte Tonnage gebildet werden. Ihr Dialog stellt sich nun so dar:

Der ausgefüllte Dialog für die Gruppierung

Der ausgefüllte Dialog für die Gruppierung

Eigentlich sollte die Sortierung nun so sein, wie es meinen Vorstellungen entspricht. Aber wenn sie genau hinsehen werden Sie in Zeile 13 und Zeile 14 einen Ausreißer erkennen und die Mitarbeiter sind auch nicht wirklich nach Alphabet sortiert. Darum sortieren sie nun nach Datum und anschließend nach Mitarbeiter jeweils Aufsteigend. Das sieht schon einmal sehr gut aus. Löschen Sie jetzt noch die Spalte Datum, sie wird nicht mehr gebraucht und würde das Bild durch den jeweiligen Monatsersten nur verfälschen bzw. irritieren. Wenn Ihnen diese Darstellungsweise reicht, können Sie jetzt auf Schließen & laden klicken und der Job wäre beendet.

▲ nach oben …

Direkt zur Kreuztabelle

Etwas „schicker“ geht es auf diese Weise:

  • Markieren Sie die Spalte Monat
  • Wechseln Sie zum Register Transformieren
  • Wählen Sie im Menüband den Punkt Pivotieren   (nicht entpivotieren)
  • Bei Wertespalte wählen Sie Tonnage
  • Klicken Sie auf Erweiterte Optionen und achten Sie darauf, dass dort Summe steht.
  • Beenden Sie den Dialog mit OK.

Diese Darstellungsweise gefällt mir um einiges besser und ist auch übersichtlicher, wenn weitere Monate dazu kommen. Schließen & laden und sie werden wahrscheinlich noch die Spalten mit den Tonnage-Werten so formatieren, dass stets 1 oder 2 Nachkommastellen angezeigt werden und vielleicht auch als Benutzerdefiniertes Zahlenformat ein “ to“ anhängen:

Die fertig erstellte Tabelle in Excel

Die fertig erstellte Tabelle in Excel

Die fertige Datei können Sie hier von unserem Server herunterladen. Vergleichen Sie Ihr eigenes Werk mit dem, was ich erstellt habe. Und damit ist diese Aufgabe -was das Stichwort Power Query betrifft- erledigt.

Elegant und professionell

Ich habe zu Beginn dargelegt, dass es weitere Lösungswege gibt. Der eben dargelegte Weg ist durch die Redundanzen vielleicht nicht der schnellste und erscheint gewiss auch etwas umständlich, holperig. Dafür aber ist er auch für Einsteiger gut nachvollziehbar und glänzt dadurch, dass keine einzige Formel zum Einsatz gekommen ist. 😎 

Ein durchaus eleganter Weg arbeitet mit Formeln der Sprache M und ist aus meiner Sicht auch wegen der fehlenden Redundanzen einfach „sauberer“. Wenn Sie daran Interesse haben, schreiben Sie mir eine Mail über diesen Link und Sie erhalten für eine kleine Spende die Datei mit dem eben angesprochenen Weg sowie auf Wunsch einer Beschreibung, die ähnlich wie dieser Beitrag hier aufgebaut ist. Und um Suchmaschinen nicht zu „irritieren“ brauchen Sie ein Passwort für den verlinkten Beitrag zum Thema „Spende“: $-loesung. Hinweis: Auch wenn Sie nicht innerhalb 24 Stunden eine Antwort haben, ich antworte bestimmt!

▲ nach oben …

Reference: #0592

Dieser Beitrag wurde unter Datentyp anpassen, Datum & Zeit, Foren-Q&A, Power Query abgelegt und mit , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.