Wyszukiwanie w witrynie

Power Query to rewolucyjna funkcja Excela, której nie używasz — oto 5 sposobów, aby ją wykorzystać

Patrząc wstecz na moje stare arkusze Excel, nie mogę uwierzyć, że przetrwałem bez Power Query. Zamienia skomplikowaną automatyzację w coś zaskakująco przyjaznego dla użytkownika. Jeśli unikałeś go, bo wygląda na onieśmielający, ten przewodnik jest dla ciebie.

Przeprowadzę Cię przez pięć kroków transformacji przy użyciu tego samego zestawu danych — zaczynając od ekstrakcji i oczyszczania tekstu, a następnie przechodząc do podsumowywania dużych zestawów danych bez użycia tabeli przestawnej.

Aby śledzić ten przewodnik podczas czytania, pobierz darmową kopię skoroszytu programu Excel używanego w przykładach. Po kliknięciu linku znajdziesz przycisk pobierania w prawym górnym rogu ekranu.

Udoskonal i popraw dane na swoim bieżącym arkuszu

Przekształć nieuporządkowany tekst i podsumuj wartości

Power Query jest jednym z najpotężniejszych narzędzi w Excelu do naprawiania nieczystych danych bezpośrednio przed Tobą. Zamiast pisania długich, zagnieżdżonych formuł, które są wrażliwe i trudne w utrzymaniu, możesz użyć Edytora Power Query do zapisania serii kroków transformacji. To sprawia, że czyszczenie danych jest spójne, powtarzalne i łatwe do audytu.

Załóżmy, że otrzymałeś tę sformatowaną tabelę Excel o nazwie T_Sales. Zawiera ona nieuporządkowaną kolumnę SKU, która wymaga precyzyjnej korekty, wraz z długą listą transakcji, które chcesz podsumować.

Najpierw załaduj swoją tabelę do Edytora zapytań Power Query. Wybierz dowolną komórkę w danych, a na karcie Dane kliknij Z tabeli/zakresu.

Możesz teraz zobaczyć swoją tabelę w nowym oknie.

Proces roboczy 1: Izolowanie i porządkowanie konkretnych punktów danych

Wydobywaj i oczyszczaj dane dla lepszego raportowania

Twój cel: Przekształcenie surowego ciągu znaków, takiego jak TSHIRT_XL_RED_001, w trzy oddzielne, czyste kolumny: jedną dla poprawnie napisanego nazwy przedmiotu („T-shirt”), jedną dla rozmiaru („XL”) i jedną dla koloru („Red”).

Wyodrębnienie nazwy lub rozmiaru przedmiotu z nieuporządkowanego numeru SKU zwykle wymaga powodującej ból głowy kombinacji funkcji LEFT, MID i FIND. W Power Query, jednak, można przeprowadzić chirurgiczne czyszczenie w kilka sekund.

Krok 1: Podziel i zmień nazwę swoich kolumn

Pierwszym krokiem jest podzielenie kolumny SKU na przedmiot, rozmiar, kolor i ID oraz nadanie im przejrzystych nazw:

  1. Wybierz kolumnę SKU.
  2. Na karcie Narządźa główne kliknij Podziel kolumnę > Według ogranicznika.
  3. Wybierz Niestandardowy jako typ ogranicznika, wpisz podkreślenie (_), wybierz Każde wystąpienie ogranicznika i kliknij OK.
  4. Kliknij dwukrotnie nagłówek każdej nowej kolumny i zmień ich nazwę na Przedmiot, Rozmiar, Kolor i ID.

Podczas wykonywania tych czynności spójrz na panel Zastosowane kroki po prawej stronie. Power Query zapisuje każdy kliknięcie, jeden po drugim, więc jeśli popełnisz błąd, możesz po prostu kliknąć X obok kroku, aby go cofnąć i spróbować ponownie.

Krok 2: Usuń niepotrzebną kolumnę ID

Zamiast standardowego usuwania, stwórz „chronioną listę” kolumn do zachowania. To mówi Power Query, aby od teraz patrzył tylko na te konkretne kolumny. Jeśli ktoś później doda kolumnę Notatki do Twoich danych źródłowych, Power Query zignoruje dodatkowy szum, utrzymując Twój raport przejrzysty i odporny na zmiany w schemacie.

  1. Przytrzymaj Ctrl i kliknij wszystkie nagłówki kolumn, oprócz ID.
  2. Kliknij prawym przyciskiem myszy jeden z wybranych nagłówków i kliknij Usuń pozostałe kolumny.

Ruch 3: Popraw swoje formatowanie

Teraz, gdy struktura jest ustalona, możesz poprawić formatowanie kolumny Item. Jest to ruch w dwóch fazach: pierwsza to prosta transformacja, która zmienia wielkie litery na standardową kapitalizację (na przykład „TSHIRT” na „Tshirt”), a druga to zmiana „Tshirt” na „T-shirt.”

  1. Wybierz kolumnę Przedmiot.
  2. Na karcie Transformuj kliknij Format > Pokaż Wielką Każde Słowo.
  3. Kliknij prawym przyciskiem myszy nagłówek kolumny Element i wybierz Zamień wartości.
  4. Zamień Tshirt na T-shirt, a następnie kliknij OK.

Ruch 4: Przetłumacz kody kolorów

Na koniec musisz zamienić trzyznakowe kody kolorów na właściwe nazwy. Możesz ponownie użyć narzędzia Zamień wartości, ale byłby to czasochłonny proces, który tworzy kilka kroków. Zamiast tego dodaj Kolumnę warunkową:

  1. Na karcie Dodaj kolumnę kliknij Kolumna warunkowa.
  2. Nazwij nową kolumnę Właściwy kolor.
  3. Dodaj swoją pierwszą regułę: Jeśli Kolor równa się WHT, wtedy wyjście Biały.
  4. Kliknij Dodaj klauzulę, powtórz ten proces dla innych kolorów i kliknij OK.
  5. Kliknij i przeciągnij nową kolumnę na lewo, tak aby była trzecią kolumną.
  6. Kliknij prawym przyciskiem myszy nagłówek oryginalnej, nieuporządkowanej kolumny Color i wybierz Usuń.

W przeciwieństwie do standardowej siatki Excela, usunięcie kolumny źródłowej w Power Query nie spowoduje błędów w późniejszych krokach, o ile te kroki się do niej nie odnoszą. Dzieje się tak, ponieważ Power Query przetwarza transformacje w określonej kolejności. Ponieważ już użyłeś funkcji Usuń pozostałe kolumny, proste usunięcie tutaj jest bezpieczne.

Twoje dane są teraz czyste, spójne i standardyzowane, i jesteś gotowy przejść do Przepływu pracy 2.

Przepływ pracy 2: Podsumowywanie ogromnych zbiorów danych bez tabeli przestawnej

Agregowanie sum transakcji do raportowania na wysokim poziomie

Twój cel: Zgromadzić swoją listę transakcji tak, aby zamiast widzieć każdy pojedynczy wiersz, widzieć całkowitą wartość „Sprzedane jednostki” dla każdej kategorii.

Przed wykonaniem ostatecznego ruchu musisz rozdzielić swoje dane na dwa niezależne zapytania. Zachowuje to wykonaną już pracę oczyszczania jako warstwę bazową, pozwalając ci budować podsumowanie na jej podstawie.

  1. W panelu Zapytania po lewej stronie kliknij prawym przyciskiem myszy swoje istniejące zapytanie i wybierz Odwołanie.
  2. Kliknij dwukrotnie to nowe zapytanie i zmień jego nazwę na Summary_Report.
  3. Z wybranym tym nowym zapytaniem, jesteś gotowy do wykonania ostatniego Kroku 5.

Ruch 5: Grupuj według kategorii

Funkcja Grupuj według w Power Query pozwala na wykonanie agregacji, którą zwykle rezerwujesz dla tabel przestawnych. Chociaż tabele przestawne są świetne do interaktywnej analizy, Power Query lepiej nadaje się do powtarzalnych, odświeżalnych procesów raportowania oraz unikania zduplikowanych struktur danych. Osobiście uważam, że Edytor Power Query jest łatwiejszy w użyciu niż okienko pól tabeli przestawnej.

  1. Wybierz kolumnę Kategoria.
  2. Na karcie Transformacja (lub na karcie Strona główna w niektórych wersjach) kliknij Grupuj według.
  3. W oknie dialogowym upewnij się, że wybrano Podstawowe, a w polu Nazwa nowej kolumny ustaw Łączna liczba sprzedanych jednostek.
  4. Ustaw Operację na Suma i Kolumnę na Sprzedane jednostki.
  5. Kliknij OK.

Teraz twoje dane są zwięźle podsumowane w łatwej do odczytania tabeli.

Na koniec kliknij górną część podzielonego przycisku Zamknij i załaduj na karcie Strona główna, aby załadować nowe tabele do osobnych kart arkusza. Jeśli dane źródłowe ulegną zmianie, kliknij Dane > Odśwież wszystko, aby zaktualizować oba zapytania.

Używanie Power Query do automatyzacji swojego przepływu pracy to świetny sposób na odzyskanie czasu w Excelu. Chodzi o mądrzejszą pracę, pozwalając oprogramowaniu wykonywać powtarzalne zadania za Ciebie. Gdy opanujesz te techniki, odkryjesz, że używanie Pythona w Excelu to kolejny doskonały sposób na czyszczenie i naprawianie danych, gdy Twoje zestawy danych stają się szczególnie skomplikowane.

Powiązane artykuły