Jak stworzyć tabelę przestawną w Excel z kilku arkuszy

Stworzenie tabeli przestawnej nie jest trudnym zadaniem. Wystarczy, że zaznaczymy jakąś komórkę w obrębie naszyc danych i skorzystamy z adekwatnej funkcji Excela na wstążce. Kiedy jednak mamy skomplikowany skoroszyt z wieloma arkuszami i danymi, stworzenie dla nich tabeli przestawnej przestaje być tak banalne.

No więc zastanawiasz się jak stworzyć tabelę przestawną, kiedy dane, które chcesz zawrzeć w tabeli, rozmieszczone są w kilku różnych arkuszach? W Excelu 2013 do dyspozycji mamy funkcję o nazwie Model Danych, która umożliwia łatwie skomponowanie takiej tabeli na podstawie relacji charakterystycznych dla typowych baz danych.

Do tego artykułu dołączony jest plik źródłowy, tabela-przestawna.xlsx, który możesz pobrać i wykorzystać do powtórzenia czynności opisanych w artykule. Plik to skoroszyt Excela, zawierający 3 arkusze. Pierwszy z nich, to Zamówienia:

Na tym arkuszu znajdują się informacje dotyczące każdego zamówienia w 2014 roku w pewnym umownym sklepie meblowym. Każde zamówienie opisane jest swoim unikalnym numerem, a następnie datą, kiedy zostało złożone, produktem, którego dotyczyło zamówienie, a także sposobem, w jaki zostało dokonane – poprzez wizytę w sklepie stacjonarnym lub przez stronę internetową. Kolejny arkusz, Klienci obejmuje oczywiście listę klientów:

Każdy klient opisany jest imieniem, nazwiskiem oraz nazwą miasta, gdzie mieszka, a także numerem zamówienia, którego dokonał w sklepie. Numer zamówienia jest naszym kluczem. To znaczy, na jego podstawie możemy sprawdzić, jakiego zamówienia dokonał dany klient, po prostu porównując numer zamówienia z tego arkusza Klient z numerem zamówienia z arkuszu Zamówienia.

Natomiast w trzecim, ostatnim arkuszu Płatności mamy dodatkowe informacje o każdej transkacji, a konkretnie kwota, sposób płatności i wreszcie status – czy zamówienie zostało już zrealizowane, czy jest w trakcie realizacji.

Tutaj także mamy kolumnę Nr zamówienia. Dzięki temu numerowi, możemy łatwo połączyć informacje o płatności z zamówieniem, którego dotyczy na arkuszu Zamówienia, oraz klientem, który złożył to zamówienia, na zakładce Klienci.

Ta kolumna Nr zamówienia jest częścią wspólną dla wszystkich trzech arkuszy, a więc to na niej oprzemy naszą tabelę przestawną. Dzięki takiej tabeli przestawnej, nie będziemy musieli przełączać się między tymi akruszami za każdym razem, gdy będziemy chcieli uzyskać inne informacje o danym zamówieniu (określonym swoim unikalnym Nr zamówienia). Tak więc Nr zamówienia będzie dla nas czymś w rodzaju łącznika między arkuszami, na zasadzie podobnej do tego jak działa klucz główny (primary key) w relacyjnych bazach danych.

Posiadanie takiego unikalnego klucza głównego w swoich danych na różnych arkuszach nie jest warunkiem koniecznym do stworzenia łączącej ich tabeli przestawnej, ale znacząco zmniejsza ryzyko popełnienia błędu.

Przed stworzeniem tabel przestawnych, utworzmy tabele dla danych na każdym z trzech arkuszów. Zaczynamy od arkusza Zamówienia – mając ten arkusz aktywny zaznaczamy jakąkolwiek komórkę w obrębie danych, np. C11:

Następnie klikamy na zakładce Wstawianie, odszukujemy ikonkę Tabela i klikamy na niej:

Wyświetli się okno dialogowe Tworzenie tabeli, której obszar powinien automatycznie objąć wszystkie dane na tym arkuszu. Innymi słowy, dane w arkuszu powinny być otoczone przerywaną linią, tzw. Maszerującymi mrówkami. Upewniamy się, że w oknie dialogwym mamy zaznaczoną opcję „Moja tabela ma nagłówki”.

Klikamy OK. Nasze dane powinny przybrać format tabeli z przyciskami do filtrowania oraz naprzemiennie oznaczonymi rzędami niebiskim kolorem.

Teraz wchodzimy na kartę Projektowanie na wstążce. Ta karta jest widoczna tylko wtedy, gdy mamy zaznaczoną jakąś komórkę w obrębie naszej tabeli, więc upewniamy się, że tak właśnie jest. W przeciwnym razie karty Projektowanie nie będziemy widzieć na wstążce. Z lewej strony tej karty znajduje się pole Nazwa tabeli – zmieniamy nazwę z domyślnej Tabela3 na Dane_zamowien.

Teraz powtarzamy ten krok w każdym z naszych arkuszów. Nazwy utworzonych tabel ustawiamy na odpowiednio: Dane_klientow oraz Dane_platnosci. Po ukończeniu tej czynności możemy nareszcie utworzyć tabelę przestawną.

Wchodzimy na arkusz Płatności i zaznaczamy dowolną komórkę w tabeli. Następnie na zakładce Wstawianie klikamy ikonkę Tabela przestawna. Powinniśmy ujrzeć okno dialogowe tworzenia tabeli przestawnej. Obszar danych do tabeli powinien być zaznaczony automatycznie i prawidłowo, miejsce docelowe wyznaczamy jako nowy arkusz, natomiast kluczowym elementem jest zaznaczenie pola przy opcji „Dodaj te dane do modelu danych”:

Po kliknięciu OK zostaniemy przeniesieni na utworzony arkusz1 z tabelą przestawną. Z lewej strony jest obszar, w którym pojawi się tabela, a po prawej stronie znajduje się belka, która daje nam możliwość wybory pól odo tabeli przestawnej. Jeśli miałeś wcześniej styczność z tabelami przestawnymi, jest to standardowy widok po utworzeniu nowej tabeli przestawnej w nowym arkuszu. Dodatkowo, aktywna zakładka na wstążce to Analiza. Klikamy na nim na przycisk Relacje.

W wyświetlonym w ten sposób oknie dialogowym utworzymy relacje do naszej tabeli przestawnej. Są one nam potrzebne, aby połączyć ze sobą dane z 3 róznych arkuszy w logiczne zestawienie. Klikamy na przycisk Nowy. W kolejnym oknie tworzymy pierwszą relację – będzie to relacja arkusza z zamówieniami z arkuszem klientów, a łącznikiem, czyli kluczem głównym będzie tabela Nr zamówienia. W polu Tabela wybieramy Dane_zamowien, w polu Kolumna (obiekt obcy) wybieramy Nr zamówienia, w polu Pokrewna tabela wybieramy Dane_klientów, a w polu Pokrewna kolumna (obiekt podstawowy) wybieramy również Nr zamówienia – jak na poniższym obrazie:

W ten sposób wiersze Excel skojarzy ze sobą wiersze z obu tych arkuszy, posiadające ten sam numer zamówienia. Powtórzy tę samą czynność, tym razem łącząc arkusze Zamówień z Płatnościami – również za pomocą kolumny Nr zamówienia. Po wykonaniu tych akcji nasze relacje powinny prezentowac się następująco:

Klikamy Zamknij. Tabela przestawna jest już gotowa. Najwyższa pora ją do czegoś wykorzystać. Wyznaczmy więc pola, które chcemy wyświetlić. Jest to moment, w którym możesz poeksperymentować i zobaczyć, jakie ciekawe informacje możesz uzyskać na podstawie tych 3 arkuszów. Ja proponuję zestaw pól poniżej. Najpierw jednak kliknij na WSZYSTKIE, aby wyświetlić dane ze wszystkich 3 arkuszów:

Następnie kliknij na te małe trójkąciki przy każdym z arkuszów, aby otrzymać listę pól z każdego z nich:

Teraz przesuń pola do obszarów w ten przykładowy sposób:

• Miasto do obszaru WIERSZE
• Produkt do obszaru KOLUMNY
• Kwota do obszaru WARTOŚCI
• Kanał do obszaru FILTRY

Powinieneś uzyskać taką tabelę:

Zachęcam do eksperymentowania z innym układem danych w tabeli – jak w każdej innej tabeli przestawnej.