Wykresy dla Zaawansowanych

Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Wykresy dla Zaawansowanych.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

Przykład 1.

(Arkusz: ‘WdZ 1’)

W tym przykładzie utworzymy wykres, którego format w Excelu nie został przewidziany, a który jest bardzo użyteczny do prezentowania zmian w wartościach absolutnych (kwotowych). Wykres ten bywa najczęściej nazywany: waterfall lub bridge.

Na podstawie poniższej tabeli sporządzimy wykres przedstawiający, sytuację początkową, zmiany absolutne i sytuację końcową.

Wykres będzie wyglądał najlepiej, jeśli posortujemy dane, w naszym przykładzie brandy, malejąco według wielkości zmiany rok do roku.

Kolejnym krokiem jest przygotowanie dodatkowych kolumn (H, I, J), które zawierają informację o wartości bazowej dla zmiany i wysokości słupka zmiany przedstawionej na wykresie.

Dla danych w kolumnie baza używać będziemy 3 różnych formuł. Pierwsza z formuł dla liczb w wierszach od 3 do 8 to suma bazy z poprzedniego wiersza i wysokości słupka (zmiany z kolumny J) także z poprzedniego wiersza. Formuła ta jest używana dla wszystkich brandów, dla których zmiana z kolumny F jest dodatnia.

Dla pierwszego wiersza, w którym w kolumnie ‘wzrost $’ występuje wartość ujemna formuła będzie sumować ‘bazę’ i ‘zmianę’ z poprzedniego wiersza i dodatkowo odejmie od nich ‘zmianę’ z tego samego wiersza.

Formuła dla każdej kolejnej wartości ujemnej odejmuje od ‘bazy’ z poprzedniego wiersza ‘zmianę’ z tego samego wiersza, w którym się znajduje.

Jeżeli jako opisów danych używamy liczb, w naszym przykładzie 2010 i 2011, dobrze jest przed nimi wpisać znak apostrofu aby Excel nie potraktował tej kolumny jako jeszcze jednej kolumny z danymi do pokazania na wykresie.

Przygotowując wykres na podstawie tych danych zaznaczamy dane i ich opisy z kolumn H, I oraz J tak jak jest to pokazane na poniższym rysunku.

Wybieramy drugi podtyp wykresu kolumnowego ‘Skumulowany kolumnowy’..

Excel przygotuje wykres, który powinien wyglądać tak, jak na poniższym rysunku.

Formatujemy wykres tak, aby wyglądał tak jak poniżej.

(Wszystkie czynności niezbędne do takiego sformatowania wykresu zostały opisane w lekcji ‘Wykresy dla Początkujących’.)

Dla dolnej serii danych wybieramy kolor biały.

Dwa pojedyncze kliknięcia lewym klawiszem myszy powodują wybranie tylko jednego punktu danych, wybieramy pierwszy słupek dolnej serii i zmieniamy jego kolor na niebieski.

Tak samo robimy z ostatnim słupkiem. Dodatkowo kasujemy etykiety górnej serii dla pierwszego i ostatniego elementu (zera) i dodajemy etykiety tylko do pierwszego i ostatniego elementu dolnej serii danych. W efekcie tych zmian uzyskamy wykres taki jak na poniższym rysunku.

Excel daje możliwość automatycznego ustawiania etykiet, ale tylko w ramach słupka, ponieważ będziemy chcieli ustawić etykiety nad lub pod słupkami musimy to zrobić ręcznie, przeciągając każdą z nich oddzielnie. Etykiety górnej serii danych przeciągamy nad słupki dla dodatnich wartości zmiany i pod nie dla ujemnych.

Dla łatwiejszego zrozumienia wykresu słupki obrazujące wzrosty pozostawimy zielone a dla tych pokazujących spadki zmienimy kolor na czerwony.

Podobnie jak dla etykiet nie można wybrać kilku słupków i na raz je pokolorować, musimy powtórzyć operację kolorowania osobna dla każdego z 4 słupków.

Dla ujemnych wartości możemy też wstawić znaki minus w etykietach, ale tylko wtedy jeżeli dane nie będą więcej aktualizowane.

UWAGA!

Excel nie aktualizuje zmienionych etykiet przy zmianie danych zmieni się tylko długość słupka, cyfra do której dopisano minus nie zostanie zaktualizowana.

Uzyskany wykres bardzo jasno pokazuje, które brandy i w jakim stopniu przyczyniły się do wzrostu sprzedaży pomiędzy latami 2010 i 2011, a które wynik obniżyły poprzez spadek swojej sprzedaży.

Ze względu na jasny przekaz i podkreślenie kwot a nie wzrostów procentowych, wykres ten jest powszechnie stosowany w prezentacjach wyników sprzedaży.

Przykład 2.

(Arkusze: ‘WdZ 2’, ‘WdZ 3’)

Wykres typu licznik (nazywany też speedometer chart lub wykresem tachometrycznym) spotykany jest w periodycznych analizach przygotowywanych dla kierownictwa i często nazywanych ‘dashboards’ co w dosłownym tłumaczeniu oznacza tablicę rozdzielcze. To co ma on przekazywać, to nie dogłębne spojrzenie na dane i pomoc w ich analizie, ale zorientowanie się już na pierwszy rzut oka czy sytuacja jest dobra czy zła.

Zwykle zestawia się kilka takich wykresów dla różnych regionów/brandów/oddziałów na jednym slajdzie/arkuszu, i dzięki temu, że wszystkie mają taką samą skalę łatwo jest zauważyć które części biznesu rozwijają się dobrze a które źle.

Poniżej przedstawiam rozwiązanie pozwalające utworzyć taki wykres w jego podstawowej postaci.

Zaznaczamy zarówno dane, które utworzą tarczę licznika jak i te dzięki którym uzyskamy strzałkę.

Wstawiamy wykres pierścieniowy i pierwszy jego podtyp.

Wykres który zostanie utworzony nawet dla osób o dużej wyobraźni nie przypomina licznika.

Kasujemy legendę.

Wybieramy największą część wewnętrznego pierścienia (dwa pojedyncze kliknięcia na niej lewym klawiszem myszy) i z karty ‘Narzędzia główne’ wybieramy dla niej ‘Brak wypelnienia’.

Zmieniamy kolory pozostałych części wewnętrznego pierścienia zgodnie z poniższym rysunkiem, dodajemy etykiety danych i przeciągamy je poza wewnętrzny pierścień, w miejsca gdzie łączą się elementy pierścienia.

Zaznaczamy zewnętrzny pierścień, klikamy na nim prawym klawiszem myszy i wybieramy polecenie ‘Zmień typ wykresu seryjnego…’.

Wybieramy pierwszy podtyp wykresów kołowych.

Wykres kołowy znajduje się teraz pod pierścieniowym. Możemy go kliknąć w otworze wykresu pierścieniowego. Klikamy go prawym klawiszem myszy i wybieramy polecenie ‘Formatuj serię danych…’.

Po czym z karty ‘Opcje serii’ wybieramy ‘Oś pomocnicza’.

Wykres kołowy jest teraz na wierzchu, a pierścieniowy pod nim, powoduje to chwilowe zakrycie tarczy naszego licznika.

Wybierzmy teraz brak wypełnienia dla zielonej i niebieskiej części wykresu kołowego a dla najcieńszej części (która utworzy wskazówkę naszego licznika) kolor czarny, dzięki czemu będziemy dobrze widoczna.

Do etykiet ręcznie wpisujemy właściwe wartości procentowe i poprawiamy ich umiejscowienie.

Ponieważ ramka wykresu nie wygląda dobrze, klikamy na wykresie prawym klawiszem i wybieramy polecenie ‘Formatuj obszar wykresu…’

Po czym w oknie ‘Formatowanie obszaru wykresu’ na karcie ‘Kolor krawędzi’ wybieramy ‘Brak linii’.

Pozostaje nam już tylko przetestowanie działania wykresu poprzez zmiany wartości komórki D4.

Gdybyśmy chcieli użyć innej skali niż -10% +10% należy odpowiednio zmienić formuły w kolumnie C.

Używając dokładnie tych samych metod możemy przygotować wykresy w formie licznika w innych formatach, np. takim jak pokazany poniżej.

(wykres dostępny jest w pliku Wykresy dla Zaawansowanych.xlsx)

Przykład 3

(Arkusz: ‘WdZ 4’)

Ten przykład jest bardzo podobny do przykładu drugiego z lekcji ‘Wykresy dla Zaawansowanych’, ale oprócz marży i sprzedaży mamy także ich wartości planowe. Przerobienie tego przykładu pozwoli opanować łączenie różnych typów wykresów i przypisywanie ich do odpowiedniej osi Y.

Zaznaczamy dane (bez Totala),

Z karty ‘Wstawianie’ wybieramy pierwszy podtyp wykresu Kolumnowego.

Excel tworzy wykres z 4 seriami danych, z czego słupki dla 2 serii są tak małe że ich nie widać.

Formatujemy wykres do postaci przedstawionej poniżej.

Prawym klawiszem myszy klikamy w serię danych znajdującą się pomiędzy niebieskim a zielonym słupkiem. Ponieważ jej wartość jest bardzo mała, trafienie w tą serie może wymagać nieco cierpliwości. Wybieramy polecenie ‘Formatuj serię danych…’.

W oknie ‘Formatowanie serii danych’ na karcie ‘Opcje serii’ zaznaczamy ‘Oś pomocnicza’.

Excel utworzy drugą oś, a nowy słupek prawie całkowicie zakryje te wcześniej widoczne.

Klikamy go prawym klawiszem i wybieramy polecenie ‘Zmień typ wykresu seryjnego…’.

Zmieniamy na pierwszy podtyp wykresu Liniowego.

Powyżej opisane czynności powtarzamy dla drugiej serii danych zawierających marżę.

W efekcie uzyskujemy wykres na którym są 2 serie danych przedstawione jako kolumny i przypisane do osi głównej, oraz 2 serie danych w postaci linii przypisane do osi pomocniczej.

Wykres będzie bardziej czytelny jeśli zmieniamy skalę tak aby linie i kolumny nie nachodziły na siebie.

Metodą prób i błędów dochodzimy do tego jakiej liczby użyć jako maksimum skali, aby serie danych nie przecinały się.

Proponuję także zmienić kolory dla danych planowych na kolor zielony (kolor nadziei) a wyniki na niebieski.

Kolory linii zmieniamy w oknie ‘Formatowanie serii danych’, na karcie ‘Kolor linii’, po wybraniu opcji ‘Linia ciągła’.

W wyniku opisanych powyżej czynności wykres, który uzyskamy powinien wyglądać tak jak poniżej.

Przykład 4.

(Arkusz: ‘WdZ 5’)

W tym przykładzie pewna firma charakteryzująca się stabilnym wzrostem sprzedaży oraz ilości zamówień, chciałaby oszacować w prosty sposób wzrost sprzedaży mając dany przewidywany wzrost zamówień.

W tym celu przygotujemy wykres punktowy, utworzymy dla niego trend wraz z jego równaniem oraz wyliczeniem współczynnik R², który mówi nam, w jakim stopniu zmienność Y jest uzależniona od zmienności X.

Pierwszym krokiem będzie zaznaczenie wyłącznie zakresu zawierającego dane. Po tym wybieramy z karty ‘Wstawianie’ wykres punktowy i pierwszy z jego podtypów.

Powinniśmy otrzymać wykres taki, jak pokazany na rysunku poniżej.

Kasujemy legendę i linię siatki.

Klikamy jeden z punktów prawym klawiszem i wybieramy polecenie: ‘Dodaj linię trendu...’.

W oknie ‘Formatowanie linii trendu’ na karcie ‘Opcje linii trendu’ wybieramy ‘Liniowy’, a poniżej ‘Wyświetl równanie na wykresie’ oraz ‘Wyświetl wartości R-kwadrat na wykresie’.

Na skutek wyżej opisanych działań wyświetlona zostanie linia trendu a także jej wzór i współczynnik R², który w typ przypadku wynosi aż 0,9598, dzięki czemu możemy być pewni, że używając uzyskanej funkcji do prognozowania uzyskamy dobrej jakości przewidywania.

Równanie musimy skopiować np. do komórki pod tabelą, z pominięciem y i zastępując x adresem komórki w której będzie wpisana zmiana ilości zamówień.

Jeśli wzrost ilości zamówień szacowany jest na 8% to wpisujemy tą wartość w komórkę D29 i wyliczamy, że możemy się spodziewać wzrostu sprzedaży na takim samym poziomie.

Przykład 5

(Arkusz: ‘WdZ 6’)

Aby utworzyć wykres typu XY z opisami punków niestety nie wystarczy rozszerzyć zakres o kolumnę z opisami. Przy próbie formatowania serii danych nie znajdziemy opcji pozwalającej wyświetlić nam nazwy punków.

Aby dodać nazwy punktów na wykresie należy utworzyć wykres na którym każdy punkt będzie osobną serią. Jest to nieco pracochłonne ale raz utworzony wykres możemy używać wielokrotnie.

Zaczniemy nietypowo od wstawienia wykresu punktowego bez wcześniejszego zaznaczenia danych.

Klikamy prawym klawiszem na pustym wykresie, który został dodany i wybieramy polecenie ‘Zaznacz dane’.

W oknie ‘Wybieranie źródła danych’ klikamy przycisk ‘Dodaj’.

Po czym wprowadzamy serię danych złożoną z nazwy serii w postaci kraju, oraz punktu X i Y odpowiadających temu rynkowi.

Po kliknięciu OK., ponownie w oknie ‘Wybieranie źródła danych’ klikamy przycisk ‘Dodaj’ i wprowadzamy 4 kolejne serie tak jak jest to pokazane na rysunkach poniżej.

W efekcie mamy 5 serii danych.

A na wykresie pokazanych jest 5 punktów.

Kasujemy legendę oraz linie siatki, a kolory wszystkich punktów zmieniamy na czarny.

Klikamy na dowolny punkt prawym klawiszem myszy i wybieramy polecenie ‘Formatuj serię danych…’.

W oknie ‘Formatowanie serii danych’ na karcie ‘Opcje znaczników’ zaznaczamy typ ‘Wbudowany’ i wybieramy ‘Typ’ koło.

Operację tą powtarzamy dla wszystkich punktów.

Do wszystkich punktów dodajemy etykiety danych (po kliknięciu prawym klawiszem na punkcie).

Po czym dla każdego punktu klikamy na nim prawym klawiszem myszy i wybieramy polecenie ‘Formatuj etykiety danych…’.

W oknie ‘Formatowanie etykiet danych’ na karcie ‘Opcje etykiet’ odznaczamy ‘Wartość Y’ i zaznaczamy ‘Nazwa serii’.

Po powtórzeniu tego dla wszystkich 5 punktów uzyskamy wykres punktowy, w którym etykiety opisowe będą podążały za punktem przy zmianie danych, lub skopiowaniu wykresu i użyciu go dla innych danych.

Tak jak napisałem na początku tego przykładu utworzenie tego wykresu jest pracochłonne, ale raz utworzony możemy używać wielokrotnie.

Przykład 6

(Arkusz: ‘WdZ 7’, ‘WdZ 8’)

Wykres typu termometr (‘Thermometer Chart’) używany jest do uatrakcyjnienia analiz i prezentacji, nie przekazuje on nic więcej niż można by przekazać przy pomocy zwykłego wykresu słupkowego.

Zaczynamy od zaznaczenia tylko danych dla maksimum i wstawienia najprostszego wykresu kolumnowego.

Formatujemy wykres który uzyskamy poprzez skasowanie legendy, linii siatki i zmianę kształtu wykresu na wąski i wysoki oraz zmianę tytułu.

Klikamy na wykresie prawym klawiszem i wybieramy polecenie ‘Zaznacz dane…’.

Dodajemy drugą serie danych według poniższego rysunku.

Dodatkowa seria danych zostanie pokazana obok już istniejącej. Klikamy ją prawym klawiszem i wybieramy ‘Formatuj serię danych…’.

Po czym zmieniamy oś na oś pomocniczą.

Zmiana ta spowodowała dodanie drugiej osi i całkowite zakrycie pierwszej serii danych przez drugą.

Przed nami znowu trochę formatowania. Zaznaczamy i kasujemy oś X.

Dla obu osi Y wprowadzamy maksimum wynoszące 100.

Dla obu osi Y na karcie ‘Liczby’ okna ‘Formatowanie osi’ w kategorii ‘Niestandardowe’ wpisujemy kod formatu: 0C i po kliknięciu przycisku ‘Dodaj’ klikamy ‘Zamknij’.

Na skutek tych zmian nasz wykres powinien wyglądać tak jak poniżej.

Formatujemy niebieski słupek danych, zmniejszając szerokość przerwy, wybierając ‘Brak wypełnienia’ i ‘Kolor krawędzi’ – ‘Linia ciągła’, kolor czarny.

Zmieniamy kolor „słupka rtęci” na czerwony i zwężamy wykres.

W ten sposób uzyskujemy wykres wyglądający jak termometr.

Dokładnie na tej samej zasadzie powstał poniższy wykres przedstawiający zaawansowanie prac nad 2 projektami. Kolory tła obrazują kolejne etapy prac.

(wykres ten dostępny jest w pliku Wykresy dla Zaawansowanych.xlsx Arkusz: ‘WdZ 7’.)

Rozwinięciem tych samych metod jest poniższy wykres dla miłośników potencjometrów. W tym wypadku z wykresu kolumnowego skumulowanego pozostawiono widoczną co drugą serię danych, z których każda ma kolor czarny i przesłania będący na drugiej osi zielony wykres kolumnowy. Sprawia to wrażenie jakby słupki składały się z wielu cegiełek lub diod potencjometru.

(wykres ten dostępny jest w pliku Wykresy dla Zaawansowanych.xlsx Arkusz: ‘WdZ 8’.)

Jak widać na powyższych przykładach dzięki kilku prostym wykresom Excela można przedstawić dane na wiele ciekawych sposobów.

Jak daleko można się posunąć? Bardzo daleko, poniżej wykres XY Excela przedstawiający krzywą która jest śladem jaki pozostawia punkt okręgu toczący się wewnątrz większego okręgu tzw hipocykloida. Polecam włączenie animacji i przejrzenie kilku z ustawionych możliwości parametrów (przyciski Następny, Poprzedni), lub popróbowanie samemu. Ciekawostka.

Więcej na temat tej krzywej możesz przeczytać w wikipedii: http://pl.wikipedia.org/wiki/hipocykloida

Przygotowując wykresy musimy pamiętać o tym aby atrakcyjna forma nie przysłoniła przekazu, który jest najważniejszy we wszelkiego rodzaju analizach i prezentacjach. Możliwości ludzkiego postrzegania są ograniczone i dobrze dobrana forma ma ułatwiać zrozumienie danych i ich powiązań a nie starać się dowieść naszej wybitnej znajomości Excela czy dbałości o estetykę.

Więcej na ten temat w lekcji: ‘Profesjonalne raporty dla zaawansowanych’

Więcej na temat wykresów w Lekcji: ‘Wykres Przestawny’.