Tabele przestawne w Excelu – jak zacząć i o co w tym chodzi?

Dziś pora na kolejny wpis dotyczący analizy danych w Excelu. Tym razem na warsztat weźmiemy tabele przestawne i zobaczymy, jak zacząć ich używać. Inna popularna nazwa to tzw. „piwoty” (od angielskiego pivot table).  Poznamy podstawowe założenia i dowiemy się, jak przyspieszają pracę z danymi. Jeśli nie czytaliście jeszcze poprzedniego wpisu na temat przydatnych funkcji w Excelu, jeszcze zanim przystąpimy do analizy, to polecam 🙂 Znajdziecie go pod tym linkiem. Zapraszam do lektury!

Od razu uprzedzę, że ten artykuł będzie trochę inny niż większość, które znajdziecie w sieci. Chciałbym wyjaśnić przede wszystkim ideę stojącą za tabelami przestawnymi i do czego nadają się najlepiej. Dla tych potrzeb opiszę pewne porównania do życia codziennego. Przejdziemy następnie przez podstawowe funkcje i zobaczymy prosty przykład, jak stworzyć tabelę przestawną. Nie będziemy się jednak zagłębiać dalej w szczegóły. Temat ten jest zbyt szeroki na pojedynczy artykuł 😉 Natomiast dziś głównym zadaniem jest zrozumienie do czego służą tabele przestawne, jak szybko zacząć je używać i jak bardzo ułatwiają analizę danych. No to zaczynamy!

Tabele przestawne  – o co w tym chodzi?

Kiedy widzimy pierwszy raz tabele przestawne często skupiamy się na kwestiach technicznych. Na tym, żeby je poprawnie zbudować, albo na tym, że coś możemy przestawiać. Ale tu jednak stop 🙂 Żeby efektywnie je używać, powinniśmy najpierw dobrze zrozumieć do czego one służą. Zastanawiając się, jak zacząć stosować tabele przestawne, należy najpierw zrozumieć ich kontekst. A do tego możemy posłuzyć się przykładami z dnia codziennego. Tak na prawdę nasz umysł na co dzień używa bardzo często podobnego mechanizmu. To wszystko dzieje się, aby lepiej zrozumieć pewne zjawiska. Robi to także, aby porównać ze sobą informacje, wyciągnąć szybciej wnioski itd.  Czyli praktycznie tak samo, jak my używamy tabel przestawnych w Excelu. Hmm…pewnie myślicie jak to? Otóż jedne z głównych zalet piwotów to agregowanie (grupowanie) danych oraz zastosowanie operacji matematycznych, jak sumowanie, obliczanie średniej i inne.

Tabele przestawne są wokół nas

Kiedy my tak robimy? Otóż dzieje się tak bardzo często. Powiedziałbym nawet, że świat stoi na tabelach przystawnych 😉

Weźmy pod uwagę na przykład szkołę. W pewnym rozumieniu jest to zbiór uczniów, obojętne czy to będzie 100 czy 500 czy więcej osób. Można też wziąć pod uwagę nauczycieli i inny personal, ale na tą chwilę skupmy się na dzieciach. W praktyce rzadko myślimy o tej grupie próbując wyobrazić sobie wszystkich jednocześnie. Często za to używamy grupowania. Szczególnie, kiedy chcemy coś wyjaśnić czy „usadowić” osoby czy zdarzenie. Najbardziej klasyczne to grupowanie po klasach jak A, B, C itd. Co więcej, używamy często dwóch poziomów agregacji. Mówimy przecież pierwsza A lub czwarta B, a więc patrzymy na rocznik i klasę. Mówimy, że ktoś chodzi do czwartej B albo, że ktoś przeniósł się do szóstej C itd. Natomiast raczej nie mówimy przy każdej okazji, że Marek chodzi do klasy z Basią, Asią, Jankiem, Helą….i tak wymienialibyśmy wszystkich w klasie. Grupowanie pozwala na znaczne uproszczenie przekazu.

Inny przykład? Proszę bardzo, weźmy choćby wszystkich mieszkańców naszego kraju. Podczas wszelakich analiz używamy różnych grupowań. Może być po województwie, powiecie, regionie, miasto czy wieś itp. Może to być też wykształcenie, pochodzenie społeczne, jest cała masa sposobów, po których możemy to robić. Oczywiście nie zawsze to grupowanie jest tak precyzjne, jak powyżej dla szkoły, ale sam zabieg stosujemy bardzo często.

Wspomniałem również, że drugą charakterystyczną cechą są funkcje, a więc w tym przypadku operacje jak suma, liczenie itd. O co chodzi? Otóż wróćmy jeszcze na chwilę do szkoły i innego przykładu. Średnia ocen klasy czwartej B wynosi 4.5 i jest wyższa od średniej dla piątej B która wynosi 4.0. Bierzemy daną grupę (z uwagi na klasę i rocznik) i liczymy średnią. Dokładnie takie same operacje bardzo często robimy w przypadku tabel przestawnych. Ustalamy po czym grupujemy nasze dane (np. klasy w szkole) i co chcemy porównać (np. średnią ocen uczniów).

Tabele przestawne do czego używamy?

Mam nadzieje, że widać już przynajmniej próbkę tego, kiedy możemy efektywnie używać tabel przestawnych. Mając dowolny zbiór danych ułożony według struktury (kolumny, wiersze) możemy je dowolnie grupować i analizować. Może to być jeden lub wiele poziomów, jedna lub wiele funkcji. Możemy szybko policzyć rzeczy, jak suma, średnia, ilość, wartość maksymalna/minimalna dla wybranej kategorii lub podkategorii. Co niezwykle istotne, możemy szybko zmieniać kryteria grupowania lub dodować/usuwać informacje. Niejako „przestawiamy” elementy w tabeli.

Inna bardzo ważna cecha tabel przestawnych to fakt, powstaje niejako obok oryginalnej tabeli, a więc żadne nasze działania jej nie naruszają. Możemy tworzyć czy usuwać lub modyfikować wiele tabel przestawnych na bazie tego samego zbioru danych i patrzeć na te same informacje wybierając różne grupowania czy funkcje. Wszystko po to, żeby „wycisnać” maksymalnie dużo wniosków z naszego raportu.

Oczywiście, tych możliwości konfiguracji i scenariuszy jest trochę więcej, ale bardzo ważne, aby poznać założenia i cel tworzenia piwotów. Innymi słowy, zastanawiając się, jak zacząć budować tabele przestawne, warto najpierw przemyśleć, czego chcemy się dowiedzieć.

Przykład na podstawie sprzedaży

Czas na konkretny przykład, gdzie zobaczymy, jak zacząć budować tabele przestawne. Zrzuty ekranowe są w języku angielskim, ale staram się też podawać polskie odpowiedniki. Poniżej też zamieszczam do pobrania plik z ćwiczeniem 🙂 Dla przykładu weźmy próbkę raportu sprzedaży. Zawiera on jedynie 20 pozycji, ale gdybyśmy mieli szybko odpowiedzieć np. jaki Region czy Kategoria produktu pojawia się najczęściej, to licząc „na piechotę” musielibyśmy spędzić chwilę czasu. Nie mówiąc o tym, że zazwczaj kolumn i jak wierszy może być znacznie więcej. Zróbmy więc podstawowy piwot i pogrupujmy dane po Regionie oraz oddzielnie po Kageogrii.

Zawsze rozpoczynamy tak samo. Klikamy na jakąkolwiek komórkę oryginalnej tabeli, a następnie w górnym menu klikamy na Wstaw i Tabela Przestawna (w wersji angielskiej to Insert i Pivot Table):

Następnie Excel domyślnie pyta czy bierzemy pod uwagę całą tabelę (table/range) oraz czy umieścimy ją w nowej zakładce (new worksheet). Można to oczywiście zmienić, ale w tym przypadku zgadzamy się i dajemy OK.

Excel automatycznie tworzy nową zakładkę i przenosi nas tam. Tak na prawdę teraz zaczynamy właściwą pracę z tabelami przestawnymi. Po lewej stronie widzimy obszar, gdzie pojawi się nasz pivot, a po prawej rodzaj mini tabeli, gdzie dokonujemy podstawowej konfiguracji.

Według jakiego wzorca budujemy tabele?

Tu także jest często pierwsza i istotna przeszkoda. Następnym krokiem jest bowiem umieszczenie odpowiednich kolumn we wspomnianej mini tabeli po prawej stronie ekranu. Można przeciągnąć myszką wybraną kolumnę do jednego z czterech pól umieszczonych poniżej. Te cztery obszary to Filtry (Filters), Kolumny (Columns), Wiersze (Rows), Wartości (Values). Tu jednak warto wrócić na chwilę do naszego wprowadzenia i sformułować proste zdanie, co chcemy zobaczyć. W naszym przypadku będzie to ILE transakcji przypada na REGION. Mamy zatem dwie informacje do przekazania Excelowi. Chcemy kolumnę REGION i chcemy policzyć, ILE transakcji tam przypada.

Aby to zrobić wrzucamy w obszar Wiersze (Rows) kolumnę REGION, a w obszar Wartości (Values) kolumnę PRODUKT (strzałki poniżej). W przypadku tego drugiego możemy użyć także innej kolumny, bo tak czy inaczej chcemy policzyć wszystkie linie, a w naszej tabeli jeden wiersz to pojedyncza transakcja.

Excel zazwyczaj na podstawie danych w kolumnie sam próbuje dopasować, jaką podstawową funkcję wybrać – czy suma (sum) czy zlicz (count). W naszym przypadku potrzebowaliśmy policzyć transakcje (count), a więc mamy dokładnie to czego chcieliśmy. W razie potrzeby klikamy na strzałę obok i możemy zmienić funkcję. Musimy jednak pamietać, że w niektórych przypadkach będzie to np. tylko policz, jeśli używamy kolumny z tekstem jak poniżej. Excel co prawda pozwoli nam na zamianę funkcji, ale w tabeli pojawią się wówczas błedy. Funkcje matematyczne typu suma czy średnia stosujemy zatem tylko do kolumn z liczbami.

Popatrzymy na nasz wynik. Wykonaliśmy praktycznie kilka kliknięć i mamy zestawienie mówiące, ile transakcji przypada na poszczególny region. Ja dodatkowo zastosowałem sortowanie (tak samo jak w standardowej tabeli) i mamy wartości od największej do najmniejszej.

Transformuj się, czyli jedna z największych zalet tabel przestawnych

Poza grupowaniem po regionie chcemy zobaczyć także, jak sytuacja przestawiała się dla poszczególnych kategorii. Chcemy zatem policzyć ILE transakcji przypada na KATEGORIĘ. Musimy postąpić analogicznie, jak poprzednio. Tu jednak dobra wiadomość, bo nie potrzebujemy tworzyć tabeli od początku 😀 Wystarczy wymienić kolumnę, której użyliśmy za pierwszym razem, a więc zamieniamy REGION na KATEGORIA.

Usuwamy zatem kolumnę REGION. Tu mamy klika sposobów, na przykład chwytamy lewym przyciskiem myszy pozycję REGION, przeciągamy element poza okno i puszczamy lewy przycisk myszy. Możemy również nacisnąć na strzałkę obok a następnie wybrać opcję „usuń pole” (remove field w wersji angielskiej).

Następnie, tak jak w przypadku kolumny REGION, dodajemy teraz kolumnę KATEGORIA w obszar Wiersze (Rows). I znów sukces 🙂 W szybki sposób mamy informacje pogrupowane według kategorii produktu, a nie według regionu.

Grupowanie – dodawanie poziomów

Pamiętacie, jak mówiliśmy o klasach w szkole? Często używamy w praktyce podwójnego grupowania, aby określić gdzie ktoś się uczy. Mówimy zazwyczaj chodzę do szóstej B, niż po prostu do szóstej klasy albo do B. Chyba że któraś tych informacji jest oczywista dla drugiej osoby. Tak czy inaczej, tabele przestawne bardzo prosto pozwalają na dodawanie dodatkowych poziomów agregacji. Aby to zrobić, po prostu dodajemy kolejne pole do obszaru Wiersze (Rows) i ustawiamy hierarchię. W tej drugiej chodzi po prostu ustalenie kolejności grupowania. W naszym przykładzie może to być Region i przypadające dla każdego z nich Kategorie produktów. Ale może być też odwrotnie, a więc na pierwszym miejscu chcemy zobaczyć Kategorie produktu, a później informację na temat obszaru (Region), gdzie został sprzedany. Łatwiej będzie to zrozumieć na przykładzie.

Po lewej widzimy strukturę tabeli przestawnej (konfiguracja), natomiast po prawej jak zachowują się nasze dane. W tym przypadku dodaliśmy po prostu kolumny REGION i KATEGORIA w obszarze wiersze. Kolor żółty wskazuje poniżej region jako informację, od której chcemy zacząć analizę, a więc jest wyżej w hierarchi. Innymi słowy interesuje nas przede wszystkim region, a później jakie mieszczą się tam kategorie.

W przypadku, jeśli używamy więcej niż jeden poziom grupowania, Excel automatycznie dodaje sumy częściowe. Przykładowo widzimy, że na region Wschód przypada 9 transakcji, z czego 6 na art. Papiernicze, 1 to napoje , a 2 to zabawki. Jeśli chcemy można to wyłączyć – przykładowo w menu górnym idziemy do Projekt (Design), Sumy częściowe (Subtotals) i wybieramy Nie pokazuj Sum Częściowych (Do Not Show Subtotals).

A co się stanie, jak zmienimy kolejność tj. najpierw KATEGORIA, później REGION? Dane będą wyglądać, jak poniżej. Wystarczy tylko w obszarze Wiersze (Rows) kliknąć lewym przyciskiem myszy na wybraną pozycję i przeciągnąć w górę lub w dół w zależności od potrzeb. Kolejność grupowania zależy od waszych potrzeb, ale zmiany można zrobić bardzo szybko.

Dodawanie filtrów i zmiana funkcji

Jeśli potrzebujemy coś wykluczyć, możemy użyć filtrowania. Po prostu przeciągamy kolumnę z wartością, którą chcemy użyć. W naszym przypadku będzie to DATA TRANSAKCJI. Automatycznie powyżej tabeli przestawnej pojawi się pole z filtrem, gdzie możemy ustawić co wykluczamy. Jeśli wybierzemy tylko niektóre pozycje, jak na przykład poniżej data „5/7/2020” , to wówczas dane zostaną przefiltrowane według tego kryterium.

Inną rzeczą bardzo przydatną jest szybka zmiana funkcji. Zatem zamiast np. widzieć liczbę transakcji chcemy zobaczyć sumę. Musimy jednak pamiętać, aby wcześniej wybrać odpowiednią kolumnę z liczbą. Jeśli mamy np. kolumnę z nazwą (tekst), jak region czy kategoria, to w takim przypadku zadziała tylko ich zliczanie (count). W poniższym przykładzie dodałem zatem kolumnę KWOTA, która jest liczbą. Domyślnie Excel pokazuje sumę, co oczywiście może byc wystarczające.

Jeśli chcemy jednak zmienić funkcję to należy kliknąć na strzałkę obok nazwy. Następnie otwiera się okno, gdzie wybieramy Ustawienia (Value Field Settings). Otwiera się kolejne okno, gdzie możemy wybrać inna funkcję np. średnią (average) zamiast sumy. Klikakamy na OK i potwierdzamy.

Po powyższych zmianach tabela nadal pokazuje grupowanie po Regionie, ale w kolumnie B widzimy śrędnią kwotę transkacji (average) na podstawie kolumny Kwota. Ja jeszcze dodatkowo zaokrągliłem wartości, aby wynik wyglądal lepiej. Prawda, że piękne? 🙂

To tylko początek

I to będzie tyle na dziś, choć to tak na prawdę tylko początek piwotów 😉 Tabele przestawne maja znacznie więcej ustawień i możliwych konfiguracji. Jak wspominałem wcześniej, temat wykracza (zdecydowanie) poza pojedynczy wpis, więc tu dziś się zatrzymamy. Mam nadzieję, że to wprowadzenie i prosty przykład sprawią, że bardziej pewnie wkroczycie w ten temat. Zobaczyliśmy na czym polegają tabele przestawne i w ogóle jak zacząć je stosować. Jeśli planujesie analizować wasze dane czy raporty w Excelu to właśnie wykonaliście ważny krok do przodu 🙂

I na koniec jeszcze bonus i niespodzianka. Jeśli wolicie uzupełnienie tego tekstu w wersji w wideo to zapraszam na You tube 🙂 Link poniżej!

Dajcie znać w komentarzach czy chcecie więcej!

Pozdrawiam,

Michał