Динамически пополняемая диаграмма в MS EXCEL

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

При создании обычных (статических) диаграмм для рядов данных источником значений является конкретный диапазон ячеек исходной таблицы (например, =лист1!$C$3:$C$50). Если в эту таблицу регулярно заносятся данные, то для их корректного отображения на диаграмме потребуется каждый раз переопределять диапазон, либо изначально задавать диапазон большего размера (на вырост). В результате можем получить диаграмму с несколькими пустыми значениями.

Избавиться от последних пустых значений (месяцы от августа по ноябрь на рисунке выше) в диаграмме можно скрыв соответствующие строки в исходной таблице (см. статью Динамические диаграммы. Часть1: Скрытие строк). Однако, это предполагает дополнительное вмешательство пользователя.

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

Идея проста: в качестве источника данных для ряда диаграммы необходимо указать не ссылку на конкретный диапазон ячеек, а ссылку на Динамический диапазон, созданный например, с помощью функции СМЕЩ().

Для начала создадим Именованную формулу для определения количества введенных значений:

  • нажмите кнопку Присвоить имя (Формулы/ Определенные имена/ Присвоить имя);
  • в поле имя введите ЧислоЗначений;
  • в поле диапазон, предварительно всё удалив, введите =СЧЁТ($C3:$C40);
  • нажмите ОК.

О подсчете ячеек, содержащих числа, можно прочитать в статье Подсчет ЧИСЕЛ (в диапазонах с ТЕКСТом и ЧИСЛАми). Предполагается, что числовые значения вводятся в столбец С без пропусков.

Аналогично создадим 2 динамических диапазона Месяцы и Платежи, указав в поле Диапазон формулу =СМЕЩ($B$3;;;ЧислоЗначений) для подписей по горизонтальной оси и =СМЕЩ($C$3;;;ЧислоЗначений) для сумм платежей. Первая формула будет возвращать ссылку на диапазон от ячейки B3 до последней строки, содержащей значение в столбце С. Вторая формула вернет ссылку на аналогичный диапазон в столбце С.

Чтобы изменить ссылку на значения ряда - выделите диаграмму.

Через меню Конструктор / Данные/ Выбрать данные укажите в качестве источника значений для рядов диаграммы имена только что созданных динамических диапазонов Месяцы и Платежи. Перед именем диапазона необходимо указывать название книги (если область видимости имени была задана Книга),

иначе EXCEL будет выдавать ошибку.

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

Протестируем. Добавим значение 40000, соответствующее августу 2011г.

На рисунке ниже - вид диаграммы ДО добавления нового значения.

 Теперь, на рисунке ниже - вид диаграммы ПОСЛЕ добавления значения.

Диаграмма автоматически обновилась без вмешательства пользователя.

СОВЕТ: В случае удаления всех значений из таблицы, значение именованной функции ЧислоЗначений станет равным 0 и, соответственно, значение формулы =СМЕЩ($B$3;;;ЧислоЗначений) вернет ошибку, т.к. значение аргумента «высота» функции СМЕЩ() должно быть положительным числом.

Для восстановления работоспособности диаграммы добавьте хотя бы одно значение в таблицу (месяц и сумму платежа), сохраните файл, закройте его и откройте заново.

СОВЕТ: Для начинающих пользователей EXCEL советуем прочитать статью Основы построения диаграмм в MS EXCEL, в которой рассказывается о базовых настройках диаграмм, а также статью об основных типах диаграмм.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (4 оценок)
Яндекс.Метрика