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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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