Czyli wykres liniowy bez załamania, gdy jeszcze nie ma wartości

Nowy rok się właśnie zaczął, a z nim nowe raportowanie sprzedaży. Może warto pomyśleć o rozwiązaniu, nad którym się nie narobimy za bardzo? Czyli chcemy, aby wykres był dynamiczny: sam rysował się w prawo – my tylko dopisujemy wartości sprzedaży.

Załóżmy, że chcemy raportować sprzedaż 2019, 2020, PLAN 2020 i wykonanie planu 2020 po miesiącach. Klasyka. Pierwsze 3 wskaźniki mają być wykresem kolumnowym, wykonanie planu – liniowym. Przygotowaliśmy sobie formatkę (dalej w artykule), tworzymy wykres i… zonk! Kolumny wyglądają ładnie, natomiast wykres liniowy załamał się do zera po wyświetleniu wartości dla stycznia i lutego (tak, wybiegłam trochę w przyszłość ;)).

My tak oczywiście nie chcemy, chcemy mieć ładną linię tak, gdzie są dane. Oto porównanie PRZED i PO:

Jak to zrobić? Jest jeden prosty trik, który opisuję w tym artykule…

Formatka

Formatka wygląda następująco:

Formatka

Opis kolumn:

• Sprzedaż 2020 (D) – będzie uzupełniana na bieżąco przez użytkownika
• Sprzedaż 2019 – stała
• PLAN 2019 – stała
• Wykonanie planu – formuła: =D5/E5, sformatowana na procent

Gdyby z tych danych zrobić wykres kolumnowo-liniowy (kombi), wyszłoby coś takiego:

„Załamany” wykres liniowy

Czego oczywiście nie chcemy.

Najprostszym trikiem, który można tutaj zastosować jest stworzenie kolumny pomocniczej na Wykonanie planu, która będzie zawierała formułę:

=JEŻELI(D5="";BRAK();F5)

Cała tajemnica tkwi właśnie w funkcji BRAK(). Jedynym jej zadaniem jest zwrócenie błędu #N/D! (BTW: jest to jedna z nielicznych bezargumentowych funkcji w Excelu). Błąd ten informuje wykres liniowy, że nie ma wartości do wyświetlenia. Najcudowniejsze jest to, że etykieta też nie będzie wyświetlała wartości, bo jej tam po prostu nie będzie!

Trik ten można zastosować też dla wykresu kolumnowego, natomiast wtedy trzeba pamiętać o jeszcze jednej opcji: Pokaż błąd N/A jako pustą komórkę. Tę opcję możemy znaleźć w menu Projekt wykresu/Zaznacz dane (jeśli nie masz menu Projekt wykresu masz zapewne Narzędzia wykresów/Projektowanie – jest to zależne od wersji Excela…). Następnie wybieramy przycisk Ukryte i puste komórki i dalej zaznaczamy wspomnianą opcję:

Opcja: Pokaż błąd N/A jako pustą komórkę

Ciekawe jest to, że jeśli opcja ta będzie odznaczona, to wykres liniowy nic nie wyświetli w etykiecie (zgodnie z oczekiwaniami), natomiast wykres kolumnowy wyświetli w etykiecie błąd #N/D!. Trzeba na to uważać.

W naszej formatce i tak nie ma to znaczenia, ponieważ to user wpisuje ręcznie wartość sprzedaży.

Formatka z kolumną pomocniczą wygląda tak:

Kolumna pomocnicza

Teraz trzeba tylko oprzeć na niej wykres liniowy i zrobione.

Edycja wykresu

Możesz edytować istniejącą serię danych (Wykonanie 2020) poprzez:

1. zaznaczenie istniejącej linii na wykresie
2. przesunięcie (drag-and-drop) ramki naokoło danych na kolumnę G:

Edycja zakresu serii „Wykonanie 2020”

I gotowe! Efekt będzie następujący:

WYNIK

Jeśli nie podoba Ci się, że w danych jest kolumna pomocnicza – najprościej będzie ją po prostu ukryć. Wtedy natomiast koniecznie pamiętaj, aby powiedzieć wykresowi, że ma wyświetlać dane z ukrytych komórek. Robimy to w tym samym miejscu, co błąd N/A:

Opcja: Pokaż dane z ukrytych wierszach i kolumnach

Dobrym sposobem jest też ukrywanie danych do wykresu przed użytkownikiem, czyli tworzenie oddzielnych danych, które user widzi i oddzielnych „technicznych”. Jak zwykle wszystko zależy ot tego, do czego finalnie nasze raporty mają służyć.

Produkty, które mogą Cię zainteresować:

• WEBINAR: Zagnieżdżanie funkcji w Excelu – podczas tego webinaru pokazuję jak w prosty sposób zagnieżdżać funkcje jedna w drugiej i trzeciej…
• WEBINAR: Tabele przestawne – wprowadzenie – omawiam na nim o tworzeniu klasycznych tabel przestawnych od podstaw. Jak powinien wyglądać zakres danych? Co, jak i gdzie ułożyć, aby otrzymać czytelny i ładny raport, jakiego potrzebujemy (albo szef potrzebuje;))? Na te pytania odpowiadam podczas tego webinaru.

 Tutaj możesz pobrać plik z rozwiązaniem:

MalinowyExcel Dynamiczny wykres liniowy bez załamania dw.xlsx

I wersja wideo na YT: