50 przykładów obliczeń na tabelach przestawnych

Tabele przestawne są proste. Od tego warto zacząć. Wystarczy zrozumieć konstrukcję danych, które leżą u ich podstaw (co nie jest trudne) oraz opanować kilkanaście operacji na nich (bo w gruncie rzeczy właśnie tyle wykonuje się na nich powtarzanych na różne sposoby działań) i tyle. Analiza danych robi się niemal samodzielnie. Przeciągnij i upuść pole z listy danych, a następnie zmień ustawienia obliczeń – to cała filozofia tabel przestawnych.

Dosyć szczegółowo tłumaczę je w moim nowym ebooku pod tytułem „Excel. Nauka na przykładach„. Znajdują się tam instrukcje, które krok po kroku omawiają poszczególne aspekty tworzenia tabel przestawnych. Dodatkowo zawarłem w tym Excel Ebooku sporo przykładów praktycznych – tj. zadań z rozwiązaniami. To doskonały sposób na nauczenie się tabel przestawnych. Zachęcam do odwiedzenia strony ebooka i poznania całego spektrum poruszanych w nim zagadnień. Excela warto się uczyć.

Poniższy wpis stanowi ogólny wstęp do zagadnienia tabel przestawnych w Excelu. Opisana jest tu ich idea oraz główne pojęcia, które należy poznać, aby lepiej zrozumieć sens stosowania tabel przestawnych. Do całego artykułu dodaję bezpłatny plik EXCEL z 50 przykładami obliczeń za pomocą tabel przestawnych. Jest on demonstracją możliwości tego narzędzia. Za pomocą tabel przestawnych można wyczarować naprawdę wiele ;)

Wstęp do tabel przestawnych – wymiary i miary

Korzystałem już przynajmniej z kilkunastu narzędzi do przetwarzania i analizy danych. Moja opinia w zakresie tabel przestawnych od lat jest niezmienna – kiedy ma się już przygotowaną tabelę z danymi, która posiada liczne wymiary i miary, wtedy bezdyskusyjnie najlepiej się je analizuje właśnie przy wykorzystaniu tabel przestawnych. Mówię oczywiście o tabelach danych nie większych niż powiedzmy 1 mln rekordów. Ale nawet jak pracuję na większych zbiorach danych, liczonych w dziesiątkach milionów, to często analiza przebiega w ten sposób, że w narzędziu SQL-owym (np. SAS, MS SQL Server) przetwarzam i agreguję dane, a następnie eksportuje je do Excela. A tam już przy pomocy tabel przestawnych tworzę tabele i wykresy pod analizę danych.

Czym są wspomniane wymiary i miary? Najlepiej je zrozumieć na przykładzie. Wyobraźmy sobie firmę, która sprzedaje produkty. Gdybyśmy chcieli opisać wyniki sprzedaży tej firmy to moglibyśmy posługiwać się następującymi wymiarami:

• Kategoria produktu (np. elektronika, meble, art. biurowe, itd.)
• Nazwa produktu (np. telewizor Omega4x, krzesło drewniane Jurand, niebieski ołówek StqZ)
• Województwo sklepu, w którym został sprzedany (np. mazowieckie, małopolskie)
• Miasto sklepu, w którym został sprzedany (np. Warszawa, Kraków).
• I tak dalej…

Łatwo zauważyć, że możemy wyróżnić wymiary nadrzędne i wymiary wobec nich podrzędne (Kategoria produktu > Nazwa produktu, Województwo sklepu > Miasto sklepu). Wymiary służą do grupowania danych. Dzięki nim możemy różnicować badane zjawisko ze względu na kategorie danych – tj. przynależność do danego wymiaru.

Miary to natomiast liczności lub wartości danych wymiarów. Przykładową miarą może być: liczba sprzedaży produktu, przychód ze sprzedaży, cena jednostkowa produktu, liczba transakcji, itd.

Budowa zbioru danych pod tabelę przestawną to zatem przypisywanie kolejnym wymiarom odpowiadających im miar. Ilustracją tego procesu jest poniższy rysunek. Gdzie kolejno pokazano przykłady tabeli bardzo ogólnej opisującej sprzedaż wg województw, następnie tabelę z dokładnością do województw i kategorii produktów oraz trzecią tabelę powiększoną dodatkowo o nazwy produktów. Jak łatwo zauważyć wraz z powiększaniem się liczby wymiarów, miary (tj. wartości sprzedaży) stają się coraz mniejsze. Jednakże za każdym razem istnieje możliwość ich ponownej agregacji do wymiaru wyższego. Przygotowanie poprawnego zbioru danych to połowa sukcesu w każdej analizie. Polecam zatem lekturze wcześniejszy wpis na ten temat: Poradnik – 20 najprzydatniejsze Excel formuły do robienia i porządkowania tabel

Ustawienia i obliczenia na tabelach przestawnych

Gdy znamy już ogólną koncepcję budowy zbioru danych pod tabelę przestawną przejdźmy teraz do operacji wykonywanych na tabelach przestawnych. Nie będę opisywał sposobu tworzenie tabeli przestawnej, zainteresowanych odsyłam do Excel Ebooka po dokładne instrukcje. Skoncentrujmy się na obliczeniach oraz możliwościach samych tabel przestawnych. Na przykładowym zbiorze danych, dotyczącym sprzedaży produktów w pewnej fikcyjnej firmie, spróbujmy pokazać, jakie zestawienia i analizy można przygotować.

W załączonym pliku, który można pobrać klikając w poniższy link, znajduje się 50 różnych tabel przestawnych, które zostały zbudowane tylko dla jednego zbioru danych. Celem tego pliku jest pokazanie bogactwa możliwości tabel przestawnych i zadań, jakich za ich pomocą można wykonać. Jeżeli chcecie sprawdzić, w jaki sposób zostały zbudowane poszczególne tabele, wystarczy je zaznaczyć i zobaczyć jak wyglądają etykiety kolumn oraz wierszy oraz jak zostały ustawione poszczególne opcje. Aby pobrać plik w Firefox – kliknij prawy przycisk myszy i wybierz: „Zapisz element docelowy jako” (w innych przeglądarkach otwiera się bez problemu).

Kliknij, aby pobrać: 50 przykładów obliczeń na tabelach przestawnych

Znajdziecie tam objaśnienia do najczęściej wykorzystywanych operacji na tabelach przestawnych. Nie będę w tym wpisie opisywał ich wszystkich.

Poniżej przestawiam tylko wybrane zagadnienia (i przykładowe rysunki) przedstawiane bardziej szczegółowo w pliku.

• Ustawienia początkowe. Po utworzeniu tabeli przestawnej najpierw warto zadbać o jej ustawienia, np. można:
  – odznaczyć opcję automatycznego dostosowywania szerokości kolumn
  – ustawić sposób wyświetlania nagłówków kolumn
  – wybrać pomiędzy wyglądem nowoczesnym, a klasycznym tabeli przestawnej

• Usuwanie powtarzających się wartości oraz zliczanie wystąpień każdej z unikatowych wartości. Tabele przestawne działają w ten sposób, że po przeciągnięciu pola do ETYKIET WIERSZY lub ETYKIET KOLUMN wyświetlą się tylko unikatowe wartości występujące w danej kolumnie danych. Ustawienie POLA WARTOŚCI na LICZNIK skutkuje zliczeniem wystąpień danej wartości w kolumnie.

• Wykonywanie różnych obliczeń na jednym polu danych. Wiele osób zapomina o tym, że to samo pole możemy przeciągać do WARTOŚCI kilka razy i za każdym razem ustawiać inny typ obliczeń (SUMĘ, ŚREDNIĄ, UDZIAŁY PROCENTOWE.

• Powtarzanie wartości wymiarów w każdym wierszu. Domyślnym ustawieniem tabel przestawnych jest niepowtarzanie wartości wymiarów. Zwiększa to czytelność tabeli. Ale zdarzają się sytuacje, kiedy potrzebujemy tradycyjnej tabeli. Wtedy w USTAWIENIACH POLA (prawy przycisk myszy przy dowolnej wartości pola), w zakładce UKŁAD i DRUKOWANIE odhaczamy POWTARZAJ ETYKIETY ELEMENTÓW.
• Sortowanie i filtrowanie danych w tabeli przestawnej to jedne z najczęściej wykorzystywanych opcji. Oprócz standardowych opcji filtrowania warto zwrócić uwagę na „FILTR 10 PIERWSZYCH WARTOŚCI”, gdzie możemy dowolnie wybrać liczbę filtrowanych rekordów pod względem ilościowym i procentowym. Przykładowo możemy wyfiltrować listę 15 najważniejszych klientów pod względem wolumeny sprzedaży lub też ustawić filtr tak, aby pokazał listę największych klientów generujących 80% przychodów.

• Dodawanie kolumn oraz zmienianie zakresu tabeli przestawnej (zmiana źródła danych). Podczas analizy do pierwotnych danych trzeba często dodać kolejne kolumny. Są to zwykle dodatkowe znaczniki, obliczenia lub kategorie. Jednakże, aby nowe pole pojawiło się na liście w tabeli przestawnej należy zmienić zakres danych tabeli przestawnej a następnie kliknąć ODŚWIEŻ. W załączonym pliku przedstawione są 2 przykłady dodatkowych kolumn – jednak z miesiącem transakcji, druga ze znacznikiem kategoryzującym wysokość przychodów. Jednym ze sposobów na przyspieszenie zmiany zakresu danych jest przekształcenie arkusza danych w format tabelaryczny. Wtedy po dodaniu kolejnej kolumny lub rekordów, zakres danych zmienia się automatycznie (o ile w zakres danych tabeli przestawnej jest wpisana nazwa tabeli z danymi) i wystarczy tylko odświeżyć.

• Grupowanie danych. Tabele przestawne mają wbudowaną opcję grupowania danych. Zatem nie zawsze trzeba dodawać własną kolumnę z dodatkowymi wyliczeniami. Jednak działająca opcja grupowania w tabeli przestawnej ma pewne ograniczenia – grupowanie można dokonać tylko na danych liczbowych i datach, a dodatkowo odstęp liczbowy między kolejnymi grupami musi być stały.

Na zakończenie chciałbym powtórzyć, że nauczenie się tabel przestawnych jest proste. A możliwości ich wykorzystania przeogromne. Zachęcam zatem do nauki, bo to świetna inwestycja :)