Dzięki Power Query praca z danymi rozproszonymi w arkuszach, a nawet skoroszytach stała się łatwiejsza.
Jedną z rzeczy, w których dodatek Power Query może zaoszczędzić dużo czasu, jest konieczność scalania tabel o różnych rozmiarach i kolumnach na podstawie pasującej kolumny.
Poniżej znajduje się wideo, w którym pokazuję dokładnie, jak scalać tabele w programie Excel za pomocą dodatku Power Query.
Jeśli wolisz czytać tekst niż oglądać wideo, poniżej znajdują się pisemne instrukcje.
Załóżmy, że masz tabelę, jak pokazano poniżej:
Ta tabela zawiera dane, których chcę użyć, ale nadal brakuje w niej dwóch ważnych kolumn – „Identyfikatora produktu” i „Region”, w którym działa przedstawiciel handlowy.
Informacje te są podane w oddzielnych tabelach, jak pokazano poniżej:
Aby zebrać wszystkie te informacje w jednej tabeli, musisz połączyć te trzy tabele, aby następnie utworzyć tabelę przestawną i przeanalizować ją lub wykorzystać do innych celów raportowania/dashboardingu.
I przez scalanie nie mam na myśli prostego kopiowania i wklejania.
Będziesz musiał zmapować odpowiednie rekordy z Tabeli 1 z danymi z Tabeli 2 i 3.
Teraz możesz polegać na funkcji WYSZUKAJ.PIONOWO lub INDEX/MATCH, aby to zrobić.
Lub jeśli jesteś świstem VBA, możesz napisać kod, aby to zrobić.
Jednak te opcje są czasochłonne i skomplikowane w porównaniu z dodatkiem Power Query.
W tym samouczku pokażę, jak połączyć te trzy tabele Excela w jedną.
Aby ta technika działała, musisz mieć kolumny łączące. Na przykład w Tabeli 1 i Tabeli 2 wspólną kolumną jest „Pozycja”, a w Tabeli 1 i Tabeli 3 wspólną kolumną jest „Przedstawiciel handlowy”. Zwróć też uwagę, że w tych łączących kolumnach nie powinno być powtórzeń.Uwaga: Dodatek Power Query może być używany jako dodatek w programach Excel 2010 i 2013 i jest funkcją wbudowaną od programu Excel 2016 i nowszych. W zależności od Twojej wersji niektóre obrazy mogą wyglądać inaczej (przechwycone obrazy używane w tym samouczku pochodzą z programu Excel 2016).
Scal tabele za pomocą dodatku Power Query
Nazwałem te tabele, jak pokazano poniżej:
- Tabela 1 - Sprzedaż_Dane
- Tabela 2 - Pdt_Id
- Tabela 3 - Region
Zmiana nazw tych tabel nie jest obowiązkowa, ale lepiej podać nazwy opisujące, o co chodzi w tabeli.
Za jednym razem możesz scalić tylko dwie tabele w dodatku Power Query.
Więc najpierw musimy połączyć Tabelę 1 i Tabelę 2, a następnie scalić z nią Tabelę 3 w następnym kroku.
Łączenie Tabeli 1 i Tabeli 2
Aby scalić tabele, musisz najpierw przekonwertować te tabele na połączenia w dodatku Power Query. Gdy masz już połączenia, możesz je łatwo połączyć.
Oto kroki, aby zapisać tabelę programu Excel jako połączenie w dodatku Power Query:
- Wybierz dowolną komórkę w tabeli Dane_Sprzedaży.
- Kliknij kartę Dane.
- W grupie Pobierz i przekształć kliknij „Z tabeli/zakresu”. Spowoduje to otwarcie edytora zapytań.
- W edytorze zapytań kliknij kartę „Plik”.
- Kliknij opcję „Zamknij i załaduj do”.
- W oknie dialogowym „Importuj dane” wybierz „Tylko utwórz połączenie”.
- Kliknij OK.
Powyższe kroki spowodowały utworzenie połączenia o nazwie Sales_Data (lub dowolnej nazwie, którą nadałeś tabeli Excel).
Powtórz powyższe kroki dla Tabeli 2 i Tabeli 3.
Kiedy skończysz, będziesz mieć trzy połączenia (o nazwach Sales_Data, Pdt_Id i Region).
Zobaczmy teraz, jak połączyć tabele Sales_Data i Pdt_Id.
- Kliknij kartę Dane.
- W grupie Pobierz i przekształć dane kliknij Pobierz dane.
- W menu kliknij Połącz zapytania.
- Kliknij Scal. Spowoduje to otwarcie okna dialogowego Scal.
- W oknie dialogowym Scal wybierz „Sales_Data” z pierwszego menu rozwijanego.
- Wybierz „Pdt_Id” z drugiego menu rozwijanego.
- W podglądzie „Sales_Data” kliknij kolumnę „Item”. Spowoduje to wybranie całej kolumny.
- W podglądzie „Pdt_Id” kliknij kolumnę „Pozycja”. Spowoduje to wybranie całej kolumny.
- W menu rozwijanym „Dołącz rodzaj” wybierz „Lewo zewnętrzne (wszystkie od pierwszego, pasujące od drugiego)”.
- Kliknij OK.
Powyższe kroki spowoduje otwarcie edytora zapytań i wyświetlenie danych z Sales_Data z jedną dodatkową kolumną (z Pdt_Id).
Scalanie tabel Excel (Tabela 1 i 2)
Teraz proces scalania tabel będzie się odbywał w edytorze zapytań z następującymi krokami:
- W dodatkowej kolumnie (Pdt_Id) kliknij podwójną strzałkę w nagłówku.
- W oknie opcji, które zostanie otwarte, usuń zaznaczenie wszystkich nazw kolumn i wybierz tylko element. Dzieje się tak, ponieważ mamy już kolumnę nazwy produktu w istniejącej tabeli i chcemy tylko identyfikatora produktu dla każdego produktu.
- Odznacz opcję „Użyj oryginalnej nazwy kolumny jako prefiksu”.
- Kliknij OK.
Dałoby to wynikową tabelę zawierającą wszystkie rekordy z tabeli Sales_Data i dodatkową kolumnę z identyfikatorami produktów (z tabeli Pdt_Id).
Teraz, jeśli chcesz połączyć tylko dwie tabele, możesz załadować ten Excel, gotowe.
Ale mamy trzy tabele do połączenia, więc jest jeszcze więcej do zrobienia.
Musisz zapisać tę tabelę wynikową jako połączenie (abyśmy mogli użyć jej do połączenia z Tabelą 3).
Oto kroki, aby zapisać tę scaloną tabelę (z danymi z tabeli Sales_Data i Pdt_Id) jako połączenie:
- Kliknij kartę Plik
- Kliknij opcję „Zamknij i załaduj do”.
- W oknie dialogowym „Importuj dane” wybierz „Tylko utwórz połączenie”.
- Kliknij OK.
Spowoduje to zapisanie nowo scalonych danych jako połączenia. Jeśli chcesz, możesz zmienić nazwę tego połączenia.
Scalanie tabeli 3 z tabelą wynikową
Proces scalania trzeciej tabeli z tabelą wynikową (którą otrzymaliśmy łącząc tabelę 1 i tabelę 2) jest dokładnie taki sam.
Oto kroki, aby scalić te tabele:
- Kliknij kartę Dane.
- W grupie Pobierz i przekształć dane kliknij „Pobierz dane”.
- W menu kliknij „Połącz zapytania”.
- Kliknij „Scal”. Spowoduje to otwarcie okna dialogowego Scal.
- W oknie dialogowym Scal wybierz „Scal1” z pierwszego menu rozwijanego.
- Wybierz „Region” z drugiego menu rozwijanego.
- W podglądzie „Scal1” kliknij kolumnę „Przedstawiciel handlowy”. Spowoduje to wybranie całej kolumny.
- W podglądzie regionu kliknij kolumnę „Przedstawiciel handlowy”. Spowoduje to wybranie całej kolumny.
- W menu rozwijanym „Dołącz rodzaj” wybierz Lewy zewnętrzny (wszystkie od pierwszego, pasujące od drugiego).
- Kliknij OK.
Powyższe kroki spowoduje otwarcie edytora zapytań i wyświetlenie danych z Merge1 z jedną dodatkową kolumną (Region).
Teraz proces scalania tabel będzie się odbywał w edytorze zapytań z następującymi krokami:
- W dodatkowej kolumnie (Region) kliknij podwójną strzałkę w nagłówku.
- W otwartym oknie opcji usuń zaznaczenie wszystkich nazw kolumn i wybierz tylko Region.
- Odznacz opcję „Użyj oryginalnej nazwy kolumny jako prefiksu”.
- Kliknij OK.
Powyższe kroki dadzą ci tabelę, która ma wszystkie trzy połączone tabele (tabela Sales_Data z jedną kolumną dla Pdt_Id i jedną dla regionu).
Oto kroki, aby załadować tę tabelę w programie Excel:
- Kliknij kartę Plik.
- Kliknij „Zamknij i załaduj do”.
- W oknie dialogowym „Importuj dane” wybierz opcje Tabela i Nowe arkusze.
- Kliknij OK.
Dałoby to wynikową tabelę scaloną w nowym arkuszu.
Jedną z najlepszych cech dodatku Power Query jest to, że możesz łatwo dostosować wszelkie zmiany w danych źródłowych (Tabela 1, 2 i 3), po prostu je odświeżając.
Załóżmy na przykład, że Laura zostaje przeniesiona do Azji i otrzymujesz nowe dane na następny miesiąc. Teraz nie musisz ponownie powtarzać powyższych kroków. Wszystko, co musisz zrobić, to odświeżyć tabelę i zrobi to wszystko od nowa.
W ciągu kilku sekund otrzymasz nową scaloną tabelę.
Mogą Ci się również spodobać następujące samouczki dotyczące dodatku Power Query:
- Połącz dane z wielu skoroszytów w programie Excel (za pomocą dodatku Power Query).
- Połącz dane z wielu arkuszy w jeden arkusz w programie Excel.
- Jak unpivot danych w programie Excel za pomocą dodatku Power Query (inaczej Get & Transform)
- Uzyskaj listę nazw plików z folderów i podfolderów (za pomocą dodatku Power Query)
