Jeden z Czytelników zapytał mnie, w jaki sposób można sterować wyświetlaniem dat na wykresie liniowym w Excelu. Bez programowania VBA w Excelu są dwa sposoby: wykorzystanie osi czasu w tabeli przestawnej (dostępne od Excela 2013) lub wstawienie przycisku – paska przewijania i posłużenie się funkcją PRZESUNIĘCIE.

Sposób 1: Oś czasu

Przygotowanie danych dla Osi czasu – tabela przestawna

Od czasu wejścia do użytku wersji programu Excel 2013 dostępna jest możliwość wykorzystywania specyficznego fragmentatora, jakim jest Oś czasu. Opcja ta była już opisywana na blogu dla przykładu w artykule Oś czasu – nowy sposób na filtrowanie dat na dashboardzie w programie Excel 2013. Dane użyte do stworzenia dynamicznego wykresu zostały pobrane z rozpatrywanej już wcześniej bazy sprzedaż.xls, którą w tym przypadku delikatnie przebudowano.
Niezbędne w przypadku chęci wykorzystania opisywanego narzędzia filtrującego jest utworzenie tabeli przestawnej. Wykorzystamy do tego danego dotyczące daty zamówienia podzielonej na miesiąc oraz rok, jak również uzyskany z niego zysk.

Zwinięta tabela przestawna powinna wyglądać w ten sposób

 

Wybór, utworzenie i modyfikacje wykresu

Kolejnym krokiem jest utworzenie wykresu. Celem estetycznego i wygodnego ukazania danych stworzony zostanie wykres liniowy.

Dla zasugerowanych danych powinien on wyglądać mniej więcej tak

Jak widać, posiada on kilka przycisków oraz tytuł, które z powodzeniem można w tym przypadku usunąć. Pozwoli to uzyskać znacznie lepszą przejrzystość. Ukrycie przycisków dokonywane jest poprzez zaznaczenie wykresu, a następnie zastosowanie się do następującej ścieżki ANALIZA>Pokazywanie/ukrywanie>Przyciski pól>Ukryj wszystko.

Wykres przyjmuje w tym momencie następującą formę

 

Wprowadzenie Osi czasu

Przyszła kolej na wstawienie osi czasu. Odnaleźć ją można korzystając z następującej ścieżki: WSTAWIANIE>Filtry>Oś czasu

Po kliknięciu na ikonkę skorelowaną z tą funkcją pojawi się okno dialogowe Wstawianie osi czasu.

Ilość opcji wyboru zależy w tym momencie od ilości danych o formacie daty.

Po wybraniu odpowiedniego zakresu danych (w tym przykładzie – data zamówienia) pojawi się gotowa do użytkowanie oś czasu. Posiada ona kilka możliwości definiowania ukazywanego zakresu czasu. Filtry dostępne są w prawym górnym rogu.

 

Sposób 2: Zastosowanie suwaka zintegrowanego z wykresem

Drugim ze sposobów na udynamicznienie wykresu jest wprowadzenie formantu, którym jest pasek przewijania dostępny w zakładce Deweloper. Jednak to jak go umieścić zostanie wyjaśnione za moment.

 

Przygotowanie danych

Ponownie jak powyżej tak i w tym przypadku pierwszym etapem jest określenie wykorzystywanego zakresu danych. Nie chcąc wprowadzać zbytniego zamieszania, użyte zostaną te same dane co w przykładzie z osią czasu. Tym razem jednak nie ma potrzeby tworzenia tabeli przestawnej.

Kolejnym etapem jest utworzenie wykresu. Tym razem będzie to wykres kolumnowy. Przy zaznaczeniu pełnego zakresu danych powinien w tym przypadku wyglądać mniej więcej tak

Jego czytelność pozostawia wiele do życzenia. Należy również pamiętać, iż w przypadku większej ilości danych przejrzystość będzie coraz bardziej zaburzana. By tego uniknąć wprowadzony zostanie wspomniany już pasek przewijania.

Zmniejszenie wyświetlanego zakresu danych

Pierwszym etapem jest ustanowienie zakresu danych jakie jednocześnie będą wyświetlane na wykresie. Przyjmijmy, iż będzie to 6 kolumn, czyli sześć miesięcy. Zakresy te będą się systematycznie przesuwać, a pomoże nam w tym wykorzystanie funkcji PRZESUNIĘCIE. Nazwijmy pierwszy z zakresów Data i wybierzmy 6 spośród zawartych w wykazie dat.

Jako odwołanie należy wpisać:

=PRZESUNIĘCIE(Suwak!$A$2;PRZESUNIĘCIE;0;6;1)

Drugim zakresem będzie zysk. Utworzyć trzeba go analogicznie do poprzedniego. W tym jednak przypadku jako odwołanie wpisujemy:

=PRZESUNIĘCIE(Suwak!$B$2;PRZESUNIĘCIE;0;6;1)

Komórką sterującą, niezbędną w przypadku stosowania paska przewijania, będzie komórka D2, którą również nazwiemy jako osobny zakres. Przyjmijmy, iż będzie to zakres PRZESUNIĘCIE. Zakres ten, co łatwo zauważyć, używany jest we wcześniejszych odwołaniach. Bez problemu można także wywnioskować znaczenie pojawiającej się w formułach cyfry 6. Jest to oczywiście wielkość przesuwalnego zakresu, która oznacza ile dat będzie jednocześnie wyświetlanych na wykresie.

 

Definiowanie ustawień zakresu

Następnym etapem jest zmiana wartości serii ukazywanej na wykresie. Wykorzystany zostanie do tego utworzony przed momentem zakres „Zysk”.

Ustawienie te podłącza nazwę zdefiniowaną Zysk do serii wykresu.

Analogicznie postępujemy z serią Data, którą podłączamy do etykiet:

Na sam koniec pozostaje wprowadzenia wspominanego już paska przewijania. Odnaleźć go można na karcie DEWELOPER>Formanty>Wstaw>Kontrolki formularza>Pasek przewijania

 

Jako łącze komórki należy wstawić zakres nazwany wcześniej PRZESUNIĘCIE. Ostatecznie po umieszczeniu paska przewijania w dowolnym miejscu, dana konstrukcja wygląda następująco:

Plik do pobrania

Pobierz Dynamiczna zmiana ilości wyświetlanych danych w Excelu i wstaw swoje dane.