Sparkline z KPI w Excelu

Jeżeli posiadacie Excela w wersji od 2010 roku, to z pewnością niejednokrotnie korzystaliście z wykresu przebiegu w czasie zwanego potocznie jako Sparkline. Sparklines przydają się szczególnie, przy budowie dashboardów lub rozbudowanych graficznie raportów. W dzisiejszym wpisie chciałbym przedstawić niecodzienną metodę wykorzystania sparklines, które wzbogacone zostaną o sygnał wskaźnika KPI. Takie rozwiązanie pozwoli nie tylko na śledzenie trendu danych, ale i na szybką lokalizację obszarów wymagających dodatkowych zmian. Gotowy plik do pobrania tutaj.

Za źródło danych do zbudowania wykresu, posłużymy nam roczne zestawienie sprzedaży, w podziale na poszczególne województwa. Wykresy sparklines, które utworzymy pozwolą na szybką identyfikację trendu sprzedaży oraz wskazanie tych województw, dla których sprzedaż w 2016 roku była niższa niż w 2010. Poniższą tabelę sporządzamy w arkuszu o nazwie Dane.

W kolejnym kroku przechodzimy do drugiego arkusza - Wykres, w którym od komórki B10 uzupełniamy listę województw, a następnie w zakresie F10:F25 umieszczamy wykresy sparklines. Aby wstawić wykresy, zaznaczamy obszar F10:F25 i z karty Wstawienie, grupy Wykresy przebiegu w czasie, wybieramy wykres liniowy. Jako obszar danych zaznaczamy pole danych z tabeli znajdującej się w arkuszu Dane.

Po utworzeniu wykresów, do kodu arkusza Wykres wklejamy poniższe makro.

Zapisz

Private Sub Worksheet_Activate()
Dim spkline As SparklineGroup
Dim lRow As Long

    lRow = Cells(Rows.Count, "B").End(xlUp).Row
    Range("F10:F" & lRow).SparklineGroups.Ungroup
    For Each spkline In Range("F10:F" & lRow).SparklineGroups
        With Application.Range(spkline.Item(1).SourceData)
            If .Offset(, .Columns.Count).Resize(1, 1).Value = 1 Then
                spkline.Points.Lastpoint.Visible = True
                spkline.Points.Lastpoint.Color.Color = 255
            Else
                spkline.Points.Lastpoint.Visible = True
                spkline.Points.Lastpoint.Color.ThemeColor = 5

            End If
        End With
    Next
End Sub

Powyższe makro zadziała przy aktywacji arkusza i wykona następujące działania:

- poda nr ostatniego niepustego wiersza w kolumnie B

- uzyskaną liczbę podstawi do zakresu określającego grupę wykresów sparklines, które zostaną rozgrupowane

- następnie dla każdego pojedynczego wiersza ze sparkline sprawdzi czy przynależna mu kolumna w arkuszu dane o nazwie Pomoc zawiera wartość 1

- jeżeli zależność zostanie spełniona ostatni punkt na wykresie zostaje zabarwiony na czerwono, w przeciwnym razie na niebiesko

- powyższa reguła sprawdza całą kolumnę z wykresami

Teraz wystarczy już tylko przenieść się do innego arkusza a następnie powrócić do arkusza Wykres, aby wykresy sparklines wzbogaciły się o kolorowy, ostatni punkt na wykresie.

Na koniec warto poszerzyć komórki, w których znajdują się wykresy, aby prezentacja danych była czytelniejsza oraz zmienić kolor samych linii.

Od tej pory każda różnica wynikająca ze zmiany kolumn "2010" i "2016" w arkuszu Dane, będzie skutkowała zmianą koloru znacznika powyższych wykresów sparklines.

Zapisz

Excel Raport

Sparkline z KPI w Excelu

Dodaj komentarz Anuluj pisanie odpowiedzi