Wykresy dla Zaawansowanych

Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Wykresy dla Zaawansowanych.xls, tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji.

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

Przykład 1.

(Arkusz: ‘Wykresy dla Zaawansowanych 1’)

W 2 przykładzie będziemy korzystali z tych samych kategorii danych, ale dodatkowo mamy także dane planowe.

Zaczynamy w ten sam sposób.

Niestety tym razem Excelowi nie udało się wybrać najbardziej przejrzystego pokazania danych.

Aby zmienić sposób, w jaki prezentowane są dane, klikamy prawym klawiszem, serię danych, dla których sposób prezentacji chcemy zmienić i wybieramy opcję ‘Typ wykresu...’.

Zmieniamy typ na kolumnowy, zmiana będzie dotyczyła tylko 1 (wybranej serii danych). W ten sposób możemy łączyć na 1 wykresie różne typy prezentacji danych, ale nie wszystkie połączenia wyglądają dobrze. Najbardziej polecam łączenie wykresów warstwowych i liniowych, oraz właśnie kolumnowych i liniowych, przy 3 lub większej ilości typów wykresy stają się coraz bardziej nieczytelne dla każdego, z wyjątkiem autora.

Po zamianie typu wykresu na kolumnowy jeszcze raz klikamy go prawym klawiszem i wybieramy opcję ‘Formatuj serie danych...’.

W oknie ‘Formatuj serię danych, w karcie ‘Oś’ wybieramy opcję ‘Oś główna’.

Na skutek zmiany osi dla ‘Wyniku Sprzedaży’ na osi dodatkowej zostały tylko dane wyrażone w wartościach procentowych, co spowodowało zamianę skali znajdującej się po prawej stronie na procentową. Dzięki temu zaczęło być widać dane ‘Plan marża’ i ‘Wynik Marża’, które do tej pory były tak małe, że przylegały do osi X.

Zmieniamy teraz typ wykresu dla serii ‘Plan Marża’ na liniowy.

A później pozbywamy się ‘obszaru kreślenia’ i ‘znaczników’, pogrubiamy linie i zwiększamy skalę na osi głównej, zmieniamy kolory słupków i linii (dla planu najwłaściwszym kolorem wydaje się być kolor nadziei – zielony) oraz przenosimy i zmieniamy rozmiar legendy.

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

Przykład 2.

(Arkusz: ‘Wykresy dla Zaawansowanych 2’)

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

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 brandy w poniższej tabeli malejąco wg. kolumny ‘wzrost $’.

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 jest ‘wzrost $’ z kolumny F jest dodatni.

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.

Przygotowując wykres na podstawie tych danych zaznaczamy kolumny H, I oraz J tak jak jest to pokazane na poniższym rysunku.

Wybieramy drugi podtyp wykresu kolumnowego i klikamy przycisk ‘Zakończ’.

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

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

Dwa pojedyncze kliknięcia lewym klawiszem myszy spowodują wybranie tylko jednego punktu danych, kliknięcie go prawym klawiszem myszy pozwoli wybrać opcję ‘Formatuj punkt danych...’.

Ukrywamy wszystkie wartości bazowe poprzez wybranie opcji ‘brak’ dla Obramowania i Obszaru (osobno dla każdego punktu danych).

Kolejnym krokiem będzie dodanie etykiet danych do słupków bazy dla 2007 i 2008, w tym celu należy wybrać tylko jeden słupek klikając na niego dwukrotnie (z dłuższym odstępem, pomiędzy kliknięciami), po czym można kliknąć go prawym klawiszem i wybrać opcję ‘Formatuj punkt danych’ i zaznaczyć na karcie ‘Etykiety Danych’ opcję ‘Wartość’.

Formatujemy wykres zmieniając kolory słupków, zielone dla słupków obrazujących wzrosty i czerwone dla słupków obrazujących spadki.

Dodatkowo przeciągamy etykiety nad słupki dla dodatnich wartości i pod nie dla ujemnych.

Dla ujemnych wartości możemy też wstawić znaki minus w etykietach, ale wtedy nie będą one aktualizowane przez Excela.

Ostatnim krokiem w przygotowaniu tego wykresu będzie zmiana szerokości przerwy pomiędzy słupkami.

Jest to możliwe w menu: ‘Formatuj serię danych w karcie ‘Opcje’.

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

Ze względu na jasny przekaz wykres ten jest powszechnie stosowany w prezentacjach wyników sprzedaży, szczególnie prezentowanych dla dużych grup.

Przykład 3.

(Arkusz: ‘Wykresy dla Zaawansowanych 3’)

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 XY 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 Menu ‘Wstaw’ opcję ‘Wykres’ i w Kreatorze wykresów wybieramy wykres ‘XY (punktowy)’ znajdujący się na karcie ‘Standardowe typy’. Po dokonaniu wyboru możemy od razu kliknąć przycisk ‘Zakończ’.

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

Kasujemy legendę, szare tło i poziome kreski.

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

W oknie ‘Dodaj linię trendu’ na karcie ‘Typ’ wybieramy opcję ‘Liniowy’ a na karcie opcję zaznaczamy opcje: ‘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.

Przykład 4.

(Arkusz: ‘Wykresy dla Zaawansowanych 4’)

Aby utworzyć wykres typu XY z opisami punków niestety nie wystarczy rozszerzyć zakres o kolumnę z opisami. Jeżeli tak zrobimy Excel utworzy wykres taki jak poniżej. Po zatrzymaniu kursora myszki nad punktem wyświetli się komunikat z serią, nazwą punktu i danymi na podstawie których został utworzony.

Jednak przy próbie formatowania serii danych na karcie ‘Etykiety danych’ nie znajdziemy opcji pozwalającej wyświetlić nam nazwy punków.

Aby przygotować wykres typu XY z opisami punków należy zaznaczyć mały fragment tabeli z danymi (wystarczą 4 pola) i utworzyć wykres.

Dla utworzonego wykresu kasujemy legendę i poziome linie, po czym przechodzimy do ‘Danych źródłowych’.

Ograniczamy zakres warości X i wartości Y do 1 pozycji i jako nazwę serii wskazujemy pole B5.

Korzystając z przycisku ‘Dodaj’ tworzymy kolejne serie, z których każda składa się tylko z 1 punktu, a nazwą serii jest nazwa kraju występująca w kolumnie B.

Po utworzeniu serii pozostanie nam już tylko ujednolicenie wyglądu punktów i wybranie dla każdego z nich w oknie ‘Formatowanie Serii Danych’ na karcie ‘Etykiety Danych’ opcji ‘Nazwa Serii’.

Utworzenie takiego wykresu jest dość pracochłonne, ale po utworzeniu go raz możemy go wielokrotnie kopiować do innych dokumentów zmieniając jedynie ilość serii w zależności od potrzeb.

Przykład 5.

(Arkusze: ‘Wykresy dla Zaawansowanych 5’, ‘Wykresy dla Zaawansowanych 6’)

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 danych i wybrania z Menu ‘Wstaw’ à ‘Wykres’. Wybieramy kartę ‘Typy niestandardowe’ i wykres ‘Liniowo-kolumnowy (2 osie)’. Wciskamy przycisk ‘Dalej >’

Na kolejnym ekranie zmieniamy zaznaczenie ‘Serie w:’ z ‘Kolumny’ na ‘Wiersze’ i wciskamy przycisk ‘Zakończ’.

Wynik nawet dla osób obdarzonych dużą wyobraźnią nie przypomina termometru.

Kasujemy legendę, szare tło i dolną oś (zaznaczając te elementy i każdorazowo wciskając na klawiaturze przycisk ‘Delete’)

Formatujemy obie osie Y, wybierając:

- na karcie ‘Skala’, ‘Minimum’ 0 i ‘Maksimum’ 100.

- na karcie ‘Liczby’, ‘Kategorie:’ ‘Niestandardowe’ a w okno ‘Typ:’ wpisując ‘0C’ (zero nie literę O)

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

Formatujemy słupek danych, wybierając pogrubione obramowanie i opcję brak dla ‘Obszaru’.

Klikamy punkt danych prawym klawiszem i zmieniamy ‘Typ wykresu…’ na ‘Kolumnowy’

Nasz wykres zaczyna przypominać termometr.

Formatujemy niebieski słupek z danymi zmieniając na karcie ‘Opcje’ ‘Szerokość przerwy na 200. (lub inną wartość w zależności od własnego poczucia estetyki).

Pozostało już tylko zmienienie koloru ‘słupka rtęci’ np. na czerwony i zwężenie wykresu.

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 Cwiczenia.xls)

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 Cwiczenia.xls)

Przykład 6.

(Arkusze: ‘Wykresy dla Zaawansowanych 7’, ‘Wykresy dla Zaawansowanych 8’)

Wykres typu licznik (‘Speedometer Chart’) spotykany jest w periodycznych analizach przygotowywanych dla kierownictwa i często nazywach ‘dashboard’ 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ż n pierwszy rzut oka czy sytuacja jest dobra lub zła.

Zwykle zestawia się kilka takich wykresów dla różnych regionów/Brandów etc. na jednym slajdzie, 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 dane które mają być podstawą do tarczy licznika i na ich podstawie tworzymy wykres pierścieniowy. Po utworzeniu zwężamy obszar wykresu.

Kasujemy legendę.

Wybieramy największą (niebieską) część wykresu klimamy ją prawym klawiszem myszy, wybieramy polecenie ‘Formatuj punkt danych’

Na karcie ‘Opcje’ obracamy ją o 90 stopni, a na karcie ‘Desenie’ wybieramy brak obramowania i obszaru.

Wybieramy całą serię i dodajemy Etykiety danych wartość.

Kolorujemy poszczególne części wykresu wg potrzeb i ręcznie wpisujemy w etykietach wartości które mają tam być wyświetlane.

Ponieważ z reguły przygotowujemy wiele wykresów licznikowych z tą samą skalą rozwiązanie to choć ‘prymitywne’ wydaje się bardzo efektywne.

Istnieje sposób automatycznego wypełniania etykiet ale jest dość skomplikowany, po więcej informacji odsyłam na stronę JW.

Dodajemy 2 serie danych obejmującą zakres C3:C5

Wybieramy nową serie danych i zmieniamy typ wykresu na kołowy. Powoduje to chwilowe zakrycie tarczy naszego licznika.

Ostatnimi etapami pracy nad tym wykresem będą formatowanie serii danych wykresu kołowego tak aby 2 większe wycinki koła nie miały obszaru ani obramowania a najcieńsza część miała obszar w kolorze czarnym, dzięki czemu będziemy ją mogli wykorzystywać jako wskazówkę naszego licznika.

Jeśli chodzi o obramowanie i obszar wykresu to nie można go zmniejszyć tak aby dolna niepotrzebna nam część wykresu była poza nim, efekt taki można uzyskać wybierając brak obszaru i obramowania dla wykresu, po czym należy narysować wokół niego prostokąt (korzystając z Menu ikon Rysowanie à Prostokąt).

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 ten dostępny jest w pliku Cwiczenia.xls)

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 określony 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’.

Szkolenie Excel 2003 Szkolenie Excel 2007