Динамические диаграммы в EXCEL. Общие замечания
history 4 октября 2012 г.
- Группы статей
- Диаграммы и графики
Диаграммы строятся на основании данных, содержащихся в таблице. Часто требуется построить диаграмму на основании не всех данных из таблицы, а только тех, которые удовлетворяют определенным критериям. Выбрать необходимые данные из таблицы для отображения их на диаграмме можно разными способами: фильтрацией , формулами или даже простым скрытием строк. В любом случае, после модификации исходной таблицы, EXCEL автоматически перестроит диаграммы. Рассмотрим это на практике.
Если изменились критерии фильтрации значений, которые должны попасть в диаграмму, то, скорее всего, придется в ручную перестраивать соответствующие диаграммы, а это не самое увлекательное занятие. К счастью, в большинстве случаев это можно поручить EXCEL. Назовем динамической , диаграмму, которая автоматически перестраивается без вмешательства пользователя. Можно выделить несколько вариантов построения динамических диаграмм.
СОВЕТ : Для начинающих пользователей EXCEL советуем прочитать статью Основы построения диаграмм в MS EXCEL , в которой рассказывается о базовых настройках диаграмм, а также статью об основных типах диаграмм .
Построение динамических диаграмм с использованием формул
Часто на диаграмме нужно отобразить из исходной таблицы не все данные, а только ту часть, которая удовлетворяет заданным условиям, например вывести на графики информацию о продажах только за 1 квартал (исходная таблица содержит данные за год). Эти условия могут изменяться пользователем в определенных пределах (сначала выбрали первый квартал, затем второй и т.д.). Для создания такой диаграммы необходимо сначала создать отдельную таблицу (столбец) для отобранных в соответствии с условиями данных. Выборку данных из исходной таблицы можно осуществлять функциями ЕСЛИ() , СУММПРОИЗВ() , СУММЕСЛИМН() , формулами массива или другими.
Построение динамических диаграмм через скрытие строк
Если создавать отдельную таблицу для данных, удовлетворяющих критериям не хочется, то можно использовать одно очень удобное свойство EXCEL: данные из скрытых строк не отображаются на диаграмме. Скрытие строк можно организовать вручную или фильтрацией .
Построение динамических диаграмм с помощью функции СМЕЩ()
Иногда требуется из исходной таблицы отобразить только данные находящиеся в последних (или первых) строках, либо данные начиная с определенной позиции. В этом случае нет необходимости создавать отдельную таблицу, можно использовать функцию СМЕЩ() .
В статье Динамические диаграммы. Часть5: график с Прокруткой и Масштабированием приведен пример диаграммы для удобного представления больших объемов данных.
Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Создание динамической диаграммы в Excel с помощью именованных диапазонов
Мы уже рассматривали концепцию динамической диаграммы в одной из предыдущих статей, когда проектировали дашборд. На тот момент, внедренная полоса прокрутки, позволила нам отображать определенную часть информации и прокручивать столбцы диаграмм по мере необходимости. Данный функционал дает возможность экономить место на рабочем листе Excel и фокусироваться на тех данных, которые на самом деле важны. Сегодня мы рассмотрим, как с помощью выпадающего списка и именованных диапазонов создать динамическую диаграмму, которая меняет свой внешний вид в зависимости от ваших потребностей.
Описание проблемы
Представьте, что у вас есть таблица с показателями, разбитыми по годам и вам необходимо визуализировать их.
Создание диаграммы по всем рядам данных ни к чему хорошему не приведет – диаграмма будет перегружена и нечитабельна. Выходом будет создание интерактивной диаграммы, которая будет отображать только тот ряд данных, который нам необходим.
Создание динамической диаграммы
В первую очередь необходимо создать выпадающий список, откуда мы будем выбирать, интересующий нас, показатель. Переходим по вкладке Разработчик в группу Элементы управления, выбираем Вставить –> Элементы управления формы –> Поле со списком.
После того, как вы щелкните по иконке Поле со списком, ваш курсор превратится в перекрестье, это означает, что Excel ожидает указание места, куда необходимо разместить элемент управления. Щелкните в то место на рабочем листе, где вы хотите разместить выпадающий список. Вы увидите большое квадратное поле с треугольником внутри – это и есть первоначальный вид поля со списком. Задайте вашему элементу требуемые размеры, используя маркеры, находящиеся по краям элемента.
Щелкните правой кнопкой мыши по выпадающему списку, выберите Формат объекта. В появившемся диалоговом окне Формат элемента управления, задайте диапазон ячеек, откуда будет формироваться список (в нашем случае, это список всех показателей, по которым мы будем строить график), и ячейку, куда будет помещаться результат выбора из списка.
Далее необходимо создать два именованных диапазона. Первый будет отвечать за значения ряда данных, второй за название. В зависимости от выбора элемента из списка, оба диапазона будут менять свои адреса.
Переходим по вкладке Формулы в группу Определенные имена, выбираем Диспетчер имен и создаем два диапазона с именами значения и название с соответствующими формулами.
На рабочем листе с таблицей с данными выбираем диапазон A1:H2, переходим по вкладке Вставка в группу Диаграммы, выбираем Диаграмму с областями. Excel построил нам диаграмму с одним рядом данных, как мы его и просили.
Щелкаем по ряду данных в области диаграммы. В строке формул вы увидите функцию формирующую ряд данных диаграммы. Где первый параметр отвечает за название ряда, второй – за ось категорий, третий — это значения нашего ряда данных.
Меняем значения первого и третьего параметра на уже подготовленные именованные диапазоны
=РЯД(ДинамДиагр! $A$2 ;ДинамДиагр!$B$1:$H$1;ДинамДиагр! $B$2:$H$2 ;1)
Должно получиться так:
=РЯД(ДинамДиагр! название ;ДинамДиагр!$B$1:$H$1;ДинамДиагр! значения ;1)
Теперь при изменении значения в выпадающем списке, наша динамическая диаграмма будет менять внешний вид. Так как именованные диапазоны, которые мы подставили вместо статических параметров в формуле РЯД, тоже будут менять свои адреса.
Осталось задать привлекательный формат нашей диаграмме. Убираем все лишние элементы: линии сетки и название диаграммы. Меняем цвет ряда данных, добавляем к нему линии проекции. Задаем цвет области построения и области диаграммы.
Чтобы название диаграммы вписывалось в общую композицию, вставляем элемент Надпись над диаграммой. Щелкаем левой кнопкой мыши по границе надписи, при этом линия границы становится сплошной линией, в строке формул вставляем именованный диапазон, отвечающий за название ряда данных. В нашем случае он так и называется название.
Динамическая диаграмма готова.
Вам также могут быть интересны следующие статьи
- Планки погрешностей в Excel — нестандартное использование
- Создание пулевой диаграммы (bullet graph)
- Создание диаграммы в виде спидометра в Excel
- Создание графика в Excel с отрицательными и положительными значениями
- Создание квадратной/ вафельной диаграммы в Excel
- Excel дашборд по обслуживанию клиентов — создание графиков [часть 3 из 4]
- Создание простейшего дашборда в Visio — импорт данных из Excel в Visio
- Что такое Treemap и как его сделать в Excel
- Бесплатный шаблон дашборда в Excel — потребленная энергетическая ценность продуктов
- Диаграмма водопад (waterfall chart) в Excel
15 комментариев
есть подозрение, что формула смещения должна быть =СМЕЩ(ДинамДиагр!$A$4;ДинамДиагр!$A$16;1;;7)
а в формате объекта «поле со списком» должна быть связь с ячейкой A16….иначе фокус не удается…..