Tabela przestawna w Excelu to narzędzie do szybkiego podsumowywania i analizowania dużych zbiorów danych bez pisania skomplikowanych formuł. Pozwala zmieniać układ raportu przez przeciąganie pól, a dane źródłowe pozostają nienaruszone.
Czym jest tabela przestawna w Excelu?
Tabela przestawna tworzy podsumowanie na podstawie listy danych, na przykład rejestru sprzedaży, kosztów, zamówień lub pracowników. Excel może automatycznie obliczyć sumę, średnią albo liczbę rekordów, a następnie pokazać wynik według wybranych kategorii.
Nazwa wynika z możliwości „przestawiania” informacji. To samo źródło danych możesz przedstawić według produktów, regionów, miesięcy albo dowolnego połączenia tych kategorii. Zmiana układu wymaga zwykle przeciągnięcia pola w inne miejsce, a nie przebudowywania formuł.
Tabela przestawna czy formuły?
Funkcje takie jak SUMA.JEŻELI, SUMA.WARUNKÓW czy LICZ.JEŻELI sprawdzają się przy pojedynczych, z góry określonych wynikach. Tabela przestawna jest wygodniejsza, gdy analizujesz wiele rekordów i często zmieniasz sposób prezentacji danych.
| Kryterium | Formuły, na przykład SUMA.JEŻELI | Tabela przestawna |
|---|---|---|
| Tworzenie raportu | Wymaga napisania i skopiowania formuł | Polega głównie na przeciąganiu pól |
| Zmiana układu | Często wymaga edycji zakresów i kryteriów | Można ją wykonać przez przeniesienie pól |
| Analiza wielu kategorii | Może wymagać wielu osobnych formuł | Łatwo dodasz kolejne wiersze, kolumny i filtry |
| Obsługa nowych danych | Zależy od użytych zakresów i konstrukcji formuł | Wymaga odświeżenia raportu, a czasem także rozszerzenia źródła |
Nie oznacza to, że tabela przestawna zawsze zastępuje formuły. Gdy potrzebujesz jednego wyniku w konkretnym miejscu arkusza, formuła może być prostsza. Przy raportach wymagających wielu przekrojów tabela przestawna zwykle oszczędza czas.
Jak przygotować dane do tabeli przestawnej?
Poprawne dane źródłowe są warunkiem prawidłowego działania raportu. Najlepiej, gdy mają postać prostej listy, w której jeden wiersz opisuje jeden rekord, a każda kolumna zawiera jeden rodzaj informacji. Przed utworzeniem tabeli sprawdź następujące elementy:
- Nagłówki znajdują się w jednym, pierwszym wierszu i każda kolumna ma własną, niepustą nazwę.
- W zakresie danych nie ma scalonych komórek.
- Nie występują puste wiersze ani puste kolumny rozdzielające rekordy.
- W każdej kolumnie zachowano spójny typ danych, na przykład daty są datami, a wartości liczbowe liczbami.
- Nie ma dodatkowych wierszy tytułowych nad właściwymi nagłówkami.
Dobrym rozwiązaniem jest zamiana zakresu na tabelę Excela. Dzięki temu nowe wiersze łatwiej obejmiesz źródłem tabeli przestawnej. Przy bardzo nieuporządkowanych danych pomocny może być Power Query, ale do pierwszego raportu wystarczy zwykle ręczne uporządkowanie listy.
Ostrzeżenie: scalone komórki, puste wiersze i mieszanie tekstu z liczbami mogą spowodować błędne grupowanie albo nieprawidłowe podsumowania. Tabela przestawna nie naprawia automatycznie źle przygotowanego źródła.
Jak utworzyć tabelę przestawną?
Podstawowy proces w Excelu obejmuje trzy etapy:
- Zaznacz dowolną komórkę w uporządkowanym zakresie danych. Jeśli Excel nie rozpozna całego zakresu, zaznacz go ręcznie.
- Wybierz Wstawianie > Tabela przestawna. W oknie dialogowym sprawdź zakres danych i zdecyduj, czy raport ma trafić do nowego, czy do istniejącego arkusza.
- Wybierz miejsce raportu i kliknij OK. Następnie przeciągaj nazwy kolumn z panelu pól tabeli przestawnej do wybranych obszarów.
Najprostszy przykład to raport sprzedaży według kategorii. Przenieś pole „Kategoria” do obszaru Wiersze, a pole „Sprzedaż” do obszaru Wartości. Excel utworzy zestawienie sumy sprzedaży dla każdej kategorii, bez potrzeby wpisywania funkcji SUMA.JEŻELI.
Do czego służą cztery obszary?
Panel pól tabeli przestawnej zawiera cztery obszary. To właśnie ich układ decyduje o wyglądzie raportu:
- Filtry – ograniczają raport do wybranych danych, na przykład jednego regionu, sprzedawcy lub roku.
- Kolumny – tworzą dodatkowy podział poziomy, na przykład miesiące pokazane obok siebie.
- Wiersze – określają główne kategorie widoczne po lewej stronie raportu, na przykład produkty lub regiony.
- Wartości – wskazują, co Excel ma obliczyć, na przykład sumę sprzedaży, średnią cenę albo liczbę zamówień.
Jedno pole może pełnić różne funkcje. Pole „Region” umieszczone w obszarze Filtry pozwoli wybrać jeden region, w Wierszach pokaże regiony jeden pod drugim, a w Kolumnach utworzy zestawienie krzyżowe. Do jednego obszaru możesz dodać więcej niż jedno pole, aby uzyskać kolejne poziomy szczegółowości.
Excel domyślnie stosuje sumę dla danych liczbowych. Aby zmienić sposób obliczeń, kliknij pole w obszarze Wartości i wybierz Ustawienia pola wartości. Możesz tam wybrać między innymi średnią, minimum, maksimum lub liczbę elementów.
Jak odświeżyć tabelę przestawną?
Po dopisaniu lub zmianie danych źródłowych raport nie zawsze zmieni się automatycznie. Kliknij dowolną komórkę tabeli przestawnej, a następnie wybierz polecenie Odśwież. Możesz też użyć skrótu Alt+F5.
Jeżeli nowe wiersze znajdują się poza pierwotnym zakresem, samo odświeżenie może nie wystarczyć. Wtedy wybierz opcję zmiany źródła danych i wskaż większy zakres. Problem ten ogranicza się, gdy dane źródłowe są przechowywane jako tabela Excela, ponieważ jej zakres rozszerza się wraz z dopisywaniem kolejnych rekordów.
Tabela przestawna najlepiej sprawdza się wtedy, gdy dane mają formę uporządkowanej listy, a raport trzeba oglądać z kilku perspektyw. Wiersze i kolumny pokazują podział, Wartości wykonują obliczenia, a Filtry zawężają wynik. Zmiana układu wymaga kilku przeciągnięć, natomiast po modyfikacji źródła trzeba pamiętać o odświeżeniu.
