Создание динамической диаграммы в Excel с помощью именованных диапазонов

Мы уже рассматривали концепцию динамической диаграммы в одной из предыдущих статей, когда проектировали дашборд. На тот момент, внедренная полоса прокрутки, позволила нам отображать определенную часть информации и прокручивать столбцы диаграмм по мере необходимости. Данный функционал дает возможность экономить место на рабочем листе Excel и фокусироваться на тех данных, которые на самом деле важны. Сегодня мы рассмотрим, как с помощью выпадающего списка и именованных диапазонов создать динамическую диаграмму, которая меняет свой внешний вид в зависимости от ваших потребностей.

Описание проблемы

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

Таблица данных

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

Динамическая таблица в Excel

Создание динамической диаграммы

В первую очередь необходимо создать выпадающий список, откуда мы будем выбирать, интересующий нас, показатель. Переходим по вкладке Разработчик в группу Элементы управления, выбираем Вставить –> Элементы управления формы –> Поле со списком.

Поле со списком

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

Щелкните правой кнопкой мыши по выпадающему списку, выберите Формат объекта. В появившемся диалоговом окне Формат элемента управления, задайте диапазон ячеек, откуда будет формироваться список (в нашем случае, это список всех показателей, по которым мы будем строить график), и ячейку, куда будет помещаться результат выбора из списка.

Формат объекта

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

Переходим по вкладке Формулы в группу Определенные имена, выбираем Диспетчер имен и создаем два диапазона с именами значения и название с соответствующими формулами.

=СМЕЩ(ДинамДиагр!$A$4;ДинамДиагр!$A$16;1;;7)

=ИНДЕКС(ДинамДиагр!$A$5:$A$14;ДинамДиагр!$A$16)

Диспетчер имен

На рабочем листе с таблицей с данными выбираем диапазон A1:H2, переходим по вкладке Вставка в группу Диаграммы, выбираем Диаграмму с областями. Excel построил нам диаграмму с одним рядом данных, как мы его и просили.

Диаграмма с областями

Щелкаем по ряду данных в области диаграммы. В строке формул вы увидите функцию формирующую ряд данных диаграммы. Где первый параметр отвечает за название ряда, второй – за ось категорий, третий — это значения нашего ряда данных.

Формула РЯД

Меняем значения первого и третьего параметра на уже подготовленные именованные диапазоны

=РЯД(ДинамДиагр!$A$2;ДинамДиагр!$B$1:$H$1;ДинамДиагр!$B$2:$H$2;1)

Должно получиться так:

=РЯД(ДинамДиагр!название;ДинамДиагр!$B$1:$H$1;ДинамДиагр!значения;1)

Теперь при изменении значения в выпадающем списке, наша динамическая диаграмма будет менять внешний вид. Так как именованные диапазоны, которые мы подставили вместо статических параметров в формуле РЯД, тоже будут менять свои адреса.

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

Осталось задать привлекательный формат нашей диаграмме. Убираем все лишние элементы: линии сетки и название диаграммы. Меняем цвет ряда данных, добавляем к нему линии проекции. Задаем цвет области построения и области диаграммы.

Чтобы название диаграммы вписывалось в общую композицию, вставляем элемент Надпись над диаграммой. Щелкаем левой кнопкой мыши по границе надписи, при этом линия границы становится сплошной линией, в строке формул вставляем именованный диапазон, отвечающий за название ряда данных. В нашем случае он так и называется название.

Название диаграммы

Динамическая диаграмма готова.

Скачать файл с примером динамической диаграммы в Excel.


15 комментариев

  1. есть подозрение, что формула смещения должна быть =СМЕЩ(ДинамДиагр!$A$4;ДинамДиагр!$A$16;1;;7)
    а в формате объекта «поле со списком» должна быть связь с ячейкой A16….иначе фокус не удается…..

  2. Добрый день, а есть ли возможность создать динамическую диаграмму с возможностью выбора не одного показателя, а группу показателей? То есть, в выпадающем списке у нас идут «Группа 1», «Группа 2», …. , а в самой диаграмме уже несколько показателей по этим группам.

    К примеру, у меня есть три таблицы, первая — количество проданных товаров по регионам, вторая — количество расходов по филиалам, третья — доходы по типу товаров. Требуется создать диаграмму, где в выпадающем списке выбираются условно пункты «Таблица 1», «Таблица 2» и «Таблица 3», а в диаграмме отображаются показатели всех филиалов или регионов из соответствующих таблиц.

  3. Видимо, у меня проблемы с диапазонами.
    а еще у вас в тексте формулы одни, а в примерах другие… Таким, как я, трудно разобраться.

  4. Добрый день! На второй картинке залита одним цветом область «под графиком». Возможно ли сделать разные цвета для каждого «сектора»? И если да, то с какой версии данный функционал поддерживается. Заранее спасибо!

  5. Добрый день! Формулу записала, как в примере, получаю «Формула на этом листе содержит одну или несколько недопустимых ссылок». С чем это может быть связано?

  6. А можно подробней о функции =СМЕЩ(ДинамДиагр!$A$4;ДинамДиагр!$A$16;1;;7)
    У меня немного другая таблица и хотелось бы понять, что означают аргументы «1», затем два раза подряд точка с запятой, затем «7».

    Не могу приспособить это к своей таблице.

  7. Почему на скриншоте таблицы одно, а по коду идет совсем другое, ужасный пример! Если уж делаете, то делайте по одному примеру, а не по двум

  8. Нормальный пример, просто напутаны немного диапазоны.
    Что бы все работало и соответствовало таблице на первом рисунке должно быть так:

    В выпадающем списке — диапазон $A$2:$A$11 — столбик с наименованиями
    Связь с ячейкой $A$16 — в нее будет выводится порядковый номер наименования

    Диспетчер имен (выводится клавишами CRTL + F3):
    для наименования «название» формула:
    =ИНДЕКС(ДинамДиагр!$A$2:$A$11;ДинамДиагр!$A$16) — расшифровка: =ИНДЕКС([Название листа]![столбик с наименованиями];[Название листа]![ячейка, в которую выводится порядковый номер наименования])
    для наименования «значения» формула:
    =СМЕЩ(ДинамДиагр!$A$1;ДинамДиагр!$A$16;1;;7) — расшифровка: =СМЕЩ([Название листа]![первая ячейка первого столбика, с которой начинается таблица];[Название листа]![последняя первого столбика, в которой заканчивается таблица, в нашем случае — ячейка вывода];[номер столбика, в котором начинаются значения];;[номер столбика в котором заканчиваются значения]), т.е. если в таблице 20 столбцов со значениями вместо 7 будет 20.

  9. Добрый день.

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

    Спасибо!

  10. Файл — Параметры — Дополнительно — раздел «Диаграмма» — убрал флаг с пункта «Свойства изменяются при изменении точек данных диаграммы для текущей книги».

    Только не понимаю, за что конкретно отвечает этот параметр.

Добавить комментарий для DENis Отменить ответ

Ваш e-mail не будет опубликован. Обязательные поля помечены *