Łączenie wielu arkuszy w jedną tabelę

Posted on Posted in Raport

W dzisiejszym poście pokażę szybką metodę na połączenie danych z kilku arkuszy w jedną tabelę. Zabieg ten pozwala na analizę kilku źródeł danych, które do tej pory znajdowały się w różnych arkuszach. W celu wykonania takiego połączenia posłużymy się dodatkiem Power Query, który jest częścią pakietu PowerBI Microsoft Office. Pakiet dostępny jest w Office Professional Plus, Office 365 Professional Plus oraz samodzielnej wersji programu Excel 2013. Samo Power Query w wersji Offica 2016 na szczęście będzie już standardowym narzędziem.

 

1. Dane do analizy

Załóżmy, że otrzymaliśmy dane dotyczące sprzedaży z trzech krajów(w tym wypadku korzystanie z Power Query może wydać się nadużyciem, ale gdybyśmy dysponowali np. 10 arkuszami to gra na pewno warta jest zachodu). W arkuszach mamy tabelę z nazwą kraju, sprzedawanym asortymentem i wartością sprzedaży. Plik można pobrać tutaj.

 

2. Prace w Power Query

Otwieramy nowy skoroszyt Excela i nazywamy go AnalizaSprzedaży. Klikamy na kartę POWER QUERY (pod tym linkiem można pobrać dodatek https://www.microsoft.com/pl-pl/download/details.aspx?id=39379).  Z grupy Pobieranie danych zewnętrznych klikamy na Z pliku a następnie z Folderu.

 

Pobieranie danych z folderu przez Power Query

 

Wybieramy interesujący nas folder Sprzedaż po wcześniejszym rozpakowaniu i klikamy Ok.

 

Ścieżka do pliku Power Query

 

Naszym oczom ukaże się okno zapytania Power Query z pobranymi danymi. Widzimy tutaj informację m.in o nazwie pliku, jego rozszerzeniu czy ścieżce dostępu. Nas interesować będzie jedynie kolumna Content z danymi Binary. Dlatego też zaznaczmy pozostałe kolumny i usuwamy je.

 

Usuwanie kolumn z zapytania Power Query

 

Następnie tworzymy kolumnę niestandardową, dzięki której pobierzemy dane z naszych skoroszytów. W karcie narzędzie główne, w grupie Utwórz klikamy na ikonkę Wstawianie kolumny niestandardowej a następnie wpisujemy formułę Excel.Workbook([Content]) w polu Formuła kolumny niestandardowej i klikamy Ok.

 

Kolumna niestandardowa w Power Query

 

Tym sposobem utworzyliśmy nową kolumnę o nazwie Custom. W jej górnym prawym rogu widać strzałeczki. Klikamy na nie i wybieramy tylko Data, czyli dane jakie znajdują się w naszych arkuszach. Druga opcja (Name) pozwala jedynie na wyświetlenie nazw arkuszy, w jakich znajdują się zestawienia sprzedaży i nie daje możliwości dalszego rozwijania danych.

 

Rozwinięcie danych w kolumnie niestandardowej

 

Nasza kolumna Custom zmieniła nazwę na Custom.Data, teraz po ponownym kliknięciu strzałek i wybraniu Ok rozwiniemy dane z tabel znajdujących się w skoroszytach. Tabele ułożą się jedna pod drugą.

 

Rozwinięte dane w Power Query

 

W tym miejscu nie pozostaje nam nic innego jak tylko ustawić właściwy nagłówek dla naszej nowej tabeli i pozbyć się pozostałych znajdujących się wewnątrz danych. Klikamy w grupie Przekształć na przycisk Użyj pierwszego wiersza jako nagłówków. Tym samym pierwszy wiersz tabeli z kraju Australia stanie się nadrzędnym nagłówkiem. Następnie w kolumnie Country klikamy na strzałeczkę filtra i odhaczamy z listy słowo Country. Dzięki temu pozbędziemy się zduplikowanych nagłówków wewnątrz tabeli. Ostatecznie dane powinny wyglądać następująco.

 

Przefiltrowane dane Power Query

 

Zauważmy, że każdy wykonany przez nas krok został zarejestrowany w zakładce Ustawienia zapytania --> Zastosowane kroki. Jeżeli chcielibyśmy cofnąć się do danego momentu obróbki danych wystarczy kliknąć na dany opis. Używając X przy każdym z opisów możemy skasować dany krok.

 

Cofanie kroków w Power Query

 

Kiedy nasze dane zostały już scalone zrzucamy je do Excela w celu dalszej analizy. Najpierw jednak musimy zaznaczyć w jakiej postaci mają zostać zachowane. W tym celu w zakładce Ustawienia zapytania --> Załaduj ustawienia odhaczamy pozycję Załaduj do arkusza. Gdybyśmy ją zostawili nasze dane zostałby wstawione do arkusza w postaci tabeli. Zaznaczenie drugiej opcji spowodowałoby dodanie ich do modelu danych obsługiwanego przez kolejny dodatek z rodziny PowerBI a mianowicie Power Pivot. My natomiast odhaczamy obydwa pola i klikamy na ikonę Zastosuj i zamknij w grupie Zapytanie. Opcja ta spowoduje, że nasze dane zostaną zapamiętane jako zewnętrze źródło danych. W Excelu powinna pojawić się zakładka Zapytania skoroszytu z naszym Zapytaniem nr 1.

 

Zapytanie skoroszytu

 

 

Nasze zapytanie jest włączone i gotowe do użycia. W każdej chwili możemy je edytować klikając na nie PPM i wybierając Edytuj. Aby utworzyć tabelę przestawną z nowo powstałego zapytania klikamy na karcie Wstawianie w grupie Tabele ikonkę Tabeli przestawnej i jako źródło danych wybieramy Zewnętrzne źródło danych jakim jest nasze zapytanie.

 

Tabela przestawna oparta na zewnętrznym źródle danych

 

W tym momencie możemy już analizować naszą sprzedaż w podziale na asortyment czy kraj. Wśród pól tabeli przestawnej jest pole o nazwie Column1 - to nagłówek tabeli z danymi binary. Aby się go pozbyć możemy skorzystać z możliwości edycji zapytania, usunąć zbędną kolumnę i ponownie Zapisać i zamknąć zapytanie. Potem wystarczy już tylko odświeżyć dane tabeli przestawnej i przeprowadzać swoje analizy.

2 thoughts on “Łączenie wielu arkuszy w jedną tabelę

  1. Cześć! Mam wielki problem z konsolidacja danych. Otrzymałam od promotora 3 różne arkusze ( docelowo ma być ich ok 5 ). Muszę złożyć je w jeden ( mam do tego gotowy wzór). Problem polega na tym, że każdy z nich zawiera inne dane a wspólnym mianownikiem jest tylko nazwa firmy. Jeden arkusz dotyczy lokalizacji, drugi rodzaju dzialalnosci, trzeci ma kody tych działalności, dane kontaktowe itd. Absolutnie nie wiem jak ruszyć. Klasyczna konsolidacja nie ma tu zastosowania. Ostateczna wersja musi zawierać nazwę firmy i wszystkie dotyczące jej dane z innych plików. Firm jest ok 20 tysięcy. Bardzo ładnie proszę o pomoc. Jestem w ślepym zaułku. Pozdrawiam serdecznie.edyta

Skomentuj Konsolidacja? Ale jak? Anuluj pisanie odpowiedzi

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *