PQ: Zeiterfassung, mehrere Projekte je Kalenderwoche

Erfassung und Auswertung von Arbeitszeiten, wo ausschließlich Kalenderwoche und Wochentag gegeben ist

Die Basis dieser Aufgabe habe ich als Fragestellung in einem Forum gesehen. Um mit der (neuen) DSGVO in keiner Weise in Konflikt zu geraten, habe ich nur das Gerüst übernommen und praktisch alle Daten komplett verändert.

Die eigentliche Aufgabenstellung stellt sich so dar: In dieser Tabelle sind Kalenderwochen, Projekte und die einzelnen Wochentage als Spaltenüberschriften geschrieben und darunter die entsprechenden Daten. Bei den Wochentagen sind jeweils die erfassten Zeiten in Industrie-Stunden erfasst worden, an Tagen, wo für das entsprechende Projekt kein Arbeitsaufwand erfolgte, blieb der Inhalt der jeweiligen Zelle leer. Für den ersten Überblick hier ein Screenshot der unformatierten Daten:

Die Roh-Daten in unformatierter Form

Die Roh-Daten in unformatierter Form

Ich stelle ich Ihnen hier 2 grundsätzlich unterschiedliche Vorgehensweisen vor. Beide haben ihre Vor- aber auch Nachteile. Beiden gemeinsam ist, dass ich Power Query nutze aber die Abfragesprache M nicht voll ausreize; das würde an dieser Stelle einfach nicht in den Rahmen der kostenlosen Informationsvermittlung des Blogs passen. Ich möchte Ihnen damit sagen, dass die Berechnung des Montags der entsprechenden Kalenderwoche zwar auch mit Mitteln des Power Query durchgeführt werden kann, ich jedoch in die Quelldaten eine entsprechende Hilfsspalte einfüge. Hilfsspalten sind ja per se nicht böse und lassen sich ja auch problemlos ausblenden (und werden dennoch von Power Query mit übernommen).

▲ nach oben …

Vorbereitung

Prinzipiell sollte vor jedem Import von Daten aus einem Excel-Arbeitsblatt jener Bereich, der die zu importieren den Daten enthält, als Liste/Intelligente Tabelle formatiert werden. Ich klicke dazu in die zu importieren den Daten und dann StrgL oder StrgT; Sie können aber auch gerne den Weg über das Menü (Register Start) gehen, um den gleichen Effekt zu erreichen. Anschließend markiere ich die Spalte C und füge dort eine neue, leere Spalte ein. Hier berechne ich das Datum des Montags der entsprechenden Kalenderwoche. Dazu verwende ich in C2 diese angepasste Formel (Quelle: Excelformeln.de):
=7*KÜRZEN((2&-1&-(JAHR(A2)*1))/7+(LINKS(JAHR(A2);2)*1))-5

Dank der Funktionalität der formatierten Tabelle wird die Formel automatisch bis zur letzten Zeile der Daten nach unten kopiert und der jeweilige Montag der dazugehörigen Kalenderwoche ausgegeben. Normalerweise werden die berechneten Ergebnisse als serielle Zahl dargestellt, der Optik und der Überprüfbarkeit wegen formatieren Sie diese (erst einmal) als Datum; es reicht gewiss das Kurzformat ohne den Wochentag. Ich selbst vollziehe diesen Schritt recht selten, weil die Daten in einer der kommenden Schritte sowieso in das reine Zahlenformat (die serielle Zahl) umgewandelt werden.

▲ nach oben …

Lösung 1 (auch für Einsteiger nachvollziehbar)

Ganz zu Beginn werden Sie die Rohdaten in den Power Query Editor importieren. Dazu muss eine beliebige Zelle im Bereich der Liste markiert sein. Je nach Excel-Version klicken Sie nun in Excel 2010/2013 auf den Menüpunkt Power Query, in 2016/365 wählen Sie das Menü Daten. Im Menüband anschließend auf Von Tabelle bzw. Aus Tabelle. Es öffnet sich der Editor und das Ganze stellt sich so dar:

Die Daten direkt nach dem Import

Die Daten direkt nach dem Import

Fast jede der Spalten hat einen „sprechenden“ Namen, ausgenommen die Spalte mit dem Datum des Montags der jeweiligen Kalenderwoche. Nicht nur der Transparenz wegen ändere ich diese Überschrift auf Datum. – Eine kurze Überprüfung, ob alles o. k. ist, was es standardmäßig auch sein sollte. Dass die Spalte Datum grundsätzlich als Datum/Zeit formatiert ist gehört zu den kleinen Unannehmlichkeiten des Power Query; ich würde mir wünschen, dass ich einen Schalter setzen kann, dass kalendarischen Daten, die ohne Zeitangabe in den Rohdaten stehen, dann auch (nur) als Datum ohne das angehängte 00:00:00 dargestellt werden. Aber das nur nebenbei …

Power Query kann nicht so ohne weiteres einen Tag zu einem gegebenen Datum addieren oder subtrahieren. Also eine berechnete Spalte mit der Formel =[Datum]+1 wird zu einem Fehler führen. Darum mache ich einen kleinen Umweg und formatiere die Spalte Datum als Datentyp: Ganze Zahl. Da steht jetzt beispielsweise in der Datenzeile 1 der Wert 42.730 (in Power Query ohne den Tausenderpunkt, derartige Formatierung kennt PQ nicht).

Da es im Laufe der Arbeit noch einige Abfragen mehr werden, gebe ich dieser Query einen sinnvollen Namen: RawData; auch wenn ich die deutsche Sprache im Normalfall vorziehe ist es mitunter sinnvoll oder hilfreich, den typischen internationalen Namen zu verwenden. Am einfachsten ändern Sie den Namen dieser Abfrage im rechten Seitenfenster, direkt unter Eigenschaften | Name.

Damit ist jetzt eine „gesunde“ Basis geschaffen. Ich speichere diese Abfrage jetzt, aber nicht über einen Klick auf die Schaltfläche Schließen & laden sondern auf den Text darunter und wählen dann im Auswahlmenü die Position Schließen & laden in…, um anschließend im Dialog die Auswahl Nur Verbindung zu treffen. Das hat den Vorteil, dass die zu erstellenden Tabellen nur im Arbeitsspeicher des Computers verwaltet werden und nicht in die Arbeitsmappe geschrieben werden. Das gilt übrigens auch für die gleich zu erstellenden und anschließend bearbeiteten Duplikate dieser Abfrage.

Ich persönlich ziehe es vor, solch eine minimal angepasste Basis-Abfrage in exakt dieser Form und Ausführung zu behalten. Ich weiß ja nie, wozu das gut sein kann; vielleicht brauche ich diese Abfrage später noch einmal bei einer anderen Gelegenheit. Darum erstelle ich jetzt von dieser Abfrage 5 Duplikate. Als Vorgehensweise bevorzuge ich, diese (bislang einzige) Abfrage zu öffnen und dann im linken Seitenbereich des Editors auf das senkrecht stehende Wort Abfragen zu klicken; das führt dazu, dass aus diesem schmalen Streifen ein richtiges Seitenfenster wird. Per Rechtsklick auf das Wort RawData in diesem Bereich wähle ich im Kontextmenü Duplizieren und wiederhole anschließend exakt diesen Vorgang 4 mal. Danach benenne ich die Duplikate um; aus RawData (2) wird Montag, aus RawData (3) Dienstag, etc.

Mir fällt es leichter, die logische bzw. gewohnte Reihenfolge einzuhalten. Darum beginne ich mit der folgenden „Runde“ wieder beim Montag. Im linken Seitenfenster also ein Klick auf die Abfrage dieses Wochentags. Um die zu nutzenden Werte des Montags zu separieren, markiere ich die ersten 4 Spalten der Abfrage. Anschließend ein Rechtsklick in eine der Überschriften und im Kontextmenü wähle ich Andere Spalten entfernen. Als letzten Schritt werden Sie die Überschrift des jeweiligen Wochentages (hier: Montag) an die Inhalte der Spalte anpassen; vergeben Sie-auch später in den anderen Spalten- einen neutralen „sprechenden“ Namen: Stunden.

Der Montag ist damit erst einmal abgeschlossen. Wechseln Sie zum Dienstag. Prinzipiell ist hier das gleiche Vorgehen angesagt. Nur werden Sie hier die ersten 3 Spalten markieren und anschließend mit Strg die Spalte Dienstag. Wie gehabt löschen Sie die nicht markiert Spalten per Rechtsklick in eine der markierten Überschriften und dann im Kontextmenü Andere Spalten entfernen. Da die jeweilige serielle Zahl aber für den Montag und nicht für den Dienstag der entsprechenden Kalenderwoche steht, muss die Zahl um den Wert 1 erhöht werden. Es gibt verschiedene Wege, das Ziel zu erreichen ich wechsele dazu in das Register Transformieren, markiere die Spalte Datum und im Menüband ein Klick auf Standard | Addieren. Im Dialog geben Sie (natürlich) eine 1 in das Textfeld ein, denn sie wollen ja den Wert um 1 erhöhen. Im Prinzip gleiches Vorgehen bei Mittwoch bis zum Freitag, nur dass Sie die Zahl der zu addierenden Tage entsprechend anpassen.

Jetzt existieren 5 Abfragen mit den korrekten Werten für die einzelnen Wochentage. Und da die Spalten stets die gleichen Überschriften haben und der Datentyp auch immer identisch ist, lassen sich diese Queries auch hervorragend einer neuen Abfrage zusammenfassen. Meine Macke mit der logischen Reihenfolge treu bleibend beginne ich wieder mit dem Montag. 😎 

Wechsel zum Menüregister Start, Schaltfläche Kombinieren und im Dropdown den Menüpunkt Abfragen anfügen durch einen Klick auf  erweitern, dann Abfragen als neu anfügen. Im Dialog erst einmal die Auswahl 3 oder mehr Tabellen markieren. Das stellt sich nun so dar:

Um mehr als 1 Tabelle anzufügen …

Um mehr als 1 Tabelle anzufügen …

Der Montag ist im Bereich Anzufügende Tabellen bereits eingetragen. Markieren Sie im linken Bereich Verfügbare Tabelle(n) die Einträge Dienstag bis Freitag und klicken Sie dann auf Hinzuf… Umgehend sind in der rechten Box alle Wochentage sichtbar. Schließen Sie das Fenster mit einem Klick auf den OK Button. Die neue Abfrage stellt sich so dar:

Nach dem zusammenfügen aller Wochentage-Tabellen

Nach dem zusammenfügen aller Wochentage-Tabellen

Eigentlich ist damit die Abfrage komplett, das Ziel erreicht. Wie gesagt, eigentlich. Denn was noch ziemlich stört ist die Darstellung, die Formatierung der Spalte Datum. Darum vergeben sie hier den Datentyp Datum und es steht ein für uns Anwender besser lesbarer Werte in jeder Zeile der Spalte, das offensichtliche Datum. 😆 Als Kosmetik werden sie jetzt die Abfrage zuerst nach Datum aufsteigend sortieren und anschließend ebenfalls aufsteigend nach Projekt. Als letzten Schritt filtern Sie die Spalte Stunden so, dass sie beim Wert (NULL) das Häkchen entfernen.

Ein Klick auf das Symbol Schließen & laden und in einem neuen Arbeitsblatt wird eine Tabelle mit 4 Spalten und 39 Zeilen plus Überschriften erstellt. Und das ist nun wirklich das gewünschte Ziel. Wenn Sie möchten können Sie die Daten nun noch in einer PivotTable weiter auswerten. Und natürlich können Sie auch mit Power Query noch dieses oder jenes anstellen. 💡 

▲ nach oben …

Lösung 2 (Kurz, knapp, effizient)

Nach dieser vielleicht etwas provokanten Überschrift werden sich vielleicht fragen, warum eine Lösung mit diesen Attributen nicht für Einsteiger geeignet sein soll. Nun ja, das hat etwas mit einer weiteren Macke von mir zu tun: Ich lege bei der Wissensvermittlung Wert darauf, dass die Anwender das Vorgehen nicht nur abschreiben, sondern die einzelnen Schritte auch nachvollziehen, möglichst sogar verstehen können, um darauf später in Eigeninitiative aufbauen zu können. Diese Lösung ist zwar um einiges professioneller und vor allen Dingen kürzer als die erste, dafür aber auch längst nicht so transparent und sie setzt schon etwas Erfahrung mit Power Query voraus. Als Dokumentation wie komplex diese Lösung ist hier ein Screenshot der einzelnen aber auch einzigen Schritte:

Übersichtliche Zahl der Arbeitsschritte

Übersichtliche Zahl Arbeitsschritte

Das ist wirklich alles. Sie können entweder wieder mit den unformatierten Quelldaten aus dem Download beginnen (vorher bei Bedarf die eben erstellte Lösungs-Datei umbenennen) oder aber in der bisherigen Arbeitsmappe weiterarbeiten und darin eine weitere Abfrage erstellen.

Wenn Sie wieder bei Null anfangen, dann formatieren sie die Daten idealerweise erst einmal als Tabelle, fügen die Formel für die Berechnung des Montags der entsprechenden Kalenderwoche wie bereits weiter oben beschrieben ein, ändern für die neue Spalte die Überschrift auf beispielsweise MoDatum (Montag-Datum) und importieren Sie die Tabelle anschließend wie gewohnt in den Power Query Editor. – Falls Sie direkt auf dem bisher erarbeiteten aufbauen ohne die Datei zu wechseln, erstellen Sie eine Kopie, also ein Duplikat der Abfrage RawData und ändern den Namen eventuell auf RawData (Kopie). Dadurch ist die Ausgangslage für beide Vorgehensweisen identisch. Achten Sie aber bitte in jedem Fall darauf, dass die kalendarischen Daten in der Spalte MoDatum als serielle Zahl vorliegen; erforderlichenfalls wandeln Sie den Datentyp in Ganze Zahl.

Markieren Sie nun die ersten 3 Spalten, also KW, Projekt und MoDatum genau in dieser Reihenfolge; das geht übrigens auch mithilfe der Taste Shift. Nun ein Rechtsklick in eine der markierten Überschriften und Andere Spalten entpivotieren. Dadurch entsteht eine 5-spaltigen Liste mit insgesamt 39 Zeilen:

Dem Ziel sehr nahe: Nach dem entpivotieren

Dem Ziel sehr nahe: Nach dem entpivotieren

Wie auch in der ersten Übung muss dem Wochentag ja das korrekte Datum zugeordnet werden. Das geschieht -ähnlich wie in Excel- mit einer WENN-Formel, die hier aber die eine oder andere Besonderheit hat. Auf der einen Seite ist die Sprache der Weisungen, Funktionen, Entscheidungen, … stets Englisch. Und auf der anderen Seite ist es wichtig, die Schlüsselbegriffe wie beispielsweise if und else in exakt dieser Groß- Kleinschreibung zu verwenden. Um das Vorhaben zu realisieren, erstellen Sie eine neue Spalte. Im Menü-Register aktivieren Sie Spalte hinzufügen, im Menüband dann Benutzerdefinierte Spalte. Im Dialog geben Sie bei Neuer Spaltenname Datum ein, im Bereich Benutzerdefinierte Spaltenformel diese Formel:

= if [Attribut]="Dienstag" then [MoDatum]+1 else
   if [Attribut]="Mittwoch" then [MoDatum]+2 else
   if [Attribut]="Donnerstag" then [MoDatum]+3 else
   if [Attribut]="Freitag" then [MoDatum]+4
   else [MoDatum]

(Sie können übrigensdiesen Formel-Text hierüber mit der Maus markieren, in die Zwischenablage kopieren und dann in Ihrem Dialogfenster einfügen.)

Die fertig eingegebene Formel im Dialog

Die fertig eingegebene Formel im Dialog

Nach einem Klick auf OK wird eine neue Spalte mit den ganzzahligen, seriellen Werten der berechneten kalendarischen Daten des jeweiligen Wochentages erstellt. Hier beginnend mit 42730, 42731, … Im nächsten Schritt löschen Sie die Spalten MoDatum und Attribut. Geben Sie der Spalte Wert eine neue Überschrift: Stunden. Verschieben sie diese Spalte nun ans Ende.

Jetzt haben Sie es fast geschafft. Bleibt nur noch, die Spalte Datum auch den Datentyp: Datum zuzuweisen, um ein „richtiges“ Datum 😉 zu sehen. Nun die Spalte Datum aufsteigend sortieren und anschließend auch die Spalte Projekt aufsteigend sortieren. Die letzte „Amtshandlung“ besteht darin, dass sie jetzt Schließen & laden in… wählen um die Abfrage in einem neuen Arbeitsblatt abzulegen oder diese Abfrage an gewünschter Stelle zu positionieren. Der Weg über Schließen & laden in… ist erforderlich, da Sie sonst (wahrscheinlich) nur eine Verbindung erstellen und das Ergebnis nicht als Tabelle in der Arbeitsmappe sehen. – Und wenn Sie jetzt schneller waren, als ich schreiben konnte dann schauen Sie einmal hier nach, um das elegant wieder gerade zu biegen.

Wenn Sie die beiden Vorgehensweisen vom Ergebnis her vergleichen werden Sie feststellen, dass es keinen Unterschied gibt, obwohl die Vorgehensweise beider Übungen recht unterschiedlich ist. – Da das Ergebnis einer ganz normale formatierte Tabelle ist, können Sie diese natürlich auch auf beliebige Weise weiterverarbeiten und auswerten. Vielfach bietet sich da eine PivotTable an.

Sollten Sie das Bedürfnis haben, in Power Query weitere Auswertungen vorzunehmen (beispielsweise die zu berechnende Stundenzahl für jeden Kunden im einzelnen Monat per Gruppierung), dann empfehle ich Ihnen, von der letzten erarbeiteten Version wiederum ein Duplikat oder einen Verweis zu erstellen und diese zusätzliche Abfrage als Basis  zu nehmen.

▲ nach oben …

Dieser Beitrag wurde unter Daten zusammenführen, Datum & Zeit, Datum und Zeit, Filtern & Sortieren, Foren-Q&A, Ohne Makro/VBA, Power Query, PQ-Formeln (Sprache M), Stundenabrechnung abgelegt und mit , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.