Jak prezentować podsumowania (np. sprzedaży) w Tabelach Przestawnych?

Z lekcji dowiesz się:

• Do czego służą Tabele Przestawne? Jakie oferują podsumowania?
• Jak zmienić sposób pokazywania podsumowań?
• Jak przekształcać podsumowania w zależności od potrzeb?
• Jak sortować dane w kolumnie?

Zagadnienie to omówimy na podstawie przykładowych danych (tabela tSprzedaż) przedstawionych na rysunku nr 1 w arkuszu Sprzedaż w pliku do pobrania. Dane te zawierają informacje sprzedażowe dotyczące konkretnych sprzedawców i produktów.

Na podstawie danych z rysunku nr 1 tworzymy tabelę przestawną. Zaznaczamy dowolną pojedynczą komórkę w obszarze naszej tabeli z danymi, a następnie wybieramy polecenie Tabela przestawna (punkt nr 2 na rysunku nr 2) z karty Wstawianie (punkt nr 1 na rysunku nr 2).

Otworzy się okno budowania tabeli przestawnej, gdzie musimy określić jej parametry. Pierwszym krokiem jest wybranie danych, które chcemy przeanalizować. Excel automatycznie w polu Tabela/zakres wstawi tabelę tSprzedaż (punkt nr 1 na rysunku nr 3), ponieważ zaznaczyliśmy komórkę z tej właśnie tabeli przed wyborem polecenia Tabela przestawna. W kolejnym kroku wybieramy, gdzie chcemy wstawić raport w formie tabeli przestawnej. W naszym przykładzie wybieramy Nowy arkusz (punkt nr 2 na rysunku nr 3). Tak ustawione parametry zatwierdzamy klikając przycisk OK.

Otworzy się nowy arkusz z miejscem (zakresem) pod tabelę przestawną oraz oknem Pól tabeli przestawnej. W oknie Pól tabeli przestawnej możemy zrobić podsumowanie, w naszym przykładzie podsumujemy dane po Województwach, Kategoriach produktów i Przychodzie. Pole Województwo przeciągamy do obszaru etykiet Wierszy (strzałka z numerem 1 na rysunku nr 4), natomiast pole Kategoria przeciągamy do obszaru etykiet Kolumn (strzałka z numerem 2 na rysunku nr 4), następnie pole Przychód przeciągamy do obszaru Sumy wartości, ponieważ naszym celem jest podsumowanie danych po wysokości Przychodu.

Otrzymamy podsumowane dane w tabeli przestawnej przedstawione na rysunku nr 5.

Otrzymane dane chcemy mieć w formie tabelarycznej, czyli rozwijamy polecenie Układ raportu (punkt nr 2 na rysunku nr 6) z karty Projektowanie, a następnie wybieramy polecenie Pokaż w formie tabelarycznej (punkt nr 3 na rysunku nr 6).

Zanim zaczniemy zmieniać sposób pokazywania wartości podsumowań, musimy nałożyć formatowanie liczbowe na wartości w tabeli przestawnej. Klikamy na dowolną komórkę z podsumowaniem wartości prawym przyciskiem myszy i z podręcznego menu wybieramy polecenie Format liczby (rys. nr 7).

Otworzy nam się okno Formatowania komórek, gdzie wybieramy Kategorię formatowania – Walutowe (punkt nr 1 na rysunku nr 8), następnie ustawiamy 0 miejsc po przecinku (punkt nr 2 na rysunku nr 8) i nasze ustawienia zatwierdzamy przyciskiem OK.

Otrzymamy sformatowane dane jak widać na rysunku nr 9.

W naszych podsumowanych danych mamy podane Sumy końcowe. Może mieć miejsce sytuacja, że te sumy nie pokażą się automatycznie, należy wtedy wybrać polecenie Sumy końcowe (punkt nr 2 na rysunku nr 10) z karty Projektowanie, a następnie polecenie Włącz dla wierszy i kolumn (punkt nr 3 na rysunku nr 10).

Dane przedstawione na rysunku nr 9 przedstawiają podsumowane w standardowy sposób dane – bez dodatkowych przekształceń, ale tabele przestawne dają możliwość pokazywania podsumowań na wiele sposobów. Jeśli chcemy wprowadzić dodatkowe przekształcenia/obliczenia na tych danych musimy kliknąć na dowolną komórkę z podsumowaniem prawym przyciskiem myszy i z podręcznego menu wybrać polecenie Pokaż wartości jako (rys. nr 11).

Polecenie powyżej polecenia Pokaż wartości jako, czyli Podsumuj wartości według zmienia funkcję jaką użyjemy do podsumowania. Może to być na przykład średnia, minimum bądź maksimum (rys. nr 12). Domyślną funkcją w tabeli przestawnej dla liczb jest Suma.

Zmienimy sobie sposób pokazywania tych wartości (podsumowań). Najpierw zajmiemy się pierwszymi trzema sposobami, czyli % sumy końcowej, % sumy kolumny i % sumy wiersza. Wybierzemy sobie polecenie % sumy końcowej (pokazane na rysunku nr 11). Otrzymamy odpowiednio podsumowane dane pokazane na rysunku nr 13.

W tym przypadku przydają się pokazane sumy końcowe, ponieważ mamy pewność, że obliczenia są poprawne (suma końcowa po wierszach i kolumnach wynosi 100%).

Poszczególne komórki w obliczeniach stanowią procentową część sumy całkowitej. Dzięki tak przedstawionym podsumowaniom możemy w łatwy sposób przeanalizować dane, np. łatwo zauważyć że największą część przychodu stanowi sprzedaż Monitorów (aż 83,16%), natomiast najmniejszy udział w sprzedaży mają Pendrive (tylko 4,30%). Możemy też łatwo odczytać z tych podsumowań, że największa sprzedaż ma miejsce w województwie Małopolskim.

Kolejnymi rodzajami podsumowania są % sumy kolumny i % sumy wiersza (rys. nr 11). Wybierzmy dla przykładu polecenie % sumy wiersza i wtedy nasza tabela przestawna będzie się prezentować następująco (rys. nr 14):

W takiej prezentacji danych każdy wiersz zostanie podsumowany osobno (dla każdego wiersza suma całkowita będzie wynosić 100%). Otrzymamy podsumowanie dla każdego województwa i odpowiednio udział każdego produktu w całkowitej sprzedaży z tego województwa. Łatwo zauważyć, że większość przychodu otrzymujemy ze sprzedaży Monitorów (aż 82,77%), natomiast najgorsze wyniki mają Pendrive.

Analogicznie możemy zrobić podsumowanie po % sumy kolumn. Otrzymamy wtedy dane przedstawione na rysunku nr 15.

Mamy tutaj przedstawione podsumowanie po kolumnach, czyli dla każdej kolumny suma sprzedaży całkowitej wynosi 100%. Możemy łatwo wskazać w którym województwie dany produkt sprzedaje się najlepiej. Przykładowo Monitory sprzedają się najlepiej w województwie Małopolskim.

W zależności od tego jak chcemy pokazać dane i co dla nas jest najistotniejsze, mamy wiele możliwości przekształcenia podsumowania przychodu.

Abyśmy do każdego polecenia nie musieli tworzyć nowych tabel przestawnych, mamy je już przygotowane w kolejnych arkuszach w pliku do pobrania. Kolejne zagadnienie omówimy na podstawie przykładowej tabeli przestawnej z arkusza Różnice (rys. nr 16).

W tym przykładzie chcemy zająć się „Różnicą”. W pierwszej kolumnie mamy pogrupowane dane po miesiącach. W celu pogrupowania danych musimy kliknąć prawym przyciskiem myszy na dowolną komórkę z datą z pierwszej kolumny i z podręcznego menu wybrać polecenie Grupuj (rys. nr 17).

Otworzy nam się okno Grupowania, gdzie wybieramy według czego chcemy pogrupować dane, w naszym przykładzie wybieramy Miesiące (oznaczone zieloną strzałką na rysunku nr 18). Wybrany rodzaj grupowania zatwierdzamy przyciskiem OK.

W tabeli przestawnej mamy dane podsumowane według województw i miesięcy w konkretnym roku. Naszym celem jest przeanalizowanie danych i sprawdzenie czy nasza sprzedaż w skali roku rosła czy malała. Klikamy prawym przyciskiem myszy na dowolną wartość podsumowania i z podręcznego menu wybieramy polecenie Pokaż wartości jako, a następnie Różnica (rys. nr 19).

Otworzy nam się okno Pokazywania wartości jako, gdzie musimy określić takie parametry jak Pole podstawowe i Element podstawowy. Pole podstawowe, czyli po czym będziemy rozpatrywać dane (po Dacie). Element podstawowy, czyli na jakim elemencie będziemy się wzorować (do czego porównywać dane), może to być konkretny miesiąc, ale również może to być opcja Poprzedni lub Następny miesiąc. W naszym przykładzie będziemy się wzorować na wartości z poprzedniego miesiąca. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 20).

Otrzymamy tabele przestawną przedstawioną na rysunku nr 21.

Możemy prześledzić te dane dla każdego miesiąca i przeanalizować jak zmieniała się wartość sprzedaży w odniesieniu do poprzedniego miesiąca. Dowiemy się w którym miesiącu sprzedaż była wyższa, a w którym niższa od sprzedaży z miesiąca poprzedzającego aktualnie rozpatrywany. W danych brakuje wartości ze stycznia ponieważ jest to nasza wartość bazowa, a dopiero w lutym otrzymujemy wartość w odniesieniu do wartości ze stycznia. Jeśli wartość w lutym jest dodatnia to znaczy, że sprzedaż wzrosła w porównaniu do stycznia. Analogicznie jeśli sprzedaż w lutym jest ujemna, to znaczy, że sprzedaż w lutym spadła w porównaniu do stycznia. Wartość z każdego kolejnego miesiąca odnosi się do miesiąca poprzedniego i na tej podstawie mamy spadek bądź wzrost przychodu.

Jeśli chcielibyśmy pokazać różnicę jako wartości procentowe wystarczy kliknąć na dowolną komórkę z podsumowania prawym przyciskiem myszy i z podręcznego menu wybrać polecenie Pokaż wartości jako a następnie % różnicy (rys. nr 22).

Otworzy nam się okno Pokazywania wartości jako, w którym ustawiamy takie same parametry jak w poprzednim przypadku (jak na rysunku nr 20) i zatwierdzamy je przyciskiem OK. Otrzymamy dane przedstawione na rysunku nr 23.

Dane te są podsumowane jako różnice procentowe a nie konkretne wartości, czyli mamy podany o ile procent spadł lub wzrósł przychód w danym miesiącu w porównaniu do poprzedniego miesiąca.

Kolejny sposób prezentacji danych w tabeli przestawnej omówimy sobie na podstawie tabeli zapisanej w arkuszu Bieżąca w pliku do pobrania (rys. nr 24).

Mamy dane przedstawiające podsumowanie przychodu po miesiącach. Do tych danych chcemy dołożyć nową kolumnę przedstawiającą Sumę bieżącą. Musimy jeszcze raz przeciągnąć pole Przychód do obszaru etykiet Sumy wartości w oknie Pól tabeli przestawnej (zaznaczone zieloną strzałką na rysunku nr 25).

Otrzymamy tabelę przestaną z dodatkową kolumną (taką samą jak kolumna Suma z przychód). Dla drugiej kolumny zmienimy sobie sposób pokazywania danych. Klikamy prawym przyciskiem myszy na komórkę w kolumnie Suma z Przychód2 i z podręcznego menu wybieramy polecenie Pokaż wartości jako, a następnie % suma bieżącej w (rys. nr 26).

Otworzy nam się okno Pokazywania wartości jako, gdzie musimy określić parametr Pole podstawowe – w naszym przykładzie Data. Nasz parametr zatwierdzamy przyciskiem OK (rys. nr 27).

Otrzymamy podsumowane wartości w tabeli przestawnej pokazane na rysunku nr 28.

Dane przedstawiają narastające wartości procentowe w stosunku do sumy końcowej.

Lepiej zrozumiałe będzie użycie polecenia Suma bieżąca w (rys. nr 26), ponieważ wartość ta będzie rosła z miesiąca na miesiąc (w każdym miesiącu będzie dodawany przychód z danego miesiąca). Dane te będą wyglądać następująco (rys. nr 29):

Oczywiście musimy pamiętać o odpowiednim formatowaniu liczbowym. Klikamy prawym przyciskiem myszy na dowolną komórkę z kolumny z sumą bieżącą i z podręcznego menu wybieramy polecenie Format liczby. Otworzy nam się okno Formatowania komórek, gdzie wybieramy Kategorię formatowania - Walutowe oraz liczbę miejsc po przecinki – 0 (identycznie jak na rysunku nr 7 i 8). Nasze parametry zatwierdzamy przyciskiem OK i otrzymamy dane przedstawione na rysunku nr 30.

W kolumnie Suma z Przychód mamy przedstawioną sumę przychodów w danym miesiącu, natomiast w kolumnie Suma z Przychód2 mamy sumę bieżącą, czyli w każdym miesiącu mamy sumę z danego miesiąca i miesięcy go poprzedzających.

Kolejny sposób podsumowania danych przedstawimy na podstawie tabeli przestawnej z arkusza Nadrzędny w pliku do pobrania (rys. nr 31).

Klikamy prawym przyciskiem myszy na dowolną komórkę z podsumowaną wartością w tabeli przestawnej i z podręcznego menu wybieramy polecenie Pokaż wartości jako, a następnie % sumy wiersza nadrzędnego (rys. nr 32). Są jeszcze polecenia % sumy kolumny nadrzędnej i % sumy elementu nadrzędnego, które działają w analogiczny sposób.

Otrzymamy dane przedstawione na rysunku nr 33.

W naszej tabeli w obszarze etykiet wierszy mamy Województwo i Sprzedawcę, natomiast w obszarze etykiet kolumn mamy Kategorię (Klawiatura, Monitor, Mysz i Pendrive). Dane te możemy rozumieć następująco: np. sprzedawca Ireneusz uzyskał sprzedaż klawiatur na poziomie 3,51 % w stosunku do sprzedaży tego produktu w całym województwie Małopolskim. Kiedy zsumujemy wszystkie wartości procentowe dla sprzedawców z województwa Małopolskiego dla produktu Klawiatura to otrzymamy 100%. Można to sprawdzić w pasku informacyjnym (oznaczone zieloną strzałką na rysunku nr 34).

Te 100 % jest pokazane w wierszu Małopolskie suma, ale wiersz ten jednocześnie pokazuje sumę procentową z wiersza nadrzędnego (27,97 %) (rys. nr 33). Wierszem nadrzędnym dla wiersza Małopolskie suma jest dopiero wiersz Suma końcowa (100 %).

Dla polecenia % sumy kolumny nadrzędnej nasze dane wyglądały by następująco (rys. nr 35):

Ostatnią rzeczą jaką chcemy pokazać w temacie podsumowania danych w tabeli przestawnej jest ranking, czyli pokazanie która wartość jest największa lub najmniejsza. Zagadnienie to pokażemy na podstawie przykładowej tabeli przestawnej z arkusza Ranking w pliku do pobrania (rys. nr 36).

Mamy dane podsumowane po Sprzedawcach i Kategoriach. Klikamy prawym przyciskiem myszy na dowolną komórkę z podsumowaną wartością i z podręcznego menu wybrać polecenie Pokaż wartości jako, a następnie Porządkuj od najmniejszych do największych lub Porządkuj od największych do najmniejszych. Pierwszego polecenia najczęściej używamy w stosunku do porządkowania czasu, natomiast porządkowania od największych do najmniejszych dla przychodów (zysków), ponieważ często chcemy poznać sprzedawcę, który miał największą wartość przychodu bądź sprzedawcę najmniej efektywnego (rys. 37).

Otworzy nam się okno Porządkowania wartości, gdzie musimy określić jakie wartości chcemy uporządkować, czyli w Polu podstawowym wybieramy Sprzedawcę (rys. nr 38).

Otrzymamy tabelę przedstawioną na rysunku nr 39.

Otrzymaliśmy dane z alfabetycznie uporządkowanymi sprzedawcami. Na tym etapie możemy posortować dane w kolumnie Klawiatura. Zaznaczamy komórkę w kolumnie Klawiatura i wybieramy polecenie Sortuj od Z do A (punkt nr 2 na rysunku nr 40) z karty Dane.

Dane po posortowaniu zostały pokazane na rysunku nr 41.

Tak przedstawione dane mogą być mylące, np. sprzedawca Paulina nie sprzedała 1 klawiatury. Ona zajmuje pierwsze miejsce w sprzedaży klawiatury, a pod miejsce w rankingu kryje się wartość sprzedaży. Z naszego rankingu wychodzi, że sprzedawca Paulina ma najlepsze wyniki sprzedażowe w prawie każdej kategorii i jest najlepszym sprzedawcą ze wszystkich.

Podsumowując istnieje wiele różnych sposobów prezentacji danych sprzedażowych w tabeli przestawnej, które możemy dopasować do naszych potrzeb i priorytetów (które dane są dla nas najistotniejsze). Wszystkie te tabele przestawne powstały na podstawie jednego zbioru danych w zależności jakich danych aktualnie potrzebowaliśmy i jakich użyliśmy do opisania obszaru etykiet wierszy i kolumn oraz przede wszystkim po jakiej wartości robiliśmy podsumowanie.

Polecamy także:

• Kolorowanie naprzemienne – jak poprawić przejrzystość danych?
• Funkcje Tablicowe – jak wykorzystać je w Twojej firmie?