Czyli praktyczne wykorzystanie słupków błędów na wykresie

Jest wiele sytuacji, jakie chcemy przedstawić na wykresie, natomiast jak zaczniemy się zabierać do dzieła, to okazują się one niemożliwe. Przynajmniej na pierwszy rzut oka. I tak w tygodniu przed świętami otrzymałam dwa totalnie różne pytania, które posiadały jednak bardzo podobną excelową odpowiedź. Pomyślałam, że skoro kilka osób w tym samym czasie tego potrzebowało, to może jeszcze komuś z Was może się to przydać, więc napisałam o tym wpis.

Jedno pytanie padło od czytelnika, a drugie od uczestników szkolenia o dashboardach, które prowadziłam. Czytelnik chciał wiedzieć jak na wykresie porównać liczbę zgłoszeń do określonego fachowca, w podziale na miasta, do łącznej liczby zgłoszeń. Uczestników szkolenia natomiast interesowało w jakim stopniu pewni wykonawcy są zależni finansowo od ich firmy, i to w podziale na lata.

Każdy chciał upiec dwie pieczenie przy jednym ogniu i pokazać dwa wykresy na jednym wykresie. W każdym przypadku nie było to możliwe tradycyjnymi metodami. Dla każdego jednak wystarczyło zastosować pewien sprytny wykresowy trik, który postanowiłam dla Was opisać w tym wpisie.

Na tapetę wzięłam sytuację, gdy interesuje nas liczba zgłoszeń do wybranych fachowców w różnych miastach: krawcowej, hydraulika, stolarza i fryzjera. Tę liczbę zgłoszeń chcemy docelowo porównać do liczby wszystkich zgłoszeń do takich fachowców. Czyli docelowo chcielibyśmy otrzymać taki wykres:

Wykres wynikowy

W tym wpisie pokażę Wam, jak to zrobić 🙂

Formatka to zwykła tabelka dzieląca fachowców na lokalizacje (miasta) i wygląda następująco:

Formatka

Ważne jest tutaj to, że ogólna liczba zgłoszeń nie jest prostą sumą zgłoszeń do naszych fachowców. Nie wystarczy więc po prostu wyświetlić jej w dodatkowej etykiecie. Idealnie więc byłoby uzyskać wykres, który porównuje liczbę zgłoszeń w podziale na lokalizację do łącznej liczby zgłoszeń. Czyli coś takiego:

Idealne rozwiązanie

Problem jest jednak taki, że rozwiązanie to łączy w sobie 2 typy wykresów: kolumnowy grupowany i kolumnowy skumulowany. A niestety tych dwóch typów Excel nie pozwala nam narysować na jednym wykresie ;(. Chce je połączyć w jeden wykres, a szkoda. Musimy sobie więc poradzić nieco inaczej. Konkretnie uzyskamy taki efekt, jak pokazywałam na początku wpisu. Nie jest to idealnie to, co na rysunku powyżej, ale ma taki sam sens i, co ważne, można to zrobić na jednym wykresie. Trzeba jednak nieco pokombinować… :). O tym kombinowaniu będzie oczywiście ten wpis.

Krok 1. Tworzenie wykresu

Zacznijmy od najprostszego, czyli w ogóle od stworzenia wykresu. Żeby było co przerabiać. Czyli:

1. zaznacz dane (B3:B8 i D3:G8)

2. utwórz wykres kolumnowy skumulowany (Wstawianie/ Wykres kolumnowy/ Skumulowany)

W wyniku otrzymasz coś takiego (kolory mogą się różnić – są zależne od schematu kolorów w Twoim Excelu):

Wykres zaraz po utworzeniu

Na tym wykresie jest wiele niedoróbek i teraz będziemy je likwidować.

Krok 2. Niezbędne przeróbki

Po pierwsze chcemy, aby na oddzielnej kolumnie były zawody, nie miasta. Żeby to zrobić zastosujemy bardzo prosty myk. Po zaznaczeniu wykresu, w menu Projektowanie kliknij przycisk Przełącz wiersz/ kolumnę. Otrzymasz coś takiego:

Wykres po przełączeniu wiersz/kolumna

Wow! Jeszcze gorzej :). Ale jakby się temu bliżej przyjrzeć, to wystarczyłoby tylko odciąć żółtą serię danych i już połowa pracy zrobiona, ponieważ pozostałe serie danych są ok. Samo odcięcie tej serii jednak nie wystarczy, ponieważ przecież chcemy ją przedstawić na wykresie, tylko nie w ten sposób. Zamieńmy więc typ wykresu żółtej serii danych (liczba wszystkich zgłoszeń) na liniowy (albo na punktowy – też będzie OK). Aby to zrobić, należy:

1. zaznaczyć ją (nie jest to konieczne, ale wygodne)

2. kliknąć na nią prawym przyciskiem myszy i z menu kontekstowego wybrać Zmień typ wykresu seryjnego. Pojawi się następujące okienko (od wersji 2013), w którym należy zmienić typ wykresu dla liczby zgłoszeń (nasz żółty) i wybrać liniowy (lub liniowy ze znacznikami od razu).

Zmiana typu wykresu liczby zgłoszeń

Uwaga! W zależności od wersji Excela te kroki mogą wyglądać nieco inaczej (ja korzystam tutaj z 2013). Koncepcja jednak będzie taka sama w każdej z nich.

Po zatwierdzeniu otrzymujemy coś takiego:

Zmieniony typ wykresu

Nadal źle, ale już nie tak bardzo. Przynajmniej widać, że żółte są wyżej niż kolumny. Jednak nie potrzebujemy tej żółtej kreski nad danymi. Przydałyby się tylko punkty, a najlepiej kolumny, które są tak wysokie, jak te żółte punkty „przegięcia”.

Ok. Pozbądźmy się zatem żółtej linii i zastąpmy ją znacznikiem (przy okazji zmienimy kolor na fioletowy). Aby to zrobić:

1. Zaznacz żółtą linię i wejdź do jej opcji (np. Ctrl + 1).
Uwaga! Znów w zależności od wersji Excela okienka będą się różnić, jednak każde zawiera te same opcje.

2. W okienku, które się pojawi ustaw linię na brak linii

Ustawienia linii

3. Teraz w ustawieniach znacznika, wybierz typ (np. linia czy kwadrat), kolor wypełnienia i obramowania (u mnie: fioletowy) oraz szerokość obramowania 3 pt:
PS. Te ustawienia dostosuj wg swoich upodobań, ja podałam przykładowe. Można równie dobrze w ogóle nie wyświetlać znacznika.

Ustawienia znacznika

Efekt będzie taki (po usunięciu osi pionowej, linii siatki i przerzuceniu legendy na prawą stronę i zmianie tytułu wykresu):

Znaczniki po przeróbkach

W sumie to nawet na tym etapie ten wykres można byłoby zostawić – moim zdaniem jest ok, ponieważ pokazuje to, co ma pokazywać.

Żeby jednak uzyskać efekt kolumn pokazujących łączną liczbę zgłoszeń – należy zastosować jeszcze jeden trik. Są to słupki błędów.

Krok 3. Słupki błędów jako kolumny podsumowujące

Tak, oto cały sekret. Słupki błędów (what?!;)) to bohaterowie naszego wykresu, a moją niekwestionowaną mistrzynią ich użycia jest Leila Gharani (znaleźć ją możesz tutaj). Na szczęście nie musimy się wgryzać w sens ich istnienia ;), ważne, żeby wiedzieć jak je wykorzystać do naszych potrzeb. Najpierw je dodajmy, a potem oczywiście będziemy modyfikować.

Dodajemy je do wykresu liniowego, czyli:

1. zaznacz wykres liniowy (fioletowe kwadraciki)

2. z plusika obok wykresu (wersje 2013 i wyższe, menu Układ w niższych wersjach Excela) wybierz Słupki błędów i od razu wejdź do ich ustawień (np. zaznacz wybrany i Ctrl + 1), i tam wybierz:

a) kierunek: minus

b) styl końca: bez zakończenia

c) wielkość błędu: niestandardowa, kliknij Określ wartość i wskaż zakres komórek, które mówią o łącznej liczbie zgłoszeń (D8:G8)

Ustawienia słupków błędów

Teraz troszkę formatowania dla słupków, czyli niech będą linią ciągłą, nadajmy im kolor (fioletowy) i szerokość (np. 7 pt). MOżna jeszcze dorzucić etykiety danych (np. do łącznej liczby zgłoszeń).

Po tych wszystkich zmianach nasz wykres jest już wreszcie gotowy i wygląda tak:

Wykres wynikowy

Moim zdaniem super i mam nadzieję, że Ci pomogłam 🙂

Oraz film: