Tabele przestawne

Tabele przestawne w programie MS Excel to jedno z najcenniejszych narzędzi oferowanych przez ten program. Niestety użytkownicy Excela rzadko je wykorzystują uważając je za narzędzie zbyt trudne i skomplikowane.

Przykłady będziemy wykonywać na podstawie danych przedstawionych na poniższym rysunku. 

Równocześnie zaznaczamy, że w poniższym tekście przedstawiamy niewielką liczbę opcji jakie możemy zastosować przy pomocy tabel przestawnych. Więcej informacji o ich zastosowaniu znajdą Państwo na Kursie Excel Tabele Przestawne.

Rys. 1. Przykładowe dane

Tabele przestawne: jak stworzyć tabelę przestawną w MS Excel

Tabele przestawne tworzy się wybierając kartę Wstawianie, grupę Tabele opcję Tabela przestawna. Po wykonaniu tych czynności pojawia się okno Tworzenie tabeli przestawnej tak jak widać to na rysunku nr 2. Należy w nim wskazać zakres danych, które chcemy mieć w tabeli oraz miejsce jej utworzenia.

Rys. 2. Tworzenie tabeli przestawnej

Zaakceptowanie wyboru spowoduje utworzenie pustej tabeli przestawnej (rys. 3).

Rys. 3. Pusta tabela przestawna

Ułożenie pól w obszarach jest uzależnione od tego jakich informacji potrzebuje użytkownik, dlatego nie ma standardów tworzenia tabel przestawnych.

Tabele przestawne: case study nr 1 – czyli jak obliczyć sumę sprzedaży poszczególnych szkoleń

Celem pierwszego zadania jest sprawdzenie, jakie były przychody ze realizowanych szkoleń w firmie X. Oby uzyskać odpowiedź na to pytanie możemy wykorzystać tabele przestawne w MS Excel.W tym celu do wartości przeciągamy pole cena netto.  Efektem tego działania będzie obliczenie sumy przychodów netto dla wszystkich szkoleń – efekt ten widoczny jest na rysunku nr 4.

Rys. 4. Podsumowanie przychodu wszystkich szkoleń

Po przeniesieniu pola Nazwa szkolenia do etykiet wierszy otrzymamy żądany stan (suma przychodów poszczególnych szkoleń - rys. 5).

Rys. 5. Suma przychodów dla poszczególnych szkoleń

Nic nie stoi na przeszkodzie, aby umieścić pole o nazwie Nazwa szkolenia do etykiety kolumn, dzięki czemu uzyskuje się inny wygląd tabeli przestawnej (rys. 6).

Rys. 6. Suma przychodów dla poszczególnych szkoleń w etykiecie kolumn

Tabele przestawne: case study nr 2 – czyli jak obliczyć sumę sprzedaży z podziałem na dwie kategorie

W tym akapicie przedstawimy jak obliczyć sumę sprzedaży z podziałem na szkolenia oraz z podziałem na daty, a także suma sprzedaży z podziałem na daty oraz szkolenia. Wbrew pozorom jest ogromna różnica pomiędzy tymi ustawieniami, ponieważ uzyskuje się inny rodzaj informacji. W przypadku, gdy mamy bierzemy dane z dwóch pól, i je umieszczamy do etykiety wierszy lub kolumn, to liczba kombinacji możliwych do utworzenia wynosi cztery:

• I kombinacja: pole1 oraz pole2 umieszczamy w etykiecie wierszy

• II kombinacja: pole1 oraz pole2 znajduje się w etykiecie kolumn

• III kombinacja: pole1 będzie w etykiecie wierszy, natomiast pole2 – w etykiecie kolumn

• IV kombinacja: następuje zamiana trzeciej kombinacji pole1 wędruje do etykiety kolumn a pole2 – do etykiety wierszy

Rys. 7 przedstawia wygląd pierwszej kombinacji (pole1 to Data, pole2 to Nazwa szkolenia).

Rys. 7. Suma sprzedaży z podziałem na pole Data oraz Nazwa szkolenia

Na powyższym rysunku widać, że daty są przedstawione w postaci dni, jednak dane lepiej prezentowałyby się z podziałem na miesiące i lata. Aby zgrupować dane należy kliknąć na komórkę prawym przyciskiem myszy (PPM) z datą (nie jest istotne, która to będzie data) .

Rys. 8. Lokalizacja opcji Grupuj

Pojawi się okno Grupowanie, gdzie należy zaznaczyć odpowiedni przedział czasowy (domyślnie wszystkie daty będą wyświetlane) oraz okres względem którego chcemy grupować dane. W tym przykładzie wybrano Miesiące oraz Lata (rys. 9).

Rys. 9. Grupowanie data względem okresów

Grupowanie pozwoliło na zwiększenie czytelności danych (rys. 10).

Rys. 10. Wygląd danych po grupowaniu

W tym przypadku otrzymujemy kilka informacji:

• suma sprzedaży z podziałem na lata

• suma sprzedaży w poszczególnych miesiącach z uwzględnieniem lat

• wartość sprzedaży w poszczególnych szkoleń biorąc pod uwagę miesiące oraz lata

Gdybyśmy odwrócili kolejność pól, to analiza takich danych dałaby inne informacje:

• suma sprzedaży z podziałem na nazwę szkolenia

• suma sprzedaży dla szkoleń z podziałem na lata

• suma sprzedaży dla szkoleń rozbijając na lata i miesiące

Rys. 11. Podział danych na pole Nazwa szkolenia oraz Data (dodatkowo pogrupowanie po miesiącach i latach)

Tabele przestawne: wskaźniki statystyczne

Jeśli w etykiecie wierszy znajdzie się pole Data (tak aby były sumowane lata i miesiące), a w wartości – pole Cena netto, to otrzymamy podsumowanie sprzedaży w podziale na lata i miesiące (rys. 12).

Rys. 12. Suma przychodów z podziałem na lata i miesiące

Na powyższym rysunku przedstawione zostały wskaźnik statystyczny dotyczący sumy przychodów w podziale na lata oraz miesiące, jednak nic nie stoi na przeszkodzie aby obliczyć inne wskaźniki. W tym celu klikamy PPM na kolumnę Suma z Cena netto, a następnie wybieramy opcję Ustawienia pola wartości (rys. 13).

Rys. 13. Opcja umożliwiająca zmianę statystyk

Dostępne statystyki w oknie Ustawienia pola wartości to np. licznik, średnia, minimum oraz maksimum (rys. 14). 

Rys. 14. Statystyki

Po wybraniu odpowiedniej statystyki zmienia się Nazwa niestandardowa (którą oczywiście można edytować) i zaakceptowaniu zmienią się wartości liczbowe (rys. 15).

Rys. 15. Średnia wartość przychodów w podziale na lata i miesiące

Tabele przestawne: filtrowanie wartości

Czasami chcemy dowiedzieć się, jaka jest wartość konkretnej kategorii bez oglądania pozostałych kategorii. Aby o zrobić, wystarczy dołączyć odpowiednie pole do obszaru filtru raportu. W tym przypadku przeciągamy pole Data do etykiet wierszy i ustawiamy ją na lata i miesiące. Do filtru raportu należy przenieść pole Nazwa szkolenia, natomiast do wartości przesunąć pole o nazwie Cena netto. Wygląd przedstawiono na rys. 16.

Rys. 16. Dodanie filtru raportu umożliwia wyświetlenie rekordów spełniających kryteria

Dzięki dodaniu pola Nazwa szkolenia do filtru raportu mamy możliwość wyświetlania sumy przychodów dla konkretnej lub określonych kategorii. Skorzystanie z filtru polega na kliknięciu trójkąta znajdującego się obok pola, wybraniu odpowiednich wartości (rys. 17) i kliknięciu OK.

Rys. 17. Filtrowanie danych

Identyczny efekt można uzyskać za pomocą narzędzia Fragmentator. Aby z niego skorzystać, należy przygotować odpowiednio dane w tabeli przestawnej. W tym celu kasujemy pole z filtru raportu, a następnie przechodzimy do karty Opcje i z grupy Sortowanie i filtrowanie wybramy Wstaw fragmentator (rys. 18).

Rys. 18. Umiejscowienie narzędzia Fragmentator

Pojawi się okno Wstawianie fragmentatorów (rys. 19).

Rys. 19. Okno Wstawianie fragmentatorów

W tym oknie należy wybrać Nazwa szkolenia i kliknąć OK. Dzięki temu pojawi się charakterystyczne okno w którym można wybierać nazwy szkoleń. W przypadku gdy chcemy zaznaczyć szkolenia nie sąsiadujące sobą, to należy wykorzystać przycisk Ctrl (rys. 20).

Rys. 20. Excel wyświetla statystyki dla zaznaczonych elementów

Tabele przestawne: jak obliczyć podatek VAT w MS Excel za pomocą tabel przestawnych 

Podatek VAT = Cena brutto – Cena netto.

Oczywiście to równanie można przełożyć na przychód (podatek VAT = Przychód brutto – Przychód netto). Dzięki zastosowaniu pola obliczeniowego można tworzyć różne obliczenia. Jeśli z karty Opcje (musimy być aktywni na tabeli przestawnej)  z grupy Obliczenia wybierzemy opcję pola, elementy i zestawy to będziemy mieć dostęp do pola obliczeniowego (rys. 21).

Rys. 21. Pole obliczeniowe

W oknie Wstawianie pola obliczeniowego można zmienić nazwę pola oraz wskazać Excelowi w jaki sposób je obliczyć (rys. 22).

Rys. 22. Dodawanie pola obliczeniowego

Po zaakceptowaniu mamy obliczony podatek VAT zgodnie z podanym wzorem (rys. 22).

Rys. 23. Wygląd tabeli po dodaniu nowego pola obliczeniowego

Tabele przestawne: wykorzystywanie

Tabele przestawne to zaawansowane obiekty w Excelu, dzięki którym m.in. możemy przeprowadzić analizę statystyczną posiadanych danych. Największą ich zaletą jest ich szybkość tworzenia w porównaniu do innych narzędzi istniejących dostępnych w arkuszy MS Excel np. formuł.

Autorem artykułu jest Grzegorz Plak, trener Cognity.

Aby dalej rozwijać swoje umiejętności w zakresie tworzenia i wykorzystywania narzędzia tabel przestawnych w Excelu zapraszamy na:

• Kurs Excel Tabele Przestawne
• Kurs Wizualizacja i Prezentacja Danych z MS Excel i MS PowerPoint
• Kurs PowerPivot w MS Excel: wielowymiarowa analiza i raportowanie z PowerPivot
• Kurs Excel Zaawansowany

Polecamy również artykuły, w których zapoznasz się z pozostałymi trikami dostępnymi w programie Excel:

• Usuwanie duplikatów, czyli jak oczyszczać dane w arkuszu MS Excel
• Filtrowanie danych w Excelu
• Prezentacja danych - niezbędnik
• Wyszukaj.pionowo krok po kroku
• Jak stworzyć tabelę przestawną

MS Excel - tabele przestawne by Cognity is licensed under a Creative Commons Uznanie autorstwa-Użycie niekomercyjne 4.0 Międzynarodowe License.