Tabela przestawna jest dla analityka oczywistym i intuicyjnym rozwiązaniem w przypadku chęci skondensowania dużej ilości danych. Umożliwia interaktywne prezentowanie tylko interesujących nas elementów bez konieczności uprzedniego filtrowania, ograniczenia i wycinania niepotrzebnych danych. Wykres przestawny jest graficznym odbiciem danych w tabeli przestawnej z zachowaniem interaktywności. Jednak czy jest jakiś sposób, żeby „wycisnąć” z obu tych narzędzi jeszcze więcej? Tak, opcja Pokaż wartości jako…

Co to jest tabela przestawna?

Jeżeli nie wiesz, czym są tabele przestawne i jaka siła w nich drzemie, zacznij z zapoznaniem się z naszą serią Tabela przestawna krok po kroku. Czytaj także o tabelach przestawnych w programie Excel 2013, które otworzyły nowy rozdział w korzystaniu z tabel przestawnych w połączeniu z PowerPivot.

Więcej o zaawansowanych funkcjach w tabelach i wykresach przestawnych przeczytamy w:

Przygotowanie danych źródłowych do tabeli przestawnej

Tabela i wykres przestawny zostanie przygotowana na postawie danych prowizyjnych jednego z czołowych polskich operatorów telefonii komórkowej i stacjonarnej. Dane zawierają poszczególne rekordy, które dotyczą pozyskania i utrzymania usług mobilnych oraz stacjonarnych (lata – od 2010 do 2014; kanał sprzedaży – business, online, salony sprzedaży, telesales; rodzaj zdarzenia – pozyskanie mobilne, utrzymanie mobilne, pozyskanie stacjonarne; długość umowy; wartość abonamentu; wartość opłaty aktywacyjnej oraz prowizja. Rekordów jest ok kilkadziesiąt tysięcy, więc przedstawienie danych w formie tabeli przestawnej wydaje się być najlepszym rozwiązaniem.

Źródłem danych jest baza Access, dane są wynikiem jednej z kwerend na tabeli prowizyjnej. Dane: długość umowy, abonament i opłata aktywacyjna, nie zostały przekonwertowane na liczby, dlatego zaznaczamy je, a następnie klikamy na żółty wykrzyknik i wybieramy polecenie: Konwertuj na liczbę. Konwertowanie potrwa jakiś czas, ponieważ dane, jak było wspomniane powyżej, zawierają kilkadziesiąt tysięcy rekordów.

Zaznaczamy przekonwertowane dane (upewnijmy się wcześniej, że każda z kolumn ma nazwę, w innym przypadku przygotowanie tabeli i wykresu będzie niemożliwe). Następnie w poleceniu Wstawianie wybieramy Wykres przestawny (opcja tabela i wykres).

Utwórzmy tabelę i wykres w nowym arkuszu.

W poprzednich artykułach zostało szczegółowo opisane jak stworzyć tabelę i wykres przestawny. Przypomnę tylko, że po wyborze polecenia wstawienia tabeli oraz wykresu przestawnego, pojawia się interaktywne okno, które podpowiada co powinniśmy następnie wykonać.

Po kliknięciu na pole z prawej stronie arkusza pojawia się pionowe okno, w którym są wyświetlane nazwy kolumn w danych źródłowych (dlatego tak ważne jest poprawne nazewnictwo danych) oraz elementy samej tabeli, czyli: filtr, kolumny, wiersze oraz wartości.

Do pola Kolumny przeciągamy pole Data, w polu Wiersze umieszczamy Kanał i Rodzaj zdarzenia, a jako Wartości będzie pokazywana Prowizja. Taki zabieg należy wykonać albo podczas tworzenia tabeli albo wykresu, drugi element jako sprężony z pierwszym, wyświetli te same dane.

Tabela przestawna i wykres będą prezentowały się jak poniżej.

Domyślnie prowizja została pokazana jako licznik, zmienimy wyświetlanie. Aby tego dokonać należy w Liście pól, a dokładnie w Wartościach kliknąć prawym przyciskiem na Liczbę z Prowizja i wybrać polecenie Ustawienia pola wartości.

Następnie jako typ obliczeń wskazujemy Sumę.

Możemy użyć separatora liczb oraz wskazać miejsca dziesiętne (w tym przypadku jest to 0). Wszystko to można wykonać poprzez polecenie Format liczby.

Po powyższych zmianach pivot i wykres prezentują się jak poniżej.

Kolejną modyfikacją będzie zmiana układu tabeli przestawnej na klasyczny (tj. taki, który wiersze pokaże obok siebie, a nie wg hierarchii). Żeby zmienić układ klikamy prawym przyciskiem na tabelę i wybieramy kartę Wyświetlanie, a następnie Układ klasyczny tabeli przestawnej.

Po dokonaniu powyżej zmiany Wiersze prezentują się w dwóch kolumnach zamiast jednej.

Wykres natomiast prezentuje się jak poniżej:

Pokaż wartości jako… w tabeli przestawnej i na wykresie przestawnym

Przechodzimy teraz do kluczowego dla tego artykułu działania: przejrzymy możliwości prezentowania danych liczbowych w pivocie oraz na wykresie. Danymi wyjściowymi jest suma prowizji dla poszczególnego kanału, rodzaju zdarzenia oraz roku. Często zdarza się, że wartości chcielibyśmy pokazać w odniesieniu do innych wartości i w takim wypadku z pomocą może przyjść polecenie Pokaż wartości jako. Polecenie można wywołać klikając prawym przyciskiem na wartość liczbową lub prawym przyciskiem na Wartość na Liście pól, następnie na Ustawienia pól wartości i zmieniając kartę na Pokazywanie wartości jako.

Wartości w tabeli przestawnej mogą być wyświetlane bez obliczeń (wartość domyślna) lub jako:

% sumy końcowej;
% sumy kolumny;
% sumy wiersza;
% z…;
% sumy wiersza nadrzędnego;
% sumy kolumny nadrzędnej;
% sumy elementu nadrzędnego…;
Różnica…;
% różnicy…;
Suma bieżąca w…;
% sumy bieżącej w…;
Porządkuj od najmniejszych do największych…;
Porządkuj od największych do najmniejszych…;
Indeks;

% sumy końcowej

Po wyborze tej opcji suma końcowa prezentowana jest jako 100%, a pozostałe wartości jako jej część.

Opcja ta może być niezwykle pomocna w przypadku chęci zaprezentowania jaki jest udział poszczególnego kanału, zdarzenia i roku w całej wypłaconej prowizji.

Po dokonaniu zmiany wyświetlania danych możemy zauważyć, że w roku 2014 został wypłacony największy % prowizji, a liderem były Salony sprzedaży.

Usuwamy sumę częściową…

…i możemy zobaczyć wartości bez podsum na kanałach.

Na chwilę usuńmy także sumę końcową.

Po powyższych modyfikacjach dane prezentują się jak poniżej:

Wykres prezentuje się jak poniżej. Wartość wszystkich słupków sumuje się do 100%.

Przywróćmy usunięte sumy i wybierzmy kanał Business. Wartości procentowe ulegają zmianie w taki sposób, aby wyświetlane dane nadal sumowały się do 100%.

Po ograniczeniu do jednego roku wartości nadal sumują się do 100%.

% sumy kolumny

Format ten sumuję wartości do 100% w kolumnach. W tym przypadku etykietami kolumn są poszczególne lata i wartości dla tych poszczególnych lat będę sumowały się do 100%.

Usuńmy sumę częściową dla kanałów.

Słupki na wykresie dla każdego roku sumują się do 100%.

Następnie odfiltrujmy kanał i wybieramy Salony sprzedaży – wartości w każdej kolumnie nadal sumują się do 100%.

Zawężamy pivota do usług utrzymaniowych – kolumna wciąż sumuję się do 100%.

% sumy wiersza

Kolejna możliwość prezentacji danych to % sumy wiersza.

Polecenie to umożliwia pokazywanie danych procentowych, które dla każdego wiersza sumują się do 100%. Idealnie sprawdza się kiedy interesują nas różnice w poszczególnych latach.

Ograniczmy dane do lat 2010-2011. Można zauważyć, że wiersze nadal sumują się do 100%.

W przypadku wykresu to rodzaj zdarzenia w poszczególnych latach sumują się do 100%.

% z …

Czwartą z kolei możliwości prezentowania danych jest % z. Umożliwia pokazywanie wartości procentowych wyliczonych na podstawie wybranych elementów.

Po wyborze tego polecenia pojawi się okno, w którym wybierzmy pole podstawowe oraz element podstawowy, na podstawie których wartości zostaną przeliczone.

Jako pole podstawowe mamy do wyboru Datę, Kanał oraz Rodzaj zdarzenia (kolumny oraz wiersze).

Po wskazaniu pola podstawowego element podstawowy dopasowuje się do wybranego pola:

dla Daty będą to lata 2010, 2011, 2012, 2013 i 2014;
dla Kanału będą to Business, Online, Salony sprzedaży i Telesales;
dla Rodzaju zdarzenia – pozyskanie mobilne, pozyskanie stacjonarne, utrzymanie mobilne i utrzymanie stacjonarne;

Jako pole podstawowe zostanie wskazana Data, a jako element podstawowy rok 2014.

Wartości dla roku 2014 wypełniają się jako 100%. Wartości dla pozostałych lat wyliczają się w odniesieniu do roku 2014.

Na wykresie można zauważyć, że wszystkie niebieskie słupki (rok 2014) mają wartość właśnie 100%.

Zmieńmy Pole podstawowe na Rodzaj zdarzenia, a Element podstawowy na Pozyskanie mobilne.

Po powyższej zmianie wszystkie wartości wyliczają się w odniesieniu do pozyskania mobilnego wyświetlanego jako 100%.

Słupki, które dotyczą pozyskania mobilnego, sięgają do linia siatki odpowiadającej 100%.

% sumy wiersza nadrzędnego

Dane możemy również prezentować jako % sumy wiersza nadrzędnego.

Uzyskany efekt jest podobny do tego otrzymanego poprzez wybranie polecenia % sumy kolumny, z tą różnicą, że składowe każdego wiersza nadrzędnego (Kanał) sumują się do 100% wewnątrz kategorii, a wiersze nadrzędne sumują się do 100% w sumie końcowej. Jako wiersz nadrzędny wskazana jest suma końcowa, a pozostałe wartości wyliczają się jako % w odniesieniu do tej sumy.

Słupki dla każdego z lat będą sumować się do 100%.

% sumy kolumny nadrzędnej

Kolejną możliwością prezentowania danych jest % sumy kolumny nadrzędnej.

Uzyskany efekt jest porównywalny z efektem otrzymanym po zastosowaniu polecenia % sumy wiersza, ponieważ nie mamy kolumn nadrzędnych.

Każdy rodzaj zdarzenia na przestrzeni lat będzie miał wartość 100%.

% sumy elementu nadrzędnego

Dane można zaprezentować jako % sumy elementu nadrzędnego.

Po wybraniu tego polecenia pojawia się okno, w którym możemy wskazać pole podstawowe, na podstawie którego będą przeliczać się dane. Istnieje możliwość wyboru: Daty, Kanału i Rodzaju zdarzenia.

Po wybraniu Kanału dane dla Business, Online, Salonów sprzedaży oraz Telesales oddzielnie sumują się do 100% – każdy kanał traktowany jest jako element nadrzędny.

Po wyborze Rodzaju zdarzenia to właśnie ten element będzie wskazany jako ten nadrzędny.

Jednak, aby takie wyświetlanie danych miało sens, zamienimy kolejnością Rodzaj zdarzenia z Kanałem, tak aby Rodzaj zdarzenia był z lewej strony. Po powyższej modyfikacji pozyskanie mobilne, pozyskanie stacjonarne, utrzymanie mobilne oraz utrzymanie stacjonarne będą traktowane w pivocie oddzielnie – każde z nich sumuje się do 100%.

Jest to idealne rozwiązanie w przypadku chęci zobaczenia udziału poszczególnych kanałów sprzedaży w wypłaconej prowizji za każdy z rodzajów zdarzenia – np. największy udział w wypłaconej prowizji za pozyskanie mobilne mają Salony sprzedaży.

Różnica

Dane mogą być prezentowane również jako różnica. Polecenie to umożliwia pokazanie danych liczbowych, które wyliczają się jako różnica w stosunku do wybranych elementów.

Po wybraniu powyższego polecenia pojawia się okno z możliwością wyboru pola oraz elementu podstawowego.

Jako pole podstawowe zostanie wskazany Kanał, natomiast jako element podstawowy: Salony sprzedaży.

Dane dla pozostałych kanałów prezentują się jako różnica w stosunku do Salonów sprzedaży. Rozwiązanie jest pomocne w przypadku chęci zaprezentowania Salonów sprzedaży jako kanału bazowego i kluczowego.

Tutaj należy zwrócić uwagę na użycie odpowiedniej skali, tak żeby wykres był możliwy do odczytania.

% różnicy

Poprzednia forma prezentowania danych umożliwiała wychwycenia różnic liczbowych pomiędzy wybranym polem i elementem podstawowym a pozostałymi danymi, natomiast kolejna pozycja umożliwia pokazanie różnicy procentowej.

Jako pole podstawowe zostanie wskazany kolejny raz Kanał, a elementem podstawowym będą Salony sprzedaży.

Po wybraniu powyższego polecenia dane dotyczące kanału Business, Online oraz Telesales wyświetlają się jako różnica procentowa w stosunku do Salonów sprzedaży.

Zwróćmy uwagę na poprawną skalę na wykresie.

Suma bieżąca w…

Suma bieżąca umożliwia pokazanie danych liczbowych narastająco według wybranego elementu.

Dane zostaną pokazane narastająco według daty, więc jako pole podstawowe zostanie wybrana właśnie data.

Po wybraniu powyższego polecenia dane prezentują się jak poniżej – np. wartości dla roku 2010 i 2011 to suma danych z roku 2010 i 2011 itd.

% Sumy bieżącej w…

Poprzednie polecenie umożliwia prezentowanie danych narastających dla danego elementu. Z kolei % sumy bieżącej wyświetla wartości procentowe, również narastająco, dla wybranych elementów.

Jako pole podstawowe wybierzemy znów Datę i dane będą sumować się do 100% w 2014 roku.

Po wskazaniu powyższego polecenia dane prezentują się jak poniżej:

Słupki dla 2014 roku mają wartość równą 100%.

Porządkuj od najmniejszych do największych

Dwoma przedostatnimi poleceniami jest sortowanie. Pierwsze z nich porządkuje dane od najmniejszych do największych.

Dane zostaną uporządkowane według pola podstawowego – zostanie wybrany Kanał.

Można zauważyć, że najmniejsza sumaryczna prowizja otrzymuje wartość 1, natomiast największa wartość 4 – 1 zostaje przyporządkowane do kanału Business, 2 do Online, 3 do Telesales, a 4 do Salonów sprzedaży.

Tak też zostały oznaczone wartości na wykresie.

Porządkuj od największych do najmniejszych

Kolejna opcja porządkowania umożliwia uporządkowanie danych od największych do najmniejszych.

Ponownie jako pole podstawowe zostanie wskazany Kanał.

W tym przypadku to Salony sprzedaży otrzymują wartość 1 jako te, z największą sumą prowizji. Business otrzymuje wartość 4 – suma prowizji jest najmniejsza.

Słupki na wykresie odpowiadają wartościom w tabeli przestawnej.

Indeks

Ostatnią opcją prezentowania danych jest Indeks, który zamienia wartość na indeks. Każdy indeks odzwierciedla wpływ danej liczby na wartość całego pivota (wyliczany jest na podstawie średniej arytmetycznej ważonej). Dokładny wzór na wyliczenie pojedynczego indeksu jest następujący:

(Wartość komórki * Suma końcowa) / (Suma końcowa wiersza tej komórki * Suma końcowa kolumny tej komórki)

Jak interpretować indeks? Indeksy o wartości zbliżonej do 1 oznaczają niewyróżniający się wpływ na wiersze i kolumny. Indeksy poniżej 1 oznaczają bardzo mały wpływ na wiersze i/lub kolumny, powyżej 1 – bardzo duży. Mój komentarz jest taki, że opcja ta nie jest intuicyjna.