Когда вы создаете диаграмму в Excel и исходные данные меняются, вам необходимо обновить источник данных диаграммы, чтобы убедиться, что он отражает новые данные.
Если вы работаете с часто обновляемыми диаграммами, лучше создать динамический диапазон диаграмм.
Что такое динамический диапазон диаграммы?
Диапазон динамической диаграммы — это диапазон данных, который обновляется автоматически при изменении источника данных.
Затем этот динамический диапазон используется в качестве исходных данных на диаграмме. По мере изменения данных динамический диапазон обновляется мгновенно, что приводит к обновлению диаграммы.
Ниже приведен пример диаграммы, в которой используется динамический диапазон диаграммы.
Обратите внимание, что диаграмма обновляется новыми точками данных за май и июнь, как только данные будут введены.
Как создать динамический диапазон диаграмм в Excel?
Есть два способа создать динамический диапазон диаграммы в Excel:
- Использование таблицы Excel
- Использование формул
В большинстве случаев использование таблицы Excel — лучший способ создания динамических диапазонов в Excel.
Давайте посмотрим, как работает каждый из этих методов.
Щелкните здесь, чтобы загрузить файл примера.
Использование таблицы Excel
Использование таблицы Excel — лучший способ создания динамических диапазонов, поскольку она автоматически обновляется при добавлении к ней новой точки данных.
Функция таблиц Excel была представлена в версии Windows для Excel 2007, и если у вас есть более ранние версии, вы не сможете ее использовать (см. Следующий раздел о создании динамического диапазона диаграмм с помощью формул).
Совет профессионала: Чтобы преобразовать диапазон ячеек в таблицу Excel, выберите ячейки и используйте сочетание клавиш — Ctrl + T (удерживая клавишу Ctrl, нажмите клавишу T).
В приведенном ниже примере вы можете видеть, что как только я добавляю новые данные, таблица Excel расширяется, чтобы включить эти данные как часть таблицы (обратите внимание, что рамка и форматирование расширяются, чтобы включить их в таблицу).
Теперь нам нужно использовать эту таблицу Excel при создании диаграмм.
Вот точные шаги по созданию динамической линейной диаграммы с использованием таблицы Excel:
- Выделите всю таблицу Excel.
- Перейдите на вкладку Вставка.
- В группе диаграмм выберите диаграмму «Линия с маркерами».
Вот и все!
Приведенные выше шаги позволят вставить линейную диаграмму, которая будет автоматически обновляться при добавлении дополнительных данных в таблицу Excel.
Обратите внимание, что хотя добавление новых данных автоматически обновляет диаграмму, удаление данных не приведет к полному удалению точек данных. Например, если вы удалите 2 точки данных, на диаграмме отобразится некоторое пустое пространство справа. Чтобы исправить это, перетащите синюю отметку в правом нижнем углу таблицы Excel, чтобы удалить удаленные точки данных из таблицы (как показано ниже).
Хотя я взял пример линейной диаграммы, вы также можете создавать другие типы диаграмм, такие как столбчатые / гистограммы, используя эту технику.
Использование формул Excel
Как я уже упоминал, использование таблицы Excel — лучший способ создавать диапазоны динамических диаграмм.
Однако, если вы по какой-то причине не можете использовать таблицу Excel (возможно, если вы используете Excel 2003), есть другой (немного сложный) способ создания диапазонов динамических диаграмм с использованием формул Excel и именованных диапазонов.
Предположим, у вас есть набор данных, как показано ниже:
Чтобы создать динамический диапазон диаграммы из этих данных, нам необходимо:
- Создайте два динамических именованных диапазона, используя формулу СМЕЩЕНИЕ (по одному для столбцов «Значения» и «Месяцы»). Добавление / удаление точки данных автоматически обновит эти именованные диапазоны.
- Вставьте диаграмму, в которой названные диапазоны используются в качестве источника данных.
Теперь позвольте мне подробно объяснить каждый шаг.
Шаг 1 — Создание динамических именованных диапазонов
Ниже приведены шаги по созданию динамических именованных диапазонов:
- Перейдите на вкладку «Формулы».
- Щелкните «Диспетчер имен».
- В диалоговом окне Диспетчер имен укажите имя как ChartValues и введите следующую формулу в поле Ссылается на деталь: = OFFSET (Formula! $ B $ 2 ,,, COUNTIF (Formula! $ B $ 2: $ B $ 100, ””))
- Щелкните ОК.
- В диалоговом окне «Диспетчер имен» нажмите «Создать».
- В диалоговом окне Диспетчер имен укажите имя как ChartMonths и введите следующую формулу в поле Ссылается на деталь: = СМЕЩЕНИЕ (Формула! $ A $ 2 ,,, СЧЁТЕСЛИ (Формула! $ A $ 2: $ A $ 100, ””))
- Щелкните ОК.
- Щелкните «Закрыть».
Вышеупомянутые шаги создали два именованных диапазона в Рабочей книге — ChartValue и ChartMonth (они относятся к диапазонам значений и месяцев в наборе данных соответственно).
Если вы обновите столбец значений, добавив еще одну точку данных, именованный диапазон ChartValue теперь будет автоматически обновляться, чтобы отобразить в нем дополнительную точку данных.
Здесь волшебство творится с помощью функции СМЕЩЕНИЕ.
В формуле именованного диапазона ChartValue мы указали B2 в качестве контрольной точки. Формула СМЕЩЕНИЕ начинается там и распространяется на все заполненные ячейки в столбце.
Та же логика работает и в формуле именованного диапазона ChartMonth.
Шаг 2 — Создайте диаграмму, используя эти именованные диапазоны
Теперь все, что вам нужно сделать, это вставить диаграмму, которая будет использовать именованные диапазоны в качестве источника данных.
Вот шаги, чтобы вставить диаграмму и использовать динамические диапазоны диаграммы:
- Перейдите на вкладку Вставка.
- Нажмите «Вставить линию или диаграмму с областями» и вставьте диаграмму «Линия с маркерами». Это вставит диаграмму в рабочий лист.
- Выделив диаграмму, перейдите на вкладку «Дизайн».
- Щелкните Выбрать данные.
- В диалоговом окне «Выбор источника данных» нажмите кнопку «Добавить» в «Записи легенды (серии)».
- В поле «Значение серии» введите = Formula! ChartValues (обратите внимание, что вам необходимо указать имя рабочего листа перед именованным диапазоном, чтобы это работало).
- Щелкните ОК.
- Нажмите кнопку «Редактировать» в «Ярлыках горизонтальной (категории) оси».
- В диалоговом окне «Ярлыки осей» введите = Formula! ChartMonths.
- Щелкните ОК.
Вот и все! Теперь ваша диаграмма использует динамический диапазон и будет обновляться при добавлении / удалении точек данных в диаграмме.
Несколько важных вещей, которые следует знать при использовании именованных диапазонов с диаграммами:
- В данных диаграммы не должно быть пустых ячеек. Если есть пробел, именованный диапазон не будет относиться к правильному набору данных (так как общее количество приведет к тому, что он будет относиться к меньшему количеству ячеек).
- При использовании имени листа в источнике диаграммы необходимо соблюдать соглашение об именах. Например, если имя листа представляет собой одно слово, такое как Формула, вы можете использовать = Формула! ChartValue. Но если есть более одного слова, например Formula Chart, вам нужно использовать = ’Formula Chart’! ChartValue.
