Karty KPI i wyróżnianie warunkowe

Karta KPI to pojedyncza, wyróżniona liczba, która przekazuje czytelnikowi najważniejszą informację: łączny przychód, liczbę dzisiejszych zamówień lub średnią wartość zamówienia. Dashboardy zwykle rozpoczynają się od rzędu takich kart.

Dobra karta składa się z trzech elementów: czytelnej etykiety, dużej liczby obliczonej za pomocą formuły oraz często niewielkiego porównania, na przykład zmiany względem poprzedniego okresu.

W tej lekcji zbudują Państwo wartości KPI za pomocą formuł agregujących, dodadzą porównania między okresami i użyją logiki warunkowej, aby wyróżnić wartości wymagające uwagi.

Proszę zacząć od podstawowego wskaźnika. Łączny przychód z arkusza Sales to po prostu funkcja SUM zastosowana do kolumny kwot.

Proszę umieścić tę formułę w komórce wartości karty:

Aby policzyć zamówienia, proszę użyć funkcji COUNTA w kolumnie, która zawsze zawiera wartość, na przykład w kolumnie identyfikatorów zamówień. Etykieta karty, czyli zwykła komórka tekstowa nad wartością, określa znaczenie liczby. Każdą kartę należy ograniczyć do jednej liczby, aby można było ją odczytać natychmiast.

Wiele wskaźników KPI to ilorazy. Średnia wartość zamówienia to łączny przychód podzielony przez liczbę zamówień. Można ją zbudować z dwóch wartości zagregowanych.

Jedno zwięzłe rozwiązanie korzysta bezpośrednio z funkcji AVERAGE:

Jeśli wolą Państwo wyprowadzić tę wartość, można podzielić sumę z karty przez liczbę zamówień z karty. W obu przypadkach karta pokazuje typową wartość sprzedaży i aktualizuje się wraz ze zmianą danych.

Wskaźnik KPI ma większe znaczenie, gdy zestawi się go z celem. Załóżmy, że miesięczny cel przychodu znajduje się w komórce B1. Proszę obliczyć, o ile wynik przekracza cel lub jest od niego niższy.

Odchylenie to bieżąca wartość pomniejszona o cel:

Można też wyrazić je jako procent celu za pomocą =SUM(Sales!C2:C500)/B1. Wyświetlenie wartości 1,12 oznacza, że osiągnięto 112 procent celu. Porównanie zmienia surową liczbę w konkretną historię.

Czytelnicy lubią obserwować dynamikę. Proszę obliczyć zmianę procentową względem poprzedniego okresu. Załóżmy, że suma za bieżący okres znajduje się w D2, a suma za poprzedni okres w D3.

Formuła wzrostu wygląda następująco:

Jeśli wartość za bieżący okres wynosi 120, a za poprzedni 100, otrzymają Państwo 0,2, czyli wzrost o 20 procent. Proszę sformatować komórkę jako procent. Niewielki wskaźnik wzrostu lub spadku pozwala od razu zmienić płaską kartę w informację o trendzie.

Za pomocą funkcji IF można pokazać kierunek zmian słowami lub symbolami. Proszę odczytać komórkę zmiany i wybrać odpowiednią etykietę.

Zwraca to oznaczenie wzrostu lub spadku wraz z wartością procentową:

Gdy zmiana w E1 jest dodatnia, pojawi się informacja o wzroście; w przeciwnym razie — o spadku. Połączenie tekstu ze sformatowaną liczbą pozwala zachować zwięzłość i czytelność karty bez konieczności używania wykresu.

Formatowanie warunkowe zmienia kolor komórki na podstawie reguły, dzięki czemu ważne wartości od razu rzucają się w oczy. Proszę zaznaczyć komórki KPI, otworzyć Formatowanie warunkowe i dodać regułę opartą na formule.

Aby oznaczyć na czerwono każdą kartę poniżej celu, proszę użyć reguły formuły takiej jak:

Komórki, dla których reguła ma wartość TRUE, otrzymają wybrane formatowanie. W ten sposób dashboard staje się czerwony, gdy sprzedaż nie osiąga celu, i zielony, gdy go przekracza, bez konieczności dokładnego odczytywania liczb.

Czasami potrzebne jest oznaczenie tekstowe bezpośrednio w komórce, a nie tylko kolor. Funkcja IF wraz z operatorami porównania tworzy słowny status.

Aby opisać wyniki względem celu:

Po osiągnięciu celu pojawi się tekst Zgodnie z planem, a przy wyniku niższym od celu — Poniżej celu. Kolumna statusu dobrze współpracuje z formatowaniem warunkowym, które koloruje tekst, zapewniając zarówno sygnał wizualny, jak i słowny.

Rzeczywiste wskaźniki KPI często mają więcej niż dwa stany: dobry, ostrzegawczy i krytyczny. Funkcja IFS sprawdza warunki w kolejności i zwraca pierwszy pasujący wynik, dzięki czemu formuła jest czytelniejsza niż zagnieżdżone funkcje IF.

Aby podzielić wyniki na trzy przedziały:

IFS sprawdza warunki od góry do dołu, dlatego najbardziej rygorystyczny warunek należy umieścić jako pierwszy. Połączenie każdego przedziału z regułą kolorystyczną sprawi, że karty będą sygnalizować stan już na pierwszy rzut oka.

Oprócz pojedynczych reguł skale kolorów cieniują zakres liczb gradientem, dzięki czemu najwyższe i najniższe wartości wyróżniają się bez używania formuły. Doskonale nadają się do kolumny z sumami regionalnymi lub wartościami dziennymi.

Proszę zaznaczyć zakres, otworzyć Formatowanie warunkowe i wybrać skalę kolorów, na przykład od zielonego do czerwonego.

• Największe wartości będą zielone, najmniejsze czerwone, a wartości pośrednie przyjmą kolory pośrednie.
• Skala będzie automatycznie przeliczana wraz ze zmianą danych.

Skale kolorów doskonale uzupełniają karty KPI: karty pokazują główny wynik, a pokolorowana kolumna wskazuje położenie każdej wartości w całym rozkładzie.

Proszę rozmieścić karty w uporządkowanym wierszu u góry dashboardu. Każda karta to niewielki blok składający się z komórki etykiety, formuły wartości oraz porównania lub statusu poniżej.

• Karta 1: Łączny przychód, =SUM(Sales!C2:C500), z odchyleniem względem celu.
• Karta 2: Zamówienia, =COUNTA(Sales!A2:A500).
• Karta 3: Śr. wartość zamówienia, =AVERAGE(Sales!C2:C500), z procentową zmianą.

Formatowanie warunkowe koloruje komórki porównań. Wiersz zapewnia natychmiastowe podsumowanie, zanim czytelnik przewinie ekran do szczegółów.

Zbudowali Państwo główną warstwę dashboardu:

• Formuły agregujące, takie jak SUM, COUNTA i AVERAGE, wygenerowały główne wartości KPI.
• Formuły odchyleń i wzrostu dodały kontekst porównawczy względem celu oraz poprzedniego okresu.
• Funkcje IF i IFS przekształciły liczby w statusy, takie jak Zgodnie z planem, Uwaga i Krytyczny.
• Formatowanie warunkowe z regułami opartymi na formułach automatycznie pokolorowało komórki, wskazując to, co najważniejsze.

W połączeniu z tabelami podsumowującymi, przestawieniami opartymi na formułach i interaktywnymi listami rozwijanymi z wcześniejszych lekcji mogą teraz Państwo zbudować kompletny, aktualizowany na żywo dashboard sterowany formułami.