Cieniowany wykres liniowy w Excelu – powyżej czy poniżej średniej

Bywają takie sytuacje, kiedy potrzebujemy porównania tak wielu danych, że stosowanie prostego wykresu kolumnowego – pomimo jego licznych zalet – byłoby trudne. Jednym z możliwych rozwiązań w tej sytuacji może być cieniowany wykres liniowy.

Dobrym przykładem takiej sytuacji niech będzie efektywność sprzedaży naszej firmy w porównaniu do średniej w danej branży, z danymi z każdego dnia:

Wykres ten w bardzo plastyczny i wyraźny sposób pokazuje, w jakich dniach szło nam ponadprzeciętnie, a kiedy zostajemy w tyle. Dzięki zestawieniu tych wiadomości z datami możemy pokusić się o znalezienie przyczyny tego stanu rzeczy.
Jak stworzyć tego typu wykres? Oto krótka instrukcja w paru krokach.

Krok 1: Przygotuj dane źródłowe

Przygotuj dane, jakie mają być przedstawione na wykresie. Muszą znajdować się tam:
– daty
– efektywność ( np. sprzedaży), produktywność (np. pracowników) lub inny współczynnik opisujący skuteczność działania naszej firmy
– wartość do której porównujemy – np. średnia efektywność sprzedaży czy też średnia produktywność w danej branży, czy też inny współczynnik.

Można skorzystać z danych zawartych w przykładowym pliku Excela zamieszczonym pod tekstem (dane na temat efektywności sprzedaży firmy XYZ w porównaniu do średniej branżowej).

Dane powinny zostać uporządkowane w następujący sposób:

Krok 2: Opracuj swe dane

Tak naprawdę wykres zbudowany jest z 4 serii danych. Jakich? Zrozumieć to pomoże poniższy obrazek:

Kolumnę Minimum tworzymy korzystając z funkcji MIN, zwracającą najmniejszą liczbę z wybranego zbioru.

Zieloną część wykresu (czyli tę, gdzie efektywność naszej firmy jest większa niż średnia, czyli zielone pole jest poniżej linii oznaczającej skuteczność naszej firmy) w oparciu o kolumnę „Zielona”, w której wykorzystujemy funkcję JEŻELI, aby odrzucić wartości ujemne. Formuła powinna wyglądać następująco:

=JEŻELI(Średnia<Efektywność;Efektywność-Średnia;”brak”)

Czerwoną część wykresu (ta, gdzie efektywność naszej firmy jest mniejsza niż średnia branżowa – czerwone pole jest więc powyżej linii oznaczającej skuteczność naszej firmy) tworzymy analogicznie jak zieloną część, zamieniając jedynie kolejność argumentów:
=JEŻELI(Efektywność<Średnia;Średnia-Efektywność;”brak”)

Uwaga: Cudzysłów w powyższej formule informuje program, że w danym miejscu znajduje się wartość tekstowa, a nie żadna liczba bądź funkcja. W przypadku braku cudzysłowu zamiast słowa „brak” w komórce wyświetli się błąd „#NAZWA?”.

Linię efektywności sprzedaży naszej firmy tworzymy na podstawie danych z kolumny Efektywność. Ważnym jest, aby zrozumieć istotę tych przekształceń oraz anatomię wykresu.

Krok 3: Stwórz skumulowany wykres warstwowy

Zaznacz kolumny, o których mowa w powyższym punkcie, i stwórz skumulowany wykres warstwowy.

Powinno to wyglądać podobnie jak na obrazku poniżej:

Nie zapomnijcie zmienić etykiet osi poziomej na daty.

Krok 4: Opracuj wykres

W pierwszej kolejności zaznaczamy pole odpowiadające za przedstawienie efektywności i zmieniamy typ wykresu na liniowy:

Następnie należy kliknąć prawym klawiszem na serię Minimum, wybrać opcję Formatuj serię danych. Pojawi się okno dialogowe – wybieramy w nim zakładkę Wypełnienie, i zaznaczamy opcję Brak wypełnienia. Po tym zabiegu nasze dzieło będzie wyglądać podobnie jak na obrazku poniżej:

Uwaga: Wszelkie opcje typu Formatuj…, oprócz kliknięcia prawym klawiszem i wybrania z rozwijającego się menu, możemy wybrać także poprzez zaznaczenie interesującego nas obiektu i naciśnięcie klawiszy CTRL + 1.

Teraz zostało tylko zmienić kolory – pola powyżej efektywności na czerwono, poniżej – na zielono. Dokonujemy tego w ten sam sposób, co nadawanie przezroczystości serii Minimum, z tą różnicą, że zamiast opcji Brak wypełnienia wybieramy Wypełnienie pełne, a następnie określamy interesujący nas kolor. Nasz wykres jest już bardzo zbliżony do efektu, jaki chcemy osiągnąć:

Przystępujemy do ostatecznej obróbki. W pierwszej kolejności polecamy zająć się legendą – trzeba wyrzucić z niej napis oznaczający serię minimum oraz zmodyfikować podpisy pozostałych serii. Używamy prawego klawisza myszy na wykresie, wybieramy Zaznacz dane , następnie wybieramy interesującą nas serię danych, klikamy w przycisk Edytuj i wpisujemy pożądaną nazwę w odpowiednie pole okna dialogowego:

Następnie warto zmienić kolor liniowego wykresu efektywności na czarny – w końcu to ten wskaźnik jest tutaj najważniejszy, a czarny kolor silnie przyciąga uwagę odbiorcy:

Pora na zajęcie się osiami oraz ich opisami. Należy zmienić ich kolor na szary, aby nie odwracały uwagi od danych (mogliście już o tym przeczytać tutaj). Kolor opisów można zmienić poprzez zaznaczenie osi i kliknięcie na przycisk na pasku narzędzi, który odpowiada za zmianę koloru również wpisów w komórkach:

Co z kolorem osi?
Automatycznie powinien ustawić się kolor szary. Jeżeli tak nie jest, można zmienić to poprzez kliknięcie prawym klawiszem na oś, wybranie opcji „Formatuj oś”, a następnie w zakładce „Kolor linii” wybieramy opcję „Linia ciągła” i interesujący nas kolor.
Krótkie spojrzenie na opis poziomej osi pozwala dostrzec jego niedoskonałości :

– Daty urywają się 25.03, podczas gdy dane mamy do dnia 31.03.
– Interwał między datami mógłby być dłuższy – wpłynęłoby to korzystnie na czytelność wykresu.

Pierwszy mankament usuwamy, klikając prawym klawiszem na oś, następnie z menu kontekstowego wybieramy opcję Formatuj oś. Tutaj, w zakładce Opcje osi, zmieniamy wartość maksimum z automatycznej na stałą – w naszym przypadku jest to 31.03.2013 – do tego dnia posiadamy dane.
Zbyt małego interwału pomiędzy kolejnymi datami pozbywamy się zmieniając wartość Jednostki głównej w tym samym oknie dialogowym. W zależności od potrzeb można tam zmienić jednostkę z dni na miesiące, a nawet lata oraz ustalić, ile danych jednostek ma wynosić interwał. W naszym przypadku sensowną wartością wydaje się 15 dni, ale zachęcamy do eksperymentowania – można ustawić interwał np. co jeden miesiąc.

Nasz wykres jest już prawie gotowy:

Pozostaje nam zadecydować, co zrobić z liniami siatki.

Linie siatki są pozostałością starych czasów, kiedy wykresy były rysowane na kartkach w kratkę (wzmiankę na ten temat znajdziecie tutaj). W tym przypadku pionowe linie siatki mogą korzystnie wpłynąć na szybkość czytania wykresu, dlatego też warto rozważyć ich użycie.

Końcowy efekty powinien przypominać powyższy wykres.

Można stworzyć kilka wariacji powyższego wykresu – do wyboru w zależności od indywidualnych wrażeń estetycznych:
• Wariacja nr 1 – wykres warstwowy po jednej stronie osi poziomej:

Kolor w połączeniu z legendą mówi nam, w których okresach średnia branżowa była powyżej efektywności naszej firmy, a kiedy poniżej. Jak wykonać taki wykres? W dokładnie taki sam sposób jak poprzedni, tylko bez serii minimum. Pozostałe serie danych są tworzone w sposób identyczny jak przy wykresie cieniowanym. Ponadto zamiast wykresu warstwowego skumulowanego tworzymy zwykły wykres warstwowy.

Wariacja nr 2 – wykres kolumnowy po jednej stronie osi poziomej:

Tak jak wykres poprzedni – bez serii minimum, różni się jedynie tym, że zamiast wykresu warstwowego użyty jest wykres kolumnowy. Aby pozbyć się przerw pomiędzy poszczególnymi kolumnami, należy kliknąć na nie prawym klawiszem, wybrać opcję „Formatuj serię danych”, a następnie przy pomocy suwaków ustawić szerokość przerwy na 0%, a nakładanie serii na wartość maksymalną, czyli 100%:

Wariacja nr 3 – wykres kolumnowy po obu stronach osi poziomej

Do stworzenia tego typu wykresu potrzebujemy stworzyć nową kolumnę danych. Dokonujemy to przy pomocy prostej formuły

= efektywność – średnia

Następnie tworzymy wykres kolumnowy na podstawie danych ze stworzonej kolumny, oraz liniowy na podstawie kolumny efektywność. Wykres w tym momencie będzie wyglądał tak:

Podobnie jak w poprzedniej wariacji, zmieniamy Szerokość przerwy oraz Nakładanie serii. Jak zrobić, aby jedna seria danych miała dwa kolory? Twórcy Excela przewidzieli taką potrzebę – wystarczy wejść w opcję „Formatuj serię danych” po kliknięciu prawym klawiszem na kolumny i w zakładce Wypełnienie zaznaczyć opcję Wypełnienie pełne oraz Odwróć, jeśli ujemne – w ten sposób wybierzemy dowolne kolory oddzielnie dla kolumn nad oraz pod osią.

Następnie opracowanie wyglądu wykresu: zmienienie kolorów opisu osi, zmiana interwałów pomiędzy opisami osi poziomej, dodanie pionowych linii siatki, zmiana koloru wykresu liniowego efektywności na czarny.

Aby usunąć liczby ujemne na osi pionowej, klikamy na nią prawym klawiszem oraz wybieramy opcję „Formatuj oś”. W zakładce „Liczby”, w oknie „Kategorie” wybieramy „Niestandardowe”, a następnie wybieramy typ „0;;0”.

Daty na osi poziomej odsuwamy otwierając analogiczne okno formatowania dla osi poziomej, aby w zakładce „Opcje osi”, obok napisu „Etykiety osi” wybieramy opcję „Nisko”.

Efekt końcowy powinien być podobny do wykresu zaprezentowanego na początku tego podpunktu.

Wariacja nr 4 – Wykres warstwowy po obu stronach osi poziomej

Do stworzenia tego typu wykresu potrzebujemy dwóch kolumn nowych danych – przyczyną takiego stanu rzeczy jest to, że nie przewidziano w programie opcji Odwróć, jeśli ujemne dla wykresu warstwowego.

Możemy je stworzyć np. przy pomocy funkcji jeżeli – dla jednej kolumny będzie to formuła:

=JEŻELI(Efektywność>Średnia;Efektywność-Średnia;”brak”)

Znajdą się w niej tylko wartości dodatnie – czyli dni, w których efektywność naszej formy jest większa niż średnia branżowa. Formuła dla drugiej kolumny, która będzie się składać tylko z wartości ujemnych, różni się jedynie znakiem nierówności:

=JEŻELI(Efektywność<Średnia;Efektywność-Średnia;”brak”)

Estetycznej obróbki wykresu dokonujemy podobnie jak w poprzednich przypadkach.

Istotną wadą tego typu wykresu jest to, że zielone i czerwone pola mogą zajmować wspólną część osi poziomej – co w rzeczywistości nie ma logicznego odpowiednika. Spowodowane jest to tym, iż wykres warstwowy nie może kończyć się nagle pionową kreską, urywać się, lecz jest sprowadzany do osi zera stopniowo.

Wariacja nr 5 – Wskaźnik „lepiej / gorzej” po jednej stronie osi

Czerwone i zielone linie są tak naprawdę kolumnami o tej samej wysokości. Do ich stworzenia potrzebujemy stworzyć nową serię danych – w tym celu używamy formuły ZNAK.LICZBY, która zwraca wartość [-1] lub [1], w zależności od znaku argumentu. Równanie będzie miało więc postać:

=(ZNAK.LICZBY(Efektywność-Średnia))*10

Liczbę 10 dodajemy w celu zwiększenia wysokości wszystkich kolumn. Następnie na podstawie utworzonej danych tworzymy wykres kolumnowy. Dwa kolory jednej serii uzyskujemy dzięki wspomnianej wcześniej opcji Odwróć, jeśli ujemne.

Wariacja nr 6 – Wskaźnik „lepiej / gorzej” po jednej stronie osi

Jak łatwo się domyślić, także w tym przypadku linia składa się tak naprawdę z kolumn – rozróżnienie jej na 2 kolory jest możliwe dzięki 2 oddzielnym seriom danych. Czerwoną serię (czyli tam, gdzie efektywność formy jest mniejsza niż przeciętna branżowa) tworzymy na podstawie kolumny danych stworzonej na podstawie formuły:

=JEŻELI((Efektywność-Średnia)<0;10;”brak”)

Zieloną serię tworzymy przy pomocy formuły analogicznej, z zamienionym na przeciwny znakiem nierówności:

=JEŻELI((Efektywność-Średnia)>0;10;”brak”)

Wariacja nr 7 – Kolorowy wskaźnik efektywności

Na pierwszy rzut oka widać, że wykres ten opiera się na 2 seriach danych, dla których został stworzony wykres liniowy. Cała trudność polega na stworzeniu tych serii – dla linii czerwonej tworzymy kolumnę na podstawie formuły

=JEŻELI(Efektywność<Średnia;Efektywność;)

Dla linii zielonej analogicznie:

=JEŻELI(Efektywność>Średnia;Efektywność;)

Wszędzie tam, gdzie funkcja Jeżeli nie przejdzie testu logicznego, trzeba ją ręcznie skasować – inaczej Excel automatycznie wstawi w puste pola wartość 0, a nasz wykres będzie wyglądał wtedy tak:

Ponadto, aby wykres był ciągłą linią (nie było przerw pomiędzy czerwonymi a zielonymi liniami szerokości odpowiadającej jednemu dniu na osi poziomej), należy ręcznie stworzyć dane tak, aby linie o różnych kolorach zaczynały się i kończyły w jednym punkcie. Efekt ten uzyskujemy, opracowując dane „na zakładkę”, tak jak pokazuje rysunek: