Tabela przestawna w Excelu - jak ją zrobić krok po kroku

Tabelę przestawną w Excelu robisz w trzech ruchach: zaznaczasz dowolną komórkę w danych, wybierasz Wstawianie i Tabela przestawna, a potem przeciągasz nazwy kolumn do czterech obszarów po prawej stronie. Poniżej przechodzimy to krok po kroku na tabeli sprzedaży: filtry, dwa poziomy wierszy, układ krzyżowy, średnia zamiast sumy, grupowanie dat po kwartałach i odświeżanie po dopisaniu nowych wierszy. Przykładowy plik z danymi jest do pobrania.

W dalszej części poradnika:

1. Co to jest tabela przestawna w Excelu?
2. Jak zrobić tabelę przestawną w Excelu krok po kroku?
3. Obszar filtrowania tabeli przestawnej
4. Jak utworzyć tabelę przestawną z kilku pól danych?
5. Tabela przestawna krzyżowa dla lepszej analizy danych
6. Sumowanie kilku kolumn w tabeli przestawnej
7. Jak obliczyć średnią w tabeli przestawnej?
8. Daty w tabeli przestawnej w Excelu
9. Grupowanie w tabeli przestawnej na podstawie dat
10. Aktualizowanie nowych danych w tabeli przestawnej
11. Jak przygotować dane do tabeli przestawnej?
12. Tabele przestawne dla kilku źródeł danych (Power Pivot)
13. Podsumowanie
14. Przykładowa tabela z danymi do pobrania

Co to jest tabela przestawna w Excelu?

Tabela przestawna to narzędzie, które podsumowuje dane z wielu wierszy bez pisania ani jednej formuły. Wskazujesz Excelowi zakres danych i przeciągasz nazwy kolumn do czterech obszarów. Excel sam grupuje powtarzające się wartości i liczy dla nich sumy, średnie albo liczbę wystąpień.

Nazwa bierze się z tego, że ten sam zestaw danych możesz „przestawiać”: przenieść kategorie z wierszy do kolumn, zamienić sumę na średnią albo dołożyć drugi poziom podziału. Dane źródłowe się przy tym nie zmieniają, zmienia się tylko sposób ich pokazania.

Praktyczna różnica wobec formuł wygląda tak. Zestawienie sprzedaży w podziale na kategorie i regiony to w formułach kilkanaście SUMA.WARUNKÓW (SUMIFS), z których każdą trzeba napisać i zablokować odwołania. W tabeli przestawnej to dwa przeciągnięcia myszką, a po dopisaniu nowych danych wystarczy odświeżyć raport.

Tabela przestawna przydaje się wtedy, gdy dane są w formie listy: jeden wiersz to jedna transakcja, a każda kolumna ma nagłówek. Jeśli dane mają scalone komórki, nagłówki w dwóch wierszach albo puste wiersze w środku, najpierw trzeba je uporządkować. O tym jest sekcja o przygotowaniu danych w dalszej części poradnika.

Jeśli chcesz przejść tabele przestawne po kolei, od pierwszej tabeli do dashboardu z fragmentatorami, prowadzę kurs Excel Tabele Przestawne z ćwiczeniami do każdego działu i certyfikatem.

Jak zrobić tabelę przestawną w Excelu krok po kroku?

Tabela przestawna to narzędzie w Excelu, które umożliwia szybkie podsumowanie, analizę i porządkowanie dużych zbiorów danych. Pozwala w łatwy sposób grupować informacje, liczyć sumy, średnie, tworzyć zestawienia i raporty - wszystko bez potrzeby pisania skomplikowanych formuł.

Dla przykładu poniżej znajduje się tabela z danymi o sprzedaży produktów. Naszym zadaniem jest wykonanie zestawienia w którym pokażemy łączną wartość sprzedaży dla każdej kategorii. Zapewne wiesz jak to rozwiązać wystarczy, że zastosujemy funkcję SUMA.JEŻELI. Oczywiście w zależności od tego co będziemy chcieli obliczyć i stopnia skomplikowania kryteriów liczenia możemy stosować takie funkcje jak SUMA.WARUNKÓW, LICZ.JEŻELI itp. Ale możemy też zastosować tabele przestawne i nie martwić się o to jaką funkcję zastosować.

Aby utworzyć tabelę przestawną:

1. Zaznaczamy dowolną komórkę w zakresie danych
2. Wybieramy polecenie Wstawianie > Tabela przestawna
3. W oknie, które się pojawiło musimy potwierdzić zakres danych a następnie wybrać gdzie ma pojawić się tabela przestawna Nowy arkusz lub Istniejący arkusz (wtedy wybieramy komórkę w której ma pojawić się tabela przestawna. Ja wybiorę aby tabela przestawna pojawiła się w nowym arkuszu i zatwierdzam mój wybór przyciskiem OK.

Na ekranie pojawi nam się nowy arkusz z pustą tabelą przestawna a po prawej stronie zobaczymy nowe okno Pola tabeli przestawnej. Te dwa elementy są ze sobą powiązane - tabelę przestawną budujemy za pomocą przeciągnięcia odpowiednich Pól (kolumn danych) do jednego z 4 obszarów:

• Filtry
• Kolumny
• Wiersze
• Wartości

Wracając do naszego przykładu aby przedstawić sumę wartości sprzedaży dla każdej kategorii wystarczy że:

• Przeniesiemy Pole (Kategoria) do obszaru Wiersze
• Przeniesiemy Pole (Sprzedaż) do obszaru Wartości

W ten sposób Tabela przestawna automatycznie podsumowała nam wartości sprzedaży dla produktów

Obszar filtrowania tabeli przestawnej

Podsumowanie wyników sprzedaży możemy filtrować na podstawie innych pól dla przykładu aby sprawdzić wynik sprzedaży dla regionu północnego wystarczy, że:

1. przeciągniemy pole Region do obszaru Filtry.
2. a następnie wybierzemy z listy rozwijalnej pola filtrowania nad tabelą przestawną interesujący nas region lub grupę regionów.

Nowy kurs

Raport, który odświeżasz jednym kliknięciem zamiast budować go co miesiąc od nowa

✓Pola i elementy obliczeniowe - także pułapka ze średnią z procentów

✓Porównanie okresów: miesiąc do miesiąca i rok do roku

✓Fragmentatory, oś czasu i wykres przestawny z dynamicznym tytułem

✓40 lekcji, 9 zestawów zadań, gwarancja zwrotu 30 dni

Zobacz kurs

Gwarancja zwrotu 30 dni · wszystkie lekcje dostępne od razu

Jak utworzyć tabelę przestawną z kilku pól danych

Zamiast filtrowania możemy dodać drugi poziom kryteriów obliczeń tabeli przestawnej. Każdy obszar danych może zawierać więcej niż jedno pole. Teraz utworzymy drugi poziom sumowania i oprócz wartości dla Kategorii chcemy zobaczyć wyniki dla każdego regionu. W tym celu należy przenieść pole Region do obszaru Wiersze (pod polem Kategorii) dzięki temu otrzymamy dodatkowe sumowanie dla wszystkich kryteriów z kolumn Kategorie i Region jak poniżej.

Tabela przestawna krzyżowa dla lepszej analizy danych

Gdy chcemy przedstawić podsumowanie sprzedaży zarówno dla Kategorii i Regionu warto wykonać krzyżową tabelę przestawną. Wystarczy, że przeciągniemy pole Region do obszaru Kolumny, a pole Kategoria pozostawimy w obszarze Wiersze. Dzięki temu uzyskamy efekt tabeli krzyżowej, pozwala ona na lepszą analizę porównawczą. W celu lepszej czytelności wyników w przykładzie dodałem formatowanie warunkowe za pomocą pasków danych.

Sumowanie kilku kolumn w tabeli przestawnej

W tabeli przestawnej możemy prowadzić obliczenia dla kilku kolumn. Najpierw usuniemy pole Region z obszaru Wiersze a pole Kategoria przeniesiemy do obszaru Wiersze i będziemy chcieli obliczyć ilość i sumę sprzedaży produktów. W tym celu przenosimy do obszaru Wartości pole Ilość. Teraz widzimy, że w tabeli przestawnej mamy przedstawioną ilość i sumę sprzedaży w podziale na kategorie.

Jak obliczyć średnią w tabeli przestawnej?

Tabela przestawna standardowo liczy nam dla pola Wartość:

• sumę dla pól (kolumn) zawierających liczbę
• ilość jeśli pole (kolumna) jest tekstem.

Ale możemy zmienić domyślny sposób liczenia. Dla przykładu będę chciał przedstawić w tabeli przestawnej zamiast sumy wartości średnią wartość.

W tym celu klikam prawym przyciskiem myszy (ppm) na pole Suma Sprzedaży i wybieram Ustawienia pola wartości

Pojawi mi się okno z którego mogę wybrać sposób obliczeń dla pola wartości tutaj wybieram Średnia.

Po zmianie metody obliczeń tabela przestawna wygląda następująco:

Daty w tabeli przestawnej w Excelu

Daty są specjalnie traktowane przez tabelę przestawną. Daty możemy przedstawić jako:

• dni
• miesiące
• kwartały
• lata

Przejdźmy do przykładu w którym będziemy chcieli zobaczyć jak prezentowała się średnia sprzedaż w każdym miesiącu.

W polu Wartości pozostawiam Średnią Sprzedaż a do obszaru Wiersze przenoszę Pole Data Sprzedaży. Zauważymy że pojawią nam się dodatkowe pola Dni (Data Sprzedaży) oraz Miesiące (Data Sprzedaży). Są to pola utworzone na podstawie tabeli Data Sprzedaży i od teraz możemy używać ich niezależnie od innych pól daty.

A w samej tabeli przestawnej zobaczysz, że uzyskaliśmy średnią sprzedaż w podziale na miesiące.

Zauważ, że obok nazwy miesiąca znajduje się mały plusik którym możemy zwiększać lub zmniejszać szczegółowość wyników.

Grupowanie w tabeli przestawnej na podstawie dat

W poprzednim dziale udało nam się utworzyć tabelę przestawną pokazującą średnią wartość sprzedaży dla miesięcy. Teraz chciałbym zmienić szczegółowość i pokazać wyniki dla kwartałów. W tym celu musimy zmienić grupowanie Pola Data Sprzedaży.

1. Klikamy prawym przyciskiem myszy na datę
2. Wybieramy Grupuj…

3. W nowym oknie na liście wyboru zaznaczamy Kwartały oraz usuwamy zaznaczone automatycznie Dni i Miesiące. Zatwierdzam przyciskiem OK.

Po wykonaniu powyższych czynności zniknęły nam pola Dni (Data Sprzedaży) i Miesiące (Data Sprzedaży) za to pojawiło się nowe pole Kwartały (Data Sprzedaży).

A sama tabela przestawna zmieniła swój wygląd i otrzymaliśmy wynik dla Kwartału1.

Aktualizowanie nowych danych w tabeli przestawnej

W poprzednim przykładzie utworzyliśmy zestawienie pokazujące średnią sprzedaż dla kwartału1 z biegiem czasu do naszego zakresu danych dochodzą nowe pozycje, które będziemy chcieli podsumować w tabeli przestawnej, do zakresu danych dopisałem dwie pozycje, które pojawiły się w drugim kwartale roku.

Po dopisaniu nowych pozycji tabela przestawna nie zmieniła swojego wyglądu, dzieje się tak ponieważ zakres danych tabeli przestawnej nie obejmuje nowo wprowadzonych danych a tabela nie została odświeżona.

Każdorazowo po wprowadzeniu nowych danych lub modyfikacji danych tabelę przestawną należy odświeżyć za pomocą skrótu klawiaturowego Alt + F5 lub klikając prawym przyciskiem myszy na tabelę przestawną.

Jeśli nowo wprowadzone dane znajdują się w tabeli Excelowej to dane w tabeli przestawnej zostaną zaktualizowane.

A co w sytuacji gdy dane nie znajdują się w tabeli Excelowej?

W naszym przypadku nie utworzyliśmy takiej tabeli Excelowej a tabela przestawna bazuje na zakresie danych. W takiej sytuacji musimy ręcznie zaktualizować zakres danych:

1. Zaznaczamy dowolną komórkę w tabeli przestawnej
2. Wybieramy polecenie Zmień źródło danych znajdujące się na karcie Analiza tabeli przestawnej
3. Zmieniamy zakres danych tak aby obejmował nowe wiersze

Po zatwierdzeniu zakresu tabela przestawna zostanie zaktualizowana o nowe pozycje danych

Jak przygotować dane do tabeli przestawnej?

W tym artykule bazujemy na danych, które są przygotowane prawidłowo pod możliwość jej analizy w tabeli przestawnej. Nie zawsze (a przeważnie najczęściej) sprawa jest tak prosta jak w podanym powyżej przykładzie. Częstym problemem już na samym początku stosowania tabel przestawnych jest to, że dane nie mają formy tabelarycznej. Poniżej przedstawiam zasady przygotowania danych do tabeli przestawnej:

• Nazwanie nagłówków kolumn - nazwij nagłówki krótko i zwięźle. Nazwy kolumn mają opisywać dane zawarte w każdej z kolumn tabeli.
• Nagłówek musi znajdować się w jednym najwyższym wierszu - tabela przestawna nagłówki w drugim wierszu odczyta już jako dane co może zakłócić obliczenia.
• Jeden typ danych w każdej kolumnie - każda kolumna powinna zawierać tylko jeden typ danych określający parametr np. kolor, wielkość, lokalizację lub wartość np. suma sprzedaży czy ilość.  
• Określ format danych w kolumnach - zachowaj jeden format danych w kolumnie np. liczba, tekst, data.
• Brak scalonych komórek w tabeli - usuń scalenia, jeśli gdziekolwiek występują w zakresie danych.
• Usuń puste wiersze - często w zakresach danych stosuje się puste wiersze do oddzielenia danych, usuń je z zakresu danych.
• Dane przechowuj w tabelach Excela - po utworzeniu tabeli danych, będziesz mógł dodawać nowe dane, które automatycznie objęte zostaną zakresem tabeli przestawnej.

Przydatnym narzędziem Excela, które pozwala zorganizować duże i trudne zbiory danych do formy tabelarycznej jest edytor Power Query.

Tabele przestawne dla kilku źródeł danych (Power Pivot)

Dotychczas omawialiśmy sytuacje tworzenia tabeli przestawnej na podstawie jednego źródła danych. A co jeśli mamy kilka źródeł danych.  Oprócz aktualnej tabeli z pozycjami sprzedażowymi, mamy jeszcze specyfikację produktów jak poniżej.

Na podstawie tych dwóch tabel chcemy obliczyć wartość sprzedaży dla każdej marki produktów.

W tym przypadku musimy zastosować dodatek do Excela, Power Pivot.

1. W pierwszym kroku dodaj obie tabele do modelu danych. Zaznacz dowolną komórkę zakresu danych, przejdź na kartę Power Pivot i wybierz polecenie Dodaj do modelu danych. Jeśli dane nie są w tabelach Excela, Excel poprosi o ich utworzenie. Nazwij te tabele Produkty i Sprzedaż.

2. Następnie wybierz polecenie Zarządzaj na karcie Power Pivot.

3. W edytorze Power Pivot przejdź do polecenia Widok Diagramu na karcie Narzędzia główne i wykonaj relację pomiędzy tabelami na podstawie kolumny Relacje tworzysz poprzez przeciągnięcie nazwy kolumny z jednej tabeli do drugiej Produkt - Produkt.

4. Teraz wróć do Excela i utwórz nową tabelę przestawną z modelu danych. Wskaż lokalizację nowej tabeli przestawnej.

5. Twoim oczom powinna pokazać się pusta tabela przestawna a w oknie pól tabeli przestawnej zobaczysz dwie nazwy tabel, których wewnętrzne nagłówki możesz rozwijać. Wystarczy, że przeciągniesz pole Marka do obszaru wierszy a Sprzedaż do obszaru wartości i zadanie jest gotowe 😊

To jest prosty przykład zastosowania modelu danych. Więcej o analizie danych na podstawie modeli danych możesz dowiedzieć się, zapisując się na kurs Power Pivot i DAX.

Podsumowanie

Tabela przestawna sprowadza się do czterech obszarów i jednej zasady: dane muszą być listą z nagłówkami w pierwszym wierszu. Reszta to przeciąganie pól i zmiana sposobu liczenia.

• Wiersze i Kolumny decydują o podziale, Wartości o tym, co jest liczone, a Filtry zawężają cały raport.
• Kliknięcie prawym przyciskiem na pole wartości otwiera Ustawienia pola wartości, czyli zmianę sumy na średnią, liczbę albo udział procentowy.
• Daty grupujesz przez Grupuj na dni, miesiące, kwartały i lata.
• Po dopisaniu danych odśwież raport. Jeśli dane są w tabeli Excela, zakres rozszerza się sam. Jeśli nie, trzeba zmienić źródło danych ręcznie.

Najprostsze zabezpieczenie na przyszłość: zamień zakres danych na tabelę Excela skrótem Ctrl + T, zanim wstawisz tabelę przestawną. Wtedy nowe wiersze wchodzą do raportu same i zostaje tylko odświeżenie.

Nowy kurs

Siedem problemów z tabelami przestawnymi, które wracają u każdego

✓Dlaczego format liczb znika po odświeżeniu i jak go zatrzymać

✓Wspólna pamięć podręczna: dlaczego jedna tabela zmienia drugą

✓Grupowanie dat, liczb i grup własnych

✓WEŹDANETABELI, czyli odwołania do tabeli przestawnej w formułach

Zobacz kurs

Gwarancja zwrotu 30 dni · wszystkie lekcje dostępne od razu