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

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

Динамические Диаграммы в Excel - 1

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

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

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

Динамические Диаграммы в Excel - 2

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

Динамические Диаграммы в Excel - 3

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

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

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

Динамические Диаграммы в Excel - 4

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

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

Динамические Диаграммы в Excel - 5

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

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

Динамические Диаграммы в Excel - 6

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

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

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

Динамические Диаграммы в Excel - 8

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

Динамические Диаграммы в Excel - 9

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

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

Динамические Диаграммы в Excel - 10

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

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

Динамические Диаграммы в Excel -  11

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

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

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

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

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

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

Динамические Диаграммы в Excel - 12

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

Динамические Диаграммы в Excel - 13

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

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

Динамические Диаграммы в Excel - 14

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

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

Динамические Диаграммы в Excel - 15

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

Динамические Диаграммы в Excel - 16

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

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

Динамические Диаграммы в Excel - 17

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

Динамические Диаграммы в Excel - 18

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

Динамические Диаграммы в Excel - 19

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

Динамические Диаграммы в Excel - 20

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

Динамические Диаграммы в Excel - 22

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

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

 

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