Dla wszystkich tych, którzy na co dzień w pracy operują na danych z wielu plików oraz tabelach przestawnych, przyda się wskazówka jak połączyć dane z kilku arkuszy czy nawet plików w jedną finalną tabelę przestawną. Nie chcemy przy tym modyfikować danych w plikach źródłowych. Pierwszy sposób wykorzystuje kreatora tabel przestawnych, który w nowszych wersjach programu Excel zniknął z interfejsu.

Przygotowanie danych w programie Excel

Dane które wykorzystam w tym artykule pochodzą ze strony https://data.cityofchicago.org/Public-Safety/Crimes-2001-to-present/ijzp-q8t2 City of Chicago, która popularyzuje dostęp do danych rządowych dotyczących miasta. Wykorzystam dane dotyczące przestępczości.

Wykorzystam dwa pliki – układ kolumnowy plików będzie identyczny, różnić się będą jedynie liczbą wierszy.

Tworzenie tabeli przestawnej przy pomocy kreatora

Jedna z możliwości połączenia danych prowadzi przez kreatora tabel przestawnych. Jest to narzędzie standardowo niedostępne na wstążce, więc zaczynam od dodania go. Plik > Opcje > Dostosowywanie wstążki > Polecenia których nie ma na wstążce > (szukam alfabetycznie) > Kreator tabeli przestawnej i wykresu przestawnego > Dodaj.

W tym momencie otrzymam komunikat jak poniżej, co oznacza, że po prawej stronie okna „Karty główne”, nie mam dodanej nowej grupy w której kreator się znajdzie.

Aby dodać grupę wybieram miejsce na wstążce gdzie chcę zobaczyć kreator, sugeruję by było to w pobliżu polecenia tworzącego tabelę przestawną. Wybieram Tabele > Nowa grupa > Zmień nazwę > wpisuję Kreator > OK . Przy zaznaczonej nowej grupie (Kreator niestandardowa) , po lewej stronie wybieram Kreator tabeli przestawnej > Dodaj > OK.

Efekt na wstążce po dodaniu:

Zanim zacznę konsolidować dane, ważne by otworzyć pliki, z których dane będę łączyć.Uruchamiam kreator tabeli przestawnej. Wybieram Wiele zakresów konsolidacji > Tabela Przestawna > Dalej.

Pola strony utworzy użytkownik > Dalej

Wybieram zakresy, które będę łączyć , po każdym wyborze wybieram > Dodaj.

W następnym kroku wybieram ilość stron, które chcę dodać dla odróżnienia danych. Są to dodatkowe pola pojawiające się w raporcie nowopowstałej skonsolidowanej tabeli przestawnej, które pozwalają na rozróżnienie łączonych plików/tabel/zakresów/arkuszy. Przy łączeniu dwóch źródeł wystarczy jedna strona, jeżeli łącze dane z kilku wówczas przyda się kilka pól stron.

Wybieram pole strony 1. Zaznaczam pierwszy z zakresów na liście i w polu „pole pierwsze” wpisuję nazwę pod jaką chciałbym wybrać ten zakres na filtrze tabeli przestawnej którą, buduję. Nazywam go Żródło1, to samo powtarzam dla drugiego zakresu (gdy wybieram drugi zakres nazwa pierwszego znika z pola „Pole pierwsze”.

Przechodzę Dalej > Wybieram Nowy Arkusz dla tabeli przestawnej > Zakończ.

Finalna tabela przestawna jako filtr raportu otrzymała zaznaczone przeze mnie w kreatorze pole pierwsze jako : Strona1. Kolejne pola tabeli przestawnej zawierają:

– wiersz – to wartości z pierwszej kolumny danych źródłowych (czyi typy przestępstw)

– kolumna – to nagłówki kolumn, z tabel źródłowych

– wartości – to przecięcia nagłówków kolumn oraz poszczególnych wierszy

– strona1 – to filtr utworzony w kreatorze tabeli przestawnej.

Utworzona tabela przestawna na pierwszy rzut oka wygląda bezsensownie, aby dawała dodatkowe informacje, należy nad nią popracować i nieco ją zmodyfikować.

Pola dostępne pod filtrem Strona1 – utworzone w kreatorze.

Aby zmodyfikować tabelę przestawną można wykorzystać filtry na poszczególnych polach, zaś pole wartość można wykorzystać wielokrotnie. W poniższym przykładzie w polu kolumna wybrana jest z danych źródłowych ta o nazwie Year, następnie w polach wierszy pole „Wartość” pozwala na wyświetlenie roku.