Zademonstruję jak można wizualizować dane przy użyciu jednego, interaktywnego grafu. Zbudujemy dynamiczny wykres z wykorzystaniem formantów. Będziemy na nim pokazywać (lub ukrywać) jedną bądź kilka wybranych serii danych. W procesie wykorzystamy narzędzia o nazwie „Kontrolki formularza”. Tzw. formanty nie są zbyt często stosowane w codziennej pracy z Excelem. No może poza standardowymi przyciskami, których często używa się do uruchamiania makr. W opisywanym przykładzie z VBA nie zastosujemy w ogóle, całą sprawę załatwi kilka formuł oraz wspomniane formanty.
Efekt końcowy na jakim nam zależy wygląda jak poniżej. Na jednym wykresie chcemy zaprezentować wartości z roku aktualnego, wartości z roku poprzedniego oraz wartości planowane. To wszystko dodatkowo z podziałem na 3 kanały bądź w totalu (zsumowane razem). Zamiast tworzyć kilka równoległych grafów całość przedstawimy na jednym wykresie kombi. Użytkownik końcowy , w zależności od swoich potrzeb, będzie sterował wykresem za pomocą 2 rodzajów formantów. W tym przypadku będą to „Pola wyboru” tzw. checkboxy oraz „Przyciski opcji” umieszczone w prawym górnym rogu.
Przygotowanie danych do wykresu
Proces zaczynamy od przygotowania danych pomocniczych. Najlepiej w układzie z podziałem na lata + wartości planowane w 3 osobnych tabelach. Każda z podziałem na miesiące oraz kanały + wartość total.
W kolejnym kroku dodajemy tabelę, w której zaczytywane wartości będą sterowane formantami.
Dodanie formantów – checkboxy i przyciski opcji
Teraz dodajemy formanty. Zaczynamy od checkboxów. Za ich pomocą chcemy pokazywać/ukrywać na wykresie wynik z poprzedniego oraz wartości planowane na rok obecny. Wchodzimy w kartę „Deweloper”, wybieramy „Wstaw” i klikamy w ikonkę z ptaszkiem. Wybieramy miejsce na arkuszu i wstawiamy pierwszy checkbox. Zmieniamy jego nazwę na „Pokaż wyniki z poprzedniego roku”. Czynność powtarzamy, drugi checkbox podpisujemy „Pokaż plan na 2018”.
W kolejnym kroku dodamy 4 „Przyciski opcji”. Postępujemy podobnie jak wyżej, ale wybieramy ikonkę z kółkiem i kropką w środku. Aby nieco ułatwić proces możemy z menu wstawić formant raz, a kolejne tworzymy przez przytrzymanie pierwszego formantu lewym przyciskiem myszki z włączonym klawiszem Ctrl, jego przeciągnięcie i upuszczenie obok. Następnie zmieniamy nazwy odpowiednio na „Total”, „Kanał1”, „Kanał2”, „Kanał3”.
Połączanie formantów z komórkami
Teraz rzecz najważniejsza. Dla naszych formantów ustawiamy łącza do komórek, które będą zwracać wartości wybrane w formantach. Klikamy na pierwszy checkbox prawym przyciskiem myszki i z menu podręcznego wybieramy opcję „Formatowanie formantu”. Otworzy się nowe okienko. W ostatniej zakładce, w miejsce przeznaczone na „Łącze komórki:” wstawiamy wybrane miejsce klikając na nie w arkuszu. Czynność powtarzamy dla checkboxa nr 2. W przypadku formantów z kanałami postępujemy podobnie. Przy czym tutaj wystarczy ustawić łącze tylko raz. Jeśli wykonamy to poprawnie to zaobserwujemy, że zaznaczone checkboxy będą dynamicznie zwracać wartość „Prawda”, odznaczone „Fałsz”, a „Przyciski opcji” zwrócą numer wybranej pozycji.
Teraz pora na uzupełnienie naszej tabeli, którą finalnie podepniemy do wykresu kombi. Wykorzystamy kombinację formuł JEŻELI(), INDEKS() i BRAK(). Zacznijmy od wiersza przeznaczonego na wyniki za 2018 rok. Aktualne wyniki chcemy pokazywać za każdym razem, tzn. nie chcemy dodatkowo ukrywać ich za pomocą checkboxów. W komórce przeznaczonej dla stycznia stosujemy formułę INDEKS(), która w zależności od wartości zwróconej przez „Przycisk opcji” zaczyta odpowiednie dane. Odpowiednio blokujemy zakresy komórek i przeciągamy funkcję dla wszystkich miesięcy, aż do grudnia.
W przypadku wierszy przeznaczonych dla danych za 2017 i dla planu formuła odrobinę się zmieni. Wartości te w zależności od zaznaczenia checkboxów chcemy pokazywać bądź nie. Z tego względu formułę INDEKS() zagnieżdżamy w JEŻELI(). Dodatkowo dla FAŁSZÓW formuła powinna zwracać błąd, a konkretnie „#N/D” generowany za pomocą funkcji BRAK(). Jest to niezbędne aby nasze wykresy nie pokazywały niechcianych serii danych odznaczonych w checkboxach. Poniżej formuła dla wierszy dotyczących 2017.
Oraz dla wierszy z planem. W obu przypadkach odpowiednio blokujemy zakresy i formuły przeciągamy do końca roku – do grudnia.
Możemy teraz przetestować działanie naszych formantów. W zależności od wybranej kombinacji w naszej tabeli pokażą się odpowiednie wartości.
Dynamiczny wykres z wykorzystaniem formantów – wygląd końcowy
Dane mamy przygotowane więc w ostatnim etapie budujemy na nich dynamiczny wykres. Wyniki za 2017 i 2018 prezentować będziemy na wykresach kolumnowych, wartość planu na wykresie liniowym. Zaznaczamy zakres wartości razem z nazwami miesięcy, wstawiamy i konfigurujemy wykres. Aby ograniczyć autoskalowanie wykresu dla kanałów z mniejszymi wartościami, w opcjach formatowania wartości osi głównej ustawiamy wartość dla maximum odrobinę większe, od maksymalnej wartości miesięcznej dla naszego totalu. Kolejny zabieg jaki możemy wykonać dla większej czytelności wykresu to zgrupowanie naszych formantów w dwa „Pola grupy”, także dodawane za pomocą przycisku wstaw na karcie „Deweloper”. Gdy wykres uznamy za ukończony ustawiamy go w ten sposób, aby zakrywał nasze dane z tabel. Opcja grupowania obiektów znacznie ułatwi pracę przy przesuwaniu i ustawianiu naszej ostatecznej wersji.
Podsumowanie
Mój finalny, dynamiczny wykres z wykorzystaniem formantów wygląda w poniższy sposób. W tym konkretnym przypadku, na jednym grafie przedstawiamy 16 różnych kombinacji układu danych. Zaoszczędzi nam to masę miejsca w arkuszu, ale także czasu potrzebnego na zbudowanie i sformatowanie 16 osobnych wykresów. Dodatkowo dane zaprezentowane w ten sposób będą łatwiejsze do przeanalizowania. Łatwiej będzie wyciągnąć z nich wnioski.
Loading...
