Wiedza o tabelach przestawnych to obowiązek każdego, kto pracuje w Excelu. Tabela przestawna to jedno z najpopularniejszych elementów arkusza kalkulacyjnego Microsoftu.
Tabela przestawna — od czego zacząć?
Aby zbudować tabelę przestawną będą Ci potrzebne dane źródłowe. Najlepiej, gdy są one w układzie tabelarycznym — to znaczy, że w jednym wierszu znajduje się jedna pozycja, a kolejne kolumny opisują jej cechy.
Pamiętaj, aby Twoje dane źródłowe, oprócz wyżej wspomnianego układu, charakteryzowały się też takimi cechami:
- każda kolumna powinna mieć nagłówek — bez tego ciężko będzie Ci się pracowało w tabeli przestawnej,
- nagłówek musi zawierać się w jednej komórce, w pierwszym wierszu tabeli — tabela przestawna bierze pod uwagę nagłówki jako jeden wiersz, a więc umieść opis kolumny w jednej komórce,
- tabela nie powinna zawierać scalonych komórek — to znacznie utrudni Ci pracę z tabelą przestawną,
- ani pustych wierszy — tu czai się niebezpieczeństwo ucięcia części danych do analizy. Dla Excela pusty wiersz to informacja: tu kończy się tabela. Unikaj pustych wierszy na co dzień, nie tylko w przypadku pracy z tabelą przestawną,
- upewnij się, że daty są traktowane prawidłowo, nie zaś jako teksty — to znacznie ułatwi Ci analizę danych w oparciu o horyzont czasu,
- jedna kolumna powinna zawierać jeden typ danych — jeśli pomieszasz typy danych w kolumnie, będziesz mieć problem z grupowaniem, a i sama analiza może zwracać błędy (wartości tekstowe nie są sumowane, a część liczb może być uznana przez Excela jako teksty),
- pamiętaj, że kolory czy formatowanie warunkowe nie wpływa na dane pod kątem ich analizy tabelą przestawną, więc możesz ich używać.
Mając tak przygotowane dane, analiz staje się czystą formalnością. Jeśli nie masz danych o właściwym układzie, poświęć chwilę, aby je doprowadzić do stanu używalności. Możesz to robić ręcznie albo zautomatyzować proces przy użyciu Power Query.
Moja pierwsza Tabela Przestawna
Przed zbudowaniem tabeli przestawnej, warto sformatować dane na tabelę Excelową (CTRL+T). To spowoduje, że gdy wkleisz nowe dane źródłowe na miejsce starych, zostaną one przekazane bezstratnie do tabeli przestawnej.
Ustaw się na dowolnej komórce Twoich danych źródłowych i z karty Wstaw wybierz Tabela Przestawna.
Twoim oczom ukarze się taki obraz:
I co teraz?
W panelu tabeli przestawnej, po prawej, od góry znajdziesz kolejno nagłówki kolumn danych źródłowych (dlatego tak ważnym było, aby każda kolumna je miała i żeby mieściły się w jednej komórce).
Każda kolumna dla tabeli przestawnej ma jedno z 3 znaczeń:
1) tekstowa
2) liczbowa
3) datowa (daty w Excelu traktowane są jako liczby, w tym przypadku formatowanie na daty i właściwa zawartość kolumny determinuje właściwe rozumienie tego typu danych przez tabelę przestawną)
Poniżej listy kolumn zauważysz cztery obszary, do której zaraz będziemy przeciągać poszczególne nagłówki kolumn (tzw. Pola tabeli przestawnej):
- Filtr
- Kolumny
- Wiersze
- Wartości
Wszystkie z nich w panelu mają ten sam rozmiar. Pozwala nam to dobrze wyobrazić sobie, jak tabela przestawna będzie wyglądać po tym, gdy wypełnimy ją nagłówkami kolumn. Stworzyłem więc rysunek, który w mojej ocenie pozwali Ci lepiej zrozumieć układ:
Jak analizować dane przy użyciu tabeli przestawnej?
Pozostaje przeciągnąć odpowiednią kolumnę w odpowiednie miejsce.
Przeciągnięcie wartości do obszaru Filtra, Wierszy albo Kolumn spowoduje zwrócenie ich unikatowej listy. Jak widzisz, można łatwo w ten sposób usuwać duplikaty. Przeciągnij interesującą Cię kolumnę do Wierszy, a uzyskasz efekt gotowej listy unikatowej. Gdy pojawią się nowe dane, wystarczy odświeżyć tabelę przestawną. Dzięki temu nie musisz za każdym razem krozystać z opcji usuwania duplikatów.
Wrzucenie danych do wierszy pozwala patrzeć na wartości w podziale na kryteria. Założmy, że chcemy wykonać analizę sprzedaży pod kątem poszczególnych katergorii produktowych.
Kroki:
1) kolumna z kategoriami ląduje do wierszy,
2) kolumna z wartością sprzedaży ląduje w wartośiach
i zestawienie gotowe w mniej niż 30 sekund.
To oczywiście wierzchołek możliwości Tabeli Przestawnej.
Tutaj możesz poznać wszystkie jej możliwości.
Co jeśli wrzucimy dane i do wierszy, i do kolumn tabeli przestawnej?
Pozwoli nam to wykonać analizę pod względem więcej niż jednego kryterium. Dołóżmy do sprzedaży i kategorii – regiony. Dzięki temu wiemy jak zachowywałyby się nasze kategorie w poszczególnych regionach. Moglibyśmy dorzucić do wierszy np. lata, co dałoby nam jeszcze głębszy poziom detalu.
A po co jest pole filtru w tabeli przestawnej?
To tam wrzucamy kolumnę z danymi, przy pomocy których chcemy ograniczyć naszą analizę w tabeli przestawnej. Przykład? Jeśli mamy dane z czterech krajów, a zajmujemy się tylko Polską, warto wrzucić kolumnę Kraj do filtra i ograniczyć rezultaty do tych, które nas interesują.
Na co zwracać uwagę w polu wartości tabeli przestawnej?
Jeśli do tego pola przeciągniemy kolumnę z liczbami, domyślnie zostaną one zsumowane.
Jeśli wrzucimy do niego kolumnę z tekstem, dane zostaną zliczone. Więcej typów kalkulacji w tabeli pod tym linkiem.
Klikając dwukrotnie na nagłówek w tabeli nad wartością, możemy zmienić rodzaj kalkulacji. Do wyboru jest zliczanie, sumowanie, średnia, maksimum, iloczyn.
Mało kto widzi w surowych danych trendy. Dużo prościej jest to zauważyć na wykresach. Tabela przestawna stanowi świetne źródło dla graficznej prezentacji danych. Dzięki wykresom przestawnym może filtrować dane na żywo bezpośrednio na wykresie! Może łączyć ze sobą tabele przestawne i wykresy przestawne, tworząc efektywne dashboardy.
Budowa takiego raportu nie zajmuje wcale wiele czasu, a proces jego aktualizacji jest bajecznie prosty. Wystarczy kliknąć „Odśwież wszystko”. Aby spiąć ze sobą tabele przestawne i wykresy, będzie Ci potrzebny fragmentator. To narzędzie znajdziesz na pasku Analizy Tabeli Przestawnej w Excelu 2010 lub nowszym. Nauczysz się budować takie zestawienia też tutaj.
Grupowanie danych w tabeli przestawnej
Ta opcja pozwoli Ci patrzeć na dane w grupach. Być może będą to przedziały cenowe do analizy Pareto, może grupy faktur w poszczególnych przedziałach kwotowych czy wartość sprzedaży zgrupowana na lata, kwartały i miesiące.
Daty grupują się automatycznie od Excela 2013. Pozostałe typy danych (albo i daty, jeśli masz starszą wersję Excela) możesz zgrupować w następujacy sposób:
1) przerzuć do wierszy kolumnę, po której chcesz dokonać grupowania,
2) kliknij na nią prawym przyciskim myszy,
3) wybierz grupowanie,
4) wskaż punkt startu, końca i interwał,
i gotowe. Usuwania możesz dokonać w takim sam sposób.
Tabela przestawna i najczęstsze błędy?
1. Zadbaj o najświeższe dane w tabeli przestawnej.
Tabela przestawna pobiera dane ze źródła w momencie jej powstania i przy każdym odświeżeniu. Działa więc na zasadzie robienia zdjęcia. Jeśli „zrobisz zdjęcie” przy utworzeniu tabeli przestawnej, a później zmienisz coś w danych źródłowych, nowe informacje nie zostaną automatycznie przekazane do naszego narzędzia.
Gdy zbudujesz tabelę przestawną w oparciu o zakres, a nie tablę w rozumieniu Excela (tę założoną CTRL+T) i nowe dane będą liczyć więcej wierszy, niż te stare, różnica w wierszach nie zostanie przekazana do tabeli.
Dlatego tak ważnym jest, aby najpierw na zakresie zbudować tabelę, a dopiero później tabelę przestawną.
Pamiętaj też, aby zawsze przed rozpoczęciem analizy upewnić się, że pracujesz na tabeli przestawnej, która była odświeżona.
2. Zadbaj o daty w tabeli przestawnej.
Jeśli Twoje daty traktowane są, jako tekst, zadbaj o to, żeby działały w sposób prawidłowy. Tekst w tabeli przestawnej nie ulega grupowaniu, przez co znacznie trudniej będzie Ci poddać analizie dane z konkretnego miesiąca czy roku.
3. Nie buduj jednej tabeli przestawnej pod drugą,
ani obok drugiej, chyba że masz pewność, że nie przybędzie danych. Gdy w tej powyżej, przy odświeżeniu pojawi się więcej pozycji, dostaniesz komunikat, że jedna tabela próbuje zastąpić dane w drugiej, co nie jest możliwe.
4. Pamiętaj o opcji pól obliczeniowych w tabeli przestawnej.
Możesz stworzyć kalkulacje wewnątrz tabeli przestawnej, które będą przeliczać się przy odświeżaniu danych. Nie raz spotkałem się z sytuacją, gdy ktoś zaraz za tabelą przestawną postawił sobie kolumnę z dodatkowymi formułami, które miały liczyć jakąś metrykę. Wielokrotnie widziałem też, jak ta formuła była przeciągnięta za wysoko, co powodowało obliczenia tylko dla części danych.
5. Wyłączenie funkcji WeźDaneTabeli dla tabeli przestawnej może nie być najlepszym pomysłem.
Ta formuła pozwala pobierać z tabeli przestawnej dane o określonych kryteriach bez względu na to, gdzie znajdują się po odświeżeniu. Gdy włączymy WeźDaneTabeli, formuły odnoszą się do konkretnej komórki. Jej zawartość przy odświeżeniu może ulec zmianie: gdy przed odświeżeniem w komórce A2 była sprzedaż kategorii Szafy, po odświeżeniu mogła w zestawieniu pojawić się nowa kategoria, której wartość utargu wylądowała w C5 powodując, że formuły, używające tego odwołania, nie liczą już prawidłowo.
6. Nieznajomość opcji wyłączenia auto-formatowania tabeli przestawnej.
Każde odświeżenie powoduje u Ciebie zmianę szerokości kolumn i regularnie to poprawiasz? Możesz to bardzo łatwo wyłączyć. Ustaw się na tabeli przestawnej, wybierz Opcje i w oknie, które wyskoczy, odznacz: „Automatycznie dopasuj szerokość kolumn podczas aktualizacji” i problem z głowy.
Gdzie przyda Ci się tabela przestawna?
Na każdym stanowisku i w każdym dziale, którego pracownicy dotykają Excela. Tabela przestawna jest koniecznością w działach finansów, planowania, sprzedaży, logistyki, marketingu jest nieocenioną pomocą dla pracowników działów HR i administracji. Jeśli wybierasz się na rozmowę o pracę albo korzystasz na co dzień z Excela i nie znasz dobrze tego narzędzia, koniecznie nad tym popracuj. Warto opanować tabele przestawne Excela od A do Z. Sprawdź kurs w tym temacie. Przeprowadzę Cię przez tabele przestawne od podstaw po zaawansowane techniki.
Do zobaczenia w kursach i na live!
Michał Kowalczyk
