Xtract: Lösung einer Anfrage aus einem Forum. Für mehrere Produkte sollen die jeweiligen Wektage (unter berücksichtigung der Feiertage) mit PQ berechnet werden. Dieser Beitrag ist eine Kurzform mit knappen bis keinen Erklärungen des Weges zum Ziel.
Wissensstand: Level 3 ⇒ Excel GUT!, mindestens Basis-Kenntnisse in Power Query
Anzahl der Werktage für die Fertigung eines Produkts
(Kurzform: Andere Darstellungsweise als gewünscht)
In einen einem Forum wurde die Frage aufgeworfen, wie die Verarbeitungszeit in Tagen verschiedener Projekte war. Jedes das Projekt ist an eine eindeutige ID gebunden. Als Besonderheit kommt noch hinzu, dass zwischen dem ersten und dem letzten Tag verschiedene weitere Einträge (beispielsweise für entnommene Stichproben) gemacht worden sind. Als weitere Besonderheit gilt, dass nur die Werktage Montag bis Freitag gezählt werden sollen. Außerdem sollen auch noch definierte Feiertage als arbeitsfrei berücksichtigt werden.
Ich habe die Mustertabelle aus dem Forum heruntergeladen und stelle Ihnen diese (leicht modifiziert) hier zum Download zur Verfügung. Auch wenn es seitens des Fragestellers (bislang) nicht erwähnt worden ist gehe ich davon aus, dass die berechneten Arbeitstage wie auch in Spalte C stets in die letzte Zeile des jeweiligen Projekts eingetragen werden sollen.
In diesem frei verfügbaren Beitrag gibt es zwei Einschränkungen; eine gegenüber der durch den Fragesteller gewünschten Darstellungsform (letze Zeile des Projekts) und eine in der sonst üblichen Form der Ausführlichkeit der Beiträge dieses Blogs. Mehr dazu können Sie weiter unten oder in diesem Hinweis (Passwort: Hilfe) nachlesen.
Einstieg
Zu Beginn importieren Sie nun die Daten der Spalten A:B in den Power Query-Editor. Ich habe für Sie die Formatierung als Intelligente Tabelle bereits vorgenommen und den Namen der Tabelle auf Data angepasst. Idealerweise werden sie sofort nach dem Import Schließen & laden in… auszuwählen und die Auswahl Nur Verbindung erstellen treffen. Naturgemäß befindet sie sich danach wieder im Arbeitsblatt Tabelle1. Im Bereich D1:E13 sind die kalendarischen Daten der Feiertage für die Jahre 2019 und 2020 aufgeführt. Auch diesen Bereich habe ich entsprechend formatiert und der Liste den Namen Feiertage gegeben. Importieren Sie auch diese Tabelle nach Power Query.
Wo sie schon einmal in der Abfrage Feiertage sind können Sie auch gleich damit beginnen, die kalendarischen Daten so zu positionieren, dass diese nur in einer Spalte untereinander angeordnet sind. Eine der Möglichkeiten wäre, eine Anfüge-Abfrage zu starten. Ich gehe mit Ihnen einen anderen Weg und markiere beide Spalten, Rechtsklick in einer der beiden Überschriften und im Kontextmenü ein Klick auf Entpivotieren. Auch wenn die chronologische Reihenfolge nicht sortiert ist kann das so bleiben. Löschen Sie die Spalte Attribut und benennen Sie die übrig gebliebene Spalte Feiertage. Nun auch hier die kalendarischen Daten in den Datentyp: Datum umwandeln und danach diese Abfrage per Schließen & laden in… als Nur Verbindung speichern.
Ersten und letzten Tag je Projekt feststellen
Im weiteren Ablauf geht es darum, den ersten und den letzten Tag jedes Projekts festzustellen und in die Abfrage einzutragen. Der hier aufgezeigte Weg ist (der Kurz-Version wegen) ohne weitere Erklärung; einfach nur nacharbeiten und zum Ziel gelangen. In der $$-Version dieses Themas wird ein etwas anderer, gewiss besser verständlicher Weg gegangen und auch entsprechend ausführlich dokumentiert.
Öffnen Sie die Abfrage Data auf beliebige Weise. Markieren Sie nun die Spalte mit der eigentlichen ID Dokument No und wählen Sie nach einem Rechtsklick in diese Überschrift Gruppieren nach… Im Dialog können Sie Neuer Spaltenname bei Anzahl belassen, ich ziehe als künftige Überschrift Daten vor. Bei Vorgang wählen Sie die unterste Zeile Alle Zeilen und danach OK. Das Ergebnis ist eine 2‑spaltige, 6 Zeilen umfassende Abfrage, wo in der zweiten Spalte (Daten) ausschließlich der Wert Table eingetragen worden ist.
Im Menü Spalte hinzufügen ein Klick auf Benutzerdefinierte Spalte und tragen Sie im Dialog bei Neuer Spaltenname den Wert Start ein und bei Benutzerdefinierte Spaltenformel schreiben Sie diese Formel:
Table.Min([Daten],"Datum")
… und das Ergebnis ist eine weitere Spalte Start mit dem durchgängigen Wert Record. Wiederholen Sie diesen Vorgang mit fast der gleichen Formel, nur dass sie statt Table.Min die Funktion Table.Max verwenden und als Überschrift natürlich Ende einsetzen. Erweitern Sie nun die beiden Spalten Start und Ende derartig, dass nur der Wert aus der Zeile Datum ausgegeben wird, anschließend löschen Sie die Spalte Daten und ändern Sie die Überschriften Datum (wieder) in Start, Datum.1 entsprechend in Ende.
Ändern Sie den Datentyp der beiden Spalten Start und Ende auf Ganze Zahl. Das ist die beste Voraussetzung um für jeden Tag des Zeitbereichs eine einzelne Zeile zu generieren. Immer noch im Menü Spalte hinzufügen legen Sie eine Benutzerdefinierte Spalte mit dem Spaltennamen Datum und der Spaltenformel
= {[Start]..[Ende]}
an. Löschen Sie die beiden Spalten Start und Ende und erweitern Sie Datum. Ändern Sie den Datentyp dieser Spalte nun wieder auf Datum.
Im Menüband Datum | Tag | Name des Tags und Power Query fügt eine Spalte mit dem landesüblichen Namen des Wochentages ein. Filtern Sie diese Spalte so, dass Samstag und Sonntag entfernt werden. Wechseln Sie zum Menü Start und ein Klick auf Abfragen zusammenführen. Wählen Sie als untere Abfrage Feiertage und markieren Sie die beiden Spalten mit den kalendarischen Daten. Bei Join-Art wählen Sie den Linken Anti-Join und nach einem OK können Sie mit Ausnahme der Spalte Document No alle anderen Spalten löschen.
Endspurt
Nun ein Rechtsklick in die einzig verbliebene Überschrift und wählen Sie Gruppieren nach… Übernehmen Sie die Vorgaben und Power Query hat das gewünschte Ergebnis berechnet. Für jede ID wurde die korrekte Anzahl von Arbeitstagen berechnet. Nach einem Klick auf Schließen & laden werden sie das Ergebnis naturgemäß noch nicht in ihrem Arbeitsblatt sehen. Aber über diesen Weg platzieren sie die Ergebnis-Tabelle an gewünschter Position.
Zugegeben, das war ein teilweise anspruchsvoller Schnelldurchgang, der nicht in üblicher Form kommentiert ist. Wie oben schon erwähnt gibt es aber einen Blog-Beitrag zu genau diesem Thema und mit exakt diesen Daten, wo zwar teilweise ein etwas anderer Weg beschritten wird aber die Vorgehensweise für „Normal-User” gewiss besser nachvollziehbar und auch in gewohnter Form ausführlich beschrieben ist. – Hier ein Hinweis dazu, das erforderliche Passwort ist Hilfe.
Ein wichtiger Hinweis
Der Link auf den Folge-Beitrag beginnt mit „$$”. Das bedeutet für alle derartig ausgezeichneten Beiträge, dass diese kostenpflichtig sind (eine geringe Spende).
Näheres dazu in der angesprochenen Mail. Auch Wissen hat einen Wert!
Wie eben schon dargelegt habe ich auch eine ausführliche Beschreibung meines Lösungsweges in bekannter, ausführlicher Form erstellt. Eine E‑Mail an mich mit dem Stichwort/Betreff $$ Fertigungsdauer in Werktagen und Sie bekommen von mir den entsprechenden Link sowie das erforderliche Passwort zum öffnen des Beitrages, sofern Sie mir eine eine Spende von 5,00€ (Überweisung, Donate-Button, PayPal Freundschaft oder Amazon-Gutschein) haben zukommen lassen.