Foren-Geflüster, rund ums Datum
In diesem Forum wurde eine recht interessante Frage diskutiert: Aus der rechten Ziffer eines numerischen Begriffs soll der Wochentag als Zahl sowie auch als Text, als Wort generiert werden. Und verschiedene durchaus funktionierende Lösungen wurden dort schon vorgestellt, mich reizte die Umsetzung in Power Query. An dieser Stelle gleich der Hinweis: Ich habe mich in diese Lösung ganz bewusst auf die dargelegte Fragestellung konzentriert; im professionellen Umfeld würde ich auch die Berechnungen der Zeiten für die Brutto- als auch Nettostunden sowie die Pause durch Power Query berechnen lassen.
Der Fragesteller verfügt zwar (nur) über die Excel-Version 2010, ich verwende hier aber die jeweiligen Menü- und Symbolbezeichnungen als auch die Möglichkeiten der Version 2016 beziehungsweise der Version 365 (Stand Herbst 2018). Die angehängte Original-Datei des Forum-Beitrages habe ich hier zur Verfügung gestellt und für meine Lösung habe ich den Bereich Versuch eins verwendet.
Der besseren Transparenz wegen habe ich den Bereich D8:I11 kopiert und in eine neue Arbeitsmappe Als Wert eingefügt. Auf die Tabellen in den Spalten M:N (in der Originalen Datei) habe ich verzichtet, den Inhalt jedoch „im Hinterkopf” behalten. 😎 Und die Zahlen 1 bis 7 stehen für Montag bis Sonntag, entweder in der Lang- oder kurz Schreibweise. Da ich für die gegebene Aufgabe die Pausenzeiten aus der Tabelle übernehmen und nicht berechnen muss, brauche ich auch nicht die Tabelle aus den Zeilen 1:6.
Im neu erstellten Arbeitsblatt klicke ich in die Daten, StrgT oder StrgL, um und daraus eine Intelligente Tabelle zu machen. Anschließend über Daten | Aus Tabelle (in Excel 2010⁄2013 über Power Query | Von Tabelle) die Daten in den Power Query Editor importieren. Das ganze stellt sich nun so dar:
Die Daten direkt nach dem Import
Dass die Werte ab der zweiten Spalte nicht als Zeit sondern als Dezimalzahlen dargestellt werden stört mich gewiss nicht, vielleicht aber Sie. Der besseren Optik wegen können Sie an dieser Stelle bereits an die fünf Spalten durch einen Klick in die Überschrift in der Spalte von, Shift und dann die Überschrift Stunden markieren. Im Menüband wählen Sie bei Datentyp die Möglichkeit Zeit und bestätigen dann die Änderung des Datentyps. Wie gesagt, und das dient nur der Optik und hat keinen Einfluss auf die Daten an sich.
Apropos Zahlenformat: Durch das Kopieren als Wert werden prinzipiell auch als Text formatierte Ziffernfolgen als Zahl eingefügt. Und wenn Sie die Daten direkt aus einer gegebenen Datei mit als Text formatierten Ziffernfolgen (Zahlen) importieren wird Power Query in den meisten Fällen automatisch ein Zahl-Zellformat wählen.
Der erste Teil der Aufgabe besteht ja darin, die letzte Ziffer in der Spalte Code zu separieren. Diese Zahl soll dann als Basis für die Berechnung des Wochentages in Textform verwendet werden. In wieweit dieser numerischer Wochentag für das weitere Vorgehen von Bedeutung ist, hat sich mir aus dem Text der Fragestellung nicht erschlossen. Aus dem Grunde werde ich die generierte Spalte auch in der endgültigen Lösung belassen und nicht löschen, obwohl ich sie für überflüssig halte.
Im ersten Schritt ein Rechtsklick in die Überschrift der Spalte Code und im Kontextmenü wählen Sie dann den Punkt Spalte duplizieren. Automatisch wird eine neue Spalte mit der Überschrift Code – Kopie erstellt. Der Typ dieser Spalte ist Naturgemäß Ganze Zahl, ändern Sie den Datentyp auf beliebige Weise in Text. Immer noch im gleichen Menüband ein Klick auf das Symbol Spalte teilen und wählen Sie dann die Auswahl nach Anzahl von Zeichen. Nun geben Sie in das Textfeld eine 1 ein und markieren Sie anschließend einmal, soweit rechts wie möglich. Nach einem OK wird die Spalte geteilt; in der ursprünglichen Spalte (jetzt Code – Kopie.1) sind alle Ziffern mit Ausnahme unter äußersten rechten Zahl, in der neuen Spalte (Code – Kopie.2) befindet sich die extrahierte letzte Ziffer, die automatisch als Ganze Zahl formatiert worden ist. Die Spalte Code – Kopie.1 wird nicht mehr benötigt, darum löschen Sie diese. Benennen Sie nun in die Überschrift der letzten Spalte in W‑Tag um:
Die umbenannte Spalte mit den numerischen Wochentagen
Damit ist das erste Ziel erreicht, nämlich aus der Spalte Code die letzte Ziffer, welche hier ein Synonym für den Wochentag sein wird, abzutrennen. Und jetzt geht es darum, aus den in der Spalte W‑Tag stehenden Zahlen 1 bis 7 den Namen des Wochentages, also Montag (1) bis Sonntag (7) zu generieren. Selbstverständlich wäre es ein denkbarer Weg, die Power Query-Alternative für den SVERWEIS() zu verwenden. Dazu würden Sie dann eine oder beide Tabellen mit den Daten als Abfrage importieren und entsprechend der vorgegebenen Werte verwenden. Einige Beispiele dafür sind in diesem Blog zu finden, wenn Sie beispielsweise in das Suchfeld Sverweis Power Query eingeben. Ich gehe jedoch einen anderen Weg, in der mir einfacher erscheint.
Wechseln Sie dazu zum Register Spalte hinzufügen und wählen dort im Menüband das erste Symbol Spalte aus Beispielen. In der ganz rechten, abgetrennten Spalte mit dem Namen Spalte1 tragen sie von Hand das Datum 01.01.2001 ein und bestätigen dann per Eingabetaste. Es bietet sich in Ihnen nun dieses Bild:
Das Datum ist ausgefüllt, aber …
Wenn Sie genau hinsehen erkennen Sie, dass die folgenden 2 Zeilen einer Logik folgend ausgefüllt worden sind aber nicht (nur) um einen Tag, sondern auch um einen Monat und um ein Jahr weiter. 🙄 Darum schreiben Sie in die zweite Zeile der nun automatisch umbenannten Spalte (Zusammengeführt) den Datums-Text 02.01.2001 und bestätigen wiederum per Enter. Jetzt stimmt die logische Abfolge und Sie können sich darauf verlassen, dass in dieser Spalte auch alle weiteren Werte korrekt berechnet werden.
An der linksbündigen Ausrichtung in den kalendarisch aussehenden Daten erkennen Sie, dass es sich dabei um Texte handelt. Der klassische Weg wäre nun, den Datentyp dieser Spalte in Datum zu ändern. Die neueren Versionen des Power Query bieten eine andere, gewiss elegantere Möglichkeit. Wechseln Sie zum Register Transformieren, markieren erforderlichenfalls noch einmal die Spalte Zusammengeführt und wählen dann beim Symbol bzw. dem Menüpunkt Datum die einzig mögliche Zeile Analysieren aus. Sie sehen, dass sich der Datentyp automatisch zu Datum geändert hat. Wenn Sie nun noch einmal auf diesen Menüpunkt im Menüband Klicken werden sie erkennen, dass jetzt fast alle Punkte des Kontextmenüs auswählbar sind. Markieren Sie Tag | Name des Tags und sofort wird und das kalendarische Datum durch den Wochentag des entsprechenden Wertes ersetzt. Zugegeben, es ist (nur) die Langschreibweise, aber der Zweck des Vorhabens sollte damit erfüllt sein.
Um der Forderung des Fragestellers gerecht zu werden, muss diese Spalte nun nur noch an die erste Position verschoben werden. Und dazu ein Rechtsklick in die Überschrift und im Kontextmenü wählen Sie Verschieben | An den Anfang. Was noch bleibt: Diese erste Spalte soll noch einen anderen, sinnvollen Namen bekommen; ein Klick in die Überschrift, F2 und Sie schreiben den Text Wochentag, gefolgt von einem Return. Und damit ist diese Aufgabe mit bzw. in Power Query endgültig gelöst worden. 🙂
Bleibt nur noch, das Register Datei anzuwählen, Schließen & laden und das Ergebnis dieser Abfrage wird in eine Tabelle eines neu erstellten Blattes geschrieben. Diese Liste können Sie dann nach Belieben an die gewünschte Position eines beliebigen Sheets verschieben. Und da es sich hierbei um eine ganz normale Excel-Tabelle handelt, können Sie diese natürlich auch weiter nach Lust und Laune bearbeiten, formatieren, …
Der hier beschriebene Weg ist prinzipiell für reine, „bodenständige” Anwender gedacht. Wer gerne mit Formeln arbeitet oder sogar auch programmieren kann, wird gewiss den Weg über die Formel-Sprache M wählen. Der wäre auch erforderlich, wenn Sie das Ergebnis beispielsweise (auch in landestypischer) Kurzschreibweise des Wochentages haben wollen. Und wie schon angedeutet wird ein eher professioneller Anwender die Spalten Std, Pause und Stunden auch von vornherein durch PQ berechnen lassen und nicht durch Excel.
Eine kleine Ergänzung: Warum sollte es gerade der 1. Januar 2001 sein? Es hätte auch jedes beliebige andere Datum sein können, solange der erste Tag des Monats ein Montag ist. Denn im Endeffekt wird aus der Zahl 1 in der Spalte W‑Tag Ein Datum generiert, wo in die 1 für den Tag des Datums steht. Und der 1. Januar 2001 war ein Montag. 💡