Dynamiczny wykres w Excel-u, prezentujący dane tylko ze wskazanego przez pole wyboru dane

Mail od dobrej znajomej:

Czy można zrobić w ten sposób, że jeżeli mam tabele z danymi A B i C i utworzony jest wykres z tych danych, to czy można zrobić listę wyboru, z której wybierasz albo A albo B albo C i wtedy wykres wyświetla tylko!! te dane w obszarze wykresu. Taka lista wyboru…

No nie wiem, ale myślę że się da:) Niestety taka odpowiedź dla mojej znajomej nie była wystarczająca… Jak to zrobić?

Po kolei, myślę, że to chodzi o coś takiego:

Wersja do ściągnięcia znajduje się tu: Dynamiczny wykres

Zmieniając pole wyboru z nazwą miesiąca, wykres prezentuje dane tylko za wskazany miesiąc.

 

Do rozwiązania wykorzytamy fantastyczną funkcję PRZESUNIĘCIE, nazwy i pole combi.

Po kolei: help do funkcji przesunięcie mówi, że należy się do niej odwoływać następująco:

=Przesunięcie(Odwołanie; Wiersze; Kolumny; Szerokość; Wysokość)

Funkcja przesunięcie potrafi zwrócić zakres komórek, zależny od argumentów tej funkcji. Sama nazwa funkcji mówi, że zwrócony zakres komórek powstaje w wyniku przesunięcia komórki o daną liczbę komórek w dół i w prawo. Np. zapis (to tylko początek formuły):

=Przesunięcie($B$2;3,1……

Powoduje, że zwrócony zakres rozpocznie się w komórce C5 (czyli w komórce o 3 w dół i 1 w prawo od komórki B2).

Ale przecież zakres komórek musi mieć swój rozmiar! Podajesz go jako kolejne argumenty funkcji. Dlatego zapis

 =Przesunięcie($B$2;3,1,1,4)

Zwraca zakres rozpoczynający się w komórce C5 i o rozmiarze 1 wiersz wysokim i 4 kolumny szerokim czyli w F5.

Gdyby zmieniać z tego wzoru 3 na inne liczby to w wyniku dostawalibyśmy inne zakresy zakresy komórek np.:

• Dla 1 zakres to C3:F3
• Dla 2 zakres to C4:F4
• Dla 3 zakres to C5:F5
• Itd.

Umówmy się zatem, że w komórce H3 będziemy wpisywać wartość o jaką należy przesuwać komórkę B2, żeby uzyskiwać odpowiednie zaznaczenia.

Za pomocą nazw zdefiniujemy teraz obszary, na podstawie których zbudujemy później wykres.

(Aby zdefiniować nazwę kliknij polecenie Definiuj nazwę na karcie Formuły)

Teraz wstaw wykres, może być zwykły kolumnowy. Na karcie projektowanie kliknij polecenie Zaznacz dane.

Musisz określić, co należy pokazywać na wykresie. Po lewej kliknij Dodaj Aby określić serię danych. Wykorzystując zdefiniowane nazwy (wykorzystujące funkcję przesunięcie) określ skąd brać dane do opisu serii danych i skąd brać jej wartości.

Pozostaje jeszcze zdefiniować jak mają się nazywać kolumny. To robi się w oknie „Wybieranie źródła danych” po prawej stronie. Kliknij Edytuj, a potem zaznacz dane z nazwami miast.

Na tym etapie wykres powinien już coś wyświetlić. Wpisz do komórki wartość 2 i naciśnij Enter, a funkcja przesunięcie przesunie zaznaczenie na wiersz lutego, wykres odwołujący się do tego zakresu przez nazwę zauważy tę zmianę i wyświetli wykres za miesiąc luty!

Ostatnia rzecz, to już tylko kosmetyka. Zrobimy rozwijane pole wyboru (Combo box), w którym będzie można wybrać miesiąc za jaki chcemy prezentować dane.

Na karcie Developer, kliknij polecenie wstaw i wybierz Pole kombi (Formant formularza). Kursor zmieni kształt na krzyżyk, którym możesz zakreślić na arkuszu miejsce, gdzie ma się wyświetlać pole wyboru.

Następnie, mając zaznaczone pole wyboru kliknij właściwości (też na karcie Developer) i wypełnij je jak poniżej. Zakres wejściowy, to teksty, które mają się wyświetlić w polu listy. Łącze komórki to adres komórki do którego zostanie wprowadzany numer wybranej pozycji. W zakresie wejściowym wskaż nazwy miesięcy, a w łączu komórki naszą decyzyjną komórkę (H3). Jeżeli wybierzesz styczeń, to do H3 wpisze się 1, jeżeli wybierzesz luty, to w H3 wpisze się 2 itd.

I to wszystko! Teraz pozostaje się pobawić i błysnąć przed znajomymi!