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 2013: ExcelSzkolenie.pl Cwiczenia Excel 2013.zip

Ta lekcja może być obejrzana lub przeczytana poniżej.

Film wygląda najlepiej jeśli będzie odtwarzany w rozdzielczości 720p HD, rozdzielczość można zmienić dopiero po uruchomieniu filmu klikając na ikonie trybika która pojawi się w prawym dolnym rogu poniższego ekranu. Po kilku sekundach od zmiany obraz wyostrzy się.

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 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’, ‘Wykres przestawny’ i ponownie ‘Wykres przestawny’ – zgodnie z poniższym rysunkiem.

Nie musimy niczego zmieniać w oknie ‘Utwórz wykres przestawny’, klikamy OK.

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

Przeciągnijmy pola wykresu przestawnego zgodnie z poniższym rysunkiem.

Wybieramy jako region ‘południe’ a jako miesiąc ‘maj’. Zmieniamy format pól danych w tabeli przestawnej.

Dane mają być analizowane oddzielnie dla każdego miesiąca i regionu więc to czy dla marży będzie suma czy średnia nie większego znaczenia. Gdyby dane miały być analizowane dla większej ilości miesięcy bądź regionów należałoby dodać do danych źródłowych pole z marżą kwotową a do tabeli przestawnej pole obliczeniowe, bardzo podobny przykład został opisany w lekcji ‘Tabele Przestawne dla Zaawansowanych’.

Na wykresie widać obecnie tylko sprzedaż ponieważ wykres marży pokrywa się z osią X.

Po kliknięciu prawym klawiszem myszy na słupkach wybieramy polecenie ‘Formatuj serię danych…’.

W oknie ‘Formatowanie serii danych’ zmieniamy ‘Kreśl serię na:’ wybierając ‘Oś pomocnicza’.

Marża pojawi się jako czerwone słupki ale wykres wciąż będzie nieczytelny.

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

Przyciski pól na wykresie nie wyglądają dobrze, aby je ukryć klikamy dowolny z nich prawym przyciskiem myszy i wybieramy polecenie ‘Ukryj wszystkie przyciski pól na wykresie’.

Po sformatowaniu wykresu i ukryciu wierszy zawierających tabelę przestawną uzyskujemy poniższy ostateczny wygląd wykresu przestawnego.

Wykres będzie się zmieniał po zmianach dokonanych w filtrach tabeli przestawnej.

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+’, 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 na początku zero.

=JEŻELI(D5<10;"0"&D5&" "&E5;D5&" "&E5)

Wynik funkcji wstawiamy jako wartości do kolumny D.

Po zmianie zakresu danych tabeli przestawnej tak aby objęła kolumnę strona+ oraz podmianie w wierszach tabeli przestawnej ze stron na strony+ i sformatowaniu etykiet 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.