Inhalte
Produktionskalender, also eine Terminliste, in der alle offiziellen Werk- und Feiertage entsprechend gekennzeichnet sind – ein Muss für jeden Microsoft-Excel-Anwender. In der Praxis können Sie nicht darauf verzichten:
- bei Abrechnungen (Gehalt, Betriebszugehörigkeit, Urlaub …)
- in der Logistik – zur korrekten Bestimmung von Lieferzeiten unter Berücksichtigung von Wochenenden und Feiertagen (erinnern Sie sich an den Klassiker „Kommen Sie nach den Feiertagen?“)
- im Projektmanagement – für die korrekte Einschätzung der Fristen, wiederum unter Berücksichtigung von Arbeitstagen und arbeitsfreien Tagen
- jede Verwendung von Funktionen wie Arbeitstag (WERKTAG) or REINE ARBEITER (NETZWERKTAGE), weil sie eine Liste von Feiertagen als Argument benötigen
- bei Verwendung von Zeitintelligenzfunktionen (wie TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR usw.) in Power Pivot und Power BI
- … etc. etc. – viele Beispiele.
Es ist einfacher für diejenigen, die in ERP-Systemen von Unternehmen wie 1C oder SAP arbeiten, da der Produktionskalender darin integriert ist. Aber was ist mit Excel-Benutzern?
Natürlich können Sie einen solchen Kalender auch manuell führen. Aber dann müssen Sie es mindestens einmal im Jahr (oder sogar öfter, wie im „jolly“ 2020) aktualisieren und alle Wochenenden, Überweisungen und arbeitsfreien Tage, die von unserer Regierung erfunden wurden, sorgfältig eingeben. Und dann wiederholen Sie diese Prozedur jedes Jahr. Langeweile.
Wie wäre es, wenn Sie ein bisschen verrückt werden und einen „ewigen“ Fabrikkalender in Excel erstellen? Eines, das sich selbst aktualisiert, Daten aus dem Internet holt und immer eine aktuelle Liste arbeitsfreier Tage für spätere Berechnungen erstellt? Verlockend?
Dies ist in der Tat überhaupt nicht schwierig.
Datenquelle
Die Hauptfrage ist, woher die Daten kommen? Auf der Suche nach einer geeigneten Quelle ging ich mehrere Optionen durch:
- Die Originaldekrete werden auf der Website der Regierung im PDF-Format veröffentlicht (hier zum Beispiel eines davon) und verschwinden sofort – nützliche Informationen können ihnen nicht entnommen werden.
- Eine verlockende Option schien auf den ersten Blick das „Offene Datenportal des Bundes“ zu sein, wo es einen entsprechenden Datensatz gibt, doch bei näherer Betrachtung stellte sich alles als traurig heraus. Die Seite ist furchtbar umständlich für den Import in Excel, der technische Support reagiert nicht (selbstisoliert?) und die Daten selbst sind dort schon lange veraltet – der Produktionskalender für 2020 wurde zuletzt im November 2019 aktualisiert (Schande!) und Unser „Coronavirus“ und das „Abstimmungs“-Wochenende 2020 sind zum Beispiel natürlich nicht enthalten.
Desillusioniert von den offiziellen Quellen begann ich, inoffizielle zu recherchieren. Es gibt viele davon im Internet, aber die meisten sind wiederum völlig ungeeignet für den Import in Excel und geben einen Produktionskalender in Form von schönen Bildern aus. Aber es steht uns nicht zu, es an die Wand zu hängen, oder?
Und bei der Suche wurde zufällig etwas Wunderbares entdeckt – die Seite http://xmlcalendar.ru/
Ohne unnötigen „Schnickschnack“, eine einfache, leichte und schnelle Seite, geschärft für eine Aufgabe – um jedem einen Produktionskalender für das gewünschte Jahr im XML-Format zu geben. Exzellent!
Wenn Sie es plötzlich nicht mehr wissen, dann ist XML ein Textformat, dessen Inhalte mit special gekennzeichnet sind
Für alle Fälle habe ich die Autoren der Site kontaktiert und sie haben bestätigt, dass die Site seit 7 Jahren existiert, die Daten darauf ständig aktualisiert werden (sie haben dafür sogar einen Zweig auf Github) und sie werden sie nicht schließen. Und ich habe überhaupt nichts dagegen, dass Sie und ich Daten für alle unsere Projekte und Berechnungen in Excel laden. Ist gratis. Schön zu wissen, dass es solche Menschen noch gibt! Respekt!
Es bleibt, diese Daten mit dem Power Query-Add-In in Excel zu laden (für Versionen von Excel 2010-2013 kann es kostenlos von der Microsoft-Website heruntergeladen werden, und in Versionen von Excel 2016 und höher ist es bereits standardmäßig integriert ).
Die Logik der Aktionen wird wie folgt sein:
- Wir stellen eine Anfrage zum Herunterladen von Daten von der Website für ein beliebiges Jahr
- Unsere Anfrage in eine Funktion umwandeln
- Wir wenden diese Funktion auf die Liste aller verfügbaren Jahre ab 2013 bis zum aktuellen Jahr an – und erhalten einen „ewigen“ Produktionskalender mit automatischer Aktualisierung. Voila!
Schritt 1. Importieren Sie einen Kalender für ein Jahr
Laden Sie zunächst den Produktionskalender für ein beliebiges Jahr, z. B. für 2020. Gehen Sie dazu in Excel auf die Registerkarte Datum (oder Power Querywenn Sie es als separates Add-on installiert haben) und wählen Sie aus Aus dem Internet (Aus dem Internet). Fügen Sie in dem sich öffnenden Fenster den Link zum entsprechenden Jahr ein, der von der Website kopiert wurde:
Nach dem Klicken auf OK Es erscheint ein Vorschaufenster, in dem Sie auf die Schaltfläche klicken müssen Daten konvertieren (Daten transformieren) or Um die Daten zu ändern (Daten bearbeiten) und wir gelangen zum Fenster des Power Query-Abfrage-Editors, wo wir mit den Daten weiterarbeiten:
Sofort können Sie sicher im rechten Bereich löschen Anforderungsparameter (Abfrageeinstellungen) Step modifizierter Typ (Geänderter Typ) Wir brauchen ihn nicht.
Die Tabelle in der Spalte Feiertage enthält Codes und Beschreibungen von arbeitsfreien Tagen – Sie können ihren Inhalt sehen, indem Sie sie zweimal „durchblättern“, indem Sie auf das grüne Wort klicken Tisch:
Um zurückzugehen, müssen Sie im rechten Bereich alle Schritte löschen, zu denen Sie zurückgekehrt sind Quelle (Quelle).
Die zweite Tabelle, die auf ähnliche Weise aufgerufen werden kann, enthält genau das, was wir brauchen – die Daten aller arbeitsfreien Tage:
Es bleibt, diese Platte zu bearbeiten, nämlich:
1. Filtern Sie nur Feiertagsdaten (dh Einsen) nach der zweiten Spalte Attribut: t
2. Löschen Sie alle Spalten außer der ersten – indem Sie mit der rechten Maustaste auf die Überschrift der ersten Spalte klicken und den Befehl auswählen Löschen Sie andere Spalten (Andere Spalten entfernen):
3. Erste Spalte durch Punkt getrennt für Monat und Tag mit Befehl teilen Spalte teilen – Nach Trennzeichen Tab Transformation (Umwandeln – Spalte teilen – Nach Trennzeichen):
4. Und schließlich eine berechnete Spalte mit normalen Daten erstellen. Dazu auf der Registerkarte Spalte hinzufügen Klicken Sie auf die Schaltfläche Benutzerdefinierte Spalte (Spalte hinzufügen – Benutzerdefinierte Spalte) und geben Sie im erscheinenden Fenster folgende Formel ein:
=#datiert(2020, [#»Attribut:d.1″], [#»Attribut:d.2″])
Hier hat der #date-Operator drei Argumente: Jahr, Monat bzw. Tag. Nach dem Anklicken OK Wir erhalten die erforderliche Spalte mit normalen Wochenenddaten und löschen die verbleibenden Spalten wie in Schritt 2
Schritt 2. Die Anfrage in eine Funktion umwandeln
Unsere nächste Aufgabe besteht darin, die für 2020 erstellte Abfrage in eine universelle Funktion für ein beliebiges Jahr umzuwandeln (die Jahreszahl wird ihr Argument sein). Dazu gehen wir wie folgt vor:
1. Erweitern (falls nicht bereits erweitert) des Panels Anfragen (Abfragen) links im Power Query-Fenster:
2. Nachdem die Anfrage in eine Funktion umgewandelt wurde, verschwindet leider die Möglichkeit, die Schritte zu sehen, aus denen die Anfrage besteht, und sie einfach zu bearbeiten. Daher ist es sinnvoll, sich eine Kopie unserer Anfrage anzufertigen und schon damit herumzutollen, und das Original in Reserve zu lassen. Klicken Sie dazu im linken Bereich mit der rechten Maustaste auf unsere Kalenderanfrage und wählen Sie den Befehl Duplizieren.
Wenn Sie erneut mit der rechten Maustaste auf die resultierende Kopie des Kalenders (2) klicken, wird der Befehl ausgewählt Umbenennen (Umbenennen) und geben Sie einen neuen Namen ein – sei es zum Beispiel fxJahr:
3. Mit dem Befehl öffnen wir den Abfrage-Quellcode in der internen Power Query-Sprache (sie heißt lapidar „M“) Erweiterter Editor Tab Bewertung(Ansicht – Erweiterter Editor) und nehmen Sie dort kleine Änderungen vor, um unsere Anfrage in eine Funktion für jedes Jahr zu verwandeln.
Es war:
Nach:
Wenn Sie an den Details interessiert sind, dann hier:
- (Jahr als Zahl)=> – wir erklären, dass unsere Funktion ein numerisches Argument haben wird – eine Variable Jahr
- Variable einfügen Jahr zum Weblink im Schritt Quelle. Da Power Query es Ihnen nicht erlaubt, Zahlen und Text zusammenzufügen, wandeln wir die Jahreszahl mithilfe der Funktion on the fly in Text um Number.ToText
- Im vorletzten Schritt ersetzen wir die Jahresvariable für 2020 #”Benutzerdefiniertes Objekt hinzugefügt«, wo wir aus den Fragmenten das Datum gebildet haben.
Nach dem Klicken auf Endziel unsere Anfrage wird zu einer Funktion:
Schritt 3. Kalender für alle Jahre importieren
Als letztes bleibt noch die letzte Hauptabfrage, die Daten für alle verfügbaren Jahre hochlädt und alle empfangenen Urlaubsdaten in einer Tabelle zusammenfügt. Dafür:
1. Wir klicken im linken Abfragebereich mit der rechten Maustaste in eine graue Leerstelle und selektieren sequentiell Neue Anfrage – Andere Quellen – Leere Anfrage (Neue Abfrage – Aus anderen Quellen – Leere Abfrage):
2. Wir müssen eine Liste aller Jahre erstellen, für die wir Kalender anfordern werden, dh 2013, 2014 … 2020. Geben Sie dazu in der Formelleiste der erscheinenden leeren Abfrage den Befehl ein:
Struktur:
={ZahlA..ZahlB}
… in Power Query generiert eine Liste von ganzen Zahlen von A bis B. Zum Beispiel der Ausdruck
={1..5}
… würde eine Liste von 1,2,3,4,5 ergeben.
Nun, um nicht starr an 2020 gebunden zu sein, nutzen wir die Funktion DateTime.LocalNow() – analog zur Excel-Funktion HEUTE bestellen (HEUTE) in Power Query – und daraus wiederum per Funktion das aktuelle Jahr extrahieren Datum.Jahr.
3. Die resultierende Jahreszahl ist, obwohl sie ziemlich angemessen aussieht, keine Tabelle für Power Query, sondern ein spezielles Objekt – Liste (Liste). Aber die Umwandlung in eine Tabelle ist kein Problem: Klicken Sie einfach auf die Schaltfläche Zu Tisch (Zu Tisch) in der oberen linken Ecke:
4. Ziellinie! Anwenden der Funktion, die wir zuvor erstellt haben fxJahr zu der resultierenden Liste von Jahren. Dazu auf der Registerkarte Spalte hinzufügen Drücken Sie den Knopf Benutzerdefinierte Funktion aufrufen (Spalte hinzufügen – Benutzerdefinierte Funktion aufrufen) und setze sein einziges Argument – die Spalte Column1 über die Jahre:
Nach dem Klicken auf OK unsere Funktion fxJahr Der Import funktioniert der Reihe nach für jedes Jahr und wir erhalten eine Spalte, in der jede Zelle eine Tabelle mit den Daten arbeitsfreier Tage enthält (der Inhalt der Tabelle ist deutlich sichtbar, wenn Sie in den Hintergrund der Zelle neben klicken das Wort Tisch):
Es bleibt, den Inhalt von verschachtelten Tabellen zu erweitern, indem Sie auf das Symbol mit den Doppelpfeilen in der Spaltenüberschrift klicken Datum (Tick Verwenden Sie den ursprünglichen Spaltennamen als Präfix es kann entfernt werden):
… und nach dem Anklicken OK wir bekommen, was wir wollten – eine Liste aller Feiertage von 2013 bis zum laufenden Jahr:
Die erste, ohnehin unnötige Spalte kann man löschen und für die zweite den Datentyp setzen Datum (Datum) in der Dropdown-Liste in der Spaltenüberschrift:
Die Abfrage selbst kann in etwas Sinnvolleres umbenannt werden als Anfrage1 und laden Sie dann die Ergebnisse in Form einer dynamischen „intelligenten“ Tabelle mit dem Befehl in das Blatt hoch schließen und herunterladen Tab Startseite (Startseite — Schließen & Laden):
Sie können den erstellten Kalender in Zukunft aktualisieren, indem Sie mit der rechten Maustaste auf die Tabelle oder Abfrage im rechten Bereich über den Befehl klicken Aktualisieren & Speichern. Oder verwenden Sie die Schaltfläche Alle erfrischen Tab Datum (Datum — Alle aktualisieren) oder Tastenkürzel Ctrl+Andere+F5.
Das ist alles.
Ab sofort brauchen Sie nie wieder Zeit und Denkanstöße mit der Suche und Aktualisierung der Feiertagsliste zu verschwenden – ab sofort haben Sie einen „ewigen“ Produktionskalender. Auf jeden Fall, solange die Autoren der Seite http://xmlcalendar.ru/ ihren Nachwuchs unterstützen, was, wie ich hoffe, noch sehr, sehr lange so bleiben wird (nochmals vielen Dank!).
- Importieren Sie den Bitcoin-Kurs aus dem Internet über Power Query in Excel
- Den nächsten Werktag mit der WORKDAY-Funktion finden
- Den Schnittpunkt von Datumsintervallen finden