В настоящей заметке рассматривается ситуация, когда вам необходимо построить диаграмму, основанную на большом числе данных, но при этом одновременно отображать не все данные, а только их часть. Идея подсмотрена мною в книге:
Скачать заметку в формате 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», «Показать вкладку разработчик на ленте».
в Excel 2010 эта задача решается гораздо проще и удобнее с помощью сводных диаграмм и нового инструмента — СРЕЗ!