Dynamiczna etykieta danych na wykresie liniowym

Powiedzmy, że co miesiąc aktualizujesz jakiś raport. Masz podpięty wykres, który pokazuje plan i wykonanie w kolejnych okresach. Na wykresie standardowo legenda, pokazująca że „ta linia” to plan, a „ta” to wykonanie.

Czy nie byłoby świetnie, gdyby zamiast konieczności odczytywania legendy, etykiety opisujące dane znalazły się bezpośrednio na wykresie. Najlepiej na końcu i to bez względu na to ile kolejnych okresów dodasz?

Dynamiczna etykieta danych to sposób na rozwiązanie powyższego problemu. Przeczytaj i zobacz jak ją stworzyć na wykresie liniowym.

Dane

Do każdego wykresu możesz podpiąć etykiety i pokazywać na nich na przykład nazwę serii danych. Jeśli zastosujesz standardowy sposób, to nazwa tej serii pokaże się w każdym punkcie danych. Ale jak wspomniałem na początku artykułu, my chcemy aby etykieta zawsze pokazywała się tylko na końcu. Zatem do dzieła. Pobierz plik i działaj ze mną.

Pierwszą rzeczą jaką musimy zrobić to dodać dwie pomocnicze kolumny. Pierwsza kolumna to będzie „Wykonanie”. Drugą kolumną będzie „Plan”. Pomocnicze kolumny pozwolą wyłapać zawsze ostatni wiersz w naszych danych. To ważne, bo właśnie dzięki temu będzie możliwe pokazanie na końcu linii dynamicznej etykiety danych.

Zatem, zgodnie z powyższym obrazkiem – ostatnim wierszem dla wykonania roku bieżącego jest sierpień. I właśnie ta wartość powinna się pokazać w kolumnie pomocniczej. Dla uproszczenia przyjmijmy, że wartości planu mamy na cały rok i od razu możemy przypisać adres komórki z grudnia.

W naszym przykładzie w komórce I39 użyję po prostu formuły:

=D39

Formuła

Nieco trudniej będzie z formułą wychwytującą ostatnią wpisaną wartość w kolumnie z wykonaniem. Jest kilka metod, aby sobie poradzić z tym zadaniem. My skorzystamy ze sposobu z formułą, która wykorzystuje poniższe funkcje:

• ILE.NIEPUSTYCH
• ILE.WIERSZY
• BRAK
• JEŻELI.

Pomysł na formułę jest następujący:

1. Funkcją ILE.NIEPUSTYCH sprawdzimy ile komórek w kolumnie „Wykonanie” jest niepustych (czyli ma wpisaną jakąś wartość)
2. Następnie porównamy te wartość z numerem wiersza, w zakresie naszych danych.
3. Jeżeli te dwa elementy będą równe, to będzie oznaczać, że jest to nasza ostatnia użyta komórka. Do porównania wykorzystamy oczywiście funkcję JEŻELI.
4. Jeśli wartości z punktów 1 i 2 nie będą równe – w komórce powinien się pojawić błąd #N/D!. Aby tak się stało – użyjemy formuły BŁĄD. Dlaczego ma się pojawić błąd? O tym nieco później.

Formuła w komórce H28 będzie wyglądała tak;

=JEŻELI(ILE.NIEPUSTYCH($C$28:$C$39)=ILE.WIERSZY($C$28:C28);C28;BRAK())

Przeanalizujmy poszczególne fragmenty formuły. Zazwyczaj bardziej złożone formuły warto rozkładać na czynniki pierwsze.

=JEŻELI(

W dalszej części formuły sprawdzamy dwie rzeczy. Po pierwsze ile jest niepustych wierszy, a po drugie ile jest wierszy w danym obszarze. Musimy przeprowadzić test logiczny, czyli po prostu sprawdzić czy obie wartości są sobie równe. Jak wiadomo najlepiej takie zadanie zrealizuje funkcja JEŻELI.

ILE.NIEPUSTYCH($C$28:$C$39)=ILE.WIERSZY($C$28:C28)

W tym fragmencie następuje porównanie, o którym mowa powyżej. Zauważ, że adres w funkcji ILE.WIERSZY ma postać mieszaną. To dlatego, że formułę będziemy kopiować w dół i zależy nam, aby początkowa komórka była zawsze taka sama. Zmieniać się będzie końcowa komórka zakresu. I właśnie tu zadziała funkcja ILE.WIERSZY. Zobacz na przykładzie:

;C28;BRAK())

Właściwie reszta powinna być już prosta. Funkcja ILE.NIEPUSTYCH policzy maksymalną liczbę użytych komórek, natomiast funkcja ILE.WIERSZY wskaże kolejne numery wierszy. Po teście, czy obie wartości są sobie równe za pomocą funkcji JEŻELI – w kolejnym fragmencie wskazujemy co ma się wydarzyć jeżeli to prawda, a co jeżeli fałsz. Jeśli obie wartości będą równe to chcemy wskazać wartość komórki z roku bieżącego. To będzie ostatnia użyta komórka. W przypadku fałszu, ma zadziałać funkcja BŁĄD(), która da wynik #N/D!. Potrzebujemy właśnie błędu, bo dzięki temu na wykresie nie będzie widać wartości zerowych.

Na podstawie tak przygotowanych danych możemy stworzyć wykres.

Wykres

Zaznaczamy zakres danych B27:D39 oraz H27:I39 (z wciśniętym klawiszem CTRL), a zatem obszar naszych danych podstawowych i dwie pomocnicze kolumny z etykietami. Wybieramy wykres liniowy na wstążce WSTAWIANIE. Teraz możemy go odpowiednio sformatować, czyli przykładowo:

• usuwamy linie siatki, obramowanie i tło,
• dostosowujemy tytuł,
• zmieniamy kolory i grubość linii.

Następnie dodajemy etykiety danych. Najlepiej kliknij plusik obok wykresu i z menu wybierz odpowiednią opcję.

Po włączeniu etykiet, zauważysz że włączyły się etykiety dla wszystkich danych. Nam zależy na tym, aby widoczna była tylko etykieta z opisem linii i to zawsze na końcu tej linii. W końcu ma to być dynamiczna etykieta danych, a więc ma się przesuwać wraz z ich zmianą.

To co musisz teraz zrobić, można sprowadzić do następujących kroków:

1. Dla serii „Plan” i „Rok bieżący” – czyli danych podstawowych – kliknij prawym przyciskiem myszy na etykietę danych i wybierz FORMATUJ ETYKIETY DANYCH.
2. Wyłącz wartości w opcjach etykiet.
3. Powyższe kroki wykonaj dla obu serii.
4. Dla serii obu kolumn pomocniczych – kliknij prawym przyciskiem myszy na etykietę danych i wybierz FORMATUJ ETYKIETY DANYCH.
5. Włącz nazwę serii w opcjach etykiet.
6. Wyłącz wartości.

Finalnie możesz (i w sumie powinieneś) usunąć legendę z wykresu. Cała idea tej metody polega właśnie na tym, aby informacje opisujące dane znalazły się bezpośrednio na wykresie – bez potrzeby dodatkowego ich opisywania w legendzie.

Wypadałoby jeszcze ukryć dane pomocnicze w kolumnach. Użyj magicznej białej czcionki 🙂 lub ukryj kolumny. Pamiętaj jednak, aby w tym drugim przypadku wybrać z opcji wykresu polecenie POKAŻ DANE W UKRYTYCH WIERSZACH I KOLUMNACH.

Podsumowanie

Opisany powyżej wykres możesz z powodzeniem wykorzystywać w różnego rodzaju raportach i dashboardach. Jest np. użyteczny przy pokazywaniu zmian w czasie w stosunku do jakieś bazowej wartości (wykonanie do budżetu, rok bieżący do poprzedniego itd.)

Co równie ważne, dynamiczna etykieta danych na takim wykresie jest dobrym sposobem na ograniczenie ilość informacji do niezbędnego minimum. To bardzo ważne, bo nie zapominajmy że wykresy i wizualizacje mają przede wszystkim przekazywać istotne informacje.

Daj znać w komentarzu, czy masz pomysł na wykorzystanie albo już wykorzystałeś podobny wykres w swojej pracy. Jeśli uważasz, tę wiedzę za wartościową – udostępnij ją na swoim Facebooku lub LinkedIn.

Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel.