Power Query – pobieranie i przekształcanie danych w Excelu bez VBA

Istnieje wiele różnych podejść do przygotowania danych do raportów w Excelu, a każdy użytkownik radzi sobie tak, jak potrafi. Są więc użytkownicy, którzy przygotowują dane metodą kopiuj–wklej. Są i tacy, którzy tworzą, kopiując tylko formuły. Najwybitniejsi proces częściowo zautomatyzowali z użyciem VBA. W artykule z kolei pokazujemy, jak można pobierać i przekształcać dane bez korzystania z VBA.

13_INFO_13.jpg

Była połowa 2013 r. Pierwsi użytkownicy programu Excel otrzymywali egzemplarze programu Excel 2013, gdy Microsoft udostępnił na swojej stronie pierwszą wersję dodatku do programu Excel o nazwie Power Query. Kto się z nim zapoznał i śledził jego rozwój, już nigdy nie wrócił do tradycyjnego sposobu przygotowywania danych do raportów. Dziś, prawie pięć lat później, dodatek nadal jest praktycznie nieznany użytkownikom programu Excel, mimo iż trafił do Excela 2016/365, a pobrać może go każdy, kto posiada co najmniej wersję Excela 2010. Dużo częściej mówią oni o VBA. Power Query możemy wyobrazić sobie w wielu miejscach jako klikane VBA dla nie-programistów.

Tradycyjny proces przygotowania raportu programie Excel

Istnieje wiele różnych podejść do przygotowania danych do raportów w Excelu, a każdy użytkownik radzi sobie tak, jak potrafi. Są więc użytkownicy, którzy przygotowują dane metodą kopiuj–wklej. Są i tacy, którzy tworzą, kopiując tylko formuły. Najwybitniejsi proces częściowo zautomatyzowali z użyciem VBA. Na każdym poziomie pracy z Excelem próbujemy jednak wykonywać podobne rzeczy:

  • pobierać dane z systemów i plików źródłowych,
  • wklejać je do arkuszy Excel,
  • obudować formułami (z których najpopularniejsze to WYSZUKAJ.PIONOWO i SUMA.JEŻELI),
  • zbudować front graficzny w postaci dashboardów złożonych z tabeli i wykresów zwykłych lub przestawnych.

Proces ten w wielu firmach i organizacjach jest powtarzany z taką częstotliwością, z jaką potrzebne są raporty – od dziennej, przez tygodniową, do miesięcznej. Jest tym żmudniejszy, że każdorazowo wymaga sprawdzenia formuł, danych, wyników, zanim plik wyślemy do odbiorców.

Nowy proces przygotowania raportu w programie Excel

Według mnie są dwie rewolucje w programie Excel. Pierwsza – wprowadzenie tabel przestawnych, druga – wprowadzenie Power Query. Różnica jest tylko taka, że wprowadzenie Power Query odbyło się bez rozgłosu i pod różnymi nazwami, więc mamy do czynienia z cichą rewolucją, o której trzeba głośno mówić.

Nowy proces przygotowania raportu wygląda tak, że nagrywamy, niczym makro, ciąg powtarzalnych poleceń, klikając predefiniowane kroki w interfejsie. Na bieżąco obserwujemy (w oknie Zastosowane kroki) podgląd naszych danych, korygujemy błędy i sprawdzamy jakość. Do wyboru mamy szereg różnych poleceń, które umożliwiają wykonywanie działań na tabelach i kolumnach.

Jak zacząć pracę z Power Query?

  • Excel 2010/2013 – pobierz plik instalacyjny ze strony Microsoft: https://www.microsoft.com/en-us/download/details.aspx?id=39379 Pamiętaj, że język i wersję (koniecznie spójną z twoją wersją Excela: 32- lub 64-bitową) wybierasz, pobierając plik instalacyjny.
  • Excel 2016 – Power Query jest wbudowane w program. Zerknij na zakładkę Dane (Data) > Pobieranie i Przekształcanie (Get & Transform).
  • Excel 365 – jw. Power Query całkowicie zastąpiło stare sposoby pobierania danych w interfejsie programu Excel.

Przykład procesu przygotowania raportu przed Power Query

Oto przykładowy opis procesu przygotowania cotygodniowego raportu w jednej z firm produkcyjnych:

  1. Wyeksportuj dane z SAP jako CSV;
  2. Wczytaj dane z CSV do programu Excel;
  3. Podziel tekst jako kolumny;
  4. Skopiuj arkusz i wklej go do szablonu raportu;
  5. Skopiuj formułę WYSZUKAJ.PIONOWO, aby dla produktów odnaleźć elementy ze słownika (Kategoria, Podkategoria);
  6. Zmień źródło tabeli przestawnej;
  7. Odśwież tabelę przestawną na tabeli z SAP;
  8. Uaktualnij arkusz z budżetem;
  9. Uaktualnij formuły SUMA.JEŻELI;
  10. Zmień zakres wykresów na raporcie, obejmując dane za nowy tydzień.

Cały proces jest jednym z prostszych procesów raportowych i zajmuje zwykle ok. 30 minut.

Wykorzystałeś swój limit bezpłatnych treści

Pozostałe 66% artykułu dostępne jest dla zalogowanych użytkowników portalu. Zaloguj się, wybierz plan abonamentowy albo kup dostęp do artykułu/dokumentu.

Kilka wariantów prenumeraty Pokaż opcje
Dwutygodniowy dostęp bez zobowiązań Wybieram

Abonament już od 100 zł miesięcznie

Dwutygodniowy dostęp bez zobowiązań

Pełen dostęp do wszystkich treści portalu
to koszt 100 zł miesięcznie
przy jednorazowej płatności za rok

WYBIERAM

Dwutygodniowy dostęp do wszystkich treści
portalu za 99 zł netto, które odliczymy od ceny
regularnej przy przedłużeniu abonamentu

WYBIERAM

Pełen dostęp do wszystkich treści portalu
to koszt 100 zł miesięcznie
przy jednorazowej płatności za rok

Dwutygodniowy dostęp do wszystkich treści
portalu za 99 zł netto, które odliczymy od ceny
regularnej przy przedłużeniu abonamentu

WYBIERAM

Polityka cookies

Dalsze aktywne korzystanie z Serwisu (przeglądanie treści, zamknięcie komunikatu, kliknięcie w odnośniki na stronie) bez zmian ustawień prywatności, wyrażasz zgodę na przetwarzanie danych osobowych przez EXPLANATOR oraz partnerów w celu realizacji usług, zgodnie z Polityką prywatności. Możesz określić warunki przechowywania lub dostępu do plików cookies w Twojej przeglądarce.

Usługa Cel użycia Włączone
Pliki cookies niezbędne do funkcjonowania strony Nie możesz wyłączyć tych plików cookies, ponieważ są one niezbędne by strona działała prawidłowo. W ramach tych plików cookies zapisywane są również zdefiniowane przez Ciebie ustawienia cookies. TAK
Pliki cookies analityczne Pliki cookies umożliwiające zbieranie informacji o sposobie korzystania przez użytkownika ze strony internetowej w celu optymalizacji jej funkcjonowania, oraz dostosowania do oczekiwań użytkownika. Informacje zebrane przez te pliki nie identyfikują żadnego konkretnego użytkownika.
Pliki cookies marketingowe Pliki cookies umożliwiające wyświetlanie użytkownikowi treści marketingowych dostosowanych do jego preferencji, oraz kierowanie do niego powiadomień o ofertach marketingowych odpowiadających jego zainteresowaniom, obejmujących informacje dotyczące produktów i usług administratora strony i podmiotów trzecich. Jeśli zdecydujesz się usunąć lub wyłączyć te pliki cookie, reklamy nadal będą wyświetlane, ale mogą one nie być odpowiednie dla Ciebie.