Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вам также могут быть интересны следующие статьи

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Нажимаем ОК и проверяем — выбираем из списка другие магазины и смотрим за изменениями графика!

Пишите комментарии если что-то было непонятно!

В диаграммах Excel есть один небольшой минус. Если построить диаграмму на основе данных, которые планируется добавлять со временем, то по мере добавления данных в таблицу, на которой основана диаграмма, придется также менять диапазон данных для диаграммы, чтобы включить их в отображение. Либо сразу указать заведомо больший диапазон, но тогда диаграмма весьма некрасиво выглядит. Это не такая уж и проблема, если данные добавляются один раз в месяц. А если это необходимо делать каждый день? Или несколько раз в день?
Итак, у нас имеется таблица в столбцах A , B и С — Дата , Количество посетителей и Количество просмотров соответственно.

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

  • Жмем Ctrl + F3
  • В поле Диапазон (Refers to) пишем формулу =СМЕЩ(Таблица!$A$2:$A$2;;;СЧЁТЗ(Таблица!$A$1:$A$1000);)
  • В поле Имя (Name) — желаемое имя для данного диапазона(в данном случае лучше давать понятное имя — имя ряда) — Дата.

Тоже самое делаем с двумя остальными рядами. В результате у нас получится три именованных диапазона:

  • Дата =СМЕЩ(Таблица!$A$2:$A$2;;;СЧЁТЗ(Таблица!$A$1:$A$1000);)
  • Количество посетителей =СМЕЩ(Таблица!$B$2:$B$2;;;СЧЁТЗ(Таблица!$B$1:$B$1000);)
  • Количество просмотров =СМЕЩ(Таблица!$C$2:$C$2;;;СЧЁТЗ(Таблица!$C$1:$C$1000);)

Теперь кликаем правой кнопкой мыши по диаграмме:

для Excel 2007-2010 — Выбрать данные (Select Data) ;
для Excel 2003 — Исходные данные (Source data) .

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

Важно: при замене диапазона заменить надо только адрес ссылки, а имя листа и книги оставить неизменными. Т.е. должно получиться — =Таблица!Дата или =Dinamic_Chart.xls!Дата

Жмем Ок. Все, теперь при добавлении данных в таблицу, данные в диаграмме тоже добавятся.

Вариант для Excel 2007 и выше
Это быстрый и просто способ, который рассчитан на то, что диаграмма строится на основании всех столбцов таблицы. Основан он на том, что Excel позволяет создавать такой объект, как таблица(в 2003 Excel это называется Список). Хоть такой объект есть и в 2003 версии, но в 2003 невозможно узнать имя этой таблицы без применения VBA.
Для начала потребуется создать так называемую "умную таблицу". Создать её можно двумя способами
Способ 1:

  1. Выделить любую ячейку области данных, перейти на вкладку Вставка (Insert) —Таблица (Table)
  2. В появившемся окне согласиться с указанным диапазоном или выбрать свой
  3. Нажать OK

Способ 2:
Выделить любую ячейку области данных
Выбрать команду на вкладке Главная (Home) —группа Стили (Styles) —Форматировать как таблицу (Format as Table)

После этого идем в диспетчер имен( Ctrl + F3 или вкладка Формулы —Диспетчер имен) и находим там нашу таблицу. На скрине ниже это "Таблица1":

это и есть нужная нам таблица.
Создаем диаграмму, правая кнопка мыши по области диаграммы:

для Excel 2007-2010 — Выбрать данные (Select Data) ;
для Excel 2003 — Исходные данные (Source data) .

Вписываем имя таблицы в качестве диапазона для данных диаграммы:

Обращаю внимание на то, что перед именем таблицы обязательно должен быть указан знак равенства "=". Иначе будет ошибка.
Нажимаем Ок. Теперь при расширении таблицы и добавлении строк эти данные будут автоматически добавлены и в диаграмму.

В примере ниже файл с реализацией обоими вариантами.
Скачать пример:

Tips_Chart_Dinamic_Chart.xls (29,5 KiB, 8 494 скачиваний)

Статья помогла? Поделись ссылкой с друзьями!

Поиск по меткам

Щелкнул по диаграмме прав кнопкой -> исходные данные -> вкладка "диапазон данных" и ниже строка "Диапазон" туда я и вписываю название именованного диапазона, нажимаю ок. Там не написано для какого именно ряда. Кстати вместо СЧЕТЗ использовал СЧЕТ, т.к. у меня формула в том столбце от куда диаграмма берет данные. А вот формула которую я использовал:
=СМЕЩ(Мощность!$AE$2500;СЧЁТ(Мощность!$AE$2500:$AE$20000)-24;;24;)
На форумах рыскал, все сюда отправляют, так что если что не ругайтесь.)

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

Петрович, Вы назначаете диапазон для всей диаграммы. прочитайте внимательно статью — Вам надо создать несколько динамических диапазонов — для кадого ряда свой диапазон. А затем рядам назначить эти диапазоны:
Выбираем нужный ряд, нажимаем «Изменить» и вписываем имя динамического диапазона в качестве диапазона для данных этого ряда.

Дмитрий(Админ), Все, разобрался_) Спасибо большое.

Статья отличная! Спасибо автору! Все очень просто и доступно тому, кто умеет думать. Жаль,что диапазон сдвигается только при пустых ячейках, а если в ячейки вбита формула вроде =IF(F335>0;F335;""), то он это за пустую ячейку не считает и не сдвигается:( будем думать.

Здравствуйте!
Петрович, подскажите пожалуйста, как у Вас получилось разобраться со временем в диапазоне? чтобы оно не стояло на 00:00:00, а отображались значения в диаграмме и за другое время в сутках. Спасибо заранее.