Spis treści
Wiele procesów biznesowych (a nawet całych biznesów) w tym życiu wiąże się z realizacją zamówień przez ograniczoną liczbę wykonawców w określonym terminie. Planowanie w takich przypadkach następuje, jak mówią, „z kalendarza” i często istnieje potrzeba przeniesienia zaplanowanych w nim wydarzeń (zamówień, spotkań, dostaw) do Microsoft Excel – do dalszej analizy według formuł, tabel przestawnych, wykresów, itp.
Oczywiście chciałbym zrealizować taki transfer nie przez głupie kopiowanie (co po prostu nie jest trudne), ale z automatyczną aktualizacją danych, aby w przyszłości wszystkie zmiany w kalendarzu i nowe zamówienia w locie wyświetlały się w Przewyższać. Taki import można zaimplementować w ciągu kilku minut za pomocą dodatku Power Query wbudowanego w Microsoft Excel, począwszy od wersji 2016 (w przypadku Excel 2010-2013 można go pobrać ze strony Microsoft i zainstalować osobno z linku) .
Załóżmy, że do planowania używamy darmowego Kalendarza Google, w którym dla wygody stworzyłem osobny kalendarz (przycisk ze znakiem plus w prawym dolnym rogu obok Inne kalendarze) z tytułem Praca. Tutaj wpisujemy wszystkie zamówienia, które należy zrealizować i dostarczyć do klientów na ich adresy:
Klikając dwukrotnie dowolne zamówienie, możesz wyświetlić lub edytować jego szczegóły:
Zauważ, że:
- Nazwa wydarzenia to kierownikkto spełnia to zamówienie (Elena) i Numer zamówienia
- Wskazany adres dostawa
- Notatka zawiera (w osobnych liniach, ale w dowolnej kolejności) parametry zamówienia: rodzaj płatności, kwotę, nazwę klienta itp. w formacie Parametr=Wartość.
Dla jasności rozkazy każdego menedżera są wyróżnione własnym kolorem, chociaż nie jest to konieczne.
Krok 1. Uzyskaj link do Kalendarza Google
Najpierw musimy uzyskać link do naszego kalendarza zamówień. Aby to zrobić, kliknij przycisk z trzema kropkami Opcje kalendarza Praca obok nazwy kalendarza i wybierz polecenie Ustawienia i udostępnianie:
W oknie, które się otworzy, możesz w razie potrzeby upublicznić kalendarz lub otworzyć do niego dostęp dla poszczególnych użytkowników. Potrzebujemy również linku do prywatnego dostępu do kalendarza w formacie iCal:
Krok 2. Załaduj dane z kalendarza do Power Query
Teraz otwórz Excel i na karcie Dane (jeśli masz Excel 2010-2013, to na karcie Zapytanie o moc) wybierz polecenie Z Internetu (Dane — z Internetu). Następnie wklej skopiowaną ścieżkę do kalendarza i kliknij OK.
iCal Power Query nie rozpoznaje formatu, ale łatwo jest pomóc. Zasadniczo iCal jest zwykłym plikiem tekstowym z dwukropkiem jako separatorem, a wewnątrz wygląda mniej więcej tak:
Możesz więc po prostu kliknąć prawym przyciskiem myszy ikonę pobranego pliku i wybrać format, który ma najbliższe znaczenie CSV – a nasze dane o wszystkich zamówieniach zostaną załadowane do edytora zapytań Power Query i podzielone na dwie kolumny po dwukropku:
Jeśli przyjrzysz się uważnie, możesz wyraźnie zobaczyć, że:
- Informacje o każdym zdarzeniu (kolejności) są pogrupowane w blok zaczynający się od słowa BEGIN i kończący się na END.
- Daty rozpoczęcia i zakończenia są przechowywane w ciągach oznaczonych DTSTART i DTEND.
- Adres do wysyłki to LOCATION.
- Notatka zamówienia – pole OPIS.
- Nazwa zdarzenia (nazwisko menedżera i numer zamówienia) — pole PODSUMOWANIE.
Pozostaje wydobyć te przydatne informacje i przekształcić je w wygodny stół.
Krok 3. Konwertuj do widoku normalnego
Aby to zrobić, wykonaj następujący łańcuch działań:
- Usuńmy górne 7 wierszy, których nie potrzebujemy przed pierwszym poleceniem BEGIN Strona główna — Usuń wiersze — Usuń górne wiersze (Strona główna — Usuń rzędy — Usuń górne rzędy).
- Filtruj według kolumny Column1 wiersze zawierające potrzebne nam pola: DTSTART, DTEND, DESCRIPTION, LOCATION i SUMMARY.
- Na karcie Zaawansowane Dodawanie kolumny wybierać Kolumna indeksu (Dodaj kolumnę — kolumnę Indeks)aby dodać kolumnę numeru wiersza do naszych danych.
- Właśnie tam na karcie. Dodawanie kolumny wybierz drużynę Kolumna warunkowa (Dodaj kolumnę — Kolumna warunkowa) a na początku każdego bloku (kolejności) wyświetlamy wartość indeksu:
- Wypełnij puste komórki w wynikowej kolumnie Blokowaćklikając prawym przyciskiem myszy jego tytuł i wybierając polecenie Wypełnij (Wypełnij).
- Usuń niepotrzebną kolumnę wskaźnik.
- Wybierz kolumnę Column1 i wykonaj splot danych z kolumny Column2 używając polecenia Przekształć – kolumna przestawna (Przekształć — kolumna Pivot). Pamiętaj, aby wybrać w opcjach Nie agreguj (Nie agreguj)aby do danych nie została zastosowana żadna funkcja matematyczna:
- W wynikowej tabeli dwuwymiarowej (krzyżowej) usuń ukośniki odwrotne w kolumnie adresu (kliknij prawym przyciskiem myszy nagłówek kolumny – Wymiana wartości) i usuń niepotrzebną kolumnę Blokować.
- Aby włączyć zawartość kolumn DTSTART и DTEND w pełnej dacie-godzinie, podświetlając je, wybierz na zakładce Przekształć – Data – Uruchom analizę (przekształcenie — data — analiza). Następnie poprawiamy kod w pasku formuły, zastępując funkcję Data, od on DataGodzina.Odaby nie tracić wartości czasu:
- Następnie, klikając prawym przyciskiem myszy na nagłówek, dzielimy kolumnę OPIS z parametrami zamówienia przez separator – symbol n, ale jednocześnie w parametrach wybierzemy podział na wiersze, a nie na kolumny:
- Kolejny raz dzielimy otrzymaną kolumnę na dwie oddzielne – parametr i wartość, ale przez znak równości.
- Wybór kolumny OPIS.1 wykonaj splot, tak jak to zrobiliśmy wcześniej, poleceniem Przekształć – kolumna przestawna (Przekształć — kolumna Pivot). Kolumna wartości w tym przypadku będzie kolumną z wartościami parametrów − OPIS.2 Pamiętaj, aby wybrać funkcję w parametrach Nie agreguj (Nie agreguj):
- Pozostaje ustawić formaty dla wszystkich kolumn i zmienić ich nazwy według potrzeb. I możesz przesłać wyniki z powrotem do programu Excel za pomocą polecenia Strona główna — Zamknij i załaduj — Zamknij i załaduj w… (Strona główna — Zamknij i załaduj — Zamknij i załaduj do…)
A oto nasza lista zamówień załadowanych do Excela z Kalendarza Google:
W przyszłości przy zmianie lub dodaniu nowych zamówień do kalendarza wystarczy tylko zaktualizować naszą prośbę poleceniem Dane – Odśwież wszystko (Dane — Odśwież wszystko).
- Kalendarz fabryczny w Excelu aktualizowany z Internetu za pomocą Power Query
- Przekształcenie kolumny w tabelę
- Utwórz bazę danych w Excelu