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 np. które produkty cieszyły się największym zainteresowaniem w poszczególnym roku, 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 zdecydowanie ułatwi wykonywanie różnych zadań. Co uprościmy sobie, wykorzystując np. tabele przestawne?

• przestawienie danych z wierszy do kolumn bądź na odwrót z kolumn do wierszy,
• zliczanie i wyświetlanie konkretnych danych,
• wyszukiwanie poszczególnych danych,
• sortowanie danych, według interesującego nas klucza,
• porządkowanie i porównywanie danych,
• analizowanie mniejszego zakresu danych (tzw. przechodzenie od ogółu do szczegółu).

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.

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ć, 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.

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

Kliknij na obrazek, aby powiększyć:

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?

Np. w którym mieście w miesiącach wakacyjnych firma wynajęła najwięcej samochodów.

Jak to uzyskać?

Zaznaczamy w bocznym menu: poszczególne miesiące i miasta, z których chcemy uzyskać wynik wynajmu. 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).

Kliknij na obrazek, aby powiększyć:

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.

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ć format liczb.

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

Kliknij na obrazek, aby powiększyć:

Następnie klikamy na format liczby, by wyskoczyło nam kolejne okno:

Kliknij na obrazek, aby powiększyć:

Tam możemy wybrać interesujący nas format np.:

• daty,
• godziny,
• zapis procentowy,
• oznaczenie waluty,
• zapis ułamkowy itd.

Po wybraniu interesującego nas formatu klikamy OK. Po zamknięciu pierwszego okna, ponownie klikamy OK. Wtedy nastąpią 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ługi 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.

Odświeżanie tabeli przestawnych – Excel

Tabele przestawne możesz również dowolnie modyfikować. Jeśli potrzebujesz np. dodać nowe dane, to koniecznie należy całą tabelę odświeżyć.

Działanie możesz wykonać na dwa sposoby:

• kliknij na dowolne pole w tabeli przestawnej najpierw lewym, a potem prawym przyciskiem myszy. Wyświetli nam się okno z opcjami, z którego wybieramy polecenie: odśwież,
• kliknij na dowolne pole w tabeli przestawnej, a następnie na górnym pasku menu wybierz: opcje albo analiza tabeli przestawnej i tam znów kliknij polecenie: odśwież. Tą drugą opcję zastosuj, jeśli pracujesz na więcej niż jednej tabeli przestawnej i potrzebujesz odświeżyć wszystkie tabele, albo więcej niż jedną.

Wizualizacja tabeli przestawnej

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.

Dwie różne tabele na jednym arkuszu

W przypadku, gdy konieczne jest pogrupowanie tych samych danych według zupełnie różnych wytycznych, trzeba stworzyć dwie różne tabele. Na przykład wyniki końcowe chcemy uzyskać dla poszczególnych miesięcy i tygodni. Jeśli wprowadzenie zmian do drugiej i każdej kolejnej tabeli zmienia układ również pierwszej tabeli, należy skorzystać z kreatora tabeli przestawnej. Kreator nie jest dostępny w ikonach widocznych w głównym menu, dlatego najpierw konieczne jest dodanie go. Jak to zrobić?

Z menu w górnym lewym rogu wybieramy przycisk dostosowania paska szybkiego dostępu , następnie z menu wybieramy: więcej poleceń i w okienku – wybierz polecenia, wybieramy: polecenia, których nie ma na wstążce i w dolnym okienku znajdujemy – kreator tabeli przestawnej – klikamy i dodajemy do menu (kreator wyświetli się w menu na górze). I dopiero z kreatora tworzymy kolejną tabelę przestawną.

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.

Możesz też usunąć tylko poszczególne elementy z tabeli przestawnej. Tutaj też do wyboru masz dwie opcje:

• odznaczając poszczególne elementy z menu tabeli przestawnej. Po odznaczeniu konkretnego elementu, tabela automatycznie zmieni wygląd,
• możesz też tradycyjnie zaznaczyć interesujący Cię element, a po kliknięciu na niego prawym przyciskiem myszy z okienka wybrać – usuń pole. Tu trzeba mieć na uwadze, że jeśli klikniemy na pojedynczy wiersz, to Excel usuwa całą kolumnę (co zresztą widoczne jest w poleceniu).

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ą.

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