Wykresy dla Ekspertów

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

Przed pracą z tą lekcją należy przerobić lekcje ‘Wykresy Podstawy’ i ‘Wykresy dla Zaawansowanych’.

Operacje wytłumaczonych w tamtych lekcjach nie są tłumaczone ponownie tutaj.

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ę.

Przykład 1.

(Arkusz: ‘WdE 1’, ‘WdE 2’, ‘WdE 3’)

Wykres typu licznik (nazywany też speedometer chart lub wykresem tachometrycznym) spotykany jest w periodycznych analizach przygotowywanych dla kierownictwa i często nazywanych ‘dashboards’ (daszbordami) co w dosłownym tłumaczeniu oznacza tablicę rozdzielczą. 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.

W tym przykładzie dane w kolumnie B utworzą Tarczę licznika, dane w kolumnie C strzałkę. Zmienia się jedynie dana w komórce D4, a jej zakres zmienności to od -10% do +10%.

Oczywiście na Wasze potrzeby możecie dowolnie modyfikować zarówno tarczę licznika jak i wygląd czy szerokość stzałki.

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

Kasujemy legendę i Tytuł wykresu.

Wybieramy największą część wewnętrznego pierścienia (dwa pojedyncze kliknięcia na niej lewym klawiszem myszy) i po kliknięciu prawym przyciskiem myszy wybieramy dla niej ‘Brak wypełnienia’.

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, jeśli pojawią się linie wiodące do etykiet kasujemy je.

Następnie klikamy prawym przyciskiem na wewnętrznym pierścieniu i wybieramy polecenie ‘Formatuj serię danych…’.

W oknie ‘Formatowanie serii danych’ zmieniamy ‘Rozmiar otworu pierścienia’, proponuję ustawić 50%.

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 wybrać jedynie poprzez kliknięcie 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.

Powiększamy wykres aby jego elementy były dobrze widoczne.

Grubość strzałki naszego wykresu zależy od tego co jest w komórce C4, jeżeli uznamy że strzałka (w rzeczywistości wycinek koła z wykresu kołowego) jest za wąska możemy zmienić jej szerokość, proponuję wprowadzić wartość 0,07.

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

Ponieważ ramka wykresu nie wygląda dobrze – licznik zajmuje tylko górną część wykresu i dolnej nie potrafimy się pozbyć, pozbywamy się obramowania wykresu.

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 i opisy na wykresie.

Alternatywny sposób formatowania przedstawia drugi z liczników pokazanych w tym samym arkuszu.

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 tym samym pliku w arkuszu ‘WdE 2’.

Zamiast robić podobny od początku proponuję użyć i ewentualnie zmodyfikować ten już przygotowany.

Możemy też wstawić do arkusza zdjęcie licznika i dodać na jego tle wyłącznie wycinek wykresu kołowego, który będzie zmieniał się w zależności od wartości wejściowych.

Przed wstawieniem ze zdjęcia w programie graficznym musimy usunąć oryginalną strzałkę.

W przypadku zdjęć będziemy musieli dopasować wielkość wykresu do zdjęcia i metodą prób i błędów dobrać formułę do skali na zdjęciu.

Jeżeli zdjęcie nie jest wykonane dokładnie na wprost tarczy licznika, ale pod kątem, nie uda się nam uzyskać formuły idealnie dopasowanej do takiej tarczy.

Poniżej przedstawiony przykład znajduje się w arkuszu ‘WdE 3’.

Przykład 2.

(Arkusz: ‘WdE 4’, ‘WdE 5’)

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.

Jest to wykres słupkowy z 2 seriami danych w których każda zawiera tylko jedną daną. Każda z serii jest na innej osi więc jedna pokazywana jest za drugą.

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

Na przygotowanym wykresie kasujemy poziome linie siatki i po kliknięciu go prawym przyciskiem myszy wybieramy polecenie ‘Zaznacz dane…’.

Dodajemy drugą serię danych według poniższych rysunków.

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

Po czym zmieniamy jej 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 wyższego ze słupków ustalamy tło brak, obramowanie czarne, po czym zmniejszamy wielkość ‘Szerokość przerwy’ dzięki czemu słupek będzie wystawał na boli poza mniejszy słupek.

Możemy jeszcze sformatować liczby na obu osiach aby po liczbie następował znak stopni Celsjusza.

Wybieramy Kategorię ‘Niestandardowe’, wpisujemy kod formatu ‘0 C’ po czym klikamy przycisk ‘Dodaj’.

Po zwężeniu wykresu i zmniejszeniu szerokości przerwy dla obu serii uzyskamy 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 tym samym arkuszu poniżej.

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.

Przykład 3.

(Arkusz: ‘WdE 6’)

Diagram Gantta nazywany także Wykresem Gantta to graf stosowany głównie w zarządzaniu projektami. Dzieli on projekt na etapy pokazane graficznie.

Część z etapów może być wykonywana jednocześnie lub niezależnie od innych, jednak z reguły dla większości konieczne jest ukończenie jednego etapu zanim będzie możliwość rozpoczęcia kolejnego.

Zadania pogrupowane są na etapy a po ich zakończeniu zazwyczaj występują kamienie milowe (Mile Stones) będące oznaczeniem zamknięcia ważnego etapu.

Istnieją wyspecjalizowane programy do projektowania i przeprowadzania procesów biznesowych np. MS Project. Jeżeli projektowanie i prowadzenie projektów nie jest naszym głównym zadaniem Excel także może być pomocny.

Zwyczajowo stosuje się poniższe oznaczenia:

W tym przykładzie przygotujemy diagram Gantta na przykładzie procesu opisującego przygotowanie katalogu produktów.

Pierwszym krokiem będzie wyliczenia długości trwania każdego z etapów.

Należy pamiętać że nie wystarczy odjąć od siebie 2 dat ale jeszcze trzeba dodać 1.

W przeciwnym razie etap trwający 1 dzień od 4 sierpnia do 4 sierpnia miałby długość 0, a pozostałe okresy byłyby o 1 dzień za krótkie.

Następnie zaznaczamy kolumnę z opisami etapów, daty rozpoczęcia i długość trwania etapów wraz z nagłówkami tych kolumn.

Wstawiamy wykres Słupkowy skumulowany.

Wykres powiększamy, a w oknie a na karcie ‘OPCJE OSI’ zaznaczamy opcję ‘Kategorie w kolejności odwrotnej.

Kasujemy legendę.

A dla osi poziomej ustalamy Minimum tak aby pierwszy z etapów rozpoczynał się na początku wykresu. Ponieważ data pokazana jest jako liczba może to wymagać kilku prób.

Na karcie ‘WYRÓWNANIE’ obracamy daty dzięki czemu będą badziej czytelne

Zagęszczamy etykiety na karcie OPCJE OSI, w tym przypadku jako Jednostkę główną obrałem 4 dni.

Wprowadzamy tytuł wykresu.

Dla konturu i wypełnienia pierwszej serii danych wybieramy opcję ‘brak wypełnienia’.

Zmniejszamy szerokość przerwy, tu wybrałem 75%.

Wstawiamy kształty rąbów w punktach gdzie znajdują się kamienie milowe. Dodajemy w tych punktach etykiety zawierające daty.

Oznaczamy kolorami wybrane etapy uzyskując ostateczny wygląd wykresu.

Rzeczywiste procesy biznesowe pokazywane na wykresach Gantta są znacznie bardziej złożone.

Podsumowania proponuję dodawać jako obrazki już po zakończeniu przygotowywania wykresu.

Przykład 4.

(Arkusz: ‘WdE 7’)

W tym przykładzie przedstawię wykres bąbelkowy. Znalazł się on pod koniec lekcji Wykresy dla Ekspertów nie ze względu na to że jest szczególnie trudny, ale dlatego że uważam iż dane pokazane na takim wykresie mogą być błędnie interpretowane co bardziej szczegółowo opisałem w lekcji ‘Profesjonalne Raporty dla Zaawansowanych’.

Jednak nawet mi zdarzało się taki wykres przygotowywać gdy otrzymałem takie zamówienie z centrali firmy, warto wtedy zrobić to dobrze.

Poproszono nas o przygotowanie wykresu bąbelkowego pokazującego Indeksy Ewolucji (IE) sprzedaży wartościowej i w sztukach dla 16 największych firm na rynku.

Pierwszym krokiem będzie wyliczenie indeksów ewolucji. Mając zmiany sprzedaży skorzystamy ze wzoru: IE = (zmiana sprzedaży firmy +1) / (zmiana sprzedaży na rynku +1) * 100.

Alternatywny sposób wyliczania IE wykorzystujący udziały rynkowe jest opisany na końcu tego przykładu.

To samo powtarzamy dla indeksu ewolucji sprzedaży wartościowej.

Wykres bąbelkowy standardowo przyjmuje jako wielkość bąbli ostatnią z kolumn, wygodnie więc będzie przenieść Sprzedaż na koniec tabeli.

Jeśli zaznaczymy całą kolumnę zawierającą Sprzedaż i przeciągniemy ją na koniec tabeli przytrzymując Shift nie będziemy musieli kasować pustej kolumny po niej.

Następnie zaznaczamy wyłącznie dane dla analizowanych firm, bez opisów i bez danych dla rynku.

Z karty ‘WSTAWIANIE’ wybieramy wykres punktowy i pierwszy z podtypów wykresu Bąbelkowego.

Powiększamy wykres i kasujemy linie siatki.

Następnie wybieramy oś poziomą i na karcie ‘OPCJE OSI’ ustawiamy ‘Przecięcie z osią pionową’ na poziomie ‘Wartość osi 100, oraz ‘ETYKIETY’, ‘Pozycja etykiety’ – Nisko.

Ustawienie pozycji etykiet będzie użyteczny gdy sformatujemy drugą z osi.

Dla osi pionowej wybieramy te same ustawienia.

Wybraliśmy w obu przypadkach przecięcie osi dla wartości 100 ponieważ IE powyżej 100 oznacza zmiany sprzedaży lepsze niż dla całego rynku, a poniżej mniejsze. Osi tych użyjemy do pogrupowania firm na 4 segmenty, o czym będzie później.

Gdy bąble nachodzą na siebie w wielu przypadkach wykres jest mniej czytelny, proponuję więc zmniejszyć standardową wartość ‘Skaluj rozmiar bąbelków do’ z 100 na 60.

Dodajemy etykiety danych, którymi będą ‘Wartości z komórek’ i tu zaznaczamy obszar w którym wpisane są nazwy firm. Następnie odznaczamy automatycznie dodane ‘Wartości Y’.

Excel nie dobiera skali idealnie dla Firmy 4 tuż pod tytułem wykresu bąbelek został ucięty.

Proponuję nieco zwiększyć skalę osi pionowej oraz zaznaczyć innym kolorem naszą firmę.

Dodatkowo będziemy potrzebowali miejsca na opisy dla ćwiartek więc nieco zwiększymy też maksimum dla osi poziomej.

W kolejnym kroku dodajemy tytuł oraz opisy dla ćwiartek w polach tekstowych.

Dochodzimy do ostatecznej formy wykresu.

Interpretacja wyników zależy od strategii przyjętej przez firmę.

Na pewno lepiej być w 2 górnych ćwiartkach niż w 2 dolnych. Intuicyjnie najlepszy wydaje się wzrost udziału jednocześnie w sztukach jak i wartościach, jednak jeśli firma wprowadziła nowe produkty upper-mass lub premium i to ich sprzedaż pozwoliła zwiększyć udział w rynku – wynik w lewej górnej ćwiartce będzie jak najbardziej oczekiwany.

Warto też zauważyć że różnice zmiany wyników pomiędzy firmami nie są duże i że rynek był w badanym okresie wyjątkowo stabilny.

Wykres bąbelkowy ma swoje ograniczenia, np. dla firm 7 i 5 bąbelki prawie całkowicie nachodzą na siebie i nie zmienimy tego nawet znacznie zmniejszając skalowanie bąbelków.

Na wykresie tym można by jeszcze poprzesuwać nazwy firm aby nie nachodziły na bąbelki.

Wspomniany wcześniej alternatywny sposób na wyliczanie indeksu ewolucji polega na podzieleniu aktualnego udziału rynkowego przez ten z przed roku (lub innego okresu z którym porównujemy).

W naszym przykładzie najpierw będziemy musieli uzyskać wartości które do tego posłużą.

Zacznijmy od dodania 3 kolumn w których wyliczymy udziały rynkowe i sprzedaż z poprzedniego roku.

Sprzedaż z poprzedniego roku wyliczamy dzieląc obecną sprzedaż przez 1+zmiana sprzedaży.

Następnie wyliczamy udziały rynkowe.

Wreszcie możemy wyliczyć indeks ewolucji sprzedaży wartościowej na podstawie udziałów rynkowych.

Oczywiście wynik musi być dokładnie taki sam jak wtedy gdy korzystamy wyłącznie ze zmian sprzedaży.

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.

Plik z tym wykresem (hipocykloida.xls) znajduje się w folderze z ćwiczeniami do tego kursu. Polecam włączenie animacji i przejrzenie kilku z ustawionych możliwości parametrów (przyciski Następny, Poprzedni), lub popróbowanie samemu zmieniając parametry krzywej. Plik będzie działał poprawnie gdy makra są włączone, o tym jak włączyć makra można przeczytać w pierwszej lekcji poświęconej VBA.

Plik nie jest mojego autorstwa znalazłem go w Internecie.

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’.