Wissensstand: Level 2 ⇒ Solides Basiswissen in Excel, etwas Erfahrung in PQ
In einem Excel-Forum wurde die Frage aufgeworfen, wie unterschiedlichste Zeiteinheiten (Wochen, Tage, Stunden), welche stets in 1 Zelle zusammengefasst worden sind, zu Minuten umgerechnet werden könnten. Hier ein Screenshot aus der Muster-Datei des ersten Beitrags:
Dieses war die Vorgabe …
Unterschiedliche Lösungen, die teilweise auf unzureichender Information des Fragestellers beruhten, wurden präsentiert und diskutiert. Mich hat das Thema gereizt und ich habe mir auf der Basis des Power Query zwei Lösungswege erarbeitet. Zugegeben, es ist nicht unbedingt ein Paradebeispiel für den Einsatz des PQ aber ich habe hier aus Ehrgeiz und Neugier Wege beschritten, welche nicht unbedingt dem Standard entsprechen; den ersten möchte ich Ihnen hier vorstellen. Und wenn es dann auch genügend Datensätze sind, dann „lohnt” sich sogar Power Query. 😎
Also, laden Sie gerne erst einmal die Muster-xlsx direkt aus dem Forum (aktualisierte Fassung aus Beitrag #9) herunter. Idealerweise werden Sie die prinzipiell identische Kopie von hier herunterladen, denn ich habe während des Schreibens erlebt, dass sich innerhalb einer Stunde extrem viele Veränderungen in der Muster-xlsx (ohne einen direkten Hinweis darauf) ergeben haben. Sie werden auch erkennen, dass die Einträge alles andere als „konventionell” oder gar systematisch sind. Aber gerade das macht ja die eigentliche Herausforderung aus. 😉 Ich habe bei meinen Lösungen bewusst darauf verzichtet, auch eventuelle Großschreibung der Zeit-Einheiten anzupassen; das wäre in einem solchen Fall nämlich unter Umständen erforderlich, weil Power Query stets zwischen Groß- und Kleinschreibung unterscheidet (case-sensitive).
Lösung 1, Spalten, Spalten, Spalten …
Dieser durchaus konservativere Weg ist gewiss etwas leichter nachvollziehbar als die zweite von mir erarbeitete Lösung. Die Grund-Idee ist, die Zeit-Kürzel zu separieren und anschließend mit unterschiedlichen if-Konstrukten einen Multiplikator zu errechnen. Anschließend ist es dann kein Problem, die jeweiligen Minuten zu berechnen und schlussendlich zu addieren. Das Ganze ergibt aber so einige (oder ein paar mehr) Spalten, die jedoch später wieder gelöscht werden, was dann schlussendlich die Übersichtlichkeit wieder herstellt. 😎
Vorarbeiten
Die Muster-Datei haben Sie bereits auf dem Schirm. Analysieren Sie in Excel die einzelnen Einträge der Spalten A; C; E und erkennen Sie, dass hier 3 individuell zu berechnende Spalten vorliegen und insbesondere die Zellen, wo ausschließlich die Minuten eingetragen sind, eine Sonderstellung haben. Behalten Sie den Aufbau „im Hinterkopf” und löschen Sie bereits hier die existierenden, durch den Fragesteller bereits eingetragenen Ergebnisspalten; die stören derzeit und sie werden die ja gleich mit Power Query berechnet werden. Das stellt sich dann nach dem Import im Editor so dar:
Die reine Daten-Basis zum auswerten
Hinweis: Wenn es Ihnen sympathischer ist, können Sie die überflüssigen Ergebnisspalten auch erst im Editor löschen. Das ist gewiss dann hilfreicher, wenn diese Berechnungen immer wieder mit veränderten Import-Daten vorgenommen werden sollen und auch ständig diese händischen Berechnungen in den Quell-Daten enthalten sind.
Um einen bessere Kontinuität in diese Daten zu bekommen, sollten Sie in PQ die Zellen mit den Nur-Minuten dahingehend anpassen, dass dort beispielsweise statt 10min künftig 10m steht. Dazu markieren Sie alle Spalten, Start | Werte ersetzen (oder über Rechtsklick in eine der Überschriften) und min durch m ersetzen lassen.
Erste Schritte
Zugegeben, ich habe einige Zeit überlegt, welches der für Sie beste Weg ist, das Ganze „aufzudröseln”, also die eingetragenen Kürzel für die Zeiteinheiten einigermaßen übersichtlich und transparent in Minuten umzurechnen. Und ich muss zugeben, dass es (meinem derzeitigen Kenntnisstand nach) mit Power Query in dieser Sache keinen wirklich übersichtlichen, „schlanken” Weg für Einsteiger gibt. Aber ich habe versucht, so weit als möglich auf die Sprache M zu verzichten und möglichst die die Schaltflächen / Symbole, also die GUI für die Berechnung einzusetzen.
Diese Abfrage (Tabelle1) ist die Basis für alle folgenden Schritte. Markieren Sie ausschließlich die 1. Spalte W/D/H_1, führen Sie einen Rechtsklick in der Überschrift durch und Sie werden im Kontextmenü weit unten den Punkt Als neue Abfrage hinzufügen. Es wird automatisch eine Liste erstellt:
Als Liste separierte Daten der ersten Spalte
Da die Daten künftig als „echte” Abfrage vorliegen müssen, Klicken Sie im Menüband auf das erste Symbol Zu Tabelle und übernehmen im anschließenden Dialog die Vorgaben. Zum Abschluss gehen Sie über das Register Spalte hinzufügen zur Auswahl Benutzerdefinierte Spalte und tragen Sie bei Neuer Spaltenname beispielsweise die Überschrift WDH ein und bei Benutzerdefinierte Spaltenformel: schreiben Sie einfach den Namen der Abfrage, welcher natürlich in Anführungsstriche gehört:
Eine neue Spalte mit dem Namen der Abfrage als Inhalt
Mit den Spalten W/D/H_2 und W/D/H_3 gehen Sie gleichermaßen vor, die Quelle ist immer wieder Tabelle1 und Sie markieren dann natürlich die passende Spalte. Und selbstredend passen Sie die die Benutzerdefinierte Spaltenformel jeweils den Gegebenheiten an. Anschließend haben Sie für jede dieser 3 Spalten eine eigene Abfrage.
Die nächsten Schritte der besseren Übersicht wegen als Auflistung:
- Wechseln Sie nun durch Klick im linken Seitenfenster zur Abfrage W/D/H_1, Start | Kombinieren
- Erweitern Sie Abfragen anfügen ▼ und dann Abfragen als neu anfügen.
- Markieren Sie im Dialog Anfügen die Option Drei oder mehr Tabellen.
- Markieren Sie im Kasten Verfügbare Tabelle(n) die beiden unteren Einträge und Klicken Sie auf Hinzuf… und anschließend ein Klick auf OK.
Sie erkennen, dass eine weitere neue Abfrage mit dem Namen Append1 erstellt wurde, wo untereinander alle Einträge der 3 zuvor erstellten Abfragen eingetragen sind. Nun ist es an der Zeit, die 1. Spalte Column1 so zu teilen, dass die überwiegend 2 Angaben künftig in getrennten Spalten stehen. Dazu gehen Sie über Start | Spalte teilen | Nach Trennzeichen (oder per Rechtsklick aus dem Kontextmenü) und wählen als Trennzeichen das Leerzeichen. Der Optik wegen können Sie vorher oder auch jetzt die Spalte WDH an den Anfang verschieben (ziehen oder per Kontextmenü); für die meisten Anwender ist diese Sichtweise angenehmer, gewohnter.
Verschiedene Zellen zeichnen sich ja dadurch aus, dass sie (wegen des vorangegangenen Ersetzungsvorgangs) leer sind und aus diesem Grunde den Wert null enthalten. Das würde bei einer der nachfolgenden Operationen zu einem Fehlerwert führen, darum markieren Sie die Spalten Column 1.1 und Column 1.2 und ersetzen den Wert null durch 0 (die Ziffer Null). Zugegeben, in Column1.1 sind wegen der geringen Zahl von Datensätzen (Zeilen) keine null-Werte sichtbar, aber schaden kann es nichts und „sicher ist sicher”… 💡 Wer weiß, wie sich geänderte Werte in den Quelldaten gestalten? Damit ist die Vorarbeit für eine etwas aufwändigere Operation abgeschlossen.
Für die folgenden Schritte gehen Sie über Spalte hinzufügen | Bedingte Spalte und tragen Sie im Dialog diese Werte ein:
- Neuer Spaltenname: W (der besseren Unterscheidung wegen als Großbuchstabe)
- Spaltenname | wenn: Column1.1 wählen
- Operator: endet mit auswählen
- Wert: w (kleines „w”)
- Ausgabe | Dann erweitern, im Dropdown Spalte auswählen: Column1.1
- Andernfalls: null
Die erste Bedingte Spalte, hier per GUI erstellt
Wenn Sie diesen Dialog mit OK bestätigt haben werden Sie rasch erkennen, dass nur die ersten 7 Zeilen einen Wert enthalten, der nicht null ist. Alle anderen Werte in der Spalte Column 1.1 sind ja auch nicht als w (Woche) ausgezeichnet.
Praktisch exakt das gleiche Vorgehen gilt für das Argument d (Day, Tag). Sie erstellen seine weitere Bedingte Spalte mit dem Spaltennamen/der Überschift D, wo sie bei Wert ein d in das Feld Wert eintragen. Der Bezug ist hier natürlich auch wieder Column 1.1. Und auch für die Argumente h (Hour, Stunde) und m (Minute) gehen Sie nach dem gleichen Muster vor.
Für die Spalte Column 1.2 ist mit einer einzigen Ausnahme das prinzipiell gleiche Vorgehen angesagt. Diese einzige Ausnahme: Da die größte Zeiteinheit der Tag ist, erzeugen Sie keine Spalte für das Argument w (Week/Woche) sondern beginnen mit dem d (day/Tag). Und logischerweise beziehen Sie sich auf die Spalte Column 1.2. Dem „Gesetz” folgend, dass in einer Tabelle keine 2 Spalten die gleiche Überschrift haben können, wird den von Ihnen vorgegebenen Überschriften (Spaltennamen) automatisch ein .1 automatisch angehängt.
Hinweis: Da ich ausgesprochen faul bin, 😎 erstelle ich diese eben erzeugten Spalten nicht über die GUI sondern über Spalte hinzufügen | Benutzerdefinierte Spalte und schreibe eine relativ kurze Formel. Diese kopiere ich für jeden neue Spalte und ändere nur die relevanten Argumente entsprechend. Das will ich hier jetzt nicht ausführen, aber vielleicht ist das ein kleiner Anreiz für Sie, in den Bereich der Sprache M etwas hinein zu schnuppern.
Mit jenen Werten, die in den eben generierten Spalten stehen, kann Power Query „natürlich” nicht rechnen, denn durch den Zusatz der Zeiteinheit sind es ja Texte. Darum markieren Sie die 7 Spalten W .. M.1 und wählen auf beliebigem Wege die Möglichkeit, Werte zu ersetzen. Im 1. Schritt werden Sie das w (das kleine w!) durch nichts ersetzen, sie lassen also das untere Textfeld einfach leer. Anschließend der gleiche Vorgang für die weiteren Zeiteinheiten d, h und m. Nun stehen nur noch Ziffern oder der Wert null in den neu generierten Spalten, und damit kann Power Query Rechenoperationen durchführen. Oder doch nicht? Nein, das sind Ziffern (also Text) und keine Zahlen, was auch ganz klar an der linksbündigen Ausrichtung erkennbar ist. Um das zu korrigieren, ändern Sie bei immer noch bestehender Markierung der 7 Spalten via Rechtsklick in eine der markierten Überschriften den Datentyp auf Ganze Zahl.
Markieren Sie nun ausschließlich die Spalte W, Transformieren | Standard | Multiplizieren und tragen Sie in das Eingabefeld die Zahl 10080 ein, denn 1 Woche hat ja 7*24*60, also 10.080 Minuten. Umgehend werden die derzeit dort stehenden Zahlen mit dem eben im Dialog eingetragenen Wert multipliziert und somit stehen nun in Spalte W die berechneten Minuten für den entsprechenden Zeitraum. – In Spalte D gehen Sie gleichermaßen vor, nur geben Sie als Multiplikator den Wert 1440 ein. Die Spalte H werden sie mit 60 multiplizieren und die Spalte M kann natürlich so bleiben, denn es sind ja schon die Minuten. Anschließend behandeln sie entsprechend die Spalten D.1 und H.1. Der jeweils getrennte Durchlauf ist erforderlich, da diese Berechnung stets nur in einer einzelnen Spalte durchgeführt werden kann. Mühsam ernährt sich das Eichhörnchen… 😉
Der nächste Schritt soll nun sein, dass zeilenweise die eben berechneten Werte und die ohne Berechnung bereits existierenden Minuten summiert werden. Dazu markieren Sie die Spalten W bis M.1, Spalte hinzufügen | Statistiken | Summe. Rechnen Sie gerne nach, das Ergebnis stimmt. 😉
Der Optik wegen
Der folgende Schritt dient in erster Linie der besseren, übersichtlicheren Darstellung. In den Spalten Column 1.1 und Column 1.2 haben sie ja den Wert null durch die Zahl 0 ersetzt, weil sonst Fehler bei der Berechnung aufgetreten wären. Da gleich diese beiden Spalten wieder in die ursprüngliche Darstellung vereinigt werden sollen, markieren Sie beide Spalten und ersetzen nun 0 durch null. Die beiden Spalten bleiben gemeinsam markiert, Transformieren | Spalten zusammenführen | Trennzeichen: Leerzeichen und als Neuer Spaltenname können Sie bereits an dieser Stelle beispielsweise RawData eintragen.
Markieren Sie nun die Spalten W bis M.1 und löschen Sie diese durch einen Klick auf Entf oder alternativ über das Kontextmenü. Als vorletzte Aktion werden sie die Spalte Addition in beispielsweise Minuten umbenennen.
Prinzipiell ist ja für jeden Wert von W/D/H_1 bis W/D/H_3 ein korrektes Ergebnis gegeben. Und wenn Sie es sich einfach machen wollen, kann diese Ansicht auch so bleiben, wie sie ist. In diesem Fall markieren Sie im Linkten Seitenfenster Tabelle1 und beispielsweise via Datei | Schließen & laden in… | Nur Verbindung erstellen und achten Sie darauf, dass auch alle weiteren Abfragen ‑mit Ausnahme von Append1- als Nur Verbindung gekennzeichnet sind. Die Query Append1 nun entweder in einem neuen Tabellenblatt oder an definierter Position im Tabellenblatt Tabelle1 speichern. Sollte Append1 auch als Nur Verbindung existieren (was durchaus denkbar ist), dann gehen Sie diesen Weg, um die Abfrage in ein Tabellenblatt zu laden.
Allerdings entspricht das derzeitige Ergebnis nicht der Wunsch-Vorgabe des Fragestellers. Aber auch das lässt sich mit Power Query verwirklichen. Auch wenn es vielleicht der eine oder andere Schritt mehr ist als es auf den ersten Blick scheint …
Aktivieren Sie erforderlichenfalls die Abfrage Append1.Erstellen Sie entweder über Start | Verwalten | Duplikat oder einen Rechtsklick auf diese Query im linken Seitenfenster einfach Duplizieren. Benennen Sie die so erstellte Abfrage idealerweise sofort um, ich nenne sie X 1. Wiederholen Sie diese Duplizierung der Query Append1 weitere 2 mal und vergeben Sie den Namen X 2 und X 3. Wählen Sie nun X 1 und filtern Sie die nach dem Begriff W/D/H_1. Entfernen Sie nun die 1. Spalte (Name: WDH). Jetzt Register Spalte hinzufügen | Index und ein 0‑basierte Index wird hinzugefügt. – Gleiches Vorgehen mit den Abfragen X 2 und X 3. Und nun speichern Sie die Abfrage X 1 auch unter Nur Verbindung erstellen, wodurch das auch für die beiden anderen X – Abfragen durchgeführt wird.
Auf in die letzte Runde! Aktivieren Sie die Query X 1. Start | Kombinieren | Abfragen zusammenführen ▼ erweitern | Abfragen als neue Abfrage zusammenführen. Wählen Sie X 2 als neue Abfrage und verknüpfen Sie die beiden Queries über die Spalte Index. Belassen Sie Join-Art bei der vorgegebenen Auswahl. Erweitern Sie nun die neue Spalte X 2, achten Sie darauf, dass das Häkchen bei Ursprünglichen Spaltennamen als Präfix verwenden nicht gesetzt ist und belassen Sie das Häkchen bei W/D/H_2 und Minuten. Das Ergebnis ist eine neue Abfrage mit dem Namen Merge1. Diese Abfrage nutzen Sie als Basis, als erste Abfrage, um auf fast gleichem Wege X 3 mit der „erweiterten” Merge1 zusammenzufügen. „Fast” gleicher Weg, weil Sie im ersten Schritt nach Kombinieren nur Abfragen zusammenführen wählen (also nicht als neue Abfrage), der Rest ist wie beschrieben. Und dass die Überschriften automatisch angepasst werden, das kennen Sie ja schon. – Jetzt nur noch die Spalte Index löschen und das Ergebnis „steht”. 😆
Bleibt nur noch meine Muster-Lösung, die Sie sich hier herunterladen können.
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag (z.B. 1,00€) Ihrerseits freuen …
Unabhängig davon: Wenn Sie nicht solch ein Fan von „Klicke di Klick” sind und lieber mit Formeln und Funktionen arbeiten, dann gibt es für diese Aufgabe einen Weg, der Ihnen gewiss mehr liegt. Durchaus anspruchsvoller aber (aus meiner Sicht) auch erheblich mehr „sexy” und effektiver! Das (spendenpflichtige | XV) File und Beschreibung sowie zusätzliche Informationen erhalten Sie als Lösung 2 auf eine Mail-Anfrage und ich versichere Ihnen, dass ich Ihre E‑Mail-Adresse nicht weiter geben werde. Ich selber ersticke in der Flut von Spam-Mails, das mute ich niemand anderem zu.