Niedawno otrzymałem od czytelnika pytanie dotyczące łączenia wielu arkuszy roboczych w tym samym skoroszycie w jeden arkusz roboczy.
Poprosiłem go, aby używał dodatku Power Query do łączenia różnych arkuszy, ale potem zdałem sobie sprawę, że dla kogoś nowego w dodatku Power Query może to być trudne.
Postanowiłem więc napisać ten samouczek i pokazać dokładne kroki łączenia wielu arkuszy w jedną tabelę za pomocą dodatku Power Query.
Poniżej film, w którym pokazuję, jak łączyć dane z wielu arkuszy/tabel za pomocą dodatku Power Query:
Poniżej znajdują się pisemne instrukcje dotyczące łączenia wielu arkuszy (jeśli wolisz tekst pisany od wideo).
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).
Połącz dane z wielu arkuszy roboczych za pomocą dodatku Power Query
Podczas łączenia danych z różnych arkuszy za pomocą dodatku Power Query wymagane jest umieszczenie danych w tabeli programu Excel (lub przynajmniej w nazwanych zakresach). Jeśli dane nie znajdują się w tabeli programu Excel, pokazana tutaj metoda nie zadziała.
Załóżmy, że masz cztery różne arkusze - Wschód, Zachód, Północ i Południe.
Każdy z tych arkuszy zawiera dane w tabeli programu Excel, a struktura tabeli jest spójna (tj. nagłówki są takie same).
Kliknij tutaj, aby pobrać dane i podążaj dalej.
Tego rodzaju dane można niezwykle łatwo łączyć za pomocą dodatku Power Query (który działa naprawdę dobrze z danymi w tabeli programu Excel).
Aby ta technika działała najlepiej, lepiej mieć nazwy dla tabel programu Excel (pracuj również bez tego, ale łatwiej jest używać, gdy tabele są nazwane).
Nadałem tabelom następujące nazwy: Dane_Wschód, Dane_Zachód, Dane_Północ i Dane_Południe.
Oto kroki umożliwiające łączenie wielu arkuszy roboczych z tabelami programu Excel za pomocą dodatku Power Query:
- Przejdź do zakładki Dane.
- W grupie Pobierz i przekształć dane kliknij opcję „Pobierz dane”.
- Przejdź do opcji „Z innych źródeł”.
- Kliknij opcję „Puste zapytanie”. Spowoduje to otwarcie edytora Power Query.
- W edytorze zapytań wpisz następującą formułę na pasku formuły: =Excel.Bieżący skoroszyt(). Pamiętaj, że w formułach dodatku Power Query rozróżniana jest wielkość liter, więc musisz użyć dokładnie takiej formuły, jak wspomniano (w przeciwnym razie wystąpi błąd).
- Naciśnij klawisz Enter. Spowoduje to wyświetlenie wszystkich nazw tabel w całym skoroszycie (pokaże również nazwane zakresy i/lub połączenia, jeśli istnieją w skoroszycie).
- [Krok opcjonalny] W tym przykładzie chcę połączyć wszystkie tabele. Jeśli chcesz połączyć tylko określone tabele programu Excel, możesz kliknąć ikonę rozwijaną w nagłówku nazwy i wybrać te, które chcesz połączyć. Podobnie, jeśli masz nazwane zakresy lub połączenia i chcesz tylko połączyć tabele, możesz również usunąć te nazwane zakresy.
- W komórce nagłówka Treść kliknij podwójną strzałkę.
- Wybierz kolumny, które chcesz połączyć. Jeśli chcesz połączyć wszystkie kolumny, upewnij się, że (Wybierz wszystkie kolumny) jest zaznaczone.
- Odznacz opcję „Użyj oryginalnej nazwy kolumny jako prefiksu”.
- Kliknij OK.
Powyższe kroki łączą dane ze wszystkich arkuszy w jedną tabelę.
Jeśli przyjrzysz się uważnie, zauważysz, że ostatnia kolumna (najbardziej po prawej) ma nazwę tabel programu Excel (Dane_Wschodnie, Dane_Zachód, Dane_Północ i Dane_Południe). Jest to identyfikator, który mówi nam, który rekord pochodzi z której tabeli Excela. Z tego też powodu powiedziałem, że lepiej mieć opisowe nazwy tabel Excela.
Oto kilka modyfikacji, które możesz wprowadzić w połączonych danych w samym dodatku Power Query:
- Przeciągnij i umieść kolumnę Nazwa na początku.
- Usuń „_Data” z kolumny nazwy (abyś pozostał ze wschodem, zachodem, północą i południem w kolumnie nazwy). Aby to zrobić, kliknij prawym przyciskiem myszy nagłówek Nazwa i kliknij Zamień wartości. W oknie dialogowym Zamień wartości zastąp _Data pustym miejscem.
- Zmień kolumnę Dane, aby pokazywała tylko daty (a nie godzinę). Aby to zrobić, kliknij nagłówek kolumny Data, przejdź do zakładki „Przekształć” i zmień Typ danych na Data.
- Zmień nazwę zapytania na ConsolidatedData.
Teraz, gdy masz połączone dane ze wszystkich arkuszy w dodatku Power Query, możesz je załadować w programie Excel - jako nową tabelę w nowym arkuszu.
Aby to zrobić. wykonaj poniższe czynności:
- Kliknij kartę „Plik”.
- Kliknij Zamknij i załaduj do.
- W oknie dialogowym Importuj dane wybierz opcje Tabela i Nowy arkusz.
- Kliknij OK.
Powyższe kroki łączą dane ze wszystkich arkuszy roboczych i dają te połączone dane w nowym arkuszu.
Jeden problem, który musisz rozwiązać podczas korzystania z tej metody
Jeśli użyłeś powyższej metody do połączenia wszystkich tabel w skoroszycie, prawdopodobnie napotkasz problem.
Zobacz liczbę wierszy połączonych danych - 1304 (to prawda).
Teraz, jeśli odświeżę zapytanie, liczba wierszy zmieni się na 2607. Odśwież ponownie, a zmieni się na 3910.
Oto problem.
Za każdym razem, gdy odświeżasz zapytanie, dodaje wszystkie rekordy z oryginalnych danych do połączonych danych.
Uwaga: ten problem możesz napotkać tylko wtedy, gdy używasz dodatku Power Query do łączenia WSZYSTKIE STOŁY EXCEL w skoroszycie. Jeśli wybrałeś konkretne tabele do połączenia, nie napotkasz tego problemu.Rozumiemy przyczynę tego problemu i jak to naprawić.
Gdy odświeżysz zapytanie, wraca ono do tyłu i wykonuje wszystkie kroki, które podjęliśmy, aby połączyć dane.
W kroku, w którym użyliśmy formuły =Excel.Bieżący skoroszyt(), dał nam listę wszystkich stołów. Za pierwszym razem zadziałało to dobrze, ponieważ były tylko cztery stoły.
Jednak po odświeżeniu w skoroszycie znajduje się pięć tabel - w tym nowa tabela wstawiona przez dodatek Power Query w miejscu, w którym mamy połączone dane.
Tak więc za każdym razem, gdy odświeżasz zapytanie, oprócz czterech tabel programu Excel, które chcemy połączyć, dodaje również istniejącą tabelę zapytań do danych wynikowych.
Nazywa się to rekurencją.
Oto jak rozwiązać ten problem.
Po wstawieniu =Excel.CurrentWorkbook() na pasku formuły dodatku Power Query i naciśnięciu klawisza Enter otrzymasz listę tabel programu Excel. Aby mieć pewność, że połączysz tylko tabele z arkusza roboczego, musisz jakoś przefiltrować tylko te tabele, które chcesz połączyć, i usunąć wszystko inne.
Oto kroki, aby upewnić się, że masz tylko wymagane tabele:
- Kliknij listę rozwijaną i najedź kursorem na Filtry tekstu.
- Kliknij opcję Zawiera.
- W oknie dialogowym Filtruj wiersze wpisz _Dane w polu obok opcji „zawiera”.
- Kliknij OK.
Możesz nie zauważyć żadnych zmian w danych, ale spowoduje to, że tabela wynikowa nie zostanie ponownie dodana po odświeżeniu zapytania.
Zauważ, że w powyższych krokach użyliśmy „_Dane”, aby filtrować, jak nazwaliśmy tabele w ten sposób. Ale co, jeśli twoje tabele nie mają spójnych nazw. Co jeśli wszystkie nazwy tabel są losowe i nie mają ze sobą nic wspólnego.
Oto sposób na rozwiązanie tego problemu - użyj filtra „nie równa się” i wpisz nazwę Zapytania (w naszym przykładzie będzie to ConsolidatedData). Zapewni to, że wszystko pozostanie bez zmian, a wynikowa tabela zapytań, która zostanie utworzona, zostanie odfiltrowana.
Oprócz tego, że dodatek Power Query sprawia, że cały proces łączenia danych z różnych arkuszy (lub nawet tego samego arkusza) jest całkiem prosty, kolejną zaletą korzystania z niego jest dynamika. Jeśli dodasz więcej rekordów do dowolnej tabeli i odświeżysz dodatek Power Query, automatycznie otrzymasz połączone dane.Ważna uwaga: W przykładzie użytym w tym samouczku nagłówki były takie same. Jeśli nagłówki są różne, dodatek Power Query połączy i utworzy wszystkie kolumny w nowej tabeli. Jeśli dane są dostępne dla tej kolumny, zostaną wyświetlone, w przeciwnym razie będą miały wartość null.
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).
- 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)
- Scal tabele w programie Excel za pomocą dodatku Power Query.
- Jak porównać dwa arkusze Excela / pliki?
