Spis treści
Kalendarz produkcji, czyli lista dat, w której wszystkie oficjalne dni robocze i święta są odpowiednio oznaczone – rzecz absolutnie niezbędna każdemu użytkownikowi Microsoft Excel. W praktyce nie można się bez niego obejść:
- w obliczeniach księgowych (wynagrodzenie, staż pracy, urlopy…)
- w logistyce – dla prawidłowego określenia terminów dostaw z uwzględnieniem weekendów i świąt (pamiętasz o klasycznym „po świętach?”)
- w zarządzaniu projektami – dla prawidłowego oszacowania terminów, z uwzględnieniem ponownie dni roboczych-nieroboczych
- dowolne wykorzystanie funkcji takich jak DZIEŃ ROBOCZY (DZIEŃ ROBOCZY) or CZYSTY PRACOWNICY (DNI ROBOCZE), bo jako argument wymagają listy świąt
- podczas korzystania z funkcji analizy czasu (takich jak TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR itp.) w Power Pivot i Power BI
- … itd. itd. – wiele przykładów.
Łatwiej jest tym, którzy pracują w korporacyjnych systemach ERP, takich jak 1C czy SAP, ponieważ wbudowany jest w nie kalendarz produkcji. Ale co z użytkownikami Excela?
Możesz oczywiście prowadzić taki kalendarz ręcznie. Ale wtedy będziesz musiał aktualizować co najmniej raz w roku (lub nawet częściej, jak w „wesołym” 2020 roku), ostrożnie wpisując wszystkie wymyślone przez nasz rząd weekendy, przelewy i dni wolne od pracy. A potem powtarzaj tę procedurę co kolejny rok. Nuda.
Co powiesz na trochę szaleństwa i stworzenie „wiecznego” kalendarza fabrycznego w programie Excel? Takiej, która sama się aktualizuje, pobiera dane z Internetu i zawsze generuje aktualną listę dni wolnych od pracy do późniejszego wykorzystania w jakichkolwiek obliczeniach? Kuszący?
W rzeczywistości nie jest to wcale trudne.
Źródło danych
Główne pytanie brzmi, skąd wziąć dane? W poszukiwaniu odpowiedniego źródła przeszedłem przez kilka opcji:
- Oryginalne dekrety są publikowane na rządowej stronie internetowej w formacie PDF (tu na przykład jeden z nich) i natychmiast znikają – nie można z nich wyciągnąć przydatnych informacji.
- Na pierwszy rzut oka kuszącą opcją wydawał się „Portal Otwartych Danych Federacji”, w którym znajduje się odpowiedni zestaw danych, ale po bliższym zbadaniu wszystko okazało się smutne. Strona jest strasznie niewygodna przy imporcie do Excela, pomoc techniczna nie odpowiada (samoizolowana?), a same dane są tam już od dawna nieaktualne – kalendarz produkcji na 2020 rok był ostatnio aktualizowany w listopadzie 2019 (hańba!) i , oczywiście, nie obejmuje na przykład naszego „koronawirusa” i weekendu „głosowania” w 2020 r.
Rozczarowany źródłami oficjalnymi zacząłem kopać te nieoficjalne. W Internecie jest ich wiele, ale większość z nich znowu zupełnie nie nadaje się do importowania do Excela i rozdawania kalendarza produkcyjnego w postaci pięknych zdjęć. Ale to nie nam powiesić go na ścianie, prawda?
A w trakcie poszukiwań przypadkowo odkryto cudowną rzecz – stronę http://xmlcalendar.ru/
Bez zbędnych „fajerwerków”, prosta, lekka i szybka strona, zaostrzona do jednego zadania – aby każdemu dać kalendarz produkcyjny na wybrany rok w formacie XML. Doskonały!
Jeśli nagle nie wiesz, to XML jest formatem tekstowym z treścią oznaczoną specjalnymi literami
Na wszelki wypadek skontaktowałem się z autorami strony i potwierdzili, że strona istnieje od 7 lat, dane na niej są na bieżąco aktualizowane (mają nawet oddział na githubie do tego) i nie zamierzają go zamykać. I nie przeszkadza mi to, że Ty i ja ładujemy z niego dane do dowolnego z naszych projektów i obliczeń w Excelu. Jest wolny. Miło wiedzieć, że wciąż są tacy ludzie! Szacunek!
Pozostaje załadować te dane do programu Excel za pomocą dodatku Power Query (w przypadku wersji programu Excel 2010-2013 można go pobrać bezpłatnie ze strony internetowej firmy Microsoft, a w wersjach programu Excel 2016 i nowszych jest już domyślnie wbudowany ).
Logika działań będzie następująca:
- Zwracamy się z prośbą o pobranie danych z serwisu za dowolny rok
- Przekształcenie naszej prośby w funkcję
- Tę funkcję stosujemy do listy wszystkich dostępnych lat, począwszy od 2013 roku aż do roku bieżącego – i otrzymujemy „wieczny” kalendarz produkcyjny z automatyczną aktualizacją. Voila!
Krok 1. Importuj kalendarz na rok
Najpierw załaduj kalendarz produkcji na dowolny rok, np. 2020. W tym celu w Excelu przejdź do zakładki Dane (lub Zapytanie o mocjeśli zainstalowałeś go jako osobny dodatek) i wybierz Z Internetu (Z sieci). W oknie, które zostanie otwarte, wklej link do odpowiedniego roku, skopiowany z witryny:
Po kliknięciu OK pojawi się okno podglądu, w którym należy kliknąć przycisk Konwertuj dane (Przekształć dane) or Aby zmienić dane (Edytuj dane) i przejdziemy do okna edytora zapytań Power Query, gdzie będziemy kontynuować pracę z danymi:
Natychmiast możesz bezpiecznie usunąć w prawym panelu Parametry żądania (Ustawienia zapytania) krok zmodyfikowany typ (Zmieniony typ) Nie potrzebujemy go.
Tabela w kolumnie święta zawiera kody i opisy dni wolnych od pracy – możesz zobaczyć jej zawartość „przeskakując” dwukrotnie klikając w zielone słowo Stół:
Aby wrócić, musisz usunąć w prawym panelu wszystkie kroki, które pojawiły się z powrotem do Źródło (Źródło).
Druga tabela, do której można wejść w podobny sposób, zawiera dokładnie to, czego potrzebujemy – daty wszystkich dni wolnych od pracy:
Pozostaje przetworzyć tę płytę, a mianowicie:
1. Filtruj tylko daty wakacji (tzn. te) według drugiej kolumny Atrybut: t
2. Usuń wszystkie kolumny oprócz pierwszej – klikając prawym przyciskiem myszy nagłówek pierwszej kolumny i wybierając polecenie Usuń inne kolumny (Usuń inne kolumny):
3. Podziel pierwszą kolumnę przez kropkę osobno na miesiąc i dzień za pomocą polecenia Podziel kolumnę — według ogranicznika Zakładka Transformacja (Przekształć — Podziel kolumnę — Według ogranicznika):
4. I na koniec utwórz kolumnę obliczeniową z normalnymi datami. W tym celu w zakładce Dodawanie kolumny kliknij przycisk Kolumna niestandardowa (Dodaj kolumnę — Niestandardowa kolumna) i wprowadź następującą formułę w wyświetlonym oknie:
=#przestarzały(2020, [#»Atrybut:d.1″], [#»Atrybut:d.2″])
W tym przypadku operator #date ma trzy argumenty: odpowiednio rok, miesiąc i dzień. Po kliknięciu OK otrzymujemy wymaganą kolumnę z normalnymi datami weekendów, a pozostałe usuwamy jak w kroku 2
Krok 2. Przekształcenie żądania w funkcję
Naszym kolejnym zadaniem jest przekształcenie zapytania utworzonego na rok 2020 w funkcję uniwersalną na dowolny rok (jego argumentem będzie numer roku). Aby to zrobić, wykonujemy następujące czynności:
1. Rozwijanie (jeśli jeszcze nie rozwinięte) panelu Zapytania (Zapytania) po lewej stronie w oknie Power Query:
2. Po przekonwertowaniu żądania na funkcję, niestety znika możliwość zobaczenia kroków składających się na żądanie i łatwej ich edycji. Dlatego warto zrobić kopię naszej prośby i już z nią poszaleć, a oryginał zostawić w rezerwie. Aby to zrobić, kliknij prawym przyciskiem myszy w lewym okienku nasze żądanie kalendarza i wybierz polecenie Powiel.
Ponowne kliknięcie prawym przyciskiem myszy na wynikowej kopii kalendarza(2) spowoduje wybranie polecenia przemianować (Przemianować) i wprowadź nową nazwę – niech to będzie np. fxRok:
3. Otwieramy kod źródłowy zapytania w wewnętrznym języku Power Query (jest on zwięźle nazywany „M”) za pomocą polecenia Zaawansowany edytor Zakładka Review(Widok — Edytor zaawansowany) i wprowadzaj tam drobne zmiany, aby nasza prośba stała się funkcją na dowolny rok.
To było:
Po:
Jeśli interesują Cię szczegóły, to tutaj:
- (rok jako liczba)=> – deklarujemy, że nasza funkcja będzie miała jeden argument liczbowy – zmienną rok
- Wklejanie zmiennej rok do łącza internetowego w kroku Źródło. Ponieważ dodatek Power Query nie pozwala na sklejanie liczb i tekstu, konwertujemy numer roku na tekst w locie za pomocą funkcji Numer.Do tekstu
- Zastępujemy zmienną rok 2020 w przedostatnim kroku #”Dodano niestandardowy obiekt«, gdzie z fragmentów utworzyliśmy datę.
Po kliknięciu koniec nasza prośba staje się funkcją:
Krok 3. Importuj kalendarze na wszystkie lata
Ostatnią rzeczą do zrobienia jest wykonanie ostatniego głównego zapytania, które załaduje dane dla wszystkich dostępnych lat i doda wszystkie otrzymane daty urlopów do jednej tabeli. Dla tego:
1. Klikamy prawym przyciskiem myszy w lewym panelu zapytania w szarym pustym miejscu i wybieramy kolejno Nowe żądanie – Inne źródła – Puste żądanie (Nowe zapytanie — Z innych źródeł — Puste zapytanie):
2. Musimy wygenerować listę wszystkich lat, dla których będziemy żądać kalendarzy, tj. 2013, 2014…2020. W tym celu w pasku formuły pustego zapytania, które się pojawi, wpisz polecenie:
Struktura:
={NumerA..NumerB}
… w dodatku Power Query generuje listę liczb całkowitych od A do B. Na przykład wyrażenie
={1}
… stworzy listę 1,2,3,4,5.
No cóż, żeby nie wiązać się sztywno do 2020 roku, korzystamy z funkcji DateTime.LocalNow() – odpowiednik funkcji Excel DZIŚ (DZISIAJ) w Power Query – i wyciąg z niego z kolei aktualny rok przez funkcję Data.Rok.
3. Wynikowy zestaw lat, choć wygląda całkiem adekwatnie, nie jest tabelą dla Power Query, ale specjalnym obiektem – podstęp (Lista). Ale przekształcenie go w tabelę nie stanowi problemu: wystarczy kliknąć przycisk Do stołu (Do stołu) w lewym górnym rogu:
4. Linia mety! Stosowanie funkcji, którą stworzyliśmy wcześniej fxRok do wynikowej listy lat. W tym celu w zakładce Dodawanie kolumny naciśnij przycisk Zadzwoń do funkcji niestandardowej (Dodaj kolumnę — Wywołaj funkcję niestandardową) i ustaw jej jedyny argument – kolumnę Column1 przez lata:
Po kliknięciu OK nasza funkcja fxRok import zadziała po kolei dla każdego roku i otrzymamy kolumnę, w której każda komórka będzie zawierała tabelę z datami dni wolnych od pracy (zawartość tabeli jest dobrze widoczna po kliknięciu w tle komórki obok słowo Stół):
Pozostaje rozwinąć zawartość zagnieżdżonych tabel, klikając ikonę z podwójnymi strzałkami w nagłówku kolumny Daty (kleszcz Użyj oryginalnej nazwy kolumny jako przedrostka można go usunąć):
… i po kliknięciu OK dostajemy to, czego chcieliśmy – zestawienie wszystkich świąt od 2013 roku do roku bieżącego:
Pierwszą, już niepotrzebną kolumnę, można usunąć, a dla drugiej ustawić typ danych dane (Data) na liście rozwijanej w nagłówku kolumny:
Samo zapytanie można zmienić na coś bardziej znaczącego niż Zapytanie1 a następnie wgraj wyniki do arkusza w postaci dynamicznej „inteligentnej” tabeli za pomocą polecenia zamknij i pobierz Zakładka Strona główna (Strona główna — Zamknij i załaduj):
Możesz zaktualizować utworzony kalendarz w przyszłości, klikając prawym przyciskiem myszy tabelę lub zapytanie w prawym okienku za pomocą polecenia Zaktualizuj i zapisz. Lub użyj przycisku Odśwież wszystko Zakładka Dane (Data — Odśwież wszystko) lub skrót klawiaturowy Ctrl+inny+F5.
To wszystko.
Teraz już nigdy nie musisz tracić czasu i namysłu na szukanie i aktualizowanie listy świąt – teraz masz „wieczny” kalendarz produkcji. W każdym razie tak długo, jak autorzy strony http://xmlcalendar.ru/ wspierają swoje potomstwo, co mam nadzieję, że będzie przez bardzo, bardzo długi czas (ponownie im dzięki!).
- Importuj stawkę bitcoin, aby osiągnąć Excel z Internetu za pomocą Power Query
- Znalezienie następnego dnia roboczego za pomocą funkcji WORKDAY
- Znajdowanie punktu przecięcia przedziałów dat