Как да създадете интерактивни диаграми с динамични елементи в Excel

Този урок ще покаже как да създавате интерактивни диаграми с динамични елементи във всички версии на Excel: 2007, 2010, 2013, 2016 и 2022.

Интерактивната диаграма е графика, която позволява на потребителя да контролира кои серии от данни трябва да бъдат илюстрирани в диаграмата, което прави възможно показването или скриването на данни с помощта на контролите за формуляри. Те идват в много форми и форми, вариращи от бутони с опции до ленти за превъртане и падащи списъци.

На свой ред, направянето на някои елементи на диаграмата динамични-като заглавието на диаграмата-дава възможност за автоматизиране на процеса на актуализиране на графиките ви, когато добавяте или премахвате елементи от основните източници на данни.

Така че създаването на интерактивни диаграми с динамични елементи ви позволява да убиете две птици с един камък.

За да започнете, интерактивните диаграми ви помагат добре да показвате огромни количества данни само с една графика:

В допълнение, превръщането на някои елементи на диаграмата от статични в динамични спестява безброй часове в дългосрочен план и ви предпазва от това да забравите да актуализирате диаграмата, когато променяте таблицата си с данни.

Като се има предвид това, този урок е краш курс за усъвършенствани техники за изграждане на диаграми в Excel: ще научите как да създавате интерактивни диаграми както с падащ списък, така и с лента за превъртане, както и ще овладеете начините да направите заглавието на диаграмата си и диапазонът на данните в графика динамичен.

Примерни данни

За да ви покажем какво е какво, имаме нужда от някои необработени данни, с които да работим. Поради тази причина, помислете за следната таблица, илюстрираща продажбите на книги в измислена онлайн книжарница:

Как да създадете заглавие на динамична диаграма

Нека първо се заемем с лесната част: да направим заглавието на графиката динамично.

Можете да направите заглавието динамично, като го свържете с определена клетка в работен лист, така че всички промени в стойността в тази клетка да се отразяват автоматично в заглавието на диаграмата.

И красотата на този метод е, че са необходими само няколко лесни стъпки, за да превърнете заглавието на вашата диаграма от статично в динамично:

1. Щракнете двукратно върху заглавието на диаграмата.
2. Щракнете върху Лента за формули и въведете „=" в него.
3. Изберете всяка клетка, за да я присвоите като ново заглавие на диаграмата.

На пръв поглед идеята изглежда доста примитивна. Но ние едва дори сме надраскали повърхността на нещата, станали възможни чрез прилагането на тази проста техника. За да научите повече, разгледайте по-изчерпателния урок, обхващащ заглавия на динамични диаграми в Excel.

Как да създадете диапазон на динамични диаграми

Следваща: настройка на динамичен диапазон на диаграмата. Отново, обикновено, всеки път, когато добавяте или премахвате елементи от диапазона от данни, използван при изграждането на диаграма на Excel, изходните данни също трябва да се коригират ръчно.

За щастие можете да си спестите неприятностите да се справяте с всичко това, като създадете динамичен диапазон от диаграми, който автоматично актуализира диаграмата всеки път, когато се променят изходните данни.

По същество има два начина да се направи това: методът на таблицата и техниката с именовани диапазони. Ще разгледаме накратко по -лесния метод на таблицата тук, но за да разберете напълно как да използвате силата на динамичните диапазони на диаграми в Excel, разгледайте урока, обхващащ всички подробности.

Както и да е, обратно към метода на таблицата. Първо, превърнете диапазона от клетки, който искате да използвате за изграждане на диаграма, в таблица.

1. Изберете целия диапазон от данни (A2: G6).
2. Насочете се към Вмъкване раздел.
3. Щракнете върху „Таблица”Бутон.

Ако данните ви нямат заглавки, премахнете отметката от „Моята маса има заглавки" кутия.

Веднъж там, диапазонът от данни ще бъде преобразуван в таблица.

Сега всичко, което трябва да направите, е да начертаете диаграмата си, докато използвате таблицата като изходни данни на диаграмата:

1. Маркирайте таблицата (A2: G6).
2. Придвижете се до Вмъкване раздел.
3. Създайте всяка 2-D диаграма-например клъстерна диаграма (Вмъкване на колона или лентова диаграма> Клъстерна лента).

Вола! Готов сте. Сега го тествайте, като вмъкнете нови елементи в таблицата, за да ги видите автоматично презаредени на графиката.

Как да създадете интерактивна диаграма с падащ списък

Сега, след като сте направили първите си стъпки в сферата на усъвършенстваните инструменти за визуализация на данни в Excel, е време да преминете към трудната част: интерактивните диаграми на Excel.

Когато имате много данни за диаграма, въвеждането на интерактивни функции във вашите графики в Excel-било то падащи списъци, квадратчета за отметка или ленти за превъртане-ви помага да запазите пространството на таблото за управление и да дегустирате диаграмата на диаграмата, като същевременно я направите по-удобна за потребителя да анализира данните.

Като пример, погледнете колоновата диаграма, показана по -долу. Вместо да показва разхвърляна графика, съдържаща цялата таблица с данни, избирането на тази интерактивна диаграма с падащ списък позволява на потребителя да увеличи данните за продажбите за определена година само с две щраквания.

Просто следвайте подробните стъпки, описани по -долу, за да научите как можете да направите същото.

Стъпка #1: Поставете основите.

Преди да започнем, достъп до Разработчик раздел, който съдържа някои от разширените функции на Excel, които ще обърнем, за да продължим по -нататък. Разделът е скрит по подразбиране, но отнема само няколко кликвания, за да го добавите лентата.

Първо щракнете с десния бутон върху всяко празно място в лентата и изберете „Персонализирайте лентата.”

В Опции на Excel диалоговия прозорец, отидете на Основни раздели списък, проверете „Разработчик”И натиснете“ДОБРЕ.”

The Разработчик сега трябва да се показва лентата. В този момент изберете всяка празна клетка за показване на изхода на падащото меню (В8). Това ще помогне на Excel да покаже правилните данни въз основа на опцията, избрана от падащия списък.

Стъпка 2: Добавете падащия списък.

Следващата ни стъпка е да добавим падащото меню към работния лист:

1. Отидете на Разработчик раздел.
2. Щракнете върху „Вмъкване.”
3. Под „Контроли на формуляри, „Изберете“Комбинирана кутия.”

Начертайте това празно комбинирано поле навсякъде, където искате. За илюстрация ето как изглежда празно падащо меню:

Стъпка #3: Свържете падащия списък с клетките на работния лист.

За да може менюто да импортира списъка с годините в таблицата с данни, трябва да го свържете със съответния диапазон от данни в работния лист. За да направите това, щракнете с десния бутон върху комбинираното поле и изберете „Контрол на формата.”

В диалоговия прозорец направете следното:

1. За "Обхват на входа, ”Маркирайте всички действителни стойности в таблицата с данни ($ A $ 3: $ G $ 6).
2. За "Клетъчна връзка, ”Изберете празната клетка, предназначена за съхраняване на изхода на падащото меню ($ B $ 8).
3. За "Падащи линии, ”Въведете броя точки от данни във вашия набор от данни (в нашия случай четири) и щракнете върху„ДОБРЕ.”

Това действие трябва да доведе до добавяне на имената на точките от данни към падащия списък, докато стойността на помощната клетка (В8) характеризира избрания в момента елемент от менюто като опция 1, 2, 3 или 4:

Стъпка #4: Настройте таблицата с данни на диаграмата.

Тъй като диаграмата трябва да илюстрира само една поредица данни наведнъж, като заобиколно решение, създайте динамична помощна таблица, която ще пресява необработените данни и ще връща единствено данните за продажбите за годината, избрани от списъка.

Тази таблица в крайна сметка ще бъде използвана като изходни данни на диаграмата, което ефективно ще позволи на потребителя да превключва изгледите с падащото меню.

Първо копирайте заглавката на таблицата с данни за продажбите и оставете празен ред за показване на филтрираните данни за продажбите.

Но как да се уверите, че данните се извличат правилно? Това е мястото, където функцията INDEX влиза в игра. Въведете следната формула в A11 и го копирайте в G11:

1	= ИНДЕКС (A3: A6, $ B $ 8)

По принцип формулата филтрира през колона Година (колона А) и връща единствено стойността в реда, чийто номер съответства на избрания в момента елемент от падащия списък.

Стъпка #5: Настройте диаграма въз основа на данните за динамичната диаграма.

Преминахте през най-трудната част, така че остава само да създадете двумерна диаграма въз основа на помощната таблица (маркирайте помощната таблица [A10: G11]> Вмъкване> създайте клъстерирана колонна диаграма).

Като последно докосване, ако искате падащото меню да бъде поставено в горната част на диаграмата, щракнете с десния бутон върху комбинираното поле, щракнете върху „Поръчка, “И изберете„Изведете отпред. ” (Алтернативно, щракнете с десния бутон върху графиката, щракнете върху „Поръчка, “И изберете„Изпрати на гърба.”)

Това е! Там имате вашата интерактивна колонна диаграма, свързана с падащото меню.

Как да създадете интерактивна диаграма с лента за превъртане

Честно казано, процесът на добавяне на лента за превъртане към графиките на Excel е подобен на стъпките, описани по -горе.

Но какво, ако искате да анализирате динамиката на продажбите на всяка категория книги (колоните), за разлика от показването на годишните данни за продажбите (редовете)? За да илюстрирате, погледнете интерактивната линейна диаграма по -долу, като направите точно това:

Как го издърпате? Е, там нещата започват да стават малко по -сложни. Ако обаче просто се придържате към стъпка по стъпка процеса по-долу, следвайки инструкциите, би трябвало да се почувствате като разходка в парка, дори ако сте напълно начинаещ.

Стъпка #1: Поставете основите.

Преди да започнете, добавете Разработчик раздел към лентата, тъй като съдържа функцията, която ни позволява да нарисуваме лента за превъртане (щракнете с десния бутон върху всяко празно място в лентата> персонализирайте лентата> основните раздели> поставете отметка в квадратчето „Програмист“> OK).

За да работят нещата по часовник, задайте празна клетка за показване на изхода на лентата за превъртане, така че Excel да може правилно да позиционира полето за превъртане в лентата за превъртане (В8).

Стъпка 2: Начертайте лентата за превъртане.

Следващата ни стъпка е да вмъкнем лентата за превъртане в работния лист:

1. Отидете на Разработчик раздел.
2. Удари "Вмъкване”Бутон.
3. Под „Контроли на формуляри, „Изберете“Плъзгач.”

В този момент можете да нарисувате лентата за превъртане, където пожелаете. Ще го преместим на място по -късно.

Стъпка 2: Свържете лентата за превъртане с данните от работния лист.

Щракнете с десния бутон върху лентата за превъртане и изберете „Контрол на формата.”

В Контрол на формата диалогов прозорец, променете настройките на лентата за превъртане:

• Текуща стойност: Тази стойност определя позицията по подразбиране на полето за превъртане в лентата за превъртане. Задайте стойността на „1.”
• Минимална стойност: Тази настройка означава най -ниската стойност на лентата за превъртане. В нашия случай въведете „1”На полето.
• Максимална стойност: Този параметър определя най -високата стойност на лентата за превъртане и трябва да съответства на броя на точките данни, които да се използват за начертаване на интерактивната диаграма. Тъй като таблицата с данни се състои от шест категории книги, променете стойността на „6.”
• Постепенна промяна: Тази стойност определя колко плавно полето за превъртане променя позицията си, когато я преместите. Винаги задавайте стойността на „1.”
• Промяна на страницата: Тази стойност определя колко плавно полето за превъртане променя позицията си, когато щракнете върху областта между полето за превъртане и стрелките. Винаги задавайте стойността на „1.”
• Мобилна връзка: Тази стойност характеризира текущата позиция на полето за превъртане. Просто маркирайте клетката в работния лист, предварително определен за тази цел ($ B $ 8).

Стъпка #3: Създайте таблица с данни на диаграмата.

След като приключите с процеса на настройка, създайте нова помощна таблица за съхранение на данните от динамичната диаграма, извлечени от необработените данни. Данните в диаграмата автоматично ще коригират стойностите в зависимост от текущата позиция на полето за превъртане, като ефективно свързват лентата за превъртане с диаграмата на диаграмата.

За да направите това, започнете с копиране колона Година както е показано на екрана по -долу (А2: А6).

Веднъж там, разгърнете следната функция INDEX, която избира диапазона от клетки, съдържащ имената на категориите книги и връща съответна стойност въз основа на позицията на полето за превъртане.

Въведете следната формула в клетка В10:

1	= ИНДЕКС ($ B $ 2: $ G $ 2, $ B $ 8)

Сега импортирайте данните за продажбите, съответстващи на текущата категория книги, като въведете тази формула В11 и го копирайте (B11: B14):

1	= ИНДЕКС ($ B $ 3: $ G $ 6, MATCH (A11, $ A $ 3: $ A $ 6,0), MATCH ($ B $ 10, $ B $ 2: $ G $ 2, 0))

Комбинацията от функции INDEX и MATCH помага за точно връщане на стойността на кой номер ред и колона отговаря на посочените критерии.

Формулата първо филтрира колона Година ($ A $ 3: $ A $ 6) и избира реда, който съответства на съответната година в помощната таблица (A11). След това формулата пресява имената на категориите книги по същия начин ($ B $ 2: $ G $ 2). В резултат на това формулата връща правилната динамична стойност, която се коригира всеки път, когато преместите полето за превъртане.

Стъпка #4: Създайте диаграма въз основа на помощната таблица.

След като таблицата с данни за диаграмата бъде събрана, просто изградете проста диаграма, за да видите лентата за превъртане в действие.

Маркирайте цялата помощна таблица (A10: B14), отидете на Вмъкване раздел и начертайте всяка 2-D диаграма-например линейна диаграма (Вмъкване на линия или областна диаграма> Линия с маркери).

ЗАБЕЛЕЖКА: Ако искате лентата за превъртане да се показва в горната част на диаграмата, щракнете с десния бутон върху лентата за превъртане, изберете „Поръчка“И изберете„Изведи напред. ” (Алтернативно, щракнете с десния бутон върху графиката, щракнете върху „Поръчка, “И изберете„Изпрати на гърба.”)

И по този начин го правите. Току -що сте научили всичко, което трябва да знаете за мощните усъвършенствани техники, които ще подобрят играта ви за визуализация на данни с крачка напред.