Po stworzeniu tabeli Excel z KPI, które możemy przewijać i sortować za pomocą jednego kliknięcia. Był to pierwszy krok do budowy w Excelu dashboardu, który po dodaniu wykresów pozwoli zarówno szybciej przeglądać i sortować KPI-e, jak i wybierać wskaźniki do szczegółowego porównania. Raport zawiera m.in. pasek przewijania, przyciski opcji, wykres punktowy i słupkowy oraz ranking dla KPI.

Jak wygląda praca na dashboardzie stworzonym w Excelu ?

Plik został stworzony na podstawie danych dotyczących danego modelu telefonu komórkowego – średnia cena, jednostkowa marża, koszt sprzedaży, realizacja budżetu przychodowego oraz udział w rynku w wybranych trzydziestu krajach. Oprócz wyżej wymienionych danych źródłowych dla każdego z KPI został wyznaczony cel. Oto jak wygląda korzystanie z dashboardu:

Przygotowanie danych w arkuszu Excel

W arkuszu o nazwie Data przygotujmy dane źródłowe.

W arkuszu Calculation przygotowujemy tabelę, w której będziemy przeliczać dane źródłowe.

Nad tabelą z przeliczonymi danymi przygotowujemy tabelkę, która będzie zawierać parametry finalnego modelu (tabelkę wklejmy od komórki C5 w arkuszu Calculation).

Komórka D7 (Sortuj wg) będzie wykorzystywana do wielu obliczeń. Dlatego nadamy jej nazwę ‘KryteriaSortowania’. W formule zamiast adresu komórki wystarczy wpisać jej nazwę.

Wpiszmy w pole KryteriaSortowania pierwszą z opcji, czyli 1. Następnie zastosujemy formułę ‘Przesunięcie’, której uczyliśmy się w poprzednim artykule z dashboardem (tu odwołanie).

Bazą formuły ‘Przesunięcie’ w kolumnie Wybrany KPI będzie komórka C10 w arkuszu Data. Komórka odpowiada pierwszej pozycji z listy krajów. Za komórką C10 wpisujemy 0 (nie chcemy przesuwaćo wiersze), następnie wpisujemy ‘KryteriaSortowania’. Formułę przeciągamy przez wskazane komórki w wybranej kolumnie.

Następnym krokiem jest stworzenie z przeliczonych wartości unikatów. Wykorzystamy to tego metodę bazującą na dodaniu do wartości ułamka według poniższego wzoru.

W kolumnie ‘Sortowanie’ skorzystamy z formuły MAX.K zwracającą największą wartość w zbiorze danych. Szukamy kolejnych pozycji w tablicy unikatów. Formuła korzysta z komórki ‘Kolejność sortowania’, w której wpisujemy wartość 1.

W kolumnie Pozycja wpisujemy formułę PODAJ.POZYCJĘ. Przypomnienie definicji: funkcja PODAJ.POZYCJĘ wyszukuje określony element w zakresie komórek, a następnie zwraca względną pozycję tego elementu w zakresie. Funkcji PODAJ.POZYCJĘ należy użyć zamiast jednej z funkcji WYSZUKAJ, kiedy jest konieczna znajomość położenia elementu w zakresie, a nie tylko znajomość samej pozycji elementu.

Przeciągamy formuły do końca w każdej z kolumn.

Obok przeliczonych danych tworzymy tabelę z krajami i pięcioma KPI.

W kolumnie ‘Kraj’ korzystamy kolejny raz z funkcji PRZESUNIĘCIE. Bazą są dane z arkusza ‘Data’, a dokładnie nagłówki. W kolumnie ‘Kraj’ formuła będzie składała się z komórki C9 (nagłówek listy krajów), która będzie przesuwana pionowo o numer pozycji (kolumna G w arkuszu ‘Calculation’).

W kolumnie z jednostkową marżą bazą w formule będzie nagłówek z kolumny dotyczącej marży z arkusza Data.

Natomiast dla kosztu własnego sprzedaży użyjemy nagłówka dla właśnie tego KPI.

Do formuły w kolumnie dotyczącej realizacji budżetu przychodowego wpiszemy oczywiście adres komórki odpowiadającej odpowiedniemu KPI:

Udział w rynku w arkuszu Calculation odwołuje się do nagłówka z ostatniej kolumny z arkusza Data.

Gotowe przeliczone dane prezentują się jak poniżej:

Budowa dashboardu w nowym arkuszu Excela

W arkuszu Dashboard przygotowujemy tabelę, która będzie prezentowała wyniki naszych działań w modelu. W tabeli będzie znajdowała się lista krajów oraz poszczególne KPI, według których będziemy sortować dane.

Kolejnym krokiem będzie uzupełnienie parametru, który zostanie wykorzystany do modelu. W komórce ‘Aktualna pozycja’ wpisujemy 1.

Dashboard będzie zawierał suwak, dlatego też tabela do modelu jest przygotowana na 10 pozycji, aby wyświetlić pozostałe będziemy korzystać z paska przewijania.

Pierwsza kolumna odpowiada liczbie pojedynczej. Ponownie wykorzystamy PRZESUNIĘCIE, bazą będzie kolumna B z arkusza ‘Calculation’, a będziemy przesuwać pionowo o wartość z ‘Aktualnej pozycji’ (komórka D5).

Druga kolumna będzie zawierać listę krajów. Bazą będzie kolumna I z arkusza ‘Calculation’, a przesuwać będziemy o ‘Aktualną pozycję’.

Obie formuły przeciągamy do końca kolumn.

Dane dla pierwszego KPI obliczamy na podstawie formuły PRZESUNIĘCIE. Bazą formuły będą dane z kolumny J (dla pozostałych KPI będą to dane z kolumn odpowiadającym tym KPI) oraz komórka ‘Aktualna pozycja’. Formułę przeciągamy do wszystkich pozostałych komórek.

Wypełniona tabela prezentuje się jak poniżej:

W nagłówku każdego KPI wpisujemy technicznie numer tego KPI: czyli dla średniej jednostkowej ceny będzie to 1, dla jednostkowej marży będzie to 2, dla kosztu własnego sprzedaży – 3, dla realizacji budżetu przychodowego – 4, a dla udziału w rynku -5.

Numery techniczne zostaną wykorzystane do formuły, która będzie sygnalizować wartości powyżej 90-ego percentylu i poniżej 10-percentylu ( zostało to określone w danych źródłowych wraz z celami dla każdego z KPI).

Skorzystamy z formuły PERCENTYL. Według definicji percentyl jest jednostką statystyczną opisującą położenie danego wyniku względem całej grupy wyników. Poszczególne centyle określają ile wyników (procentowo) było niższych bądź wyższych od pewnego wyniku. Formuła ta zwraca k-ty percentyl wartości w zakresie. Funkcję tę można stosować do określania progu akceptacji.

Dla wartości powyżej 90. percentyla pojawią się strzałki z plusami. Natomiast dla wartości poniżej 10. percentyla pojawią się strzałki z minusami. Strzałki nie będą pojawiać się w zaznaczonym KPI.

Formułę zastosujemy dla każdego KPI (przy czym formuła będzie zmodyfikowana zgodnie z numerem technicznym KPI).

Kolejnym krokiem będzie wstawienie przycisku opcji dla każdego KPI, który wykorzystywaliśmy już w modelu ‘Dashboard z KPI- przewijaj i sortuj za pomocą jednego kliknięcia’. Przyciski wstawiamy: Deweloper>Wstaw>Kontrolki formularza> Przycisk opcji.

Następnie formatujemy wstawiony przycisk.

Przycisk będzie się łączył z komórką ‘Sortuj wg’.

Model będzie zawierał także pasek przewijania, dlatego wstawiamy pasek w taki sam sposób, w jaki wstawialiśmy przycisk opcji: Deweloper> Wstaw> Kontrolki formularza> Pasek przewijania.

Aby pasek przewijania działał poprawnie należy uzupełnić parametr ‘Maksymalna pozycja’. Uzupełniamy go wykorzystując różnicę pomiędzy ilością krajów (30) a ilością wierszy w modelu finalnym (10). Dodajemy na końcu 1.

Po uzupełnieniu wyżej wymienionego parametru możemy sformatować pasek.

Podstawowy dashboard już działa. Naszym celem jest jednak wykorzystanie jeszcze kilku innych funkcjonalności, które ułatwią nam późniejszą analizę. Jedną z takich funkcjonalności są wykresy słupkowe obok wartości każdego KPI. Wykresy te będą pokazywały czy dana wartość jest poniżej bądź powyżej wyznaczonego celu. Będą również prezentowały średnią arytmetyczną dla danych. Zanim wstawimy takie wykresy należy przygotować dane źródłowe.

W arkuszu Calculation przygotowujemy tabelę dla każdego z KPI. Tabela ta zawiera pięć kolumn. Kolumna ‘Czarny’ zawiera wartości powyżej celu, ‘Czerwony’ – wartości poniżej celu, ‘Średnia’ – średnia arytmetyczna, ‘Min’ – to minimalna wartość spośród danych, a ‘Max’ – maksymalna wartość.

Tabelę, jak poniżej, przygotowujemy dla każdego z KPI (oczywiście o tytułach odpowiadających danemu KPI).

Formuła dla ‘Czarny’ będzie pokazywała jedynie wartości, które są równe lub większe od wyznaczonego celu.

Formuła dla ‘Czerwony’ będzie pokazywała jedynie wartości, które są mniejsze od wyznaczonego celu.

‘Średnia’ prezentuje średnią arytmetyczną dla wszystkich wartości danego KPI.

Formuła ‘Min’ zwraca wartość minimalną spośród wszystkich danych dotyczących KPI.

Formuła ‘Max’ zwraca wartość maksymalną spośród wszystkich danych dotyczących KPI.

Formuły przeciągamy do końca kolumn danej tabeli.

Za pięcioma tabelami z danymi źródłowymi do wykresu przygotowujemy jednokolumnową tabelę o tytule ‘Y średnia’.

Tabelę wypełnimy wartościami od 1 do 10.

Kolejnym krokiem jest uzupełnienie dwóch kolejnych parametrów w tabeli z parametrami. Dla ‘Wybranego KPI osi X’ oraz ‘Wybranego KPI osi Y’ wpiszemy na razie 1 (będą tu wartości od 1 do 5). Parametry te są niezbędne do przygotowanie danych źródłowych do wykresu punktowego.

Dane do wykresu punktowego przygotujemy w poniższej tabeli.

Wartości dla X będą przeliczać się na podstawie danych z kolumny C w arkuszu Data oraz jednego z ostatnio uzupełnionych parametrów ‘Wybrany KPI osi X’.

Wartości dla Y będą przeliczać się na podstawie danych z kolumny C w arkuszu Data oraz parametru ‘Wybrany KPI osi Y’.

Następną potrzebną tabelą do wykresu punktowego będzie tabela ‘Wybrane 10’.

Tabela będzie zawierała dane dla 10 wyświetlanych pozycji w modelu wyliczone na podstawie PRZESUNIĘCIA o aktualną pozycję paska przewijania.

Kolejnym krokiem jest wstawienie wykresów słupkowych obok wartości dla KPI. Jest to dość praco- i czasochłonne, ale efekt jest zdecydowanie zadowalający. Każdy z wykresów składa się aż z siedmiu serii danych.

Seria danych ‘Czarny’ będzie wyświetlać dane które są większe od celu.

Seria danych ‘Czerwony’ będzie wyświetlać dane które są mniejsze od celu.

‘Średnia’ wyświetla średnią arytmetyczną dla wartości danego KPI.

Seria danych ‘MinXY’ jest przygotowana dla minimalnych wartości spośród danych dla danego KPI.

Seria danych ‘MaxXY’ jest przygotowana dla maksymalnych wartości spośród danych dla danego KPI.

Dodatkowo przygotowujemy serie danych MinBar i MaxBar.

Serie danych ‘Czarny’ i ‘Czerwony’ przedstawiamy jako wykres słupkowy grupowany. Słupki dla serii ‘Czarny’ wypełniamy kolorem zielonym, słupki ‘Czerwony’ wypełniamy kolorem czerwonym. Seria danych ‘Średnia’ to wykres punktowy z prostymi liniami przedstawiony na osi pomocniczej. Serie MinXY i MaxXY to również wykresy punktowe z prostymi liniami przedstawione na osi pomocniczej, jednakże nie wypełniamy żadnym kolorem serii. Serie MinBar i MaxBar to wykresy słupkowe grupowane również niewypełnione kolorem.

Wykresy dla KPI prezentują się jak poniżej. Dzięki wypełnieniu odpowiednich serii kolorem zielonym i czerwonym zwracamy uwagę na to, jak powinniśmy interpretować poniższe wartości.

Następnym krokiem będzie dodanie strzałek, które będą sygnalizować czy pasek przewijania możemy przewijać w dół bądź w górę.

Dodamy jeszcze formatowanie warunkowe, które wypełni komórki zaznaczonego KPI na szaro.

Formatowanie warunkowe wstawiamy poprzez: Narzędzia główne > Formatowanie warunkowe > Użyj formuły do określenia komórek, które należy sformatować. Kryterium do formatowania warunkowego będzie równość pomiędzy numerem technicznym KPI, który był przybliżony powyżej, a wartością komórki ‘KryteriaSortowania’.

Wstawiamy kolejne formatowanie dla komórki z numerem technicznym. Czcionka tej komórki wraz z wyborem danego KPI będzie zmieniała kolor na kolor wypełnienia, czyli szary.

Sformatujemy także komórki zawierające strzałki z plusami i minusami. Czcionka strzałki z plusem będzie zielona, czcionka strzałki z minusem będzie czerwona. Czcionka będzie formatowana tylko w niewybranych KPI.

Oprócz wykresów przy każdym KPI poniżej tabeli z danymi wstawiamy wykres punktowy (XY), na którym będziemy porównywać wybrane KPI. Na wykresie znajdą się dane dla wszystkich 30 pozycji oraz wybranych 10 – dane te przygotowaliśmy w arkuszu Calculation.

Seria danych ‘Wszystkie’ została przygotowana na podstawie przeliczonych danych dla wszystkich danych X i wszystkich danych Y.

Seria danych ‘Wybrane 10’ została przygotowana na podstawie przeliczonych danych dla prezentowanych 10 pozycji X oraz prezentowanych 10 pozycji Y.

Wykres prezentuje się jak poniżej:

Kolejnym krokiem będzie wstawienie na wykres dwóch pól kombi: Deweloper > Wstaw > Kontrolki formularza > Pole kombi.

Górne pole kombi formatujemy. Zakresem wejściowym jest lista KPI z arkusza Calculation. Łącze komórki będzie prowadziło do komórki z tabel z paramentami: Wybrany KPI osi Y.

Polne pole kombi również formatujemy. Zakresem wejściowym będzie także lista KPI z arkusza Calculation. Łącze komórki będzie prowadziło do komórki z tabel z paramentami: Wybrany KPI osi X.

Dodajemy linię trendu liniowego dla wszystkich pozycji oraz legendę.

Obok wykresu punktowego wstawimy ranking dla 10 najlepszych pozycji według wybranego KPI.

Pierwsza z formuł w rankingu będzie odwoływać się do przeliczonych danych w arkuszu Calculation. W zależności od tego jaki KPI wybierzemy, dane będą się przeliczać i prezentować 10 najlepszych pozycji.

Obok listy z krajami umieścimy różnice procentowe pomiędzy danymi – ‘strata’ procentowa do pierwszej z pozycji. Pierwsza z pozycji na liście będzie domyślnie nazwana ‘Lider’.

Formuła, która będzie przeliczała stratę procentową, będzie prezentowała się jak poniżej:

Dodamy tytuł KPI, na podstawie którego przeliczają się dane.

Tytuł będzie wyświetlał się na podstawie poniższej formuły.

Cały dashboard prezentuje się jak poniżej:

Gotowy dashboard złożony jest z narzędzia ‘Sortuj i przewijaj’, który różni się od modelu przygotowanego w poprzednim artykule. Oprócz paska przewijania oraz przycisków opcji dashboard zawiera wykresy i ranking.