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

Что такое динамический диапазон диаграммы?

Диапазон динамической диаграммы — это диапазон данных, который обновляется автоматически при изменении источника данных.

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

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

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

Как создать динамический диапазон диаграмм в Excel?

Есть два способа создать динамический диапазон диаграммы в Excel:

В большинстве случаев использование таблицы Excel — лучший способ создания динамических диапазонов в Excel.

Посмотрим, как работает каждый из этих методов.

Использование таблицы Excel

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

Функция таблицы Excel была представлена ​​в версии Windows для Excel 2007, и если у вас более ранние версии, вы не сможете ее использовать (см. Следующий раздел о создании диапазона динамических диаграмм с помощью формул).

Совет от профессионалов: чтобы преобразовать диапазон ячеек в таблицу Excel, выберите ячейки и используйте сочетание клавиш — Control + T (удерживайте клавишу Control и нажмите клавишу 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 и введите следующую формулу в поле «Относится к части»: = OFFSET (Formula! $ A $ 2 ,,, COUNTIF (Formula! $ A $ 2: $ A $ 100, ”<>”) )
  • Щелкните ОК.
  • Щелкните «Закрыть».

Вышеупомянутые шаги создали два именованных диапазона в Рабочей книге — ChartValue и ChartMonth (они относятся к диапазонам значений и месяцев в наборе данных соответственно).

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

Здесь волшебство творится с помощью функции СМЕЩЕНИЕ.

В формуле именованного диапазона ChartValue мы указали B2 в качестве контрольной точки. Формула СМЕЩЕНИЕ начинается там и распространяется на все заполненные ячейки в столбце.

Та же логика работает и в формуле именованного диапазона ChartMonth.

Шаг 2 — Создайте диаграмму, используя эти именованные диапазоны

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

Вот шаги, чтобы вставить диаграмму и использовать динамические диапазоны диаграммы:

  • Перейдите на вкладку «Вставка».
  • Щелкните «Вставить линию или диаграмму с областями» и вставьте диаграмму «Линия с маркерами». Это вставит диаграмму в рабочий лист.
  • Выделив диаграмму, перейдите на вкладку «Дизайн».
  • Щелкните Выбрать данные.
  • В диалоговом окне «Выбор источника данных» нажмите кнопку «Добавить» в поле «Записи легенды (серии)».
  • В поле «Значение серии» введите = Formula! ChartValues ​​(обратите внимание, что вам необходимо указать имя листа перед именованным диапазоном, чтобы это работало).
  • Щелкните ОК.
  • Нажмите кнопку «Редактировать» в «Ярлыках горизонтальной (категории) оси».
  • В диалоговом окне «Ярлыки осей» введите = Формула! ChartMonths.
  • Щелкните ОК.

Это оно! Теперь ваша диаграмма использует динамический диапазон и будет обновляться при добавлении / удалении точек данных в диаграмме.

Несколько важных вещей, которые следует знать при использовании именованных диапазонов с диаграммами:

  • В данных диаграммы не должно быть пустых ячеек. Если есть пробел, именованный диапазон не будет относиться к правильному набору данных (так как общее количество приведет к тому, что он будет относиться к меньшему количеству ячеек).
  • При использовании имени листа в источнике диаграммы необходимо соблюдать соглашение об именах. Например, если имя листа представляет собой одно слово, такое как Формула, вы можете использовать = Формула! ChartValue. Но если есть более одного слова, например Formula Chart, вам нужно использовать = ‘Formula Chart’! ChartValue.
Как создать динамический диапазон диаграммы в Excel