Диаграммы в Excel. Отображение части данных с использованием элементов управления

Рубрика: 7. Полезняшки Excel

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

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

Допустим, вы менеджер торговой компании, торгующей запчастями для автомобилей, и у вас есть отчет по продажам за последние 9 кварталов:

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

1. Строим обычную диаграмму, отражающую динамику продаж только одной марки. Выбираем область, включающую заголовок и первую строку (Audi) и выбираем тип диаграммы – гистограмму:

2. Получилась диаграмма:

3. Создаем динамический именованный массив, включающий данные по продажам запчастей Audi. Для этого в ячейке М3 введем единицу, перейдем на вкладку «Формулы» → «Диспетчер имен», в открывающемся окне жмем «Создать» и далее выбираем имя диапазона (в нашем примере – «диагр») и «Диапазон»:

Пару слов о функции СМЕЩ:

Функция СМЕЩ возвращает ссылку на массив. Базовая ячейка – А1. От неё идет отсчет. Смещаемся вниз (по строкам) на 2 ячейки, смещаемся вправо (по столбцам) на три ячейки и от получившейся ячейки (D3) берем массив высотой 2 строки и шириной 3 столбца, то есть массив D3:F4. Поскольку функция СМЕЩ возвращает ссылку на массив, то если только ее забить в ячейку, как правило (но не всегда!) в ячейке будет ошибка #ЗНАЧ! Если, как в примере, взять сумму массива: СУММ(СМЕЩ(A1;2;3;2;3)), то получится значение 212. Можете проверить, что сумма чисел в ячейках D3:F4, как раз, равна 212.

4. Заменяем в диаграмме область значений. Для этого щелкаем правой кнопкой мыши на диаграмме и жмем «Выбрать данные»:

Выделяем наш ряд в поле «Элементы легенды (ряды)» (в нашем примере Audi) и жмем «Изменить»:

Заменяем имя ряда, делая ссылку на ячейку N3, и заменяем значения на имя массива:

В ячейке N3 вводим формулу

=ДВССЫЛ(«A»&$M$3+2))

которая позволит получить имя рада:

Диаграмма пока никак не изменилась.

5. Добавляем полосу прокрутки, которая будет изменять значение в ячейке M3. На вкладке «Разработчик» [1] выбираем «Вставить» и рисунок с полосой прокрутки:

Рисуем полосу прокрутки требуемого размера и щелкаем правой кнопкой мыши, выбирая «Формат объекта»:

Максимальное значение равно 38, так как именно столько строк с данными в нашей таблице. Устанавливаем связь ползунка на полосе прокрутки со значением в ячейке М3:

Мы создали полосу прокрутки, с помощью которой можем управлять выводом на диаграмме той или иной кривой:

Для управления можно: двигать  ползунок, щелкать на стрелке (шаг – 1 строка данных), щелкать на пространстве полосы прокрутки (шаг – 5 строк таблицы данных).


[1] Если эта вкладка у вас не ленте отсутствует, щелкните кнопку Office , «Параметры Excel», «Показать вкладку разработчик на ленте».

Комментарии: (1)

в Excel 2010 эта задача решается гораздо проще и удобнее с помощью сводных диаграмм и нового инструмента — СРЕЗ!


Прокомментировать