Tabele przestawne w Excelu

Do tworzenia raportów, prezentacji czy analizowania poszczególnych danych Excel jest idealnym programem. Jeśli dodatkowo poznamy tak przydatne opcje, jak np. tabele przestawne, to nawet analizowanie czy grupowanie większej ilości danych, nie zajmie nam zbyt wiele czasu. Jak działa tabela przestawna w Excelu i do czego służy?

Co to jest tabela przestawna?

Tabela przestawna (pivot table) to narzędzie ułatwiające przestawianie i porządkowanie danych w Excelu. Przy zestawieniu wyników np. ze sprzedaży z pięciu lat, wyszczególnienie informacji za pomocą manualnego kopiowania i przestawiania, mogłoby być dość uciążliwym i czasochłonnym zadaniem. Dzięki tabeli przestawnej w Excelu te informacje uzyskamy już w kilka minut.

Tabele przestawne są przydatnym narzędziem przy tworzeniu raportów czy podsumowań. Przydają się jednak nie tylko w pracy zawodowej. W domu tabele przestawne ułatwią np. analizowanie budżetu.

Do czego wykorzystuje się tabele przestawne w Excelu?

Bardzo dobra znajomość Excela znacząco ułatwia wykonywanie różnorodnych zadań biurowych, analitycznych i finansowych. Jednym z najpotężniejszych narzędzi w Excelu są tabele przestawne, które pozwalają w prosty sposób analizować i organizować dane.

Dzięki tabelom przestawnym można między innymi:

• Przestawiać dane – łatwo zmieniać układ informacji, przenosząc je z wierszy do kolumn lub odwrotnie.
• Zliczać i wyświetlać konkretne wartości – np. sumy, średnie, liczbę wystąpień określonych elementów w zbiorze danych.
• Wyszukiwać interesujące dane – szybko odnaleźć potrzebne informacje w dużych tabelach.
• Sortować dane – uporządkować informacje według określonego klucza, np. alfabetycznie, według wartości liczbowych lub dat.
• Porządkować i porównywać dane – łatwo zestawiać dane z różnych źródeł, aby znaleźć zależności i różnice.
• Analizować mniejsze wycinki danych – czyli przechodzić od ogółu do szczegółu, co ułatwia podejmowanie decyzji na podstawie wybranych fragmentów danych.

Dzięki temu narzędziu można zaoszczędzić czas, uniknąć błędów i skutecznie wizualizować oraz interpretować informacje, które w tradycyjnych tabelach byłyby trudne do analizy.

Jak zrobić tabelę przestawną Excel?

Zanim utworzymy tabelę przestawną w Excelu, musimy dysponować danymi, które w kolejnym kroku chcemy przegrupować, przeanalizować czy wyszczególnić. W tym celu trzeba zebrać dane, tak, by nie zostawiać między nimi żadnych wolnych pól i kolumn.

Przykładowo wypożyczalnia samochodów, która wynajmuje samochody na terenie kilku miast, prowadzi wykaz wynajmu wszystkich samochodów w poszczególnych miesiącach. Poniżej znajduje się nasza tabela:

Następnie tabelę należy zaznaczyć, a z górnego menu wybrać:

• wstawianie, tabela przestawna (pierwsza pozycja).

W okienku, gdzie ustawiamy tabelę przestawną możemy wybrać, po wybraniu odpowiedniego zakresu, czy:

• tabela ma być ustawiona w nowym, czy już w istniejącym arkuszu.

Jeśli ma być w istniejącym, konieczne jest zaznaczenie, od którego pola Excel będzie tworzył tabelę, np. A15.

Gdy ustawiliśmy ten sam arkusz, powinniśmy uzyskać taki obraz:

Ważne! Każda kolumna musi mieć na górze swój tytuł – miesiąc, rok, sprzedaż itd.  Inaczej z boku z menu nie będziemy mogli wykonać żadnych operacji, by np. z kolumny wybrać tylko te interesujące nas wiersze.

Co można uzyskać dzięki tabeli przestawnej?

Tabela przestawna pozwala szybko analizować i podsumowywać dane. Dzięki niej można np. sprawdzić, w którym mieście w miesiącach wakacyjnych firma wynajęła najwięcej samochodów, zliczać całkowitą sprzedaż w poszczególnych miesiącach, porównywać wyniki między miastami czy grupami produktów oraz filtrować interesujące nas fragmenty danych. To narzędzie pozwala w prosty sposób przekształcić duże zestawy danych w czytelne raporty i wnioski biznesowe.

Zaznaczamy w bocznym menu: przeciągamy miesiące i miasta do odpowiednich kategorii (wiersze i wartości). Interesuje nas miesiąc: czerwiec, lipiec i sierpień. W tym celu w tabeli przestawnej rozwijamy pasek: etykiety wierszy. Tam możemy wybrać interesujące nas miesiące (resztę odznaczamy).

Jak widać na górnym obrazku, uzyskujemy w szybki sposób łączną sumę wynajmu samochodów we wszystkich miastach, ale tylko w miesiącach wakacyjnych.

W prawym dolnym rogu znajduje się okno z czterema polami:

• filtry,
• kolumny,
• wiersze,
• wartości.

By uzyskać inny układ tabeli, wystarczy przeciągnąć poszczególne nazwy między tymi polami.

Dzięki Filtrom możemy filtrować no podstawie pól nie wyświetlanych w tabeli lub rych z których tworzone są dane w Wartości.

Formatowanie kolumn w tabeli przestawnej w Excelu

Klikając dwa razy lewym przyciskiem myszy na nagłówek każdej kolumny (bądź na pole Wartości w oknie w prawym, dolnym rogu), możemy zmieniać jak wygląda nasza tabela przestawna.

Po kliknięciu ukaże nam się takie okno:

Można tutaj zmienić nazwę kolumny, format liczby oraz zdecydować jak podsumowywane są wartości. Po dokonaniu wystarczy kliknąć OK, żeby zapisać zmiany.

Średnia suma sprzedaży – magiczne polecenia tabel przestawnych Excel

To, co w tabeli przestawnej może zająć nam dosłownie kilka minut, a przy manualnym obliczaniu nawet i kilka godzin, to uzyskiwanie konkretnych obliczeń, np. jaka była średnia ze sprzedaży w danym miesiącu, albo jaka była sprzedaż najlepszego z produktów. I tutaj nie musimy wykonywać zbyt wielu poleceń, a jedynie odnaleźć funkcję: ustawienia pola wartości.

W tym celu klikamy na pole interesującej nas kolumny (w której chcemy uzyskać konkretne wartości, np. średnią ze sprzedaży), prawym przyciskiem myszy, a z podświetlonego menu wybieramy: ustawienia pola wartości. Z kolejnego z menu wybieramy: podsumuj pole wartości według i wybieramy interesujący nas końcowy zapis. Do wyboru mamy m.in.

• suma,
• licznik,
• iloczyn,
• maksimum.

Wystarczy zaznaczyć, to co nas interesuje i właściwie sformatować liczby.

Przykład bardzo prostej tabeli, która znajduję maksymalną liczba wypożyczeń w Katowicach znajduje się poniżej:

Odświeżanie tabeli przestawnych – Excel

Tabele przestawne możesz dowolnie modyfikować, a jeśli dodasz nowe dane do źródła, konieczne jest odświeżenie tabeli, aby zmiany zostały uwzględnione.

Odświeżanie tabeli możesz wykonać na dwa sposoby:

1. Kliknięcie prawym przyciskiem myszy
   • Kliknij lewym przyciskiem na dowolne pole w tabeli przestawnej, a następnie kliknij prawym przyciskiem myszy.
   • Z wyświetlonego menu wybierz polecenie Odśwież.
2. Odświeżanie z poziomu menu
   • Kliknij dowolne pole w tabeli przestawnej.
   • Na górnym pasku menu wybierz Opcje lub Analiza tabeli przestawnej, a następnie kliknij Odśwież.
   • Tę metodę warto stosować, gdy pracujesz na więcej niż jednej tabeli przestawnej i chcesz odświeżyć wszystkie jednocześnie.

Dwie różne tabele przestawne na jednym arkuszu

Na głównym pasku narzędzi znajdziemy też wiele przydatnych funkcji do formatowania tabeli. Tu warto poznać wszystkie możliwości, jakie zapewnia Excel, ponieważ stylów tabel, możliwości podkreślania konkretnych wierszy czy dołączania symboli, znajdziemy zarówno na menu start, jak i w opcjach formatowania tabeli.

Czasami zachodzi potrzeba analizy tych samych danych na różne sposoby, np. podsumowania wyników według miesięcy oraz tygodni. W takim przypadku konieczne jest utworzenie dwóch oddzielnych tabel przestawnych.

Ważne jest, aby wiedzieć, że w standardowym Excelu zmiana układu jednej tabeli przestawnej może wpływać na pozostałe, jeśli korzystają one z tego samego źródła danych. Aby uniknąć problemów, najlepiej użyć Kreatora tabeli przestawnej, który pozwala tworzyć nowe tabele niezależnie od już istniejących.

Jak dodać Kreatora tabeli przestawnej do Excela

Kreator nie jest domyślnie dostępny na wstążce Excela, więc trzeba go dodać ręcznie:

1. W górnym lewym rogu kliknij dostosowanie paska szybkiego dostępu (mała ikona strzałki w górę w dół, zaraz obok nazwy pliku). Jeśli nie ma u Ciebie tej strzałki, kliknij PPM (prawym przyciskiem myszy) na obszar narzędzi głównych i kliknij pokaż pasek narzędzi szybki dostęp.
2. Wybierz Więcej poleceń.
3. W oknie dialogowym, przy opcji „Wybierz polecenia z”, ustaw: Polecenia, których nie ma na wstążce.
4. Z listy znajdź Kreator tabeli przestawnej, zaznacz go i kliknij Dodaj, aby przenieść go na pasek szybkiego dostępu.
5. Zamknij okno – Kreator będzie teraz dostępny w pasku szybkiego dostępu.

Dopiero korzystając z Kreatora, można tworzyć kolejne tabele przestawne bez ryzyka, że zmiany w jednej tabeli wpłyną na układ innej.

Dodatkowe wskazówki

• Oddzielne źródło danych – Aby całkowicie uniezależnić tabele, można skopiować dane do nowego arkusza lub utworzyć nowy zakres danych.
• Nazwy tabel – Nadając tabelom przestawnym różne nazwy, łatwiej nimi zarządzać i odnosić się w formułach.
• Automatyczne odświeżanie – W ustawieniach tabeli można włączyć automatyczne odświeżanie przy zmianach w źródle danych.

Jak usunąć tabelę przestawną?

Jeśli dana tabela przestawna nie jest nam już potrzebna, to możemy ją usunąć. Jeśli tabelę stworzyliśmy w arkuszu, gdzie znajdują się również wszystkie dane źródłowe, które w późniejszym czasie chcemy jeszcze wykorzystać, to usuwamy tylko tabelę – zaznaczając ją i używając klawisza DELETE. Jeśli tabela stworzona została w osobnym arkuszu, to wystarczy usunąć cały arkusz.

W tabeli przestawnej możesz usuwać pojedyncze elementy, a nie całą tabelę. Masz do wyboru dwie metody:

1. Odznaczenie elementów z menu tabeli przestawnej
   • W menu rozwijanym tabeli przestawnej zaznacz lub odznacz interesujące Cię elementy.
   • Po odznaczeniu wybranych pozycji tabela automatycznie aktualizuje swój wygląd.
2. Usunięcie elementu za pomocą prawego przycisku myszy
   • Zaznacz interesujący element w tabeli.
   • Kliknij prawym przyciskiem myszy i wybierz Usuń pole.
   • Uwaga: jeśli zaznaczysz pojedynczy wiersz, Excel usuwa całą kolumnę, co jest widoczne w treści polecenia.

Jeśli potrzebujesz dowiedzieć się więcej na temat Excela, zapraszam Cię na korepetycje z informatyki, gdzie znajdziesz rzetelnie przekazywaną wiedzę i zdobędziesz nowe umiejętności. Skontaktuj się ze mną.

Częste problemy z tabelami przestawnymi

Jednym z najczęściej spotykanych problemów przy obsłudze tabel przestawnych jest generowanie funkcji weźdanetabeli przy używaniu funkcji na polach tabeli. Jak to wyłączyć dowiesz się w naszym artykule o wyłączaniu weźdanetabeli.

Przy tworzeniu tabeli przestawnej Excel często zacznie grupować dane po miesiącach i latach.

Żeby pozbyć się tego grupowania wystarczy kliknąć na miesiąc PPM i wybrać rozgrupuj.

Kolejnym problemem może być pomijanie dat bez danych dla danego filtru.

Na przykład tutaj nie ma lutego, żeby dodać pozostałe miesiące trzeba kliknąć PPM na kolumnę z datami i wybrać ustawienia pól. Następnie należy przejść do zakładki Układ i drukowanie i wybrać opcję Pokaż elementy bez danych i kliknąć OK.

Tabela będzie teraz wyglądała tak:

Jest to dość brzydkie i mało praktyczne. Na szczęście można temu łatwo zaradzić. Klikamy na dowolne pole PPM i wybieramy Opcje tabeli przestawnej. Następnie w sekcji układ i formatowanie, w polu dla pustych komórek pokaż: wpisujemy 0 (albo po prostu odznaczamy checkboxa obok).

Teraz nasza tabela jest gotowa do obliczeń i wygląda dużo lepiej.

Ważne: CKE już raz sprawdzało znajomość tego procesu dlatego warto go zapamiętać (zadanie statek 2020).

Tabele przestawne oferują ogromne możliwości jednak ich obsługa wymaga trochę nauki. W tym artykule zostały omówione tylko najbardziej przydatne zagadnienia. Najlepiej jest samemu zająć się zakładkami Analiza tabeli przestawnej oraz Projektowanie, żeby dogłębnie poznać możliwości tabel przestawnych. To samo tyczy się opcji pojawiających się w panelu bocznym oraz menu PPM.

Najczęściej zadawane pytania o tabele przestawne w excelu

Wpisy, które mogą Cię zainteresować: