GWARANTOWANE TERMINY
Jesteś tutaj: strona głównastrefa wiedzyTabele przestawne
MENU

Tabele przestawne


Kategoria: Kurs Excel, , Analiza danych
26Aug

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.

 

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.

 

tabele przestawne

Rys. 2. Tworzenie tabeli przestawnej

 

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

 

tab przestawna

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.

 

tab przestawne

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

 

tab przestawne

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

 

tab przestawne

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

 

tab przestawne

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

 

tab przestawne

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

 

tab przestawne

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

 

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

 

tab przestawne

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

 

tab przestawne

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

 

tab przestawne

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

 

tab przestawne

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

 

tab przestawne

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

 

tab przestawne

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.

 

tab przestawne

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.

 

tab przestawne

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

 

tab przestawna

Rys. 18. Umiejscowienie narzędzia Fragmentator

 

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

 

tab przestawne

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

 

tab przestawne

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

 

tab przestawne

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

 

tab przestawne

Rys. 22. Dodawanie pola obliczeniowego

 

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

 

tab przestawne

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:

 

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

 

 

 

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

Wybrani Klienci:

Ten serwis używa plików cookies. Brak zmiany ustawień przeglądarki oznacza zgodę na ich używanie.
Więcej informacji o plikach cookies znajdziesz tutaj
Kliknij ZGADZAM SIĘ, aby ta informacja nie wyświetlała się ponownie
Cognity.pl