Wykres Przestawny

Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Wykres Przestawny.xls, zachęcam do ich samodzielnego wykonania. tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji.

Przykłady dla wszystkich lekcji szkolenia Excel 2003: ExcelSzkolenie.pl Cwiczenia Excel 2003.zip

Wykresy przestawne są szczególnie użyteczne do analizowania danych sprzedaży. Mają tą ogromną przewagę nad zwykłymi wykresami, że nawet niewprawny użytkownik przygotowanego przez kogoś wykresu przestawnego może bardzo łatwo zmieniać jego konfiguracje i korzystając z jednego wykresu może przeanalizować wiele różnych zakresów danych.

Przykład 1

(Arkusz: ‘Wykres Przestawny 1’, rozwiązanie: ‘Wykres Przestawny 2’, ‘Wykres Przestawny 3’)

Wykres przestawny przećwiczymy na następującym przykładzie:

Pewna hurtownia raz w miesiącu przygotowuje i rozsyła katalog produktów, na podstawie, którego sklepy zamawiają towary. Hurtownia ta analizuje wyniki swojej sprzedaży w 2 regionach: północ i południe, każdy katalog ma 50 stron, na których oferowane są towary z 3 różnych kategorii.

Wykres przestawny tworzymy zaznaczając obszar, w którym występują dane, które chcemy przeanalizować i następnie wybierając z Menu: ‘Dane’ polecenie: ‘Raport tabeli przestawnej i wykresu przestawnego...’.

W pierwszym z 3 okien kreatora zmieniamy rodzaj raportu na: ‘Raport wykresu przestawnego (z raportem tabeli przestawnej)

W kolejnym oknie klikamy na przycisk ‘Dalej>’.

A w kolejnym ‘Zakończ’.

Do pliku, w którym pracujemy zostaną dodane dwa nowe arkusze, pierwszy zawierający wykres przestawny i drugi z tabelą przestawną połączoną z tym wykresem.

W arkuszu z wykresem przestawnym przenosimy pola zgodnie ze strzałkami pokazanymi na poniższym rysunku. Po czym zamykamy listę pól tabeli przestawnej.

W efekcie powstanie przeniesienia pól powstanie nasz pierwszy wykres przestawny, który powinien wyglądać tak jak poniżej.

Pracownik zainteresowany analizą danych sprzedaży może łatwo wybrać region i miesiąc, który go interesuje klikając na strzałki otwierające menu wyboru.

Powyższy wykres mógłby już być z powodzeniem użytkowany, ale dodamy do niego jeszcze pozostałe informacje.

Aby przywrócić listę pól tabeli przestawnej należy kliknąć poniżej zaznaczoną ikonę z Menu: Wykres przestawny.

Dodamy teraz marżę do naszego wykresu.

Niestety efekt nie wygląda zbyt zachęcająco.

Aby poprawić wykres przechodzimy do arkusza w którym znajduje się tabela przestawna połączona z wykresem i przenosimy pole ‘Dane’ na komórkę oznaczoną jako ‘Suma’, tak jak jest to pokazana na rysunku poniżej.

Ponieważ marża jest daną, którą nie można sumować dodatkowo, klikając prawym klawiszem na komórce z napisem ‘Suma z marża’ i wybierając opcję Ustawienie Pola zamienimy sumę na średnią.

(Jest to rozwiązanie uproszczone podające tylko przybliżony wynik, ponieważ średnia nie będzie ważona sprzedażą. Aby uzyskać dokładny wynik należałoby dodać do tabeli z danymi dodatkową kolumnę z marżą wyrażoną w zł i marżę procentową wyliczać jako pole obliczeniowe.)

W efekcie powyżej opisanych działań tabela przestawna powinna wyglądać tak:

A wykres przestawny tak:

Kolejnymi krokami będzie zamknięcie listy pół tabeli przestawnej i zmiana typu wykresu na ‘Niestandardowy’ → ‘Liniowo-kolumnowy (2 osie)’.

W kolejnym kroku usuwamy legendę, ponieważ zajmuje ona zbyt dużo miejsca i jest dość oczywiste która z wartości co przedstawia.

Formatujemy wykres:

- usuwamy tło

- zamieniamy znaczniki osi dodatkowej na procenty

- formatujemy linię tak, aby nie miała znaczników

Niestety formatowanie zostanie utracone przy pierwszej zmianie filtrów w wykresie, aby formatowanie pozostało należy nagrać makro, które będzie uruchamiało się automatycznie po każdej zmianie wykresu i formatowało wykres. Więcej na temat makr można przeczytać w drugiej części tego kursu.

Przykład 2 - rozszerzona wersja przykładu 1

(Arkusz: ‘Wykres Przestawny 1’)

Kolejnym krokiem będzie dodanie nazwy kategorii przy numerze strony tak aby użytkownicy wiedzieli, która z kategorii uzyskała, jaki wynik.

Aby to zrobić wracamy do arkusza z danymi i dodajemy kolumnę: ‘strona+kategoria’, w której umieszczamy funkcję, która będzie łączyć numer strony z kategorią, aby nadal istniała możliwość sortowania po numerze strony do stron, których numery są mniejsze niż 10 dodajemy 0 na początku:

=JEŻELI(D5<10;ZŁĄCZ.TEKSTY("0";D5;" ";E5);ZŁĄCZ.TEKSTY(D5;" ";E5))

Wynik funkcji zastępujemy jej wartościami i nadpisujemy kolumnę D kolumną H.

Dokładnie powtarzamy operacje opisane w Przykładzie 1, wynikiem czego powienien być wykres taki jak poniżej. Kategoria widoczna jest przy numerze strony.

Funkcjonalność takiego wykresu można rozszerzyć np. dodając przyciski z makrami umożliwiającymi sortowanie wg. wielkości sprzedaży czy wysokości marży.

Szkolenie Excel 2003 Szkolenie Excel 2007