Po tym jak poznaliśmy definicję dashboardu oraz jego wzorcowy przykład raportu, przyszedł czas na budowę tego typu raportu w Excelu. Dla przypomnienia ma to być raport, który niczym deska rozdzielcza w samochodzie, wysyła menedżerowi sygnały, czy jest dobrze czy źle i gdzie ewentualnie znajdują się problemy. W tym celu wykorzystamy szereg funkcjonalności, których próżno szukać w Excelu 2003, a nawet 2007, gdyż Excelowa przygoda z wizualizacją zaczyna się na dobre w kolejnych wersjach.

O cyklu „Jak utworzyć dashboard menedżerski w Excelu?”

Seria „Jak utworzyć dashboard menedżerski w Excelu?” składa się z 4 odcinków, których efektem jest jednoekranowy raport dla menedżera wysyłający sygnały. Plik Excel, zawierający wszystkie wykorzystane dane i wykresy, został umieszczony na końcu czwartego odcinka, można go też pobrać tu: dashboard menedżerski Excel.

O procesie budowy dashboardu

W artykule przedstawimy tylko techniczno-wizualną stronę tego przedsięwzięcia. Każdy, kto budował jakikolwiek raport, wie, że proces ten zaczyna się znacznie wcześniej od wewnętrznych uzgodnień, które zwykle dotyczą następujących kwestii:

Co chcemy mierzyć?
Skąd wziąć dane?
Kto będzie to przygotowywał?
Jakie są nasze cele (miesięczne, roczne)?
Kiedy wyniki są niskie, kiedy akceptowalne, a kiedy wysokie?
Jakie wymiary i kategorie mają dla nas znaczenie?

Miniwykresy i sygnalizacja uliczna dla kluczowych miar dashboardu (KPIs)

Zaczynamy od czystego arkusza Excel, w którym wstawiamy nasze kluczowe miary, takie jak Sprzedaż, Zysk czy Udział w rynku. Następnie wstawiamy miniwykresy, które będą sygnalizować trend, zajmując niewielką powierzchnię raportu: Wstawianie > Wykresy przebiegu w czasie > Liniowy.

Ponieważ chcemy, żeby nasz dashboard wysyłał dużo sygnałów, oznaczmy dodatkowy obecny wynik na ciemnoszaro, a historyczny najniższy i najwyższy wynik na czerwono i zielono. Zmieńmy też kolor linii na szary. Wszystkie opcje formatowania tego wykresu znajdziemy w dodatkowej karcie Narzędzia wykresów przebiegu w czasie.

Widzimy od razu, że jeżeli ostatni wynik jest najniższy, sygnał czerwony będzie na wierzchu.

Uzyskany wykres liniowy przeciągamy w dół (kopiujemy komórkę w dół), co utworzy kilka analogicznych wykresów, szukając naszych danych w kolejnych wierszach pod danymi pierwszego wykresu.

Teraz czas na sygnalizację uliczną, czyli światła czerwone, żółte i zielone. Znając te poziomy dla poszczególnych miar, możemy skorzystać z formatowania warunkowego. Stajemy na komórce B3 i przypisujemy jej aktualną wartość wyświetlaną, łącząc ją z danymi.

Następnie wstawiamy formatowanie warunkowe: Narzędzia główne > Formatowanie warunkowe > Zestawy ikon > 3 światła sygnalizacji ulicznej.

Teraz czas na zdefiniowanie warunków dla naszego światła. Wchodzimy ponownie w Formatowanie warunkowe > Zarządzaj regułami > Edytuj regułę. Teraz wykonujemy kilka ważnych operacji: 1. Zmieniamy typ na liczbę (bo odnosimy się do liczb bezwzględnych, a nie do procentowego zakresu wartości), 2. Wpisujemy adresy komórek, które opisują nasze warunki (zielone światło dla wartości >=36, żółte dla wartości >=30) 3. Zaznaczamy Pokaż tylko ikonę. I oczywiście klikamy OK.

Po wyrównaniu tekstu komórki do środka nasze światło wygląda tak:

Czynność tę powtarzamy dla wszystkich KPI-ów oddzielnie. Dlaczego? Wartości dla warunków można podawać tylko jako wartości bezwzględne ($), dlatego w tym przypadku nie można formatowania warunkowego po prostu skopiować. Po wykonaniu tej operacji nasze główne miary wyglądają następująco:

Wykresy pociskowe dla KPI-ów

Na wzorcowym dashboardzie śledzenie celu (w polskojęzycznych materiałach i firmach działających na polskim rynku nazywanym potocznie z j. ang. targetem) odbywa się za pomocą wykresu pociskowego (o tym, dlaczego wykres pociskowy, a nie liczniki samochodowe, dowiesz się więcej z artykułu nt. jego konstrukcji). Skorzystajmy więc z wykresu pociskowego zaprojektowanego w Excelu i wstawmy go do naszego arkusza. Wykres pociskowy, który tam udostępniliśmy, był wyśrubowany pod względem wizualnym, a my go trochę zliberalizujemy. Zaczynamy od przeklejenia go na dashboard i umieszczenia, z uwagi na oś, przy ostatnim KPI-u.

W arkuszu z danymi będziemy potrzebować następujące wartości dla każdego wskaźnika: do jakiego poziomu jest sygnał czerwony, do jakiego poziomu jest sygnał żółty, jaki jest zakładany cel oraz jaki jest aktualny wynik. Wszystkie te dane muszą być wyrażone w formie procentowej. Przechodzimy zatem do arkusza Dane i tworzymy dodatkowe kolumny.

Ustawimy też maksimum osi poziomej na 150% oraz zmienimy kolory na czerwony-żółty-zielony o niskiej intensywności. Zacznijmy od dopasowania osi – przechodzimy do arkusza Dashboard > Narzędzia główne > Okienko zaznaczenia > ukrywamy 2 górne niepotrzebne wykresy (symbol oka), otwierając sobie drogę do osi wykresu, dla której ustawiamy Maksimum na 1,5.

Następnie podłączamy ten wykres do naszych danych – kolejne serie odnoszą się do obliczonych wcześniej komórek, np. czerwony – U6:

Teraz odkrywamy symbolem oka kolejny wykres, z którego składa się ten Excelowy szablon pocisku, i ponownie podłączamy do danych, tym razem do komórki S6 – wynik:

Ostatni wykres to znacznik targetu, który również odnosimy do arkusza Dane – T6:

Pierwszy wykres pociskowy jest gotowy.

Czynności powtarzamy dla pozostałych wierszy, czekając na lepsze czasy i gotowy szablon wykresu pociskowego w Excelu (może w Office 2015?). Efekt końcowy powinien wyglądać następująco: