Przedstawionego w poprzednim wpisie sposobu łączenia danych do tabeli przestawnej nie zastosują z pewnością użytkownicy dodatku Power Query (w Excelu 2016 nazywanego Get & Transform czyli Pobieranie i Przekształcanie).. Sposób numer 2 jest szybszy i wygodniejszy, dlatego jeśli jeszcze nie masz tego dodatku, a masz wersję Excela 2010 lub 2013, koniecznie zainstaluj Power Query.

Przygotowanie danych w programie Excel

Dla otrzymania przy każdym z prezentowanych rozwiązań takiego samego efektu wykorzystam dokładnie te same dane ze strony https://data.cityofchicago.org/Public-Safety/Crimes-2001-to-present/ijzp-q8t2. Dane dotyczą przestępczości w Chicago.

Power Query

W pliku znajdują się dwa arkusze z danymi o takim samym układzie kolumnowym, a różnej ilości wierszy.

Zaczynam od umieszczenia danych w modelu, by w kolejnym kroku je połączyć. By dołączyć dane do modelu umieszczam myszkę w jednej z komórek gdzie znajdują się dane. Wybieram Power Query > Dane programu Excel > Z tabeli > (Excel automatycznie znajduje zakres wypełniony danymi).

W oknie „Z tabeli” zaznaczam opcję > Moja tabela zawiera nagłówki > OK.

W oknie Power Query zmieniam nazwę utworzonej tabeli na Crimes2 (tak jak nazwa arkusza).

Następnie wybieram > Narzędzia główne > Zamknij > Zamknij i załaduj do. W oknie „Ładowanie do” wybieram opcje Utwórz tylko połączenie oraz Dodaj dane do modelu danych > Załaduj. Analogicznie postępuję z drugim arkuszem.

Gdy obie tabele są w modelu danych, widać dokładnie ile posiadają wierszy, zatem łatwo policzyć ile powinno ich być po ich połączeniu.

By połączyć dane i zobaczyć je przy pomocy jednej tabeli przestawnej wybieram > Power Query > Połącz > Dołącz. Pojawia się okno Dołączenie, bez znaczenia jest w tym przypadku,  w jakiej kolejności wybierzemy tabele do dołączenia > wybieram utworzone tabele > OK.

W oknie Power Query zmieniam nazwę utworzonej tabeli. Na dole w oknie widać natomiast ile wierszy jest w złączonej tabeli. Wybieram > Narzędzia główne > Zamknij > Zamknij i załaduj do >Utwórz tylko połączenie oraz Dodaj dane do modelu danych > Załaduj

Następnie wstawiam tabelę przestawną Wstawianie > Tabela Przestawna > Użyj zewnętrznego źródła danych > Wybierz połączenie >

Wśród istniejących połączeń wybieram utworzone przeze mnie > Zapytanie Złączone > Otwórz > wybieram miejsce dla tabeli przestawnej > OK.

W polach tabeli, widać połączenie z utworzoną tabelą w Power Query. Po jej rozwinięciu dostępne są wszystkie kolumny z tabel źródłowych. Porównując to rozwiązanie z korzystaniem z kreatora tabeli przestawnej, nie mamy informacji w powstałej tabeli, z którego arkusza pochodzą dane, ale jednak wygodniej się korzysta z pól raportu tabeli przestawnej (są dostępne bez modyfikacji i ukrywania pod pośrednimi nazwami). Dużo naturalniej można działać na takim modelu przy wybieraniu pól do raportu tabeli przestawnej.

Pobierz plik

Pobierz plik laczenie-tabel-do-tabeli-przestawnej-w-power-query.xlsx i wstaw swoje dane.