Как сделать динамическую диаграмму в excel?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Excel. Диаграмма, изменяющаяся при добавлении данных

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

В качестве пример возьмем курс доллара (рис. 1). Для начала создадим обычную диаграмму (тип «График с маркерами»).

Рис. 1. График с маркерами

Скачать заметку в формате Word, примеры в формате Excel

Далее создадим два именованных динамических диапазона: один для меток категорий (Даты), второй – для точек данных (Курс $). Для создания именованного диапазона пройдите по меню Формулы → Диспетчер имен (рис. 2).

Рис. 2. Диспетчер имен

В открывшемся окне «Диспетчер имен» нажмите кнопку создать, и в окне «Создание имени» введите имя диапазона – «Даты» и формулу для ссылки на диапазон: =СМЕЩ(Лист1!$A$1;1;0;СЧЁТЗ(Лист1!$A$1:$A$100)-1;1)

Рис. 3. Присвоение имени динамическому диапазону

Обратите внимание, что сразу же за аргументом функции СЧЁТЗ стоит «–1». Благодаря этому заголовок ряда не будет включен в именованный диапазон. Заметьте также, что в качестве аргумента функции СЧЁТЗ указан не весь столбец А, а лишь первые 100 ячеек. Если вы используете большой массив данных, укажите соответствующее число, например, 1000 или 10 000. В ранних версиях Excel такое ограничение весьма желательно, дабы не перегружать вычисления. Указывая колонку полностью, вы заставляете Excel просматривать тысячи ненужных ячеек. Некоторые функции Excel достаточно умны, чтобы определить, какие ячейки содержат данные, некоторые сделать этого не могут. В новых версиях Excel не обязательно строго ограничивать диапазон, так как обработка больших диапазонов в них улучшена.

Читать еще:  Как сделать сортировку по алфавиту в excel 2003?

Затем создайте второй именованный диапазон для данных столбца В (рис. 4)

Рис. 4. Динамический диапазон «Курс»

Теперь можно заменить в диаграмме ссылки на диапазоны данных именами динамических диапазонов. Выделяем диаграмму и щелчком правой кнопкой мыши вызываем контекстное меню, строчку «Выбрать данные» (рис. 5).

Рис. 5. Выбрать данные

В открывшемся окне «Выбор источника данных» выделяем ряд и жмем «Изменить» (рис. 6).

Рис. 6. Изменить ряд

В открывшемся окне «Изменение ряда» заменяем ссылки на ячейки на имя ряда «Курс» (рис. 7). Обратите внимание, что имя листа Excel следует оставить в неизменном виде «=Лист1!»

Рис. 7. Замена ссылок на имя диапазона

Аналогично заменяем подписи горизонтальной оси (категории): жмем другую кнопку «Изменить» в правой части окна «Выбор источника данных» (см. рис. 6) и вводим имя «Даты» вместо ссылок на ячейки (рис. 8).

Рис. 8. Замена подписей оси (категорий)

Все наши манипуляции не привели к изменению диаграммы. Мы лишь подготовились к грядущим изменениям. Как говорится: «подальше положишь, поближе возьмешь». А теперь наслаждайтесь автоматическим расширением области диаграммы при добавлении новых значений в таблицу данных, например, как на рис. 9.

Рис. 9. Новые данные, добавленные в таблицу (выделены желтым) автоматически отражаются на диаграмме

В своей работе менеджера мне приходится контролировать довольно много параметров, так что подобные хитрости я использую давно, и они значительно облегчают мне работу. А вот недавно в книге Д.Холи, Р. Холи «Excel 2007. Трюки» я прочитал о еще одной возможности, основанной на том же свойстве.

Добавление от 19 июня 2018 г. Эту же проблему гораздо проще решить, если встать на любую ячейку диапазона, и нажать Ctrl+T (англ.). Диапазон превратится в Таблицу. Создайте на ее основе диаграмму. При добавлении строк в Таблицу, диаграмма будет отражать их автоматически.

Построение диаграммы для фиксированного числа последних данных

Еще один тип именованных диапазонов, который можно использовать с диаграммами, – это диапазоны, выбирающие только последние N значений (можно указать любое число).

См. пример на Лист2 в Excel-файле. Для данных в столбце А создайте динамический именованный диапазон с именем Даты30 (последние 30 дней), который ссылается на следующие данные: =СМЕЩ($A$1;СЧЁТЗ($A$1:$A$100)-30;0;30;1). Для данных в столбце В создайте динамический именованный диапазон с именем Курс30, который ссылается на следующие данные: =СМЕЩ($B$1;СЧЁТЗ($B$1:$B$100)-30;0;30;1). Замените в диаграмме ссылки на диапазоны данных именами динамических диапазонов. Получится диаграмма, отражающая последние 30 значений (рис. 10).

Рис. 10. На диаграмме отражаются 30 последних значений

При добавлении данных в таблицу область отражения на диаграмме сместится (рис 11).

Рис. 11. При добавлении данных диаграмма по-прежнему отражает 30 последних значений

Использование динамических именованных диапазонов с диаграммами обеспечит исключительную гибкость и сэкономит огромное количество времени и усилий, которые вы потратили бы на настройку диаграмм после добавления еще одной записи к исходным данным!

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

Возможно ли в Excel создать динамическую диаграмму? В определенном смысле да. Если под динамическим понимать, автоматическое дополнение диаграммы новыми данными и выделение максимального и минимального значения. Динамическая диаграмма в Excel – возможна.

Исходные данные

Итак, пусть будут такие данные:

Есть некие значения, характеризующие определенный месяц. Это могут быть продажи, производство товаров, да что угодно. Необходимо на основе этих данных построить гистограмму с группировкой. Если делать как обычно, то есть выделить весь диапазон, или просто встав на ячейке с данными выбрать «Вставка» — «Гистограмма» — «Гистограмма с группировкой». Диаграмма будет вставлена, но это будет диаграмма на основе данных. Диаграммы такого типа не поддерживают первый необходимый элемент динамичности, а именно динамическое добавление колонок при внесении новых строк в таблицу.

Динамическое добавление колонок

Только диаграммы, основанные на таблицах Excel, поддерживают динамическое изменение количества строк. Для того что бы из диапазона ячеек В2:С8 сделать таблицу, так же достаточно встать на любой ячейки и выбрать меню «Вставка» — «Таблица», или нажать комбинацию клавиш Ctrl+T.

Откроется диалоговое окно добавления таблицы. В поле для ввода, диалогового окна будет указан диапазон ячеек, которые будут составлять таблицу. Убедимся, что в таблицу входят все ячейки и нажмем кнопку «ОК». Вместо диапазона ячеек, должна будет появиться таблица.

Встанем в любую ячейку таблицы и создадим на ее основе гистограмму.

Пока что всё как обычно. Где же динамичность? На самом деле она уже есть. Если перейти в ячейку В9, которая не является частью таблицы и ввести данные, в нашем случае это будет «Июль», то благодаря свойству таблиц, новая строка будет включена в состав таблицы, произойдет авто расширение таблицы. Так как диаграмма связанна не с диапазоном ячеек, а с таблицей то произойдет добавление нового столбца в диаграмму. Вот так:

Читать еще:  Как сделать квадратное уравнение в excel?

Значение показателя для Июля мы еще не ввели, но уже видно, что в таблице появился, пока пустой столбец Июль. Пусть значением будет число 60.

Выделение столбцов, максимальное-минимальное

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

Во первых, как определить максимальный или минимальный столбец? Только визуально, для того что бы в данных столбец с максимальным значением был выделен другим цветом, добавим еще один столбец в таблицу. Назовем столбец «Максимум» и запишем в нем такую формулу:

Да, не забываем про особенность таблиц к авто расширению. Для того что бы добавить столбец в таблицу достаточно в соседнем с последним столбцом таблицы ввести значение, так в нашем случае введя в поле D3 значение «Максимум» мы добавим столбец в таблицу. В первой строке таблицы введем формулу и при нажатии на всплывающее меню распространим действие формулы на весь столбец (но можно и привычным образом растянуть формулу на весь столбец).

Обратите внимание, что в формуле используются специальные табличные ссылки, а не привычные вида «столбец-строка». Если табличная ссылка начинается с символа «@» — это означает текущую строку, столбец с указанным именем. Если имя столбца указанно просто в квадратных скобках – это означает ссылку на столбец целиком. Таким образом, наша формула проверяет если значение в текущей строке, столбца «Показатель» равно максимальному значению столбца «Показатель», то выводится значение текущей строки столбца «Показатель», если значение не равно, выводится «#Н/Д» — нет данных. Специальное значение «#Н/Д» используем, что бы все столбцы, где значение не максимальное отсутствовали в диаграмме. После ввода формулы наша таблица и диаграмма будут иметь вид:

Минимальное значение

Эм, пока немного странно выглядит, но это не страшно. На самом деле добавив новый столбец в таблицу, мы добавили и новый столбец в диаграмму, что логично. У нас в таблице два одинаковых максимальных значения, поэтому и два столбца красного цвета. Особенностью гистограмм является использование одного цвета для значений одной группы (одного столбца), другой столбец другой цвет. Если необходимо изменить цвет, то лучше это делать на этом этапе. Как изменить цвет и настроить внешний вид диаграммы не относится к этой статье, поэтому рассматривать этот аспект не будем.

Теперь добавим еще один столбец в таблицу, назвав его «Минимум» и введя в него формулу:

Формула аналогична предыдущей, за тем исключением что вместо функции «МАКС» используем «МИН», ведь проверяем мы на минимальное значение. В итоге получим такой вид таблицы и диаграммы.

Да у элемента «Январь» в диаграмме появился еще один столбец. В нашей диаграмме каждый столбец, прижат вплотную к соседнему, на каждый элемент по три столбца. Просто для элементов значения, которых не являются ни максимальным, ни минимальным, нет данных, а значит, столбцы в диаграмме отсутствуют. То, что столбца именно три можно убедиться на примере зеленого минимального показателя, между синим и зеленым пиком есть промежуток. Тогда как в максимальных элементах между синим и красным промежутка нет. Всё потому что для элемента «Январь» значение «Максимум» отсутствует, те равно «#Н/Д».

Параметры ряда диаграммы

А сейчас самое интересное. Выделим нажатием левой кнопкой мыши синие столбцы в диаграмме. Или еще быстрее сразу нажатием правой кнопки мыши выделим столбцы и вызовем контекстное меню, в котором выберем пункт «Формат ряда данных».

В открывшемся диалоговом окне, в разделе «Параметры ряда» первый ползунок называется «Перекрытие рядов» по умолчанию его значение – 0%. Переместим его вправо к надписи «С перекрытием», или введем значение 100 в поле для ввода. Вот так:

Нажмем кнопку «Закрыть» что бы избавиться от диалогового окна. Почему закрыть, а не привычное «ОК», да потому что изменения вносимые в этом диалоговом окне тут же отражаются в диаграмме, и если у вас из под диалогового окна была видна диаграмма, то вы уже увидели что вместо трех столбцов остался только один. Два других оказались просто перекрыты. Каждая следующая колонка перекрывает предыдущую, где одна колонка остался синий столбец. Где были синий и красный, следующий красный столбец перекрыл синий, а так как зеленого не было, в диаграмме виден красный столбец. Для минимального значения получалось, что синий должен перекрываться следующим, но в следующем красном нет значения, он пустой и невидимый, зато есть третий зеленый он то и перекрывает первый. Так наша диаграмма приняла красивый законченный вид:

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

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

Читать еще:  Как сделать чтобы в excel?

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

В диаграммах 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 651 скачиваний)

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

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

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

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

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

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

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

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

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме — добро пожаловать на Форум

Ссылка на основную публикацию
×
×
Adblock
detector