Jak korzystać z dodatku Power Query w programie Excel: kompletny przewodnik

Power Query to potężne narzędzie analizy biznesowej w programie Excel, które umożliwia importowanie, czyszczenie i przekształcanie danych zgodnie z potrzebami. Jeśli kiedykolwiek spędziłeś godziny na czyszczeniu danych (my to zrobiliśmy) lub miałeś trudności z połączeniem danych z różnych źródeł (my też to zrobiliśmy), ta funkcja ułatwi ci życie.

Najlepszym sposobem nauczenia się korzystania z dodatku Power Query w programie Excel jest wykonanie typowych zadań, takich jak importowanie danych i używanie edytora dodatku Power Query do przekształcania wynikowych tabel i łączenia danych.

Ten artykuł pomoże Ci rozpocząć, przedstawiając przejrzyste przykłady dodatku Power Query wraz z przewodnikami krok po kroku . Dzięki opanowaniu Power Query możesz przenieść swoją pracę na wyższy poziom.

Jesteś gotowy? Zróbmy to.

Spis treści

• Jak sprawdzić, czy możesz uzyskać dostęp do Power Query
• Jak importować dane za pomocą dodatku Power Query
• Jak korzystać z edytora Power Query
  • Podgląd danych
  • Nieruchomości
• Jak pracować z wieloma źródłami danych
• Jak dołączyć dane za pomocą dodatku Power Query
• Jak łączyć dane za pomocą dodatku Power Query
• 5 sposobów przekształcania danych za pomocą dodatku Power Query
  • 1. Rozszerzanie i kondensowanie danych
  • 2. Sortowanie danych
  • 3. Formatowanie danych
  • 4. Dodawanie i usuwanie kolumn
  • 5. Zmiana typów danych
• Praca z formułami i językiem M
  • Pasek Formuły
  • Pisanie i modyfikowanie kodu M
• Co to jest składnia języka M?
• Jak odświeżyć połączenia danych
• Jak zintegrować Power Query z modelem danych
• Jak zautomatyzować za pomocą Power Query i VBA
• Zaawansowane funkcje Power Query
• Współpraca i udostępnianie
• Podsumujmy to

Jak sprawdzić, czy możesz uzyskać dostęp do Power Query

Zanim zaczniesz korzystać z dodatku Power Query, musisz się upewnić, że masz wersję programu Excel, która ma tę funkcję.

Oto krótkie omówienie dodatku Power Query według wersji:

• Excel 2010 i Excel 2013: dostępny jako dodatek, który należy pobrać i zainstalować.

• Excel 2016: zintegrowany z programem Excel jako „Pobierz i przekształć”.

• Excel 2019: poprawiona wydajność i funkcjonalność.

• Excel 2021 i Excel dla Microsoft 365: integracja ze źródłami danych w chmurze.

• Excel dla komputerów Mac: dostępny tylko z Microsoft 365 w wersji 16.69 i nowszych.

Aby sprawdzić, czy narzędzie jest zintegrowane ze skoroszytem programu Excel (2016 lub nowszym), przejdź do karty Dane i poszukaj sekcji „Pobierz i przekształć dane” na tym obrazku:

Jeśli masz program Excel 2010 lub 2013 w systemie Windows, możesz pobrać , zainstalować i włączyć dodatek Power Query.

Jak importować dane za pomocą dodatku Power Query

Narzędzie Power Query umożliwia importowanie i łączenie się z szeroką gamą źródeł danych:

• Pliki, w tym formaty CSV, XML i JSON

• Adresy URL lub punkty końcowe interfejsu API

• Bazy danych, w tym Access, SQL Server, Oracle i większość nowoczesnych baz danych

• Usługi platformy Azure, pliki programu SharePoint i źródła danych OData

Najprostszym źródłem danych na początek jest prosty plik CSV z kilkoma wierszami danych. Utwórz plik tekstowy i dodaj te cztery imiona i nazwiska, oddzielone przecinkiem:

• Joe, Bloggi

• Ania, Ryan

• Jakub, Stewart

• Maria, Brown

Pamiętaj, aby zapisać plik z rozszerzeniem .CSV. Twój plik będzie wyglądał tak:

Aby załadować dane za pomocą dodatku Power Query, wykonaj następujące kroki:

1. Przejdź do zakładki Dane.

2. Kliknij przycisk „Pobierz pliki” na karcie Pobierz i przekształć dane.

3. Wybierz swój przykładowy plik.

Być może będziesz musiał poczekać kilka sekund, aż narzędzie pobierze dane do okienka podglądu. Power Query zużywa sporo zasobów maszynowych nawet przy niewielkim imporcie, więc prosimy o cierpliwość.

Wskazówka: zamknij inne aplikacje, aby przyspieszyć działanie.

Power Query wyświetli okno „Import tekstu/CSV” z podglądem Twoich danych.

Jeśli dane wyglądają poprawnie, możesz przystąpić do ładowania lub przekształcania danych:

• przycisk Załaduj spowoduje załadowanie danych bezpośrednio do programu Excel.

• przycisk Przekształć dane umożliwia modyfikację danych przed ich załadowaniem.

Czy zauważyłeś, że przykładowy plik nie miał nagłówka? Jeśli zostanie załadowany bezpośrednio do programu Excel, nagłówki będą miały postać „Kolumna 1” i „Kolumna 2”.

Zamiast tego możesz pobrać dane do edytora dodatku Power Query i wprowadzić pewne zmiany. Aby to zrobić, kliknij przycisk „Przekształć dane”.

Otworzy się teraz edytor Power Query, który przenosi nas do następnej sekcji.

Jak korzystać z edytora Power Query

W tej sekcji omówimy niektóre podstawowe aspekty pracy z edytorem dodatku Power Query.

Podgląd danych

W obszarze Podgląd danych możesz przeglądać zaimportowane dane i wchodzić z nimi w interakcję. Możesz filtrować wiersze, modyfikować typy danych w kolumnach i przekształcać dane zgodnie z własnymi potrzebami.

Po zakończeniu program Excel załaduje przekształcone dane do arkusza.

Korzystając z przykładowego pliku, możesz edytować nazwy kolumn bezpośrednio w tym okienku.

Aby to zrobić:

1. Kliknij prawym przyciskiem myszy nagłówek kolumny.

2. Kliknij „Zmień nazwę” w rozwijanym menu.

3. Wpisz preferowane nazwy w polach nagłówka.

W miarę wprowadzania zmian sekcja „Zastosowane kroki” po prawej stronie okienka podglądu jest aktualizowana w celu odzwierciedlenia każdej transformacji, umożliwiając śledzenie i modyfikowanie kroków przetwarzania danych zgodnie z potrzebami.

Nieruchomości

Ustawienie właściwości zapytań jest niezbędne do organizowania i śledzenia pracy.

Okienko Ustawienia kwerendy znajduje się po prawej stronie okna dodatku Power Query.

1. Kliknij łącze „Wszystkie właściwości”.

2. Edytuj nazwę zapytania, aby była opisowa.

3. Dodaj opis, aby podać więcej informacji.

Łatwo zapomnieć, do czego zostały skonfigurowane zapytania z zeszłego tygodnia. W tym przypadku zmieniono nazwę zapytania i dodano opis, który podaje cel zapytania.

Jeśli chcesz dowiedzieć się więcej o dostępnych opcjach, zapoznaj się z tym omówieniem edytora dodatku Power Query .

Jak pracować z wieloma źródłami danych

Edytor dodatku Power Query oferuje dwie podstawowe metody łączenia danych z wielu źródeł: dołączanie i scalanie.

Dołączanie łączy tabele w pionie, układając je jedna na drugiej. Scalanie umożliwia łączenie tabel w oparciu o wspólne kolumny.

Aby zobaczyć, jak to działa, utwórz drugi mały plik CSV o takiej samej strukturze jak pierwszy. Tym razem umieść w pliku dwa nazwiska (imię i nazwisko):

• Joe, Bloggi

• Laura, Cane

Zauważ, że jedna z tych nazw jest powtórzona z poprzedniego pliku.

Teraz wykonaj następujące kroki, aby pobrać dane do skoroszytu programu Microsoft Excel.

1. Zamknij okno edytora dodatku Power Query i zapisz zmiany.

2. Dodaj nowy arkusz.

3. Wykonaj czynności opisane w poprzedniej sekcji, aby zaimportować drugi plik.

4. Kliknij przycisk Przekształć.

5. Zmień nazwy nagłówków kolumn na „Imię” i „Nazwisko”.

W prawym okienku arkusza programu Excel są wyświetlane bieżące zapytania i połączenia. W okienku powinny być teraz widoczne dwa zapytania.

Jak dołączyć dane za pomocą dodatku Power Query

W tej sekcji dołączysz drugi przykładowy plik CSV do pierwszego.

Wykonaj następujące kroki, aby dołączyć dane:

1. Kliknij prawym przyciskiem myszy pierwszą tabelę (Nazwy klientów) w okienku Zapytania i połączenia.

2. Wybierz „Edytuj”, aby otworzyć tabelę w edytorze Power Query.

3. Kliknij opcję Dołącz zapytania w sekcji Połącz.

4. Wybierz drugą tabelę z rozwijanej listy tabel do dołączenia.

Rezultatem będą dwa dodatkowe wiersze w pierwszej tabeli.

Zauważ, że powtarza się jeden wiersz – tak działa operacja dodawania. Dodaje dane z jednej tabeli do drugiej, niezależnie od tego, czy dane już istnieją.

Możesz usunąć zduplikowane dane w edytorze dodatku Power Query, wykonując następujące czynności:

1. Kliknij nagłówek kolumny „Imię”.

2. Przytrzymaj klawisz „Ctrl” (lub „Cmd” na Macu).

3. Kliknij oba nagłówki kolumn.

4. Kliknij „Usuń wiersze” na karcie Narzędzia główne wstążki edytora Power Query.

5. Wybierz „Usuń duplikaty” z menu rozwijanego.

Jak łączyć dane za pomocą dodatku Power Query

Podczas scalania danych należy wybrać pasujące kolumny między zapytaniami.

Załóżmy, że masz dwa pliki oddzielone przecinkami, z których jeden zawiera numery telefonów klientów, a drugi adresy. Oba pliki wyglądają tak:

Postępuj zgodnie z instrukcjami w poprzednich sekcjach, aby załadować każdy plik do własnego zapytania zasilania. Następnie możesz poćwiczyć łączenie tabel.

W przypadku naszych przykładowych danych obie kolumny (Imię i Nazwisko) należy wybrać jako pasujące kolumny. Przytrzymaj klawisz Ctrl , aby wybrać wiele kolumn.

Wykonaj następujące kroki:

1. Kliknij prawym przyciskiem myszy pierwszą tabelę w okienku Zapytania i połączenia.

2. Wybierz „Edytuj”, aby otworzyć tabelę w edytorze Power Query.

3. Wybierz „Scal zapytania” z grupy „Połącz”.

4. Wybierz drugą tabelę z rozwijanej listy tabel.

5. Kliknij pierwszą kolumnę w pierwszej tabeli.

6. Przytrzymaj klawisz Ctrl i kliknij drugą kolumnę w pierwszej tabeli.

7. Przytrzymaj klawisz Ctrl i kliknij obie kolumny w drugiej tabeli.

8. Zachowaj domyślny operator łączenia jako „Left Outer”.

9. Kliknij „OK”, aby zastosować zmiany.

Operator łączenia „Left Outer” zapewnia eliminację zduplikowanych danych.

W edytorze dodatku Power Query zobaczysz nową kolumnę o nazwie „Tabela” lub „Zagnieżdżona tabela” zawierającą scalone dane z drugiego zapytania.

1. Kliknij ikonę rozwijania (dwie strzałki) w nagłówku tej kolumny.

2. Odznacz pole „użyj oryginalnej kolumny jako prefiksu” (pozwoli to uniknąć długich nazw kolumn).

3. Kliknij OK".

4. Kliknij „Zamknij i załaduj”.

Scalona tabela miała cztery kolumny, w których połączono dane telefonu i adresu. Możesz jeszcze trochę posprzątać.

Na przykład możesz chcieć edytować nazwę tabeli (Merge1) i kolumnę adresu na poniższym obrazku.

5 sposobów przekształcania danych za pomocą dodatku Power Query

Przekształcanie danych w pliku programu Excel jest łatwe dzięki dodatku Power Query, co umożliwia skuteczne czyszczenie i organizowanie danych oraz manipulowanie nimi.

Niektóre z najczęstszych sposobów przekształcania danych to:

• Rozszerzanie i zagęszczanie danych

• Sortowanie danych

• Formatowanie danych

• Dodawanie i usuwanie kolumn

• Zmiana typów danych

1. Rozszerzanie i kondensowanie danych

Rozszerzanie i zagęszczanie danych jest ważne dla ułatwienia zarządzania zestawami danych. Dodatek Power Query umożliwia rozwijanie lub zwijanie tabel w celu uzyskania bardziej uporządkowanego widoku.

W poprzedniej sekcji pokazano, jak rozszerzyć, uwzględniając dane z powiązanych tabel.

Możesz także skondensować go tak, aby zawierał tylko podsumowane dane oparte na wybranej agregacji, takiej jak suma, liczba lub procent.

2. Sortowanie danych

Aby posortować dane, możesz szybko zmienić kolejność, zwiększając lub zmniejszając wartości.

Kliknij strzałkę w dół obok nazwy kolumny i wybierz „Sortuj rosnąco” lub „Sortuj malejąco”.

3. Formatowanie danych

Karta Przekształć w edytorze dodatku Power Query udostępnia wiele opcji.

Na przykład menu rozwijane Format umożliwia zmianę wszystkich danych w kolumnie lub całej tabeli na wielkie lub małe litery.

4. Dodawanie i usuwanie kolumn

Power Query upraszcza proces dodawania, usuwania i modyfikowania kolumn.

To remove a column, simply right-click on the header of the undesired column and select “Remove.” This will permanently delete the column and all of its data from the table.

To add a new column, select the existing column where you’d like the new one to appear. Then use the “Add Column From Examples” feature to create a custom column or to perform calculations between two or more columns.

5. Changing Data Types

Working with consistent and accurate data types is crucial for accurate analysis. Power Query allows you to change data types easily:

1. Right-click on the column header.

2. Choose “Change Type”.

3. Select the desired data type from the list provided.

Changing data types can be particularly useful when dealing with percentage data.

Na przykład może być konieczne przekonwertowanie kropek dziesiętnych na wartości procentowe lub przekonwertowanie wartości procentowych tekstowych na wartości liczbowe w celu ułatwienia obliczeń i porównań.

Praca z formułami i językiem M

Podczas korzystania z dodatku Power Query w programie Excel zetkniesz się z językiem M, który jest sercem tworzenia zaawansowanych formuł, manipulowania danymi i tworzenia zaawansowanych zapytań. W tej sekcji dowiesz się o:

• Pasek Formuły

• Kod M

• podstawowe elementy składni języka M

Pasek Formuły

Pasek formuły w edytorze dodatku Power Query programu Excel to obszar, w którym wprowadzasz formuły i kod M.

Aby z niego skorzystać, przejdź do edytora dodatku Power Query, wybierając zapytanie z okienka Zapytania i połączenia. Pasek formuły znajduje się nad panelem podglądu danych.

Pisanie i modyfikowanie kodu M

Aby tworzyć wszechstronne i wydajne zapytania w programie Excel, musisz napisać i zmodyfikować język dodatku Power Query znany jako kod M. Oto kilka wskazówek, jak zacząć:

• Użyj okienka Zastosowane kroki, aby napisać kod krok po kroku, ułatwiając śledzenie i modyfikowanie logiki zapytania.

• Połącz kod M z tabelą programu Excel, tabelami przestawnymi i zewnętrznymi źródłami danych, takimi jak platforma Azure i pliki tekstowe, aby tworzyć dynamiczne i zautomatyzowane raporty.

• Dostosuj Opcje zapytania, aby udoskonalić preferencje ładowania danych i poprawić wydajność.

Gdy zapoznasz się z kodem M, przekonasz się, że możesz zautomatyzować i zoptymalizować wiele procesów, dzięki czemu analiza danych w programie Excel będzie wydajniejsza i wydajniejsza.

Aby wyświetlić kod związany z zapytaniem, wykonaj następujące kroki:

1. Otwórz kwerendę w edytorze dodatku Power Query.

2. Kliknij „Edytor zaawansowany” na karcie Strona główna.

To jest kod stojący za naszą przykładową scaloną tabelą (Twoja może wyglądać inaczej):

Co to jest składnia języka M?

Zrozumienie składni języka M ma kluczowe znaczenie dla tworzenia efektywnych formuł Power Query. Pamiętaj o następujących kwestiach:

• W języku M rozróżniana jest wielkość liter, więc uważaj na wielkie litery.

• Używaj nawiasów kwadratowych [] do odwoływania się do kolumn, rekordów i list.

• Aby odwołać się do poprzedniego kroku w zapytaniu, użyj nazwy jego zmiennej (np. # „Nazwa kroku”).

• Wygeneruj krok formuły zapytania z instrukcją „in”.

W miarę zdobywania doświadczenia z przepływem języka dodatku Power Query odkryjesz nowe sposoby przekształcania i analizowania danych, dzięki czemu skoroszyty programu Excel staną się jeszcze cenniejszym zasobem.

Obejrzyj poniższy film — czas wyciągnąć szczotkę do szorowania i uporządkować niechlujne dane.

Jak odświeżyć połączenia danych

Jedną z kluczowych funkcji dodatku Power Query jest możliwość łatwego odświeżenia połączenia dodatku Power Query.

Gdy źródła danych zostały zaktualizowane i potrzebujesz najnowszych informacji w skoroszycie, możesz odświeżyć połączenia danych w okienku Zapytania i połączenia.

Jeśli nie widzisz panelu po prawej stronie arkusza, możesz go otworzyć, przechodząc do tabeli Dane i klikając „Zapytania i połączenia”.

Otwarte okienko Zapytania i połączenia zawiera listę wszystkich zapytań. Wykonaj następujące kroki:

1. Kliknij prawym przyciskiem myszy zapytanie, które chcesz odświeżyć.

2. Wybierz Odśwież z menu kontekstowego.

Spowoduje to zaktualizowanie skoroszytu o najnowsze dane z połączonych źródeł danych.

Jak zintegrować Power Query z modelem danych

Model danych programu Excel to zaawansowana funkcja, która umożliwia tworzenie narzędzi analizy biznesowej, takich jak tabele przestawne, w oparciu o relacje między danymi.

Model danych jest w zasadzie ukrytą bazą danych, która przechowuje importowane dane i relacje między tabelami.

Jest częścią funkcji Power Pivot, która jest wbudowana w bieżące wersje programu Excel. Może być konieczne zainstalowanie dodatku Power Pivot jako dodatku, aby uzyskać do niego dostęp w poprzednich wersjach).

Możesz zintegrować swoje połączenia danych dodatku Power Query z modelem danych, wykonując następujące czynności:

1. Załaduj dane przy użyciu dodatku Power Query zgodnie z opisem w poprzednich sekcjach.

2. Zamiast ładować go bezpośrednio do skoroszytu, wybierz polecenie Załaduj do z menu Załaduj.

3. Zaznacz pole wyboru Dodaj te dane do modelu danych w oknie dialogowym Importuj dane.

Jak zautomatyzować za pomocą Power Query i VBA

Dzięki integracji VBA (Visual Basic for Applications) możesz tworzyć niestandardowe połączenia danych i automatyzować kroki transformacji danych w Power Query.

Możesz wykorzystać posiadane umiejętności VBA do tworzenia zaawansowanych modeli danych poprzez włączenie danych z platform takich jak Microsoft Access, SharePoint i Analysis Services.

Chociaż Power Query nie ma natywnej obsługi języka VBA, możesz użyć kodu VBA do pośredniego otwierania, odświeżania i manipulowania zapytaniami Power Query.

Pozwala to na tworzenie wydajnych, zautomatyzowanych przepływów pracy z danymi i usprawnienie procesu analizy danych.

Oto przykładowy kod, który odświeża kwerendę zasilania w bieżącym skoroszycie.

:

Dim wb jako skoroszyt, pq jako obiekt

Ustaw wb = Ten skoroszyt

Ustaw pq = wb.Queries("Nazwy klientów")

 pq.Odśwież

Oto kod w makrze Excel VBA:

Zaawansowane funkcje Power Query

Dodatek Power Query w programie Excel oferuje mnóstwo zaawansowanych funkcji upraszczających i usprawniających proces analizy danych. Ucząc się, jak skutecznie korzystać z tych funkcji, możesz przenieść swoje umiejętności analizy danych na zupełnie nowy poziom.

Wbudowane narzędzia do analizy danych obejmują:

• Kolumny warunkowe do stosowania złożonej logiki do przekształceń danych.

• Funkcje grupowania i agregacji w celu podsumowania danych w zrozumiały sposób.

• Operacje Pivot i Unpivot w celu zmiany kształtu danych w celu łatwiejszej analizy.

Wiele z tych funkcji znajdziesz na karcie Przekształć w edytorze dodatku Power Query.

Ponadto możesz połączyć Power Query z narzędziami Business Intelligence, takimi jak LuckyTemplates, aby uzyskać bardziej zaawansowane opcje analizy i wizualizacji danych.

Współpraca i udostępnianie

Udostępnianie połączeń danych, zapytań i transformacji członkom zespołu jest niezbędne do efektywnej współpracy.

Możesz udostępnić skoroszyt z zapytaniami, zapisując go na dysku sieciowym, w programie SharePoint lub w innym udostępnionym miejscu przechowywania.

Możesz także wyeksportować kod M za pojedynczym zapytaniem, wykonując następujące czynności:

1. Edytuj zapytanie w edytorze dodatku Power Query.

2. Kliknij „Edytor zaawansowany” na karcie Strona główna.

3. Skopiuj cały kod M w oknie Edytora zaawansowanego.

4. Otwórz edytor tekstu (np. Notatnik) i wklej skopiowany kod M do edytora tekstu.

5. Zapisz plik tekstowy i wyślij go współpracownikowi.

Podsumujmy to

Dzięki szerokiej gamie funkcji i przyjaznemu dla użytkownika interfejsowi dodatek Power Query jest niezbędnym narzędziem dla użytkowników programu Excel, którzy chcą wydajnie pracować z danymi zewnętrznymi.

Ta cenna funkcja pomaga usprawnić proces tworzenia raportów, czyszczenia danych i przeprowadzania złożonych analiz w tabelach danych. Za pomocą

Power Query ostatecznie oszczędza czas i zwiększa wydajność podczas pracy z plikami Excela.