Czyszczenie i importowanie danych za pomocą Power Query w programie Excel: przewodnik krok po kroku

Szybkie linki

Od czasu debiutu w programie Excel, Power Query zmieniło sposób, w jaki profesjonaliści radzą sobie z zarządzaniem danymi. Początkowo wprowadzony jako dodatek w 2010 r., Power Query stał się niezbędną wbudowaną funkcją w programie Excel od 2016 r. Jego główna funkcja? Usprawnienie procesu czyszczenia, importowania i reorganizacji danych, aby zaoszczędzić nie tylko czas, ale także dodatkową frustrację. Skupiając się na wydajnym przygotowywaniu danych, Power Query pozwala Ci skoncentrować się na analizie i spostrzeżeniach.

Czym jest Power Query?

Power Query to w zasadzie narzędzie w programie Excel przeznaczone do transformacji danych. Oferuje prosty, ale skuteczny sposób pobierania danych z różnych źródeł, oczyszczania ich i przygotowywania do analizy. Power Query znajduje się w sekcji „Get & Transform Data” na karcie Data, zapewniając łatwą dostępność użytkownikom na wszystkich poziomach umiejętności.

Lokalizacja narzędzi Power Query na karcie Dane w programie Excel

Zapnij pasy, bo zagłębimy się w praktyczne zastosowania Power Query, które wyposażą Cię w wiedzę, dzięki której będziesz mógł sprawnie oczyszczać, importować i organizować swoje dane.

Czyszczenie danych programu Excel za pomocą Power Query

Jednym z moich ulubionych zastosowań Power Query jest czyszczenie danych. Wyobraź sobie, że wkleiłeś chaotyczną listę adresów e-mail z pola DW wiadomości e-mail programu Outlook do arkusza Excel. Twoja misja? Przekształcić te chaotyczne dane w uporządkowaną tabelę zawierającą imiona, nazwiska i adresy e-mail. Podczas gdy funkcje programu Excel mogą to osiągnąć, Power Query zapewnia płynne działanie, które wymaga minimalnego wysiłku.

Lista adresów e-mail rozdzielonych średnikami w programie Excel

Aby rozpocząć, wykonaj następujące kroki:

Otwórz Power Query: Kliknij kartę „Dane”, a następnie wybierz „Z tabeli/zakresu”. W tym oknie zostaniesz poproszony o utworzenie tabeli z wybranych danych.
Wybierz swoje dane: Upewnij się, że wybrano właściwy zakres. Jeśli Twoje dane nie mają nagłówków, pozostaw opcję nagłówków niezaznaczoną przed kliknięciem przycisku „OK”.

Po kliknięciu przycisku „OK” otwiera się Edytor Power Query, w którym możesz przygotować grunt pod transformację danych.

Twoje następne zadanie? Podziel adresy e-mail na pojedyncze wiersze:

Podział kolumny: Kliknij prawym przyciskiem myszy nagłówek kolumny, najedź kursorem na „Podział kolumny” i wybierz „Według ogranicznika”. Wybierz „Średnik” jako ogranicznik i wybierz „Wiersze” w oknie dialogowym Opcje zaawansowane.

Po potwierdzeniu zobaczysz, że każdy adres e-mail jest uporządkowany w oddzielnych wierszach.

Adresy e-mail rozdzielone na wiele wierszy w Edytorze Power Query

Teraz czas wyodrębnić imię i nazwisko. W tym celu skorzystaj z funkcji „Dodaj kolumnę z przykładów”:

Wyodrębnij nazwy: Kliknij dwukrotnie nagłówek kolumny i wprowadź imię lub tytuł, aby odtworzyć wzorzec. Power Query automatycznie zasugeruje pozostałe wpisy!
Zmień nazwy kolumn: Pamiętaj o zmianie nazw kolumn (na przykład „Imię”, „Nazwisko”, „Adres e-mail”).
Ostatnie szlify: Dostosuj kolejność kolumn, przeciągając je na żądane pozycje.

Gdy już Twoje dane będą uporządkowane i uporządkowane, nie zapomnij nadać zapytaniu zrozumiałej nazwy, zanim je zamkniesz i załadujesz ponownie do skoroszytu.

Nadawanie nazwy zapytaniu w Edytorze Power Query

Importowanie i reorganizowanie danych za pomocą Power Query

Power Query błyszczy, jeśli chodzi o importowanie danych z różnych źródeł poza arkuszami kalkulacyjnymi, takich jak pliki PDF lub strony internetowe. Ta następna sekcja pokazuje, jak wydajnie wyodrębnić i zreorganizować określone dane z istniejącego skoroszytu programu Excel.

Najpierw przejdź ponownie do zakładki „Dane”:

Rozpocznij importowanie: Kliknij „Pobierz dane” > „Z pliku” > „Z skoroszytu programu Excel”.
Wybierz plik: Znajdź plik, z którego chcesz zaimportować dane, i kliknij „Importuj”, a następnie „Przekształć dane”, aby uzyskać dostęp do edytora Power Query.

Okno nawigatora pokazujące dane dostępne do zaimportowania

W edytorze Power Query:

Awansuj wiersz do nagłówków: Kliknij „Użyj pierwszego wiersza jako nagłówków”, aby natychmiast oznaczyć pierwszy wiersz jako nagłówki kolumn.
Cofnij obrót danych: Wybierz kolumny reprezentujące Twoje unikatowe zmienne i kliknij prawym przyciskiem myszy, aby wybrać opcję „Cofnij obrót innych kolumn”. Spowoduje to transformację układu, prezentując każdą unikatową wartość w osobnym wierszu.

Nieprzestawiane dane pokazujące uporządkowany układ

Zanim wczytasz zreorganizowaną tabelę z powrotem do skoroszytu, pamiętaj, aby dostosować nazwy kolumn i zdefiniować prawidłowe typy danych w celu zachowania integralności danych.

Ustawianie typu danych dla kolumny Miesiąc w Edytorze Power Query

Po zakończeniu transformacji kliknij „Zamknij i wczytaj” > „Zamknij i wczytaj do”, aby określić miejsce, w którym dane będą wyświetlane w programie Excel.

Siłą Power Query jest jego zdolność do utrzymywania dynamicznych linków do źródeł danych. Na przykład, jeśli oryginalny zestaw danych ulegnie zmianie, po prostu kliknij prawym przyciskiem myszy na nowej tabeli i wybierz „Odśwież”, aby ją bezproblemowo zaktualizować.

Ta funkcjonalność sprawia, że Power Query staje się przełomowym narzędziem do dynamicznej analizy danych, umożliwiając aktualizację zestawów danych bez konieczności ręcznego kopiowania i wklejania.

Gotowy, aby przenieść swoje umiejętności zarządzania danymi na wyższy poziom? Poznaj Power Query jeszcze lepiej, pracując z tabelami z sieci i doświadczając jego wszechstronnych możliwości importowania z pierwszej ręki. Odkryjesz, że to narzędzie w mgnieniu oka zwiększy Twoją produktywność i dokładność!

Dodatkowe informacje

1. Jakie typy źródeł danych mogę używać z Power Query?

Power Query obsługuje wiele źródeł danych, w tym pliki Excel, pliki CSV, strony internetowe, bazy danych i wiele innych. Dzięki temu jest bardzo wszechstronny w przypadku zadań importowania i przekształcania danych.

2. Czy mogę automatycznie odświeżyć dane zaimportowane za pomocą Power Query?

Tak, możesz skonfigurować automatyczne odświeżanie dla swoich zestawów danych Power Query. Excel pozwala skonfigurować interwały odświeżania, dzięki czemu Twoje dane będą aktualne bez ręcznej interwencji.

3. Czy istnieje ograniczenie ilości danych, które mogę zaimportować za pomocą dodatku Power Query?

Chociaż Power Query radzi sobie z dużymi zbiorami danych, wydajność może się pogorszyć w przypadku zbyt dużych zestawów, zwłaszcza jeśli przekraczają one limit liczby wierszy w programie Excel, który wynosi 1 048 576. Zaleca się filtrowanie lub podsumowywanie danych przed zaimportowaniem w celu zwiększenia wydajności przetwarzania.

Źródło i obrazy