Jak łączyć tabele w Excelu: Power Query vs. Merge Tables Wizard

W tym poradniku przyjrzymy się, jak można połączyć tabele w Excelu na podstawie jednej lub więcej wspólnych kolumn za pomocą Power Query i Merge Tables Wizard.

Połączenie danych z wielu tabel jest jednym z najbardziej zniechęcających zadań w Excelu. Jeśli zdecydujesz się zrobić to ręcznie, możesz spędzić godziny tylko po to, aby dowiedzieć się, że zepsułeś ważne informacje. Jeśli jesteś doświadczonym profesjonalistą w Excelu, to możesz polegać na formułach VLOOKUP i INDEX MATCH. Makro, jak sądzisz, mogłoby wykonać tę pracę w mgnieniu oka, gdybyś tylko wiedział jak. Dobra wiadomość dla wszystkich użytkowników Excela – Power Query lub Merge Tables Wizard mogą być Twoją oszczędnością czasu. Wybór należy do Ciebie.

• Jak łączyć tabele za pomocą Power Query
• Kreator łączenia tabel – szybki sposób na łączenie tabel w Excelu
• Inne sposoby łączenia danych w Excelu

Jak łączyć tabele za pomocą Excel Power Query

W prostych słowach, Power Query (znany również jako Get & Transform w Excelu 2016 i Excelu 2019) to narzędzie do łączenia, czyszczenia i przekształcania danych z wielu źródeł do potrzebnego formatu, takiego jak tabela, tabela przestawna lub wykres przestawny.

Między innymi, Power Query może połączyć 2 tabele w 1 lub połączyć dane z wielu tabel poprzez dopasowanie danych w kolumnach, co jest głównym tematem tego samouczka.

Aby wyniki spełniały Twoje oczekiwania, pamiętaj o następujących rzeczach:

• Power Query jest wbudowaną funkcją w Excelu 2016 i Excelu 2019, ale można go również pobrać w Excelu 2010 i Excelu 2013 i używać jako dodatku. We wcześniejszych wersjach niektóre okna mogą wyglądać inaczej niż obrazy w tym samouczku, które zostały przechwycone w programie Excel 2016.
• Aby tabele zostały połączone poprawnie, powinny mieć co najmniej jedną wspólną kolumnę (określaną również jako wspólny identyfikator lub kolumna klucza lub unikalny identyfikator). Ponadto, wspólne kolumny powinny zawierać tylko unikalne wartości, bez powtórzeń.
• Tabele źródłowe mogą znajdować się w tym samym arkuszu lub w różnych arkuszach.
• W przeciwieństwie do formuł, Power Query nie wyciąga danych z jednej tabeli do drugiej. Tworzy nową tabelę, która łączy dane z oryginalnych tabel.
• Wynikowa tabela nie jest aktualizowana automatycznie. Powinieneś wyraźnie powiedzieć Excelowi, aby to zrobił. Zobacz jak odświeżyć połączoną tabelę.

Dane źródłowe

Jako przykład, połączmy 3 tabele oparte na wspólnych kolumnach ID zamówienia i Sprzedawca. Zwróć uwagę, że nasze tabele mają różną liczbę wierszy i chociaż tabela 1 ma duplikaty w kolumnie Sprzedawca, to tabela 3 zawiera tylko unikalne wpisy.

Naszym zadaniem jest zmapowanie danych z tabeli 1 z odpowiednimi rekordami z pozostałych dwóch tabel i połączenie wszystkich danych w nową tabelę jak poniżej:

Zanim zaczniesz łączyć, radziłbym nadać kilka opisowych nazw swoim tabelom, dzięki czemu łatwiej będzie Ci je później rozpoznawać i zarządzać nimi. Ponadto, chociaż mówimy „tabele”, w rzeczywistości nie musisz tworzyć tabeli w Excelu. Twoje „tabele” mogą być zwykłymi zakresami lub nazwanymi zakresami, jak w tym przykładzie:

• Tabela 1 nazywa się Zamówienia
• Tabela 2 nazywa się Produkty
• Tabela 3 nazywa się Prowizje

Tworzenie połączeń Power Query

Nie chcąc zagracać swojego skoroszytu kopiami oryginalnych tabel, będziemy konwertować je na połączenia, wykonywać łączenie w edytorze Power Query, a następnie wczytywać tylko wynikową tabelę.

Aby zapisać tabelę jako połączenie w Power Query, oto co należy zrobić:

1. Wybierz pierwszą tabelę (Zamówienia) lub dowolną komórkę w tej tabeli.
2. Przejdź do zakładki Dane > Pobierz & Przekształć grupę i kliknij Z tabeli/Zakresu.
   
3. W otwartym edytorze Power Query kliknij strzałkę rozwijaną Zamknij & Załaduj (nie sam przycisk!) i wybierz opcję Zamknij i wczytaj do…
   
4. W oknie dialogowym Importuj dane zaznacz opcję Tylko utwórz połączenie i kliknij OK.
   

   To spowoduje utworzenie połączenia z nazwą tabeli/zakresu i wyświetlenie tego połączenia w oknie Zapytania & Połączenia, które pojawi się po prawej stronie skoroszytu.

5. Powtórz powyższe kroki dla wszystkich pozostałych tabel, które chcesz połączyć (w naszym przypadku są to dwie kolejne tabele, Produkty i Prowizje).

Po zakończeniu pracy zobaczysz wszystkie połączenia w panelu:

Połącz dwa połączenia w jedną tabelę

Mając połączenia na miejscu, zobaczmy jak można połączyć dwie tabele w jedną:

1. Na zakładce Dane, w grupie Uzyskaj & Przekształcanie danych, kliknij przycisk Uzyskaj dane, wybierz Połącz zapytania z listy rozwijanej i kliknij przycisk Połącz:
   
2. W oknie dialogowym Łączenie wykonaj następujące czynności:
   • Wybierz swoją 1. tabelę (Zamówienia) z pierwszego rozwijanego menu.
   • Wybierz drugą tabelę (Produkty) z drugiej listy rozwijanej.
   • W obu podglądach, kliknij na pasującą kolumnę (ID zamówienia) aby ją wybrać. Wybrana kolumna zostanie podświetlona na zielono.
   • W rozwijanej liście Join Kind pozostaw domyślną opcję: Left Outer (wszystkie z pierwszej, dopasowanie z drugiej).
   • Kliknij OK.

   

Po wykonaniu powyższych czynności w Power Query Editor pojawi się pierwsza tabela (Zamówienia) z dodatkową kolumną o nazwie takiej jak w drugiej tabeli (Produkty). Ta dodatkowa kolumna nie posiada jeszcze żadnych wartości, jedynie słowo „Tabela” we wszystkich komórkach. Nie zniechęcaj się jednak, wszystko zrobiłeś dobrze, a za chwilę to naprawimy!

Wybierz kolumny do dodania z drugiej tabeli

W tym momencie masz już tabelę przypominającą tę na poniższym zrzucie ekranu. Aby zakończyć proces scalania, wykonaj następujące czynności w edytorze Power Query:

1. W dodanej kolumnie (Produkty), kliknij na dwustronną strzałkę w nagłówku.
   
2. W oknie, które się otworzy wykonaj następujące czynności:
   • Zachowaj zaznaczony przycisk Rozwiń.
   • Zaznacz wszystkie kolumny, a następnie zaznacz tylko kolumny, które chcesz skopiować z drugiej tabeli. W tym przykładzie wybierzemy tylko kolumnę Produkt, ponieważ nasza pierwsza tabela posiada już ID Sprzedawcy i ID Zamówienia.
   • Zaznacz pole Użyj oryginalnej nazwy kolumny jako prefiksu (chyba, że chcesz, aby nazwa kolumny była poprzedzona nazwą tabeli, z której ta kolumna została pobrana).
   • Kliknij OK.

   

Jako wynik otrzymasz nową tabelę, która zawiera każdy rekord z pierwszej tabeli i dodatkową kolumnę(y) z drugiej tabeli:

Jeśli potrzebujesz połączyć tylko dwie tabele, możesz uznać pracę za prawie wykonaną i załadować wynikową tabelę do Excela.

Połącz więcej tabel (opcjonalnie)

W przypadku, gdy masz trzy lub więcej tabel do połączenia, czeka Cię jeszcze trochę pracy. Przedstawię tutaj w skrócie kroki, ponieważ wszystko to już zrobiłeś podczas łączenia dwóch pierwszych tabel:

1. Zapisz tabelę, którą otrzymałeś w poprzednim kroku (pokazaną na powyższym zrzucie ekranu) jako połączenie:
   • W edytorze Power Query, kliknij Zamknij & Załaduj strzałkę rozwijaną i wybierz Zamknij i załaduj To….
   • W oknie dialogowym Importuj dane wybierz opcję Tylko utwórz połączenie i kliknij OK.

   To doda jedno połączenie o nazwie Merge1 do okna Zapytania & Połączenia. Możesz zmienić nazwę tego połączenia (kliknij prawym przyciskiem myszy i wybierz Zmień nazwę z menu podręcznego).
   

2. Połącz Merge1 z trzecią tabelą (Commissions) wykonując te kroki (zakładka Dane > Pobierz dane > Połącz zapytania > Połącz).

   Zrzut ekranu poniżej pokazuje moje ustawienia:
   

3. Kliknięcie OK w oknie dialogowym Merge otwiera edytor Power Query, w którym wybieramy kolumny do dodania z tabeli 3.

W tym przykładzie dodajemy tylko kolumnę Komisja:

W wyniku otrzymujemy połączoną tabelę, która składa się z pierwszej tabeli oraz dodatkowych kolumn skopiowanych z dwóch pozostałych tabel.

Import połączonej tabeli do Excela

Mając tabelę wynikową w Power Query Editor, pozostaje tylko jedna rzecz do zrobienia – załadować ją do skoroszytu Excela. I jest to najłatwiejsza część!

1. W edytorze Power Query Editor kliknij strzałkę Zamknij & Załaduj i wybierz Zamknij i załaduj do….
2. W oknie dialogowym Importuj dane wybierz opcje Tabela i Nowy arkusz.
3. Kliknij OK.

Nowa tabela łącząca dane z dwóch lub więcej źródeł pojawia się w nowym arkuszu. Gratulacje, udało Ci się!

Jako wykończenie możesz zastosować odpowiedni format liczb w niektórych kolumnach i być może zmienić domyślny styl tabeli na swój ulubiony. Po tych poprawkach moja połączona tabela wygląda bardzo ładnie:

Jak połączyć tabele na podstawie wielu kolumn za pomocą Power Query

W poprzednim przykładzie łączyliśmy tabele poprzez dopasowanie danych w jednej kluczowej kolumnie. Nic jednak nie stoi na przeszkodzie, aby wybrać dwie lub więcej par kolumn. Oto jak:

W oknie dialogowym Scalanie, przytrzymaj klawisz Ctrl i klikaj kolejno kluczowe kolumny, aby je zaznaczyć. Ważne jest, aby klikać na kolumny w tej samej kolejności w obu podglądach, aby pasujące kolumny miały te same numery. Na przykład Sprzedawca to kolumna kluczowa 1, a Produkt to kolumna kluczowa 2. Puste komórki lub wiersze, których Power Query nie jest w stanie dopasować pokazują null:

Po wykonaniu tych samych kroków jak opisane powyżej, Twoje tabele zostaną połączone poprzez dopasowanie wartości we wszystkich kluczowych kolumnach.

Jak zaktualizować/odświeżyć połączoną tabelę

Najlepszą rzeczą w Power Query jest to, że jest to jednorazowa konfiguracja. Kiedy dokonasz zmian w tabeli źródłowej, nie musisz powtarzać całego procesu od nowa. Wystarczy, że klikniesz przycisk Odśwież na panelu Połączenia zapytań &, a połączona tabela zostanie zaktualizowana od razu:

Jeśli panel zniknął z Twojego Excela, kliknij przycisk Zapytania & Połączenia na karcie Dane, aby go odzyskać.

Alternatywnie możesz kliknąć przycisk Odśwież wszystko na karcie Dane lub przycisk Odśwież na karcie Zapytania (ta karta uaktywnia się po zaznaczeniu dowolnej komórki w ramach połączonej tabeli).

Kreator łączenia tabel – szybki sposób na połączenie 2 tabel w Excelu

Gdy już znasz wbudowane narzędzie, pozwól, że pokażę Ci nasze podejście do łączenia tabel w Excelu.

W tym przykładzie będziemy łączyć te same tabele, które przed chwilą połączyliśmy za pomocą Power Query. Dodałem tylko kilka dodatkowych wierszy do drugiej tabeli, aby pokazać Ci więcej możliwości naszego dodatku:

Mając zainstalowany Kreator łączenia tabel w swoim Excelu, oto co musisz zrobić:

1. Zaznacz pierwszą tabelę lub dowolną komórkę w niej i kliknij przycisk Połącz dwie tabele na karcie Dane Ablebits:
   
2. Przyjrzyj się szybko wybranemu zakresowi, aby upewnić się, że dodatek dobrze go ustawił i kliknij Dalej.
   
3. Wybierz drugą tabelę i kliknij Dalej. Zwróć uwagę, że nasza druga tabela zawiera 26 wierszy w porównaniu z 10 wierszami w pierwszej tabeli:
   
4. Wybierz jedną lub kilka pasujących kolumn i kliknij Dalej. Ponieważ łączymy dwie tabele za pomocą jednej wspólnej kolumny, ID zamówienia, wybieramy tylko tę kolumnę:
   
   Zauważ pole dopasowania uwzględniającego wielkość liter u góry. Zaznacz je, jeśli chcesz traktować wielkie i małe litery w kluczowych kolumnach jako różne znaki. W tym przykładzie nie jest nam to potrzebne, więc pozostawimy to pole niezaznaczone.
5. Wybierz kolumny do aktualizacji w pierwszej tabeli. Ten krok jest opcjonalny i jeśli nie chcesz żadnych aktualizacji, możesz kliknąć Dalej bez zaznaczania czegokolwiek w tym miejscu.

   Wybieramy kolumnę Sprzedawcy, ponieważ mamy więcej wierszy w drugiej tabeli i chcemy, aby nowe nazwy sprzedawców pojawiły się w istniejącej kolumnie Sprzedawcy:
   

6. Wybierz kolumnę(y), które mają być dodane do pierwszej tabeli, Produkt w naszym przypadku, i kliknij Dalej:
   
7. Ten krok jest bardzo ważny, ponieważ określa, jak twoje tabele będą łączone.
   W tym przykładzie, idziemy z domyślnymi opcjami pokazanymi na zrzucie ekranu poniżej. Chciałbym jednak zwrócić uwagę na następujące 2 pola, które mogą zapobiec nadpisaniu istniejących danych w przypadku, gdy zdecydowałeś się zaktualizować niektóre kolumny:
   • Tylko puste komórki
   • Tylko jeśli komórki w tabeli wyszukującej zawierają dane

   

Podejmij decyzję, kliknij przycisk Zakończ, pozwól kreatorowi na kilka sekund na przetworzenie danych i sprawdź wyniki.

Przy domyślnych opcjach kreator podświetla nowo dodane wiersze i dodaje kolumnę Status. Jeśli nie chcesz tego robić, wyczyść odpowiednie pola w ostatnim kroku.

Aby połączyć trzy lub więcej tabel, powtórz powyższe kroki. Pamiętaj tylko, aby wybrać wynik poprzedniego łączenia jako tabelę główną.

W przeciwieństwie do Power Query, Kreator łączenia tabel nie zachowuje połączenia między tabelą wynikową a źródłową. W niektórych sytuacjach może to być wadą. Plusem jest to, że bez względu na to, co zrobisz z tabelą źródłową – edytujesz, przenosisz czy nawet usuwasz – połączona tabela pozostanie nienaruszona.

Powyższy przykład pokazał tylko jeden scenariusz, z którym nasz kreator może sobie poradzić, ale jest ich znacznie więcej! Jeśli są Państwo ciekawi innych przypadków użycia, proszę sprawdzić te przykłady.

Można również pobrać wersję próbną pakietu Ultimate Suite dla Excela, który zawiera Kreator łączenia tabel oraz ponad 60 innych przydatnych narzędzi. Jeśli spodoba ci się ten dodatek i zdecydujesz się na zakup licencji, z przyjemnością przedstawimy ci tę wyjątkową ofertę:

W przypadku, gdy chcesz połączyć tabele w inny sposób, możesz skorzystać z następujących zasobów.

Inne sposoby łączenia danych w Excelu:

Połącz tabele według nagłówków kolumn – połącz dwie lub więcej tabel na podstawie nazw kolumn. Możesz wybrać, czy chcesz połączyć wszystkie kolumny, czy tylko te, które wybierzesz.

Połącz wiele arkuszy w jeden – skopiuj wiele arkuszy do jednego arkusza zbiorczego. Oczywiście nie jest to ręczne kopiowanie/wklejanie! Wystarczy wskazać arkusze do połączenia, a nasze narzędzie Kopiuj arkusze zrobi resztę.

Porównanie dwóch plików Excela – jak porównać dwie tabele (arkusze) w poszukiwaniu różnic i połączyć je w jeden arkusz.