Dodatek Power Query może być bardzo pomocny, gdy chcesz połączyć wiele skoroszytów w jeden skoroszyt.
Załóżmy na przykład, że masz dane sprzedaży dla różnych regionów (Wschód, Zachód, Północ i Południe). Możesz połączyć te dane z różnych skoroszytów w jeden arkusz za pomocą dodatku Power Query.
Jeśli masz te skoroszyty w różnych lokalizacjach/folderach, dobrym pomysłem jest przeniesienie ich do jednego folderu (lub utworzenie kopii i umieszczenie tej kopii skoroszytu w tym samym folderze).
Na początek mam cztery skoroszyty w folderze (jak pokazano poniżej).
Teraz w tym samouczku omówię trzy scenariusze, w których można łączyć dane z różnych skoroszytów za pomocą dodatku Power Query:
- Każdy skoroszyt zawiera dane w tabeli programu Excel, a wszystkie nazwy tabel są takie same.
- Każdy skoroszyt zawiera dane o tej samej nazwie arkusza. Może tak być w przypadku, gdy we wszystkich skoroszytach znajduje się arkusz o nazwie „podsumowanie” lub „dane” i chcesz je wszystkie połączyć.
- Każdy skoroszyt zawiera wiele arkuszy i tabel, a chcesz połączyć określone tabele/arkusze. Ta metoda może być również pomocna, gdy chcesz połączyć tabelę/arkusze, które nie mają spójnej nazwy.
Zobaczmy, jak w każdym przypadku połączyć dane z tych skoroszytów.
Każdy skoroszyt zawiera dane w tabeli programu Excel o tej samej strukturze
Poniższa technika będzie działać, gdy tabele programu Excel mają taką samą strukturę (te same nazwy kolumn).
Liczba wierszy w każdej tabeli może się różnić.
Nie martw się, jeśli niektóre tabele Excela mają dodatkowe kolumny. Możesz wybrać jedną z tabel jako szablon (lub jako „klucz”, jak nazywa go dodatek Power Query), a dodatek Power Query użyje jej do połączenia z nią wszystkich innych tabel programu Excel.
W przypadku, gdy w innych tabelach znajdują się dodatkowe kolumny, zostaną one zignorowane i zostaną połączone tylko te określone w szablonie/kluczu. Na przykład jeśli wybrana tabela szablonów/kluczy ma 5 kolumn, a jedna z tabel w innym skoroszycie ma 2 dodatkowe kolumny, te dodatkowe kolumny zostaną zignorowane.
Teraz mam cztery skoroszyty w folderze, które chcę połączyć.
Poniżej znajduje się migawka tabeli, którą mam w jednym ze skoroszytów.
Oto kroki, aby połączyć dane z tych skoroszytów w jeden skoroszyt (jako pojedynczą tabelę).
- Przejdź do zakładki Dane.
- W grupie Pobierz i przekształć kliknij menu rozwijane Nowe zapytanie.
- Najedź kursorem na „Z pliku” i kliknij „Z folderu”.
- W oknie dialogowym Folder wprowadź ścieżkę do folderu zawierającego pliki lub kliknij Przeglądaj i zlokalizuj folder.
- Kliknij OK.
- W otwartym oknie dialogowym kliknij przycisk łączenia.
- Kliknij „Połącz i załaduj”.
- W otwartym oknie dialogowym „Połącz pliki” wybierz tabelę w lewym okienku. Pamiętaj, że dodatek Power Query wyświetla tabelę z pierwszego pliku. Ten plik będzie działał jako szablon (lub klucz) do łączenia innych plików. Dodatek Power Query będzie teraz wyszukiwać „Tabela 1” w innych skoroszytach i łączyć ją z tym.
- Kliknij OK.
Spowoduje to załadowanie wyniku końcowego (dane połączone) do aktywnego arkusza roboczego.
Pamiętaj, że wraz z danymi dodatek Power Query automatycznie dodaje nazwę skoroszytu jako pierwszą kolumnę połączonych danych. Pomaga to w śledzeniu, jakie dane pochodzą z którego skoroszytu.
Jeśli chcesz najpierw edytować dane przed załadowaniem ich do programu Excel, w kroku 6 wybierz „Połącz i edytuj”. Spowoduje to otwarcie wyniku końcowego w edytorze Power Query, w którym możesz edytować dane.
Kilka rzeczy, które warto wiedzieć:
- Jeśli jako szablon wybierzesz tabelę programu Excel (w kroku 7), dodatek Power Query użyje nazw kolumn w tej tabeli, aby połączyć dane z innych tabel. Jeśli inne tabele mają dodatkowe kolumny, zostaną one zignorowane. W przypadku, gdy te inne tabele nie mają kolumny, która znajduje się w tabeli szablonów, Power Query po prostu wstawi dla niej wartość „null”.
- Kolumny nie muszą być w tej samej kolejności, ponieważ dodatek Power Query używa nagłówków kolumn do mapowania kolumn.
- Ponieważ jako klucz wybrano Tabela1, dodatek Power Query będzie szukać tabeli1 we wszystkich skoroszytach i łączyć je wszystkie. Jeśli nie znajdzie tabeli programu Excel o tej samej nazwie (w tym przykładzie Tabela1), dodatek Power Query wyświetli błąd.
Dodawanie nowych plików do folderu
Teraz poświęćmy chwilę i zrozummy, co zrobiliśmy z powyższymi krokami (co zajęło nam tylko kilka sekund).
Połączyliśmy dane z czterech różnych skoroszytów w jednej tabeli w kilka sekund bez otwierania żadnego ze skoroszytów.
Ale to nie wszystko.
Prawdziwą MOCĄ dodatku Power Query jest to, że teraz, gdy dodasz więcej plików do folderu, nie musisz powtarzać żadnego z tych kroków.
Wszystko, co musisz zrobić, to przenieść nowy skoroszyt do folderu, odświeżyć zapytanie i automatycznie połączy dane ze wszystkich skoroszytów w tym folderze.
Na przykład w powyższym przykładzie, jeśli dodam nowy skoroszyt - „Środek Zachód.xlsx” do folderu i odśwież zapytanie, natychmiast otrzymam nowy połączony zestaw danych.
Oto jak odświeżyć zapytanie:
- Kliknij prawym przyciskiem myszy tabelę programu Excel załadowaną do arkusza i kliknij Odśwież.
- Kliknij prawym przyciskiem myszy Zapytanie w okienku ‘Kwerendy skoroszytu’ i kliknij Odśwież
- Przejdź do zakładki Dane i kliknij Odśwież.
Każdy skoroszyt zawiera dane o tej samej nazwie arkusza
Jeśli nie masz danych w tabeli Excel, ale wszystkie nazwy arkuszy (z których chcesz połączyć dane) są takie same, możesz użyć metody pokazanej w tej sekcji.
Jest kilka rzeczy, na które musisz uważać, gdy są to tylko dane tabelaryczne, a nie tabela Excela.
- Nazwy arkuszy powinny być takie same. Ułatwi to Power Query przeglądanie skoroszytów i łączenie danych z arkuszy, które mają taką samą nazwę w każdym skoroszycie.
- W dodatku Power Query rozróżniana jest wielkość liter. Oznacza to, że arkusz roboczy o nazwie „dane” i „dane” są uważane za różne. Podobnie kolumna z nagłówkiem „Sklep” i kolumna z „sklepem” są uważane za różne.
- Chociaż ważne jest, aby mieć te same nagłówki kolumn, nie jest ważne, aby mieć tę samą kolejność. Jeśli kolumna 2 w pliku „East.xlsx” jest kolumną 4 w pliku „West.xlsx”, dodatek Power Query dopasuje ją poprawnie, mapując nagłówki.
Zobaczmy teraz, jak szybko łączyć dane z różnych skoroszytów, w których nazwa arkusza jest taka sama.
W tym przykładzie mam folder z czterema plikami.
W każdym skoroszycie mam arkusz roboczy o nazwie „Dane”, który zawiera dane w następującym formacie (zwróć uwagę, że nie jest to tabela programu Excel).
Oto kroki, aby połączyć dane z wielu skoroszytów w jeden arkusz roboczy:
- Przejdź do zakładki Dane.
- W grupie Pobierz i przekształć kliknij menu rozwijane Nowe zapytanie.
- Najedź kursorem na „Z pliku” i kliknij „Z folderu”.
- W oknie dialogowym Folder wprowadź ścieżkę do folderu zawierającego pliki lub kliknij Przeglądaj i zlokalizuj folder.
- Kliknij OK.
- W otwartym oknie dialogowym kliknij przycisk łączenia.
- Kliknij „Połącz i załaduj”.
- W otwartym oknie dialogowym „Połącz pliki” wybierz „Dane” w lewym okienku. Zwróć uwagę, że dodatek Power Query wyświetla nazwę arkusza z pierwszego pliku. Ten plik będzie działał jako klucz/szablon do łączenia innych plików. Dodatek Power Query przejrzy każdy skoroszyt, znajdzie arkusz o nazwie „Dane” i połączy je wszystkie.
- Kliknij OK. Teraz dodatek Power Query przejrzy każdy skoroszyt, wyszuka w nim arkusz o nazwie „Dane”, a następnie połączy wszystkie te zestawy danych.
Spowoduje to załadowanie wyniku końcowego (dane połączone) do aktywnego arkusza roboczego.
Jeśli chcesz najpierw edytować dane przed załadowaniem ich do programu Excel, w kroku 6 wybierz „Połącz i edytuj”. Spowoduje to otwarcie wyniku końcowego w edytorze Power Query, w którym możesz edytować dane.
Każdy skoroszyt zawiera dane z różnymi nazwami tabel lub nazwami arkuszy
Czasami możesz nie uzyskać uporządkowanych i spójnych danych (takich jak tabele o tej samej nazwie lub arkusz roboczy o tej samej nazwie).
Załóżmy na przykład, że otrzymujesz dane od osoby, która utworzyła te zestawy danych, ale nazwała arkusze jako Dane wschodnie, Dane zachodnie, Dane północne i Dane południowe.
Osoba ta mogła też utworzyć tabele programu Excel, ale o różnych nazwach.
W takich przypadkach nadal możesz używać dodatku Power Query, ale musisz to zrobić, wykonując kilka dodatkowych kroków.
- Przejdź do zakładki Dane.
- W grupie Pobierz i przekształć kliknij menu rozwijane Nowe zapytanie.
- Najedź kursorem na „Z pliku” i kliknij „Z folderu”.
- W oknie dialogowym Folder wprowadź ścieżkę do folderu zawierającego pliki lub kliknij Przeglądaj i zlokalizuj folder.
- Kliknij OK.
- W otwartym oknie dialogowym kliknij przycisk Edytuj. Spowoduje to otwarcie edytora Power Query, w którym zobaczysz szczegóły wszystkich plików w folderze.
- Przytrzymaj klawisz Control i wybierz kolumny „Treść” i „Nazwa”, kliknij prawym przyciskiem myszy i wybierz „Usuń inne kolumny”. Spowoduje to usunięcie wszystkich pozostałych kolumn z wyjątkiem wybranych.
- Na wstążce Edytora zapytań kliknij „Dodaj kolumnę”, a następnie „Kolumna niestandardowa”.
- W oknie dialogowym Dodaj niestandardową kolumnę nazwij nową kolumnę jako „Import danych” i użyj następującej formuły = Excel. Skoroszyt([TREŚĆ]). Zauważ, że w tej formule rozróżniana jest wielkość liter i musisz ją wprowadzić dokładnie tak, jak tutaj pokazałem.
- Teraz zobaczysz nową kolumnę z zapisaną tabelą. Teraz wyjaśnię, co się tutaj wydarzyło. Podałeś Power Query nazwy skoroszytów, a dodatek Power Query pobrał obiekty, takie jak arkusze, tabele i nazwane zakresy z każdego skoroszytu (który znajduje się teraz w komórce Tabela). Możesz kliknąć białą przestrzeń obok tekstu Tabela, aby zobaczyć informacje na dole. W takim przypadku, ponieważ w każdym skoroszycie mamy tylko jedną tabelę i jeden arkusz, możesz zobaczyć tylko dwa wiersze.
- Kliknij ikonę podwójnej strzałki u góry kolumny „Import danych”.
- W wyświetlonym oknie danych kolumny usuń zaznaczenie pola „Użyj oryginalnej kolumny jako prefiksu”, a następnie kliknij przycisk OK.
- Teraz zobaczysz rozszerzoną tabelę, w której zobaczysz jeden wiersz dla każdego obiektu w tabeli. W takim przypadku dla każdego skoroszytu obiekt arkusza i obiekt tabeli są wyświetlane osobno.
- W kolumnie Rodzaj przefiltruj listę, aby wyświetlić tylko tabelę.
- Przytrzymaj klawisz Ctrl i wybierz kolumnę Nazwa i Dane. Teraz kliknij prawym przyciskiem myszy i usuń wszystkie pozostałe kolumny.
- W kolumnie Dane kliknij ikonę podwójnej strzałki w prawym górnym rogu nagłówka danych.
- W otwartym polu danych kolumny kliknij OK. Spowoduje to połączenie danych we wszystkich tabelach i wyświetlenie w dodatku Power Query.
- Teraz możesz dokonać dowolnej transformacji, a następnie przejść do zakładki Strona główna i kliknąć Zamknij i wczytaj.
Teraz spróbuję szybko wyjaśnić, co tutaj zrobiliśmy. Ponieważ nie było spójności w nazwach arkuszy ani nazwach tabel, użyliśmy formuły =Excel.Workbook do pobrania wszystkich obiektów ze skoroszytów w dodatku Power Query. Te obiekty mogą obejmować arkusze, tabele i nazwane zakresy. Gdy mieliśmy już wszystkie obiekty ze wszystkich plików, przefiltrowaliśmy je, aby uwzględnić tylko tabele Excela. Następnie rozszerzyliśmy dane w tabelach i połączyliśmy je wszystkie.
W tym przykładzie przefiltrowaliśmy dane, aby używać tylko tabel programu Excel (w kroku 13). Jeśli chcesz łączyć arkusze, a nie tabele, możesz filtrować arkusze.
Uwaga - ta technika daje połączone dane nawet w przypadku niezgodności w nazwach kolumn. Na przykład, jeśli w East.xlsx masz kolumnę z błędną pisownią, otrzymasz 5 kolumn. Dodatek Power Query wypełni dane w kolumnach, jeśli je znajdzie, a jeśli nie może znaleźć kolumny, zgłosi wartość jako „null”.
Podobnie, jeśli masz dodatkowe kolumny w dowolnym arkuszu tabel, zostaną one uwzględnione w wyniku końcowym.
Teraz, jeśli otrzymasz więcej skoroszytów, z których chcesz połączyć dane, po prostu skopiuj i wklej je do folderu i odśwież dodatek Power Query
