Jak sterować wartościami wykresu za pomocą listy rozwijanej?

Z lekcji dowiesz się:

• Jak listą rozwijaną zmieniać wyświetlany wykres?

• Jakich formuł użyć przy budowaniu wykresu?

• Na czym polega funkcjonalność „kamerki” w Excelu?

Będziemy chcieli osiągnąć taki efekt jak na rys. 1. Położona u góry lista rozwijana steruje zawartością wykresu.

Rys. 1. Cel dzisiejszej lekcji

Najważniejsze w tym zadaniu jest odpowiednie przygotowanie danych. Zadanie wykonamy na podstawie przykładowych danych z rys. 2.

Rys. 2. Przykładowe dane

Tak naprawdę musimy wykonać poszczególne wykresy na podstawie danych z rys. 2 oraz odpowiednio je sformatować. I tak wykres Przychodów i kosztów powstanie na podstawie danych z tabeli z kolumn B i C (rys. 3).

Rys. 3. Wykres Przychodów i kosztów

Wykres Trendu zysku powstał na podstawie danych z tabeli z kolumn A i D, czyli przedstawia wysokość zysku w poszczególnych miesiącach roku (rys. 4).

Rys. 4. Wykres Trendu zysku

Istotne jest, żeby każdy wykres miał dokładnie ten sam rozmiar (zajmował taki sam obszar w arkuszu). Trzeci wykres przedstawia Wykonanie planu, czyli na tym wykresie pokazane jest, czy został osiągnięty plan sprzedaży (rys. 5).

Rys. 5. Wykres Wykonania planu

Do wyniku końcowego będziemy potrzebować obrazów poszczególnych wykresów. Możemy zauważyć kiedy dany element jest obrazem, jak pokazuje się na pasku narzędzi - zakładka Format obrazu.

W pierwszym kroku musimy zająć się typami wykresów, przedstawionymi w małej tabeli na rys. 2. Przechodzimy na arkusz Wykresy, w którym wykonamy to zadanie. Najpierw musimy stworzyć listę rozwijaną, czyli wybieramy polecenie Sprawdzanie poprawności danych z karty Dane (rys. 6).

Rys. 6. Polecenie - Sprawdzanie poprawności danych

Otworzy się okno Sprawdzania poprawności danych, gdzie w Kryteriach poprawności wybieramy w pierwszym polu z listy typ Lista, a następnie w polu Źródło zaznaczamy zakres danych A16:A18 z arkusza DaneWykresy. Tak ustawione parametry sprawdzania poprawności danych zatwierdzamy przyciskiem OK (rys. 7).

Rys. 7 Parametry sprawdzania poprawności danych

Otrzymamy w arkuszu Wykresy listę rozwijaną, gdzie możemy wybrać dowolną nazwę wykresu (rys. 8).

Rys. 8. Lista rozwijana z nazwami wykresów

Tak jak wspomnieliśmy, wszystkie wykresy muszą mieć taki sam rozmiar. Przechodzimy na arkusz DaneWykresy, gdzie zaznaczamy zakres komórek, które zajmuje dowolny z tych wykresów np. Przychód i koszty, następnie kopiujemy ten zakres za pomocą skrótu klawiszowego Ctrl+C. Przechodzimy na arkusz Wykresy i od Excela 2010 możemy w bardzo prosty sposób wkleić ten wykres jako obraz połączony. Klikamy prawym przyciskiem myszy na komórkę, gdzie chcemy wkleić nasz wykres, następnie w podręcznym menu rozwijamy polecenie Wklej specjalnie i wybieramy polecenie Obraz połączony (rys. 9).

Rys. 9. Ścieżka dostępu do wklejania specjalnego obrazu połączonego

Można powiedzieć, że „robimy” zdjęcie tego wykresu, ale nie jest to tak naprawdę zdjęcie wykresu tylko zdjęcie komórek nad którymi znajduje się wykres. To zdjęcie jest połączone z komórkami arkusza i wyświetla to, co znajduje się w nich i nad nimi (wykres). Podsumowując obraz wklejony tym sposobem cały czas odwołuje się do konkretnego zakresu komórek (zakres zaznaczony strzałką na rys. 10).

Rys. 10. Wklejony obraz połączony wykresu

Aby dobrze pokazać działanie polecenia wklej obraz połączony, przesuniemy wykres w arkuszu DaneWykresy i zakres o który przesunęliśmy wykres wypełnimy kolorem żółtym jak na rys. 11.

Rys. 11. Przesunięcie wykresu i dodanie wypełnienia w arkuszu DaneWykresy

Po przejściu na arkusz Wykresy możemy zauważyć, że zakres obrazu połączonego się nie zmienił. Nadal są to te same komórki (H4:N17), ale zmienił się pokazywany obraz (rys. 12).

Rys. 12. Wpływ zmian w danych źródłowych na obraz połączony

Cofamy wprowadzone zmiany za pomocą skrótu klawiszowego Ctrl+Z. Teraz musimy sprawić, aby zakres który jest pokazywany, zmieniał się w zależności od wybranego z listy rozwijanej rodzaju wykresu.

Dodatkowo, żeby nie dawać takich skomplikowanych zakresów, aby łatwiejsze było odwoływanie do nich, nazwaliśmy je odpowiednio Wykres 1, 2 i 3 (rys. 13).

Rys. 13. Nazwane zakresy pod wykresami

Użyjemy teraz funkcji PODAJ.POZYCJĘ, aby zlokalizować komórkę z listą rozwijaną z nazwami wykresów. Pierwszym argumentem funkcji jest szukana_wartość, czyli komórka G2, którą blokujemy bezwzględnie klawiszem F4. Drugi argument funkcji to przesukiwana_tab, czyli zakres z tabeli A16:A18 z arkusza DaneWykresy, zablokowany bazwzględnie. Trzeci opcjonalny argument to typ_porównania, w tym przykładzie wybieramy dopasowanie dokładne, czyli wartość 0. Zapis całej formuły powinien wyglądać następująco:

=PODAJ.POZYCJĘ($G$2;DaneWykresy!$A$16:$A$18;0)

Wynik funkcji PODAJ.POZYCJĘ dla poszczególnych wykresów z listy rozwijanej został przedstawiony na rys. 14.

Rys. 14. Wynik funkcji PODAJ.POZYCJĘ

Teraz musimy odpowiednio przekształcić powyższą formułę, aby zwracała nam zamiast wartości 1, 2 i 3, poszczególne wykresy. Idealnie nadaje się do tego celu funkcja WYBIERZ, która na podstawie numeru argumentu (tego co zwraca nam funkcja PODAJ.POZCYJĘ), zwraca nam odpowiedni wykres. Pierwszym argumentem funkcji jest nr_arg (numer argumentu), czyli wartość uzyskana z funkcji PODAJ.POZYCJĘ. Kolejne argumenty funkcji to wartość1, wartość2 itd.

Podsumowując, jeśli funkcja PODAJ.POZYCYJĘ zwróci wartość 1 to funkcja WYBIERZ zwróci wykres nr 1. Jeśli 2 to funkcja WYBIERZ - zwróci wykres nr 2, itd. Pamiętamy, że ułatwiliśmy sobie zadanie, nazywając poszczególne zakresy pod wykresami.

Zapis całej formuły powinien wyglądać następująco:

=WYBIERZ(PODAJ.POZYCJĘ($G$2;DaneWykresy!$A$16:$A$18;0);Wykres1;Wykres2;Wykres3)

Funkcja WYBIERZ zwraca prawidłowe wyniki lecz pojawia się problem, mianowicie po skopiowaniu tej formuły nie możemy jej wkleić bezpośrednio w pasku formuły, do odwołania w obrazie (obraz nie rozumie tego polecenia). Pojawia się komunikat Excela przedstawiony na rys. 15.

Rys. 15. Komunikat Excela

Możemy sobie z tym problemem poradzić w inny sposób. Musimy skopiować całą formułę, łącznie ze znakiem =, następnie wybieramy polecenie Menedżer nazw z karty Formuły (rys. 16).

Rys. 16. Polecenie Menedżer nazw

Otworzy się okno Menedżera nazw, gdzie wybieramy przycisk Nowy (do stworzenia, zdefiniowania nowej nazwy). Następnie otwiera się okno Nowa nazwa, gdzie w polu Nazwa wpisujemy WybranyWykres. Później w polu Odwołuje się do - wklejamy skopiowaną formułę. Tak ustawioną nową nazwę zatwierdzamy przyciskiem OK (rys. 17).

Rys. 17. Okno Nowej nazwy

Po zatwierdzeniu Nowej nazwy pojawi się ona na liście nazw w Menedżerze nazw, z którego wychodzimy przyciskiem Zamknij (rys. 18).

Rys. 18. Okno Menedżera nazw

Teraz przechodzimy na arkusz DaneWykresy i zaznaczamy obraz wykresu. W pasku formuły zamiast odwołania do konkretnego zakresu, wpisujemy nazwę formuły dodanej do Menedżera nazw, czyli WybranyWykres. Wpisaną nazwę zatwierdzamy przyciskiem Enter. Możemy przetestować działanie naszej formuły, mianowicie wybieramy z listy rozwijanej wykres Trendu zysku i otrzymamy odpowiedni wykres przedstawiony na rys. 19.

Rys. 19. Wywołany wykres Trendu zysku

Po wybraniu z listy rozwijanej wykresu Wykonanie planu, otrzymamy pod spodem odpowiedni wykres (rys. 20).

Rys. 20. Wywołany wykres Wykonanie planu

Podsumowując otrzymaliśmy połączony obraz wykresu, powiązany z listą rozwijaną. Daje nam to możliwość wyboru wykresu, jaki ma się pokazywać za pomocą elementów z listy rozwijanej. Warto również nadmienić, że w Excelu jest taka funkcjonalność jak kamerka. Możemy sprawić, że będzie „patrzeć” na różne dane, w różnych arkuszach i jeśli napiszemy dla niej odpowiednią formułę to możemy pomiędzy tymi zakresami przeskakiwać.

Przeczytaj także:

• Jak w Excelu tablicowym stworzyć dynamiczny raport?

• Fragmentator dla roku fiskalnego

Adam Kopeć