Spis treści
Załóżmy, że ty i ja musimy zwizualizować dane z poniższej tabeli z wartościami sprzedaży samochodów w różnych krajach w 2021 roku (przy okazji: rzeczywiste dane zaczerpnięte stąd):
Ponieważ liczba serii danych (krajów) jest duża, próba zebrania ich wszystkich na jednym wykresie naraz doprowadzi albo do okropnego „wykresu spaghetti”, albo do zbudowania oddzielnych wykresów dla każdej serii, co jest bardzo kłopotliwe.
Eleganckim rozwiązaniem tego problemu może być wykreślenie wykresu tylko na danych z bieżącego wiersza, czyli wiersza, w którym znajduje się aktywna komórka:
Zaimplementowanie tego jest bardzo proste – potrzebujesz tylko dwóch formuł i jednego malutkiego makra w 3 wierszach.
Krok 1. Aktualny numer linii
Pierwszą rzeczą, jakiej potrzebujemy, jest nazwany zakres, który oblicza numer wiersza w arkuszu, w którym znajduje się teraz nasza aktywna komórka. Otwieranie na karcie Formuły – Menedżer nazw (Formuły — Menedżer nazw), kliknij przycisk Stwórz (Tworzyć) i wprowadź tam następującą strukturę:
- Imię – dowolna odpowiednia nazwa dla naszej zmiennej (w naszym przypadku jest to TekString)
- Obszar – dalej należy wybrać aktualny arkusz, aby tworzone nazwy były lokalne
- łodzie – tutaj używamy funkcji KOMÓRKA (KOMÓRKA), który może wydać kilka różnych parametrów dla danej komórki, w tym potrzebny nam numer linii – odpowiada za to argument „line”.
Krok 2. Link do tytułu
Aby wyświetlić wybrany kraj w tytule i legendzie wykresu, musimy uzyskać odwołanie do komórki z jej nazwą (kraju) z pierwszej kolumny. W tym celu tworzymy kolejny lokalny (tj Obszar = bieżący arkusz, nie Książka!) nazwany zakres z następującą formułą:
Tutaj funkcja INDEX wybiera z podanego zakresu (kolumny A, gdzie leżą nasze kraje podpisujące) komórkę z wcześniej ustalonym numerem wiersza.
Krok 3. Link do danych
Teraz w podobny sposób uzyskajmy link do zakresu ze wszystkimi danymi sprzedaży z bieżącego wiersza, w którym znajduje się teraz aktywna komórka. Utwórz kolejny nazwany zakres za pomocą następującej formuły:
W tym przypadku trzeci argument, którym jest zero, powoduje, że INDEKS zwraca nie pojedynczą wartość, ale jako wynik cały wiersz.
Krok 4. Podstawianie linków na wykresie
Teraz wybierz nagłówek tabeli i pierwszy wiersz z danymi (zakres) i zbuduj na ich podstawie wykres za pomocą Wstaw – wykresy (Wstaw — Wykresy). Jeśli wybierzesz wiersz z danymi na wykresie, funkcja zostanie wyświetlona na pasku formuły RZĄD (SERIA) to specjalna funkcja, której program Excel automatycznie używa podczas tworzenia dowolnego wykresu w celu odniesienia się do oryginalnych danych i etykiet:
Zastąpmy ostrożnie pierwszy (podpis) i trzeci (dane) argument w tej funkcji nazwami naszych zakresów z kroków 2 i 3:
Wykres zacznie wyświetlać dane sprzedaży z bieżącego wiersza.
Krok 5. Ponowne obliczanie makro
Ostatni szlif pozostaje. Microsoft Excel przelicza formuły tylko wtedy, gdy zmienią się dane w arkuszu lub gdy zostanie naciśnięty klawisz F9, i chcemy, aby ponowne obliczenie nastąpiło, gdy zmieni się zaznaczenie, tj. gdy aktywna komórka zostanie przesunięta po arkuszu. Aby to zrobić, musimy dodać do naszego skoroszytu proste makro.
Kliknij prawym przyciskiem myszy kartę arkusza danych i wybierz polecenie Źródło (Kod źródłowy). W oknie, które zostanie otwarte, wprowadź kod obsługi makr dla zdarzenia zmiany wyboru:
Jak łatwo sobie wyobrazić, wszystko, co robi, to uruchamia ponowne obliczenie arkusza za każdym razem, gdy zmienia się pozycja aktywnej komórki.
Krok 6. Podświetlanie bieżącej linii
Dla jasności możesz również dodać regułę formatowania warunkowego, aby wyróżnić kraj, który jest aktualnie wyświetlany na wykresie. Aby to zrobić, wybierz tabelę i wybierz Strona główna — Formatowanie warunkowe — Utwórz regułę — Użyj formuły do określenia komórek do sformatowania (Strona główna — Formatowanie warunkowe — Nowa reguła — Użyj formuły, aby określić, które komórki należy sformatować):
Tutaj formuła sprawdza dla każdej komórki w tabeli, czy jej numer wiersza odpowiada numerowi przechowywanemu w zmiennej TekRow, a jeśli istnieje dopasowanie, wyzwalane jest wypełnienie wybranym kolorem.
To wszystko – proste i piękne, prawda?
Uwagi
- W przypadku dużych tabel całe to piękno może spowolnić — formatowanie warunkowe wymaga dużych zasobów, a ponowne obliczanie dla każdego wyboru może być również trudne.
- Aby zapobiec znikaniu danych na wykresie, gdy komórka zostanie przypadkowo zaznaczona nad lub pod tabelą, możesz dodać dodatkowe zaznaczenie do nazwy TekRow za pomocą zagnieżdżonych funkcji JEŻELI formularza:
=JEŻELI(KOMÓRKA("wiersz")<4;JEŻELI(KOMÓRKA("wiersz")>4;KOMÓRKA("wiersz")))
- Podświetlanie określonych kolumn na wykresie
- Jak stworzyć interaktywny wykres w Excelu
- Wybór współrzędnych