Динамическая диаграмма в Excel

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

Как говорится - хороший вопрос! Приступим.

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

Далее создадим выпадающий список выбора (магазинов). Для этого перейдём на вкладку "Данные", в блоке кнопок "Работа с данными" нажмём кнопку "Проверка данных", выберем тип "Список", а затем укажем диапазон (источник) $A$2:$A$5 (в моём случае).

Подробнее о том как строить выпадающие списки смотрим ЗДЕСЬ.

Получим вот такую картину.

Теперь нам нужен график (диаграмма) пока только по одному магазину. Пусть это будет Ручеек.

Выделяем ячейки с A1:I2 поскольку пока нам будет нужен только он, переходим на вкладку "Вставка", в блоке кнопок "Диаграммы" жмём по треугольнику после кнопки "График" и выбираем "График с маркерами и накоплением" (для большей наглядности). Получим наш график. Как строить диаграммы смотрим ЗДЕСЬ.

И вот теперь мы немного отойдём от привычного построения диаграмм. Для построения динамической диаграммы в Excel нам придётся создать новую переменную - именованный диапазон. Переходим на вкладку "Формулы", в блоке кнопок "Определённые имена" нажмём кнопку "Диспетчер имён".

Перед нами появится следующее окно.

Нажимаем кнопку "Создать", задаём имя для нашего диапазона (я задам _chart), поле "Область" оставим "Книга", если что-то хочется написать в поле "Примечание" - смело пишем. Мы подобрались к самому интересному - полю "Диапазон". Сюда мы напишем следующую формулу:

=СМЕЩ(Лист2!$B$1:$I$1;ПОИСКПОЗ(Лист2!$L$1;Лист2!$A$2:$A$5;0);)

Поясню что есть что. Функция СМЕЩ (смещение) будет обновлять наши данные по магазинам (так как мы построили график только для магазина Ручеек).

Далее в скобках будут показаны пределы данных времени (месяцы) (у мня это от ячейки B1 до ячейки I1). Их обязательно нужно жёстко закрепить (символами $) иначе будем получать неверную информацию.

Функция ПОИСКПОЗ поможет нам найти выбранный в списке магазин, т.е. если я выбираю в ячейке L1 другой магазин формула будет искать в диапазоне от A2 до A5 точное совпадение названия.

Подробнее о функции ПОИСКПОЗ - ВИДЕО С НАШЕГО КАНАЛА.

Нажимаем "ОК", затем мы увидим, что в списке диспетчера имён появился наш диапазон _chart.

Нажимаем "Закрыть" и возвращаемся к нашему графику. По нему щёлкаем правой кнопкой мышки и берём пункт "Выбрать данные".

Где находится поле с названием нашего ряда (Ручеек) кликаем кнопку "Изменить". Имя ряда мы менять не будем (там будут меняться наши магазины), а вот в значениях напишем =Лист2!_chart (можно вообще написать в кавычках имя файла, так как поле области мы оставляли Книга и после восклицательного знака написать имя нашего диапазона).