Xtract: Zweiter Teil einer in einem Forum gestellten Frage, wie Anwesenheits-Tage von Mitarbeitern auf Monate zugeordnet werden können. PQ- und PivotTabele werden eingesetzt.
Wissensstand: Level 3 ⇒ Excel GUT!, mindestens Basis-Kenntnisse in Power Query
Nacharbeiten und Korrekturen
Der erste Teil endete ja damit, dass Sie die 100.00 € – Frage beantworten sollten: „Was war an der Auswertung formal zwar richtig, entsprach aber nicht den eigentlichen Zielen?” Falls Sie es noch nicht erkannt hatten, auf diese Lösung warteten oder Sie sich nicht sicher sind, gehen Sie mit mir „back to the roots”, also packen wir das Problem gemeinsam an der Wurzel. 😉 Okay, hier können Sie (noch einmal) meinen Stand der Dinge herunterladen. Die folgenden Ausführungen orientieren sich an dieser Datei. … Und falls bei Ihnen das rechte Seitenfenster nicht sichtbar ist, schauen Sie doch einfach einmal hier nach! – Und noch einmal der Hinweis, dass Sie bitte die Quelldaten nicht ändern, außer ich fordere Sie dazu auf!
Öffnen Sie erst einmal in dieser Arbeitsmappe im Power Query-Editor die entscheidende, aussagekräftige Abfrage Tabelle1. Klicken Sie im rechten Seitenfenster auf die erste Zeile bei Angewendete Schritte, also auf Quelle. Sie erkennen den Stand des Ablaufs direkt nach dem Import der Daten in den Power Query-Editor. Klicken Sie in die dritte Zeile darunter (Geänderter Typ1), dann haben Sie bei den kalendarischen Daten eine bessere Darstellung, eine bessere Lesbarkeit, weil nun nur das Datum ohne die Uhrzeit in den beiden Spalten steht; also welcher der Mitarbeiter von wann bis wann anwesend (gewesen) ist bzw. sein wird.
Und (beispielsweise) im Januar gibt es eine besondere „Überschneidung”, denn Lisa Musterfrau ist im Januar 2023 anwesend, ein Mitarbeiter der Firma Just4Fun ebenfalls im Januar, aber nicht im Jahr 2023 sondern 2024! Und da bislang nur der Monatsname ohne Beachtung des Jahres als Argument für die Auswertung verwendet wurde, werden später bei Gruppierte Zeilen die Tage der Monate ohne jegliche Differenzierung des Jahres gezählt; hier sind es folglich die Monate Januar bis April, welche in beiden Jahren vertreten sind und somit zu unerwünschten, zu logisch falschen Ergebnissen führen.
Wie lässt sich das lösen? Prinzipiell bieten sich zwei Lösungswege an, jeweils nach dem Auflisten der einzelnen Tage (also direkt nach dem Schritt Erweiterte Einsatztage, dieser Eintrag ist aber noch markiert):
A) Sie filtern in der Spalte Einsatztage alle kalendarischen Daten, die außerhalb des zu betrachtenden Zeitraums liegen als nicht mehr zu betrachten, Sie löschen also die Datensätze aus dem Jahr 2024. Das würde der wahrscheinlichen Zielsetzung des Fragestellers entsprechen. Und das ist auch mein Favorit.
B) Sie sorgen dafür, dass nicht nur der Monat aus dem jeweiligen Tagesdatum extrahiert wird, sondern auch das Jahr. Das können dann 2 Spalten sein oder auch (vorzugsweise) nur eine. Bei einer 2‑spaltigen Auswertung, wo anschließend auch alle Daten ausgewertet werden sollen, bietet es sich an, die beiden Spalten Monat und Jahr zu 1 Spalte zusammenzuführen und danach entsprechend zu benennen. Direkt kommen Sie übrigens auch sehr schön per M-Funktion Date.ToText zum einspaltigen Ergebnis; einfach einmal in der Hilfe nachsehen. 😉 Der weitere Weg ist bei den genannten Möglichkeiten prinzipiell genau so, wie im ersten Beitrag beschrieben und ausgeführt. Bei zweispaltigem Monat und Jahr sind eventuell kleinere Anpassungen erforderlich.
Der Weg zur Kreuztabelle
Als Basis für diesen Schritt verwende ich die für das Jahr 2023 gefilterte Abfrage und nicht die gesamten Daten einschließlich des Jahres 2024. Den Filter-Vorgang füge ich in der Query Tabellke1 direkt nach Erweiterte Einsatztage und vor Name des Monats extrahiert ein. Die Spalte Einsatztage habe ich über Datumsfilter | Zwischen… anschließend so gefiltert:
So filtern Sie (beispielsweise) die kalendarischen Daten aus 2023
Nach einen OK sind dann nicht mehr 345 Zeilen in der Abfrage, sondern nur noch 202. Und natürlich alle aus dem Jahr 2023. Das passt also schon einmal. Wenn Sie nun auf den letzten Schritt im rechten Seitenfenster Klicken, stimmen zwar die berechneten Werte für jeden Mitarbeiter aber die Darstellung ist nicht wie gewünscht. Die Monate sind nicht in der kalendarischen Reihenfolge, es fehlen die Monate ohne Eintrag und es ist auch keine Kreuztabelle.
Na gut, wie heißt es doch so schön in einem Werbe-Slogan der 50er bis 60er Jahre des letzten Jahrhunderts für einen Weinbrand: „Stück für Stück kommt man sich näher”. 😉
Den ersten Schritt kennen Sie nicht nur aus dem ersten Teil, die Abfrage Monatsnamen existiert ja schon bzw. noch! Und wenn Sie werden nun diese Query öffnen werden Sie rasch erkennen, dass in der Tat auch hier jetzt nur noch die Daten aus 2023 berücksichtigt worden sind. Die Firma Just4Fun ist ausschließlich im November und Dezember aufgeführt und nicht (mehr) im Januar bis Mai. Und die nächsten Schritte werden auch in dieser Abfrage durchgeführt.
Löschen Sie als erstes die Spalten Index und Einsatzmonate, die werden nicht mehr benötigt. Danach markieren Sie die Spalte Column1 und benennen diese zu Monate um. Mein Tipp an dieser Stelle: Erstellen Sie jetzt beispielsweise über Start | Verwalten | Duplizieren oder per Rechtsklick im linken Seitenfenster auf die aktuelle Abfrage ein Duplikat, welches dann automatisch den Namen Monatsnamen (2) bekommt.
Zurück zu der eben bearbeiteten Abfrage Monatsnamen, Markieren die zuerst Spalte Monate, wechseln dann zum Menü-Register Transformieren und in der Gruppe Beliebige Spalte wählen Sie Spalte Pivotieren. Im Dialog wählen Sie bei Wertspalte den Eintrag Anzahl Tage, um diese Werte den (künftigen) jeweiligen Monats-Spalten zuzuordnen. OK und das Ergebnis entspricht nun fast dem gewünschten Endergebnis.
Dass die Zeile 1 leer ist, irritiert mich und ich kann dafür derzeit keine schlüssige Erklärung finden. Sollten Sie da einen nachvollziehbaren Hinweis haben, bitte Mail an mich! So bleibt nur der in solchen Fällen sinnvollste Weg, alle leeren Zeilen zu entfernen (auch wenn es nur eine ist). 😉
Die nächste Aufgabe ist erst einmal die Berechnung der Summen für die einzelnen Mitarbeiter. Markieren Sie dazu die Spalten Januar bis Dezember, Spalte hinzufügen | Statistiken | Summe und PQ berechnet für jeden Datensatz die Summe der Tage aller Monate (sprich: der markierten Spalten). Wenn Sie mögen, benennen Sie die Spalte noch nach Ihrem Geschmack; ich ziehe Tage gesamt vor.
An dieser Stelle in Power Query die Summen für die einzelnen Monate unterhalb des letzten Datensatzes einzufügen wäre aus meiner Sicht viel zu aufwendig. Das geht meiner Meinung nach anders viel besser. Schließen Sie dazu den Power Query-Editor und behalten (natürlich) die Änderungen bei. Und ach ja, ich nutze die Gelegenheit, diesen Stand der Dinge jetzt unter einem anderen Namen als Excel-Datei per F12 zu speichern. Danach im rechten Seitenfenster ein Rechtsklick auf die Abfrage Monatsnamen und Laden in… Wählen Sie im Dialogfenster Tabelle und anschließend entweder Neues Arbeitsblatt oder Bestehendes Arbeitsblatt, um das Ergebnis, die Kreuztabelle an gewünschter Stelle zu positionieren.
Bleiben ja noch die eben angesprochenen Summen der einzelnen Monate. Dazu Klicken Sie (im Excel-Arbeitsblatt) mit der rechten Maustaste irgendwo in den Datenbereich der eben erstellten „intelligenten” Tabelle, Tabelle | Ergebniszeile und es wird eine neue Zeile mit dem Gesamtergebnis in der letzten Spalte angelegt. Ziehen Sie das Feld bis zum Januar nach links und das Wunschergebnis ist gegeben. Mit dem Zahlenformat 0;0;; können Sie erreichen, dass in der Ergebniszeile die Monate ohne einen Eintrag leer erscheinen (also keine 0 sichtbar ist). Und ich formatiere den Datenbereich auch noch komplett auf horizontal zentrierte Darstellung, das sieht dann auch übersichtlicher aus.
Es gibt aber auch noch eine echte Alternative für die Darstellung in der gewünschten Form. Und da ist das Stichwort „PivotTable”. Um das auszuprobieren schlage ich vor, dass Sie die Abfrage Monatsnamen (2) öffnen. Kurz noch einmal checken und überlegen, wie sich die einzelnen Datensätze darstellen sollen. Wenn Sie oft mit Pivot Tabellen arbeiten wissen Sie gewiss, was ich meine.
Ich beginne mit diesem Stand der Dinge als Datenquelle. Aber da auch diese Abfrage als Nur Verbindung gesichert ist, werden Sie jetzt die Abfrage schließen und falls Änderungen vorgenommen worden sind nicht speichern, um die nächsten Schritte besser 1:1 nachvollziehen zu können.
Im rechten Seitenfenster ein Rechtsklick auf Monatsnamen (2), Laden in… | PivotTable-Bericht und entweder als Neues Arbeitsblatt oder bei Bestehendes Arbeitsblatt die gewünschte Position wählen. Danach OK. Grund-Kenntnisse in Pivot Tabellenarbeit setze ich voraus, ansonsten einfach einmal diesen Mini-Kurs anschauen.
Zuerst ziehen Sie das Feld Monate in den Kasten (Bereich) Spalten. Name und Vorname ziehen Sie in den Bereich Zeilen und letztendlich noch Anzahl Tage in Werte.
Na ja… Erst einmal wieder die leere Zeile (ganz unten), warum auch immer. 🙁 Aber die kann ja via Filter bei Zeilenbeschriftungen 
gelöscht werden. Und dann stört mich, dass jeder Name zweizeilig ist und die Werte auch doppelt aufgeführt sind. Na ja, zumindest sind die korrekten Summen automatisch und auch korrekt berechnet worden. Nun ja, und nach dem filtern der Leer-Werten sieht das dann so aus, wenn Sie die Pivot Tabelle in einem neuen Arbeitsblatt gespeichert haben:
Die (erste) Pivot Tabelle nach der Filterung der leeren Zeilen
Wechseln Sie nun erst einmal wieder zum Power Query-Editor. Dazu Klicken Sie im Bereich rechts vom rechten Seitenfenster oben auf jenes Symbol, das derzeit nicht markiert ist:
Klicken Sie hier zum Wechsel Pivot zu Power Query
Es erscheint dann wieder das gewohnte Seitenfenster für Abfragen und Verbindungen. Und bei der Gelegenheit ist mir (endlich) aufgefallen, dass sich bei der Firma Just4Fun ein Tippfehler eingeschlichen hat. Es ist natürlich eine GmbH (statt GnbH). Also Wechsel zum Tabellenblatt Tabelle1 und in Zelle B5 den Fehler korrigieren. Dass sich in der PQ-Ergebnistabelle erst einmal nichts tut, ist normal und auch gewollt; stellen Sie sich einfach einmal vor, dass Sie eine Verbindung zu einer esternen Datenquelle aufgebaut haben und ein Kollege oder Kollegin ändert einen Wert. Was würden Sie sagen, wenn sich dann ohne Ihr Zutun plözulich Ihre PQ-Abfrage ändert? 🙁 Aber nach einem Rechtsklick in eines der Felder der Ergebnistabelle und im Kontextmenü Aktualisieren hat sich der hier erwünschte Erfolg eingestellt. 🙂
Für eine andere, gewohntere und vielleicht auch transparentere Darstellung in der Pivot Tabelle ändern Sie die Abfrage Monatsnamen (2). Name und Vorname sollen zu einem (1) Feld, zuu 1 Spalte zusammengefasst werden. Es stellt sich jetzt die Frage, ob dort dann Lisa Musterfrau oder Musterfrau, Lisa stehen soll. Im erstgenannten Fall markieren Sie zuerst die Spalte Vorname und dann Strg Name. Rechtsklick in eine der beiden markierten Überschriften und Spalten zusammenführen. Als Trennzeichen wählen Sie Leerzeichen. Das Ergebnis irritiert wahrscheinlich, denn die Reihenfolge stimmt nicht. 🙁 Kann auch nicht stimmen, denn beim genauen Hinsehen ist mir klar geworden, dass die Überschriften nicht zu den Inhalten der beiden Spalten passen. Also abändern…
Idealerweise markieren Sie bei den Schritten die Zeile Umbenannte Spalten und los geht’s. Wenn Sie aber versuchen, die Spalte Name auf Vorname zu ändern, wird Power Query das nicht akzeptieren. Es können in einer Query (exakt: in einer Liste/Tabelle) nicht zwei gleiche Überschriften existieren. Also beispielsweise Name erst einmal auf Vorname. (mit Punkt) ändern, dann Vorname auf Name und anschließend den Punkt bei Vorname entfernen.
Wenn Sie dann wieder auf die letzte Zeile der Schritte wechseln, sieht das sehr gut aus. – Sollten Sie die Anordnung Musterfrau, Lisa vorziehen, dann markieren Sie die beiden Spalten in der (nun mit korrekten Überschriften versehenen) gewünschten Reihenfolge, Rechtsklick in eine der Überschriften, Spalten zusammenführen und wählen bei Trennzeichen die Zeile Benutzerdefiniert und tragen dann in das Textfeld ein Komma, gefolgt von einem Leerzeichen ein. Geben Sie der neuen, zusammengefassten Spalte bei Bedarf noch einen sinnvollen Namen, beispielsweise Name oder Name, Vorname. Und ja, das hätten Sie natürlich auch schon beim Zusammenfügen tun können. 😉
Schließen Sie den PQ-Editor und speichern dabei (natürlich) die Änderungen. Wechseln Sie wieder zur PivotTable-Ansicht durch einen Klick auf das entsprechende Symbol ganz rechts beim Seitenfenster und ziehen Sie noch das (neue) Feld (beispielsweise Name) in den Bereich Zeilen. Vielleicht noch einmal den Filter bei Zeilenbeschriftungen setzen, damit die Leerzeile (oben) gelöscht wird und das Ergebnis lässt sich wirklich sehen! Einige „kosmetische” Korrekturen werden das Aussehen gewiss noch verschönern:
Eine aus meiner Sicht wirklich gelungene Pivot Tabelle
Es bleibt gewiss noch die Frage, warum ich meine Arbeit vor dem Wechsel zur ersten Pivot Tabellenerstellung per F12 unter einem anderen Namen gespeichert habe. Nun ja, meine langjährige Erfahrung in Sachen „Beschreiten neuer Wege” hat mir gezeigt, dass doch manchmal etwas schief gehen kann. Und so habe ich einen definierten Stand, wo ich im Falle eines Falles wieder einsteigen kann; oder aber ich möchte vielleicht auch andere Wege erforschen, die vielleicht für mich besser nachvollziehbar sind oder ich möchte einfach nur herumprobieren… Und ja, wenn ich gleich zu Beginn mit meinem Werk zufrieden bin, dann lösche ich die Zwischensicherung natürlich.
Und noch eins: Meine Lösung gibt es auf Anfrage per E‑Mail an Sie.
Rückmeldungen / Feedback gerne per Mail an mich (G.Mumme@Excel-ist-sexy.de)
Haben Ihnen die beiden Beiträge gefallen?
Erleichtert diese Beiträge Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 5,00 € freuen … (← Klick mich!)