Динамическая диаграмма в Excel по заданным позициям.

Создание динамической диаграммы в Excel наглядно. Первая часть.

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

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

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

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

Конечно, не будет ошибкой создание отдельных диаграмм по заданным группам из общего диапазона исходных сведений. Однако такой подход задачу в целом не решит:

1. Понадобится построить не одну, а несколько диаграмм, значит – увеличивается трудоемкость. Для каждой из них как минимум требуется задать свой диапазон данных
2. Диаграмм много, а значит произойдет заполнение пространства листа
3. Неудобно при большом количестве сравнивать разные диаграммы между собой, особенно если учесть, что при разных значениях максимум и минимум диаграммы, а также шаги основных и промежуточных линий различаются
4. Визуально нельзя отследить соотношение между последним показателем предыдущей диаграммы и первым показателем текущей.

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

Обратите внимание, что показатель за сентябрь 2014 года визуально меньше показателя за ноябрь 2016 года (оба выделены красным цветом), хотя на самом деле если первый превышает 1уровень в 150000, то второй до этого уровня не дотягивает. Аналогичная ситуация с показателями за ноябрь 2014 и июнь 2015 годов.

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

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

Прежде всего определяемся с максимальной и минимальной позицией первого периода для будущей диаграммы. Так как надо иметь возможность отображать все периоды таблицы, начиная с первого, то минимальное значение будет равно одному. Для нахождения же максимального значения необходимо от количества показателей отнять количество периодов в будущей диаграмме и прибавить единицу. В нашем случае всего показателей 85 (1 строка занята заголовком таблицы).

Получаем несложный расчет. 85-6+1=80. В нашем случае максимальная позиция равна 80. Запоминаем ее или записываем в отдельную ячейку.

Идем дальше. Теперь нам надо добавить полосу прокрутки с вкладки «разработчик». Если эта вкладка не отображается, переходим на вкладку «файл» и открываем параметры Excel. С ревой стороны выбираем пункт «настроить ренту», затем справа устанавливаем галочку напротив вкладки «Разработчик» После нажатия ОК вкладка разработчик появится на ленте.

Открываем эту вкладку и нажимаем на кнопку «Вставить». Появится список элементов. Необходимо выбрать элемент управления формы «полоса прокрутки». Только не перепутайте его с полосой прокрутки в элементах Active X. Это разные вещи!

Рисуем полосу прокрутки в нижней части нашего окна аналогично рисованию вытянутого горизонтально прямоугольника с использование фигур в Word или Excel. Нажимаем на нем правой кнопкой и выбираем вариант «формат объекта».

Перейдем на вкладку «элемент управления»., выведенного на экран окна если этого не произошло сразу и рассмотрим основные параметры.

• Текущее значение – значение положения ползунка полосы прокрутки в данный момент. При изменении этого положения текущее значение меняется.
• Минимальное значение – значение минимального положения ползунка прокрутки. В нашем случае оно равно 1.
• Максимальное значение – значение для максимального положения полосы прокрутки. У нас равно 80.
• Связь с ячейкой – адрес ячейки, в которую будет выводится текущее значение положения ползунка полосы прокрутки. В нашем случае текущее значения будет выводится в ячейку Е1.

Снимем выделение с полосы прокрутки, щелкнув на любом месте вне ее и приступим к настройке диапазона. Можно конечно использовать динамические диапазоны и имена, но сейчас поступим проще.

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

Учитывая, что

• исходная таблица начинается с ячейки А1,
• положение ползунка прокрутки отображается в ячейке Е1
• нумерация строк в новой таблице начинается с ячейки Е2
• необходимо учесть, что в нижних ячейках новой таблицы необходимо учесть дополнительное смещение на одну строку в исходном диапазоне
• надо учесть смещение на один столбец вправо для получения уже не дат, а собственно значений,

получаем формулу

=СМЕЩ($A$1+$E2;СТОЛБЕЦ()-СТОЛБЕЦ($F$1))

Рассмотрим ее подробнее

• СМЕЩ – указывает, что необходимо сместится от исходной ячейки на нужное количество строк и столбцов.
• $A$1 – начальная ячейка, от которой пройдет перемещение
• $E$1- количество строк, на которое надо переместится вниз
• $E2- дополнительное смещение вниз по срокам. Обратите внимание, что так как диапазон Е1-Е7 содержит последовательную нумерацию, то при протягивании формулы вниз сдвиг будет последовательно увеличиваться
• СТОЛБЕЦ()-СТОЛБЕЦ($F$1) – обеспечивает смещение по столбцам. Так как вначале номер столбца с формулой равен номеру столбца $F$1 (а это первоначально один и тот же столбец,), то в первом случае ничего не произойдет. Но при копировании формулы вправо получим уде не даты, а соответствующие им показатели, что и требуется.

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

Следующим шагом будет подготовка надписи для наименования диаграммы. В ячейке Е10 пишем формулу

=СЦЕПИТЬ(“Отчет за период с “;ТЕКСТ(F2;”ДД.ММ.ГГГГ”);” по “;ТЕКСТ(F7;”ДД.ММ.ГГГГГ”))

При перемещении ползунка на полосе прокрутки даты в полученной надписи будут меняется. Стоит заметить, что если указать первую и последнюю дату явно, а не с помощью функции ТЕКСТ, то они будут выведены в виде чисел.

Переходим к созданию собственно диаграммы. Выделяем созданный диапазон с датами и соответствующими показателями. Нажимаем сочетание Alt+F1 и получаем диаграмму на основе выделенной таблицы в виде гистограммы.

Немного перемещаем ее в сторону, чтобы было видно ячейки столбца Е. Нажимаем на названии диаграммы и щелкаем в строке формул. Ставим в нем знак «=» и указываем на ячейку Е10 с наше строкой, созданной функцией СЦЕПИТЬ. Нажимаем Enter, и теперь в названии диаграммы отображается содержимое этой ячейки.

Динамическая диаграмма готова, переходим к настройке диаграммы и настраиваем внешний вид. Каждый делает это по-своему. Мы сделаем это так.

1. Вычисляем максимальное значение для вертикальной оси. Для этого в ячейке Е11 пишем формулу =ОКРУГЛВВЕРХ(МАКС(B2:B86);-4), она округлит максимальное значение показателей до ближайшего большего значения , кратного 10000. Если нужно округлить до тысяч, вместо -4 в формуле указываем -3, для округления до миллионов указываем -6 и так далее.
2. Нажимаем правой кнопкой на вертикальной оси и выбираем формат оси. Все явно заданные значения меняем. В результате с правой стороны вместо надписи «Автоматически» появится кнопка «Сброс». Если этого не сделать, то максимальное и минимальное значение, а также шаг основных и промежуточных линий сетки будет меняться, а это мешает восприятию.

1. Добавляем основные линии горизонтальной оси и промежуточные линии вертикальной оси.

1. Настраиваем формат ряда. Я использовал прозрачную заливку и темно-синюю заливку шириной в 2 пт. Никто не мешает выбрать другой вариант. Однако лаконичные цвета, границы и отсутствие ярких красок выглядят более строго и аккуратно. Рабочая диаграмма не должна походить на расцветку попугая. Уменьшаем ширину столбцов путем изменения бокового зазора. Установим для него значение 55%

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

1. Настаиваем шрифт для всех надписей. Например, для подписей данных я выбрал шрифт Arial Narrow 14 размера тёмно-синего цвета.

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

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

 

300800cookie-checkДинамическая диаграмма в Excel по заданным позициям.no