Wykres Przestawny

Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Wykres Przestawny.xlsx tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji

Przykłady dla wszystkich lekcji szkolenia Excel 2007: ExcelSzkolenie.pl Cwiczenia Excel 2007.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 ł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’, rozwiązanie arkusz: ‘Wykres Przestawny 2’)

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 dane, które chcemy przeanalizować i wybierając z karty ‘Wstawianie’, ‘Tabela przestawna’, ‘Wykres przestawny’ zgodnie z poniższym rysunkiem.

Nie musimy niczego zmieniać w oknie ‘Tworzenie tabeli przestawnej z wykresem przestawnym’, klikamy OK.

Do pliku, w którym pracujemy zostanie dodany nowe arkusz, zawierający wykres przestawny i tabelę przestawną połączoną z tym wykresem.

Przeciągnijmy pola ‘region’ i ‘miesiąc’ nad napis ‘Upuść pola kolumn tutaj’.

Kolejnym krokiem będzie przeciągnięcie pola ‘strona’ do wierszy a pól ‘sprzedaż’ i ‘marża’ do elementów danych.

Po tych operacjach uzyskamy wykres przestawny wyglądający tak jak na poniższym rysunku.

Wybieramy jako region ‘południe’ a jako miesiąc ‘maj’. Zmieniamy format pól danych w tabeli przestawnej i dla marży wybieramy średnią.

Wybranie średniej da nam dokładny wynik jeśli wybrany jest jeden region i jeden miesiąc dla sum wynik będzie przybliżony, aby uzyskać dokładny wynik w każdym przypadku należałoby dodać do danych pole z marżą kwotową a do tabeli przestawnej pole obliczeniowe, bardzo podobny przykład został opisany w lekcji ‘Tabele Przestawne dla Zaawansowanych’.

Przeciągamy wskaźnikiem myszy nad osią X starając się znaleźć serię danych z marżą, (może to wymagać odrobiny cierpliwości), a kiedy już się uda klikamy ją prawym klawiszem myszy.

Wybieramy polecenie ‘Formatuj serie danych’ i zmieniamy opcję ‘Kreśl serię na:’ – ‘Oś pomocnicza’.

Zgodnie z poniższymi rysunkami zmieniamy typ wykresu dla marży.

Na liniowy.

Po sformatowaniu wykresu i ukryciu wierszy zawierających nagłówki wierszy, kolumn oraz dane tabeli przestawnej uzyskujemy poniższy ostateczny wygląd wykresu przestawnego. Wykres będzie się zmieniał po zmianach dokonanych w filtrach tabeli przestawnej.

W przeciwieństwie do Excela 2003 w wersji 2007 formatowanie zostanie zachowane po zmianach.

Przykład 2.

(rozszerzona wersja przykładu 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ę, łączącą 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 wstawiamy jako wartości do kolumny D.

Po odświeżeniu danych w ‘tabeli przestawnej’ i sformatowaniu wykresu uzyskamy poniższy rezultat.

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.