Как создать динамические диаграммы с раскрывающимися списками в Excel

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

How to Create Dynamic Charts With Dropdown Lists in Excel

<стр> Если вы хотите произвести впечатление на своих друзей и коллег, вы можете внести изменения в диаграмму Excel на основе раскрывающегося списка, который вы добавили на свой лист. Это можно сделать разными способами, но мы предпочитаем следующий метод, поскольку с его помощью легче обнаружить любые проблемы, если что-то пойдет не так.

Мы собираемся использовать таблицу данных, содержащую пять футболистов' имена и их игровой рейтинг для пяти игр, пока мы рассказываем вам об этом процессе.

Шаг 1. Создайте раскрывающийся список

Первый шаг — создать раскрывающийся список, который сделает вашу диаграмму динамичной. Начните с ввода слова действия в ячейку рядом с раскрывающимся списком. В нашем примере мы поместим раскрывающийся список в ячейку B9, поэтому введем слово действия в ячейку A9. Затем щелкните ячейку, в которой будет расположен раскрывающийся список, перейдите на вкладку «Данные» на ленте и нажмите «Проверка данных». в группе «Инструменты работы с данными».

How to Create Dynamic Charts With Dropdown Lists in Excel

<п>В окне «Проверка данных» выберите «Список». в разделе Разрешить. Затем нажмите «Источник». поле и выделите заголовки столбцов в таблице (данные, которые будут отображаться в раскрывающемся списке, который вы создаете). В нашем примере мы хотим выбрать номера игр, так как на диаграмме, которую мы собираемся создать позже, мы хотим видеть игроков' рейтинги каждой игры. Затем нажмите «ОК».

How to Create Dynamic Charts With Dropdown Lists in Excel

<п>Теперь вы увидите раскрывающийся список, показывающий данные, которые вы выбрали, когда нажмете стрелку. Мы также отформатировали ячейку «Выбрать», чтобы она выделялась.

How to Create Dynamic Charts With Dropdown Lists in Excel

Шаг 2. Создайте таблицу извлечения данных

Теперь нам нужно создать вторую таблицу, которая будет отделена от наших необработанных данных, но будет извлекать из нее информацию, когда мы настраиваем созданное нами раскрывающееся меню. Диаграмма, которую мы создадим позже, будет создана с использованием информации из этой таблицы поиска данных.

С этого момента мы будем называть основную таблицу данных Table 1 и таблицу поиска данных Таблица 2. <п>Выделите заголовки строк в Таблице 1, нажмите Ctrl+C и используйте Ctrl+V, чтобы скопировать список в то место, где вы хотите разместить Таблицу 2.

How to Create Dynamic Charts With Dropdown Lists in Excel

<п>Выберите и щелкните правой кнопкой мыши вставленные данные, наведите указатель мыши на пункт «Сортировать». и нажмите «Сортировать от А до Я».

How to Create Dynamic Charts With Dropdown Lists in Excel

Чтобы продолжить создание Таблицы 2, вам нужно дать заголовок следующему столбцу. Данные, отображаемые в таблице 2, будут зависеть от того, что вы выберете в раскрывающемся списке, поэтому в ячейке, в которой вы хотите разместить заголовок, введите = и щелкните раскрывающуюся ячейку. В нашем случае нам нужен заголовок в ячейке I2, и в этой ячейке мы напечатаем = и щелкнем ячейку B9.

How to Create Dynamic Charts With Dropdown Lists in Excel

Нажмите Enter, чтобы увидеть, что заголовок столбца в Таблице 2 принимает раскрывающийся список. Попробуйте изменить раскрывающийся список, чтобы увидеть изменение заголовка столбца в таблице 2.

Шаг 3: Извлечение данных из таблицы 1 в таблицу 2

<п>Теперь нам нужно извлечь соответствующие данные из таблицы 1 в таблицу 2, используя ИНДЕКС и ПОИСКПОЗ. Это самая сложная часть процесса, но давайте разберем ее, чтобы вам было легче пройти весь процесс. Вот синакс:

=ИНДЕКС(a,MATCH(b,c,0),MATCH(d,e,0))

где

<ул>

  • a — все данные таблицы 1 (исключая заголовки строк и столбцов),
  • b — это первое значение, которое мы ищем в таблице 2,
  • .

  • c — это диапазон значений в первом столбце таблицы 1,
  • .

  • d — заголовок столбца в таблице 2, а
  • e представляет заголовки столбцов данных в таблице 1.
  • Убедитесь, что ссылки на ячейки a , c , d и eявляются абсолютными ссылками, нажав F4 на клавиатуре после выбора каждой ссылки. В противном случае ваша формула не будет работать, когда вы заполните остальные данные в таблице 2.

    Давайте посмотрим на это в действии.

    В ячейке I3 мы хотим указать Excel просмотреть таблицу 1 и извлечь значение в таблицу 2 с помощью функции ИНДЕКС.

    =ИНДЕКС

    Теперь нам нужно раскрыть скобки и сообщить Excel, где находятся данные в таблице 1. Для этого выделите все данные в таблице 1, но , а не заголовки столбцов и строк. Нажмите F4, чтобы сделать это абсолютной ссылкой, и добавьте запятую.

    =ИНДЕКС<сильный>($B$3:$F$7,

    <п>Следующим шагом будет указание Excel сопоставить данные в Таблице 2 с данными в Таблице 1. Введите MATCH, откройте новую скобку и щелкните первую запись. в первом столбце таблицы 2. В нашем случае это Дэвис в ячейке H3. Добавьте запятую.

    =ИНДЕКС($B$3:$F$7,MATCH(H3,

    Далее Excel необходимо знать, что он будет использовать в качестве ссылки для поиска в таблице 1. В нашем случае это данные из ячейки A3 в ячейку A7 (имена игроков), поэтому выделите этот диапазон, нажмите F4, а затем добавьте запятую. Затем введите 0 (ноль), чтобы сообщить Excel, что мы ищем точное совпадение, и закройте круглые скобки. Добавьте еще одну запятую.

    =ИНДЕКС($B$3:$F$7,MATCH(H3,$A$3:$A$7,0), <п>Теперь мы должны повторить процесс ПОИСКПОЗ для данных, которые изменятся, когда мы выберем другой вариант в раскрывающемся списке. В нашем случае это номер игры, поэтому после ввода ПОИСКПОЗ в нашу формулу во второй раз мы щелкнем I2, нажмите F4 и добавим запятую.

    =ИНДЕКС($B$3:$F$7,MATCH(H3,$A$3:$A$7,0),MATCH($I$2,

    И снова нам нужно указать Excel, где найти эти данные в таблице 1. В нашем примере это ячейки от B2 до F4. Не забудьте нажать F4 после того, как вы ссылаетесь на эти ячейки. Затем добавьте запятую, 0 и закройте две скобки вместе. Наконец, нажмите Enter.

    =ИНДЕКС($B$3:$F$7,ПОИСКПОЗ(H3,$A$3:$A$7,0),ПОИСКПОЗ($I$2,$B$2: $F$2,0)) <п>Теперь щелкните и перетащите маркер в правом нижнем углу ячейки, в которую вы только что ввели формулу, чтобы автоматически заполнить остальные данные в таблице 2.

    How to Create Dynamic Charts With Dropdown Lists in Excel

    <п>Попробуйте изменить параметр раскрывающегося списка на другое значение и увидеть изменение данных в таблице 2 и убедиться, что они совпадают с информацией в таблице 1. Помните, что мы отсортировали первый столбец в таблице 2 в алфавитном порядке, поэтому проверьте внимательно, так как первый столбец в таблице 1 будет в другом порядке!

    How to Create Dynamic Charts With Dropdown Lists in Excel

    Теперь, когда таблица 2 успешно меняется в зависимости от нашего выбора в раскрывающемся списке, мы готовы создать диаграмму.

    Шаг 4. Вставка и форматирование диаграммы

    Выделите все данные в таблице 2 (включая заголовки столбцов и строк), откройте окно «Вставка». вкладку и выберите диаграмму, которая вам подходит.

    How to Create Dynamic Charts With Dropdown Lists in Excel

    Мы использовали простую двумерную столбчатую диаграмму.

    How to Create Dynamic Charts With Dropdown Lists in Excel

    <п>Отформатируйте диаграмму так, как вы хотите, и снова поиграйте с раскрывающимся списком, чтобы увидеть таблицу 2 и, следовательно, вашу диаграмму — настроить в соответствии с вашим выбором. Вы также увидите изменение названия диаграммы.

    Шаг 5. Добавьте среднюю линию на график

    Чтобы добавить среднюю линию на диаграмму, вам нужно добавить больше данных в таблицу 2. Добавьте еще один заголовок столбца в таблицу 2 и назовите его Average.

    How to Create Dynamic Charts With Dropdown Lists in Excel

    Теперь используйте функцию СРЗНАЧ, чтобы получить среднее значение выбранных данных. В нашем случае мы выбрали игру 4, поэтому нам нужно найти средний рейтинг среди всех пяти игроков в этой игре. Для этого введите

    .

    ​​​​​=СРЕДНЕЕ

    ​​​​​в первой ячейке нового столбца, а затем выберите данные в предыдущем столбце. Нажмите F4, чтобы сделать это абсолютной ссылкой, а затем закройте круглые скобки и нажмите Enter.

    How to Create Dynamic Charts With Dropdown Lists in Excel

    Затем с помощью маркера автозаполнения щелкните и перетащите формулу вниз в конец столбца.

    Теперь мы хотим добавить средние данные на нашу диаграмму. <п>Щелкните в любом месте ближе к краю диаграммы и выберите «Дизайн диаграммы». на ленте. Затем перейдите в группу «Данные» и нажмите «Выбрать данные».

    How to Create Dynamic Charts With Dropdown Lists in Excel

    В окне «Выбор источника данных» нажмите «Добавить».

    How to Create Dynamic Charts With Dropdown Lists in Excel

    Затем в окне «Редактировать серию» очистите поле «Значения серии», а затем выберите данные в столбце «Среднее» таблицы 2. Нажмите «ОК». и вы увидите, что среднее значение отображается в виде дополнительной полосы на вашем графике.

    How to Create Dynamic Charts With Dropdown Lists in Excel

    Чтобы превратить эти средние данные в линию поверх существующих столбцов, щелкните правой кнопкой мыши один из новых столбцов и выберите «Изменить тип диаграммы серии».

    How to Create Dynamic Charts With Dropdown Lists in Excel

    Откройте меню «Все графики». на вкладке «Комбинация» нажмите «Комбинация». в меню слева и измените ряд средних данных на «Линия»."

    How to Create Dynamic Charts With Dropdown Lists in Excel

    Нажмите «ОК», чтобы увидеть результат, и измените раскрывающийся список, чтобы увидеть динамические изменения диаграммы!

    How to Create Dynamic Charts With Dropdown Lists in Excel

    Затем вы можете отформатировать строку так, чтобы она выделялась так, как вы хотите.

    Теперь, когда вы достигли впечатляющей, динамичной диаграммы, узнайте, как сделать ее еще более заметной.

    Оцените статью