Jak tworzyć interaktywne wykresy z elementami dynamicznymi w programie Excel

Ten samouczek pokaże, jak tworzyć interaktywne wykresy z elementami dynamicznymi we wszystkich wersjach programu Excel: 2007, 2010, 2013, 2016 i 2022.

Wykres interaktywny to wykres, który pozwala użytkownikowi kontrolować, które serie danych mają być zilustrowane na wykresie, umożliwiając wyświetlanie lub ukrywanie danych za pomocą kontrolek formularza. Występują one w wielu kształtach i formach, od przycisków opcji po paski przewijania i listy rozwijane.

Z kolei uczynienie niektórych elementów wykresu dynamicznymi, takich jak tytuł wykresu, umożliwia zautomatyzowanie procesu aktualizacji wykresów za każdym razem, gdy dodajesz lub usuwasz elementy z bazowych danych źródłowych.

Tak więc tworzenie interaktywnych wykresów z dynamicznymi elementami pozwala upiec dwie pieczenie na jednym ogniu.

Na początek interaktywne wykresy pomagają porządnie wyświetlać ogromne ilości danych za pomocą tylko jednego wykresu:

Ponadto zmiana niektórych elementów wykresu ze statycznych na dynamiczne pozwala zaoszczędzić niezliczone godziny na dłuższą metę i zapobiega zapominaniu o aktualizacji wykresu przy każdej zmianie tabeli danych.

Biorąc to pod uwagę, ten samouczek jest szybkim kursem zaawansowanych technik budowania wykresów w programie Excel: nauczysz się tworzyć interaktywne wykresy zarówno z listą rozwijaną, jak i paskiem przewijania, a także opanujesz sposoby tworzenia tytułu wykresu i dynamika zakresu danych wykresu.

Przykładowe dane

Aby pokazać, co jest czym, potrzebujemy surowych danych do pracy. Z tego powodu rozważ poniższą tabelę ilustrującą dane dotyczące sprzedaży książek w fikcyjnej księgarni internetowej:

Jak stworzyć dynamiczny tytuł wykresu

Najpierw zajmijmy się prostą częścią: uczynienie tytułu wykresu dynamicznym.

Możesz uczynić tytuł dynamicznym, łącząc go z określoną komórką w arkuszu, aby wszelkie zmiany wartości w tej komórce były automatycznie odzwierciedlane w tytule wykresu.

A piękno tej metody polega na tym, że wystarczy kilka prostych kroków, aby zmienić tytuł wykresu ze statycznego na dynamiczny:

1. Kliknij dwukrotnie tytuł wykresu.
2. Kliknij Formuła Bar i wpisz „=" w tym.
3. Wybierz dowolną komórkę, aby przypisać ją jako nowy tytuł wykresu.

Na pierwszy rzut oka pomysł wydaje się dość prymitywny. Ale ledwo zarysowaliśmy powierzchnię rzeczy, które były możliwe dzięki zastosowaniu tej prostej techniki. Aby dowiedzieć się więcej, zapoznaj się z szczegółowym samouczkiem obejmującym bardziej wyczerpujące tytuły wykresów dynamicznych w programie Excel.

Jak utworzyć dynamiczny zakres wykresów

Następnie: konfiguracja dynamicznego zakresu wykresu. Ponownie, konwencjonalnie, za każdym razem, gdy dodajesz lub usuwasz elementy z zakresu danych używanego do tworzenia wykresu Excel, dane źródłowe również powinny być ręcznie dostosowywane.

Na szczęście możesz zaoszczędzić sobie kłopotów z tym wszystkim, tworząc dynamiczny zakres wykresu, który automatycznie aktualizuje wykres za każdym razem, gdy dane źródłowe są zmieniane.

Zasadniczo można to zrobić na dwa sposoby: metoda tabelowa i technika nazwanych zakresów. Omówimy tutaj pokrótce łatwiejszą metodę tabeli, ale aby w pełni zrozumieć, jak wykorzystać moc dynamicznych zakresów wykresów w programie Excel, zapoznaj się z samouczkiem obejmującym wszystkie tajniki bardziej szczegółowo.

W każdym razie wróćmy do metody stolikowej. Najpierw zamień zakres komórek, którego chcesz użyć do zbudowania wykresu, w tabelę.

1. Wybierz cały zakres danych (A2:G6).
2. Udaj się do Wstawić patka.
3. Kliknij "Tabela" przycisk.

Jeśli Twoje dane nie mają żadnych nagłówków, odznacz „Moja tabela ma nagłówki" skrzynka.

Tam zakres danych zostanie przekonwertowany na tabelę.

Teraz wszystko, co musisz zrobić, to wykreślić wykres, używając tabeli jako danych źródłowych wykresu:

1. Zaznacz tabelę (A2:G6).
2. Przejdź do Wstawić patka.
3. Utwórz dowolny wykres 2-D, na przykład grupowany wykres słupkowy (Wstaw wykres kolumnowy lub słupkowy > Słupek grupowany).

Voila! Wszystko gotowe. Teraz przetestuj to, wstawiając nowe elementy do tabeli, aby zobaczyć je automatycznie ponownie na wykresie.

Jak stworzyć interaktywny wykres z rozwijaną listą

Teraz, gdy już postawiłeś pierwsze kroki w świecie zaawansowanych narzędzi do wizualizacji danych w programie Excel, nadszedł czas, aby przejść do najtrudniejszej części: interaktywnych wykresów programu Excel.

Gdy masz dużo danych na wykresie, wprowadzenie interaktywnych funkcji do wykresów programu Excel - czy to list rozwijanych, pól wyboru, czy pasków przewijania - pomaga zachować miejsce na pulpicie nawigacyjnym i uporządkować wykres, jednocześnie czyniąc go wygodniejszym dla użytkownika do analizy danych.

Jako przykład spójrz na wykres kolumnowy pokazany poniżej. Zamiast pokazywać niechlujny wykres zawierający całą tabelę danych, wybierając ten interaktywny wykres z rozwijaną listą, użytkownik może powiększyć dane dotyczące sprzedaży z określonego roku za pomocą zaledwie dwóch kliknięć.

Wystarczy wykonać szczegółowe kroki opisane poniżej, aby dowiedzieć się, jak możesz zrobić to samo.

Krok 1: Połóż podwaliny.

Zanim zaczniemy, uzyskaj dostęp do Deweloper zakładka, która zawiera niektóre z zaawansowanych funkcji Excela, do których zamierzamy przejść dalej. Karta jest domyślnie ukryta, ale dodanie jej do niej wymaga tylko kilku kliknięć wstążka.

Najpierw kliknij prawym przyciskiem myszy dowolne puste miejsce w wstążka i wybierz „Dostosuj Wstążkę.”

w Opcje programu Excel w oknie dialogowym, przejdź do Główne karty listę, sprawdź „Deweloper” i naciśnij “OK.”

ten Deweloper karta powinna teraz pojawić się na wstążka. W tym momencie wybierz dowolną pustą komórkę do wyświetlenia danych wyjściowych menu rozwijanego (B8). Pomoże to Excelowi wyświetlić właściwe dane na podstawie opcji wybranej z listy rozwijanej.

Krok #2: Dodaj listę rozwijaną.

Naszym następnym krokiem jest dodanie menu rozwijanego do arkusza roboczego:

1. Przejdź do Deweloper patka.
2. Kliknij "Wstawić.”
3. Pod "Kontrolki formularzy," Wybierz "Pole kombi.”

Narysuj puste pole kombi w dowolnym miejscu. Aby to zilustrować, oto jak wygląda puste menu rozwijane:

Krok #3: Połącz listę rozwijaną z komórkami arkusza roboczego.

Aby menu zaimportowało listę lat w tabeli danych, musisz połączyć ją z odpowiednim zakresem danych w arkuszu. Aby to zrobić, kliknij prawym przyciskiem myszy pole kombi i wybierz „Kontrola formatu.”

W oknie dialogowym wykonaj następujące czynności:

1. Do "Zakres wejściowy”, podświetl wszystkie rzeczywiste wartości w tabeli danych ($3$:$G$6).
2. Do "Link do komórki”, wybierz pustą komórkę przypisaną do przechowywania wyjścia menu rozwijanego ($8 $).
3. Do "Linie rozwijane”, wprowadź liczbę punktów danych w swoim zbiorze danych (w naszym przypadku cztery) i kliknij „OK.”

Ta akcja powinna spowodować dodanie nazw punktów danych do listy rozwijanej, podczas gdy wartość komórki pomocniczej (B8) charakteryzuje aktualnie wybraną pozycję z menu jako opcję 1, 2, 3 lub 4:

Krok 4: Skonfiguruj tabelę danych wykresu.

Ponieważ wykres powinien ilustrować tylko jedną serię danych naraz, jako obejście, skonfiguruj dynamiczną tabelę pomocniczą, która będzie przeszukiwać nieprzetworzone dane i zwracać wyłącznie dane dotyczące sprzedaży za rok wybrany z listy.

Ta tabela zostanie ostatecznie wykorzystana jako dane źródłowe wykresu, skutecznie umożliwiając użytkownikowi przełączanie widoków za pomocą menu rozwijanego.

Najpierw skopiuj nagłówek tabeli danych sprzedaży i pozostaw pusty wiersz, aby wyświetlić przefiltrowane dane sprzedaży.

Ale jak upewnić się, że dane zostaną poprawnie wyodrębnione? W tym miejscu w grę wchodzi funkcja INDEX. Wprowadź następującą formułę do A11 i skopiuj go do G11:

1	=INDEKS(A3:A6;$B$8)

Zasadniczo formuła filtruje przez kolumnę Rok (kolumna A) i zwraca tylko wartość w wierszu, którego numer odpowiada aktualnie wybranej pozycji na liście rozwijanej.

Krok 5: Skonfiguruj wykres na podstawie danych wykresu dynamicznego.

Udało Ci się przejść przez najtrudniejszą część, więc wystarczy, że utworzysz wykres 2D na podstawie tabeli pomocniczej (podświetl tabelę pomocniczą [A10:G11] > Wstaw > utwórz grupowany wykres kolumnowy).

Na koniec, jeśli chcesz umieścić menu rozwijane na górze wykresu, kliknij prawym przyciskiem myszy pole kombi, kliknij „Zamówienie” i wybierz „Przesuń na wierzch”. (Alternatywnie kliknij prawym przyciskiem myszy wykres wykresu, kliknij „Zamówienie” i wybierz „Wyślij wstecz.”)

Otóż ​​to! Tam masz interaktywny wykres kolumnowy połączony z rozwijanym menu.

Jak stworzyć interaktywny wykres za pomocą paska przewijania

Prawdę mówiąc, proces dodawania paska przewijania do wykresów Excela jest podobny do kroków opisanych powyżej.

Ale co, jeśli chcesz przeanalizować dynamikę sprzedaży każdej kategorii książek (kolumn), zamiast wyświetlać roczne dane dotyczące sprzedaży (wiersze)? Aby to zilustrować, spójrz na poniższy interaktywny wykres liniowy, który właśnie to robi:

Jak to robisz? Cóż, w tym momencie sprawy stają się nieco bardziej złożone. Jeśli jednak trzymasz się poniższego procesu krok po kroku, przestrzeganie instrukcji powinno być jak spacer po parku, nawet jeśli jesteś kompletnym nowicjuszem.

Krok 1: Połóż podwaliny.

Zanim zaczniesz, dodaj Deweloper tabulator do wstążka, ponieważ zawiera funkcję, która pozwala nam narysować pasek przewijania (kliknij prawym przyciskiem myszy dowolne puste miejsce na Wstążce> dostosuj Wstążkę> Główne karty> zaznacz pole „Programista”> OK).

Aby wszystko działało jak w zegarku, przypisz pustą komórkę do wyświetlania danych wyjściowych paska przewijania, aby program Excel mógł poprawnie umieścić pole przewijania na pasku przewijania (B8).

Krok #2: Narysuj pasek przewijania.

Następnym krokiem jest wstawienie paska przewijania do arkusza roboczego:

1. Przejdź do Deweloper patka.
2. Uderz w "Wstawić" przycisk.
3. Pod "Kontrolki formularzy," Wybierz "Pasek przewijania.”

W tym momencie możesz narysować pasek przewijania w dowolnym miejscu. Przeniesiemy go na miejsce później.

Krok #2: Połącz pasek przewijania z danymi arkusza roboczego.

Kliknij prawym przyciskiem myszy pasek przewijania i wybierz „Kontrola formatu.”

w Kontrola formatu w oknie dialogowym zmień ustawienia paska przewijania:

• Aktualna wartość: Ta wartość określa domyślną pozycję pola przewijania na pasku przewijania. Ustaw wartość na „1.”
• Minimalna wartość: To ustawienie oznacza najniższą wartość paska przewijania. W naszym przypadku wpisz „1” w pole.
• Maksymalna wartość: Ten parametr określa najwyższą wartość paska przewijania i powinien odpowiadać liczbie punktów danych, które zostaną użyte do wykreślenia interaktywnego wykresu. Ponieważ tabela danych składa się z sześciu kategorii książek, zmień wartość na „6.”
• Przyrostowych zmian: Ta wartość określa, jak płynnie zmienia się położenie pola przewijania podczas przesuwania go. Zawsze ustawiaj wartość na „1.”
• Zmiana strony: Ta wartość określa, jak płynnie pole przewijania zmienia swoje położenie po kliknięciu obszaru między polem przewijania a strzałkami. Zawsze ustawiaj wartość na „1.”
• Link do komórki: Ta wartość charakteryzuje bieżącą pozycję pola przewijania. Wystarczy podświetlić komórkę w arkuszu roboczym wcześniej przypisanym do tego celu ($8$).

Krok 3: Utwórz tabelę danych wykresu.

Po zakończeniu procesu konfiguracji utwórz nową tabelę pomocniczą do przechowywania dynamicznych danych wykresu wyodrębnionych z nieprzetworzonych danych. Dane wykresu automatycznie dostosują wartości w zależności od aktualnej pozycji pola przewijania, skutecznie łącząc pasek przewijania z wykresem wykresu.

Aby to zrobić, zacznij od kopiowania kolumna Rok jak pokazano na zrzucie ekranu poniżej (A2:A6).

Następnie wdróż następującą funkcję INDEX, która wybiera zakres komórek zawierający nazwy kategorii książek i zwraca odpowiednią wartość na podstawie położenia pola przewijania.

Wpisz następującą formułę do komórki B10:

1	=INDEKS($B$2:$G$2,$B$8)

Teraz zaimportuj dane sprzedaży odpowiadające bieżącej kategorii książki, wprowadzając tę ​​formułę do B11 i skopiuj go (B11:B14):

1	= INDEKS ($ B $ 3: $ G $ 6, MATCH (A11, $ A $ 3: $ A $ 6,0), MATCH ($ B $ 10, $ B $ 2: $ G $ 2, 0))

Połączenie funkcji INDEKS i PODAJ.POZYCJĘ pomaga dokładnie zwrócić wartość, której numer wiersza i kolumny odpowiada określonym kryteriom.

Formuła najpierw filtruje kolumna Rok ($A$3:$A$6) i wybiera wiersz, który pasuje do odpowiedniego roku w tabeli pomocniczej (A11). Następnie formuła przeszukuje nazwy kategorii książek w ten sam sposób (2 USD: 2 USD G. W rezultacie formuła zwraca prawidłową wartość dynamiczną, która dostosowuje się za każdym razem, gdy przesuwasz pole przewijania.

Krok 4: Utwórz wykres na podstawie tabeli pomocniczej.

Po ułożeniu tabeli danych wykresu po prostu zbuduj prosty wykres, aby zobaczyć pasek przewijania w akcji.

Zaznacz całą tabelę pomocniczą (A10:B14), przejdź do Wstawić i wykreśl dowolny wykres 2D, na przykład wykres liniowy (Wstaw wykres liniowy lub warstwowy > Linia ze znacznikami).

UWAGA: Jeśli chcesz, aby pasek przewijania był wyświetlany na górze wykresu, kliknij pasek przewijania prawym przyciskiem myszy, wybierz „Zamówienie” i wybierz „Przenieść”. (Alternatywnie kliknij prawym przyciskiem myszy wykres wykresu, kliknij „Zamówienie” i wybierz „Wyślij wstecz.”)

I tak to robisz. Właśnie nauczyłeś się wszystkiego, co musisz wiedzieć o potężnych zaawansowanych technikach, które poprawią Twoją grę w wizualizację danych w zawrotnym tempie.