Jedną z technik wykorzystania koloru jest wyróżnienie wartości negatywnych, oznaczających np. stratę, przekroczenie budżetu kosztowego czy odpływ klientów. Najprostszym sposobem na ich oznaczenie w Excelu jest ręczne sformatowanie każdego punktu danych, jednak jest to działanie na krótką metę. Jak zautomatyzować kolorowanie punktów? Oto 2 sposoby: dla zwykłego wykresu kolumnowego oraz punktowego, przez swój wygląd nazywanego lizakowym (ang. lollipop chart). Przy okazji poznamy też sposób na formatowanie komórek i osi.

Przygotowanie danych w programie Excel

Zaczynamy od prostej serii danych, która będzie stanowić podstawę naszych wykresów. Poprzez formatowanie komórek już w tabeli możemy wyróżnić kolorem dodatnie i ujemne elementy, wykorzystując nazwy kolorów umieszczone w polu Typ formatowania (pierwszy wpis oznacza wartości dodatnie, potem jest średnik, następnie wartości ujemne, średnik i formatowanie wartości = 0). Innym sposobem jest wykorzystanie formatowania warunkowego.

Dwukolorowy wykres kolumnowy w Excelu

Wstawiamy zwykły wykres kolumnowy i widzimy, że oś pionowa przejęła kolory czcionek. Ekstra!

Aby zwiększyć czytelność wykresu przenosimy oś poziomą na dół – zaznaczamy oś > prawym przyciskiem myszy > Formatuj oś > Etykiety > Nisko (zamiast obok osi).

Etykiety przeniosły się na dół. Wreszcie przyszedł czas na formatowanie kolumn – klikamy prawym przyciskiem na kolumny > Formatuje serię danych > Wypełnienie > Pełne oraz zaznaczamy opcję Odwróć jeśli ujemne. W ten sposób otrzymamy dodatkowe wiaderko do kolorowania wartości ujemnych.

Po wybraniu odpowiednich kolorów nasz wykres będzie wyglądał następująco:

 

Dwukolorowy wykres punktowy w Excelu

Wykres punktowy (ang. dot plot) doczekał się na blogu całej serii artykułów. Przypomnę tylko jego najważniejsze zalety:

• zajmuje mniej miejsca niż kolumnowy przy zachowaniu czytelności
• można go rysować zarówno w pionie, jak i w poziomie
• oś nie musi się zaczynać w „0”
• świetnie pokazuje porównania kilku serii danych

Wstawienie wykresu punktowego dla takiej serii danych jak w przykładzie jest bardzo proste – korzystamy z wykresu liniowego ze znacznikami, usuwając kolor linii:

Jednak jak zmieniać kolory punktów ujemnych? I jak dorysować patyczek od lizaka?

Mam 2 wiadomości, niestety obie są złe. Ciekawy efekt końcowy w postaci wykresu lizakowego uzyskamy tylko poprzez modyfikację danych źródłowych – wykres liniowy nie ma bowiem opcji Odwróć jeśli ujemne. Do danych dodajemy 3 wiersze:

Serie te są utworzone za pomocą funkcji JEŻELI, która zwraca wartość w zależności od znaku, a gdy nie znajdzie, wstawia błąd #N/D!, dzięki czemu punkty niepotrzebne znikną z wykresu. Ostatni wiersz jest wartością bezwzględną uzyskaną z pomoc funkcji MODUŁ.LICZBY.

Następnie modyfikujemy zakres wykresu, aby obejmował serie dodatnie i ujemne, i zmieniamy formatowanie punktów każdej serii.

Ostatnim krokiem jest dodanie słupków błędów. W Excelu 2013 możemy to zrobić klikając ikonę + przy wykresie, w poprzednich wersjach opcję tę znajdziemy na karcie Układ > Słupki błędów. Dla wartości dodatnich tworzymy ujemny słupek błędów w następujący sposób:

1. Dodaj słupki błędów.
2. Zmień kierunek na Minus, a Styl końca na Bez zakończenia.
3. Określ wielkość błędów jako Niestandardowa
4. Zaznacz dane z wiersza słupek błędów

Analogicznie utworzymy słupki błędów dodatnie dla serii ujemnej, otrzymując po ich sformatowaniu punkty w kształcie lizaka w dwóch kolorach:

Plik do pobrania

Tu możesz pobrać dwukolorowy wykres kolumnowy i punktowy (lizakowy) w Excel.