Załóżmy, że prowadzisz kilka projektów z różnymi budżetami i chcesz zwizualizować swoje koszty dla każdego z nich. To znaczy z tej tabeli źródłowej:
.. zdobądź coś takiego:
Innymi słowy, musisz rozłożyć budżet na dni każdego projektu i uzyskać uproszczoną wersję wykresu Gantta projektu. Robienie tego własnymi rękami jest długie i nudne, makra są trudne, ale Power Query dla Excela w takiej sytuacji pokazuje swoją moc w całej okazałości.
Zapytanie o moc to dodatek firmy Microsoft, który może importować dane do programu Excel z niemal dowolnego źródła, a następnie przekształcać je na wiele różnych sposobów. W programie Excel 2016 ten dodatek jest już domyślnie wbudowany, a w przypadku programu Excel 2010-2013 można go pobrać z witryny firmy Microsoft, a następnie zainstalować na komputerze.
Najpierw zamieńmy nasz oryginalny stół w „inteligentny” stół, wybierając polecenie Formatuj jako tabelę Zakładka Strona główna (Strona główna — Formatuj jako tabelę) lub naciskając skrót klawiaturowy Ctrl+T :
Następnie przejdź do zakładki Dane (jeśli masz Excel 2016) lub na karcie Zapytanie o moc (jeśli masz program Excel 2010-2013 i zainstalowałeś dodatek Power Query jako osobny dodatek) i kliknij przycisk Z tabeli/zakresu. :
Nasza inteligentna tabela jest ładowana do edytora zapytań Power Query, gdzie pierwszym krokiem jest skonfigurowanie formatów liczb dla każdej kolumny za pomocą list rozwijanych w nagłówku tabeli:
Aby obliczyć budżet dzienny, musisz obliczyć czas trwania każdego projektu. Aby to zrobić, wybierz (przytrzymaj klawisz Ctrl) najpierw kolumna koniec, A następnie Start i wybierz drużynę Dodaj kolumnę – Data – Odejmij dni (Dodaj kolumnę — Data — Odejmij dni):
Wynikowe liczby są o 1 mniej niż to konieczne, ponieważ każdy projekt mamy rozpoczynać rano pierwszego dnia i kończyć wieczorem ostatniego dnia. Dlatego wybierz wynikową kolumnę i dodaj do niej jednostkę za pomocą polecenia Przekształć – Standard – Dodaj (Przekształć — Standard — Dodaj):
Dodajmy teraz kolumnę, w której obliczamy budżet dzienny. W tym celu w zakładce Dodaj kolumnę nie gram Kolumna niestandardowa (Kolumna niestandardowa) i w wyświetlonym oknie wprowadź nazwę nowego pola oraz wzór obliczeniowy, korzystając z nazw kolumn z listy:
Teraz najsubtelniejszy moment – tworzymy kolejną kolumnę obliczeniową z listą dat od początku do końca z krokiem 1 dnia. Aby to zrobić, ponownie naciśnij przycisk Kolumna niestandardowa (Kolumna niestandardowa) i użyj wbudowanego języka Power Query M, który nazywa się Lista.Daty:
Ta funkcja ma trzy argumenty:
- data rozpoczęcia – w naszym przypadku jest pobierana z kolumny Start
- ilość dat do wygenerowania – w naszym przypadku jest to ilość dni dla każdego projektu, którą policzyliśmy wcześniej w kolumnie Odejmowanie
- krok czasowy – ustalony według projektu #czas trwania(1,0,0,0), czyli w języku M – jeden dzień, zero godzin, zero minut, zero sekund.
Po kliknięciu OK otrzymujemy listę (Listę) dat, którą możemy rozwinąć do nowych wierszy za pomocą przycisku w nagłówku tabeli:
… i otrzymujemy:
Teraz pozostaje tylko zwinąć tabelę, używając wygenerowanych dat jako nazw nowych kolumn. Za to odpowiada zespół. Kolumna szczegółów (kolumna obrotowa) Zakładka konwertować (Przekształcać):
Po kliknięciu OK otrzymujemy wynik bardzo zbliżony do pożądanego:
Null jest w tym przypadku odpowiednikiem pustej komórki w Excelu.
Pozostaje usunąć niepotrzebne kolumny i wyładować wynikową tabelę obok oryginalnych danych za pomocą polecenia Zamknij i załaduj – Zamknij i załaduj w… (Zamknij i załaduj — Zamknij i załaduj do…) Zakładka Strona główna (Dom):
W rezultacie otrzymujemy:
Aby uzyskać większe piękno, możesz dostosować wygląd wynikowych inteligentnych tabel na karcie Konstruktor (Projekt): ustaw styl jednego koloru, wyłącz przyciski filtrów, włącz podsumowania itp. Dodatkowo możesz wybrać tabelę z datami i włączyć dla niej podświetlanie liczb za pomocą formatowania warunkowego na karcie Strona główna — Formatowanie warunkowe — Skale kolorów (Strona główna — Formatowanie warunkowe — Skale kolorów):
A najlepsze jest to, że w przyszłości możesz bezpiecznie edytować stare lub dodawać nowe projekty do oryginalnej tabeli, a następnie prawym przyciskiem myszy aktualizować odpowiednią tabelę datami – a Power Query automatycznie powtórzy wszystkie czynności, które wykonaliśmy .
Nie!
- Wykres Gantta w programie Excel przy użyciu formatowania warunkowego
- Kalendarz etapów projektu
- Generowanie zduplikowanych wierszy za pomocą dodatku Power Query