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.
Narzędzie jest bardzo pomocne, niestety nie mogę przebrnąć przez jeden krok. W jaki sposób w excelu 2010 przywołać okno „Nowa nazwa” celem zmniejszenia wyświetlanego zakresu? Nie za bardzo rozumiem, gdzie wpisać formułę „Przesunięcie”.
Nazwy zdefiniowane można znaleźć pod Ctrl+F3 > Nowy lub Formuły > grupa Nazwy zdefiniowane > Definiuj nazwę.