Power Query Quickies:
Produzierte Stücke je 24 Stunden, „normaler” Zeitstempel, Berechnung 3:30 Uhr bis 2:29 Uhr
Zu unregelmäßigen Zeitpunkten initiieren Mitarbeiter durch Knopfdruck an einen Gerät die Zählung der seit der letzten Erfassung produzierten Stücke einer Maschine. Die erfassten Daten enthalten Datum und Zeit sowie die Anzahl der Stücke. Datum und Uhrzeit entsprechen den realen Werten. Die Auswertung soll für jeden Produktions-Tag erfolgen, wobei dieser nicht von 0:00 Uhr bis 23:59 Uhr geht sondern von 3:30 Uhr bis 2:29 Uhr des folgenden Tages. Alle Zeiten, die vor 3:30 Uhr erfasst worden sind, zählen zum Vortag (genauer gesagt: Produktions-Vortag).
Obwohl solch eine Aufgabe auch beispielsweise mit der SUMMENPRODUKT() – Formel lösbar ist, können große Datenmengen (beispielsweise die Daten eines ganzen Jahres mit bis zu 100 Erfassungen je 24 Stunden) einen Rechner ziemlich in die Knie zwingen. 😕 Darum stelle ich Ihnen hier eine Lösung auf der Basis Power Query vor.
Beginnen Sie damit, diese gepackte csv-Datei erst zu entpacken und anschließend per Power Query in eine neue, leere Mappe zu importieren. Power Query erkennt ohne ihr Zutun, dass es sich um 2 Spalten handelt und dass als Trenner das Semikolon ; verwendet worden ist. Hinweis: Die *.csv habe ich als *.zip gepackt, damit beim Download Excel nicht automatisch die Datei öffnen kann.
Im 1. Schritt werden Sie dafür sorgen, dass Power Query jeder Datum/Zeit-Kombination jenes Datum zuweist, welches dem Produktionsdatum entspricht. Noch einmal zur Erinnerung: Der Produktionstag beginnt um 3:30 Uhr und endet um 2:29 Uhr des kommenden Tages. Dazu aktivieren Sie das Register Spalte hinzufügen | Benutzerdefinierte Spalte und schreiben in Neuer Spaltenname beispielsweise Produktions-Tag. In das große Feld Benutzerdefinierte Spaltenformel tragen Sie diese Formel ein:
[#"Produktions-Zeitpunkt"] -#duration(0, 3, 30, 0)
wobei sie den Feldnamen (Produktions-Zeitpunkt) durch einen Doppelklick auf den Eintrag im rechten Kasten übernehmen. Dadurch werden die zusätzlichen Zeichen wie die eckigen Klammern, die Raute und die Anführungszeichen automatisch übernommen. In der neuen Spalte erkennen sie, dass die Zeitangaben jeweils um dreieinhalb Stunden reduziert wurden:
Der Produktions-Tag ist nicht immer der kalendarische Tag
Sie erkennen auch, dass die Daten in der neuen Spalte das korrekte zu berechnende kalendarischen Datum tragen. Das beste Beispiel ist die Zeile 9, wo der reale Produktions-Zeitpunkt 3:30 Uhr früh war, was jedoch rechnerisch der Beginn des Produktionstages ist; darum wird hier in der Spalte Produktions-Tag auch 29.7.2018 0:00 Uhr berechnet. Und in Zeile 1 erkennen sie, dass in der Realität die Produktion zwar am 28. Juli um 2:52 Uhr erfasst wurde, jedoch 3,5 Stunden früher also am 27. Juli um 23:22 Uhr eingeordnet (berechnet) worden ist.
Da die Anzahl der produzierten Werkstücke pro Produktions-Tag berechnet werden soll, spielt die Uhrzeit jetzt keine Rolle mehr. Darum markieren Sie erforderlichenfalls die Spalte Produktions-Tag und wählen anschließend im Menüband Datum | Nur Datum. Lassen Sie die Spalte markiert, Rechtsklick in die Überschrift und wählen Sie im Kontextmenü Gruppieren nach… Im Dialog schreiben Sie als Neuer Spaltenname beispielsweise Produktion je Tag, bei Vorgang wählen Sie die Summe und bei Spalte soll die Summe natürlich aus den Zahlen der Spalte Produkte gebildet werden. OK und sie haben das Ergebnis:
Das berechnete Ergebnis je Produktions-Tag
Sie können der Klarheit wegen jetzt noch die Überschrift der 1. Spalte beispielsweise auf Datum (Produktionstag) ändern, um damit eine bessere Klarheit zu schaffen. Register Datei | Schließen & laden in… | Bestehendes Arbeitsblatt und da im Feld mit der Adresse bereits $A$1 steht, Klicken Sie direkt auf Laden. Das Ziel ist erreicht, es bietet sich hier noch an, in Spalte B das Zahlenformat so anzupassen, dass ein Tausenderpunkt eingefügt wird.