Как построить динамический ступенчатый график в Excel скачать

Полезные свойства ступенчатого графика (Step Chart) сложно переоценить. Не смотря на свою простоту, ступенчатый график позволяет визуализировать информацию под другим ракурсом точки зрения на те же показатели что и на линейном графике.

Пример построения динамического ступенчатого графика в Excel

В данном примере рассмотрим, как создать визуализацию данных с динамическим ступенчатым графиком. Но сначала небольшое описание Технического Задания (ТЗ), как говорят в кулуарах программистов: «Без ТЗ – результат ХЗ!».

Техническое Задание для построения динамического ступенчатого графика в Excel:

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

Создание таблиц с формулами для обработки исходных данных

Как всегда, по традиции начнем с подготовки и обработки исходных данных, а после чего перейдем непосредственно к визуализации. На рабочем листе Excel создайте таблицу с исходными статистическими показателями ежедневных продаж за 1 месяц отчетного периода, так как показано ниже на рисунке:

начнем с подготовки и обработки.

Сбоку от таблицы исходных данных создайте новою таблицу с формулами для их обработки:

создайте новою таблицу.

Первый столбец – это последовательность чисел 7 дней в 1 неделе. Так как график будет ступенчатым, дни за выбранный временной масштаб отображаемого периода на графике (в одну неделю) идут по парно. А во втором столбце идут формулы выборки дат.

Далее в третьем столбце второй таблицы используется формула для выборки показателей ежедневных продаж соответственных датам второго столбца:

в третьем столбце второй таблицы.

И наконец формула выборки значений продаж по четным номерам строк листа для заполнения четвертого столбца второй таблицы:

формула выборки значений продаж.

В четвертом столбце находиться значения для подписей показателей на графике.

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

Как сделать ступенчатый график в Excel

Чтобы построить ступенчатый график теперь просто выделите диапазон ячеек второго и третьего столбца второй таблицы E2:F14 и выберите стандартный инструмент построения линейного графика: «ВСТАВКА»-«Диаграммы»-«Линейный график»:

диапазон ячеек второго и третьего столбца.

Чтобы не заморачиваться с визуальным оформлением воспользуемся предустановленными стилями. Для из дополнительного меню выберите инструмент: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Стили диаграмм»-«Стиль 10»:

воспользуемся предустановленными стилями.

Затем выделите фоновую область графика кликнув по ней левой кнопкой мышки и выберите инструмент: «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Стили фигур»-«Стильный эффект – Темный, черный 1»:

Стильный эффект.

А также удалите сетку на графике кликнув рядом по кнопке плюс «+» и сняв отметку с опции «Сетка» из выпадающего меню «ЭЛЕМЕНТЫ ДИАГРАММЫ».

Далее в этом же меню отмечаем опцию «Подписи данных»:

Формат подписей данных.

Кликаем правой мышкой по любой подписи, из контекстного меню выбираем опцию «Формат подписей данных» и в появившемся дополнительном окне в «ПАРАМЕТРАХ ПОДПИСЕЙ» отмечаем опцию «значения из ячеек». Здесь же рядом кликаем на кнопку «Выбрать диапазон» и указываем на диапазон ячеек четвертого столбца второй таблицы G2:G14 и нажимаем Ок. Затем снимаем галочку с опции «значение». Ниже в разделе опций «Положение метки» отмечаем – «В центре».

После чего не снимая выделения с подписей данных выберите инструмент: «РАБОТА С ДИГРАММАМИ»-«ФОРМАТ»-«Стильный эффект – Синий, Акцент 1»:

Синий.

Переходим к созданию интерактивного элемента управления ступенчатым графиком.

Интерактивное управление динамическим ступенчатым графиком в Excel

Выберите инструмент: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Полоса прокрутки»:

Элементы управления.

Кликаем правой кнопкой мышки по созданной полосе прокрутки и из появившегося контекстного меню выбираем опцию «Формат объекта». Затем в появившемся окне «Форматирование объекта» на вкладке «Элемент управления» вносим свои параметры в поля ввода:

  • Минимальное значение: 1 – это минимальный порядковый номер дня начала временного периода, отображаемого на графике;
  • Максимальное значение: 25 – то есть: 31 день в январе месяце минус -7 дней в неделе +1 на парность значений в ступенчатом графике = 25;
  • Связь с ячейкой: $H$1 – это ссылка на ячейку где хранится переменное значение для аргументов формул во второй таблице обработки исходных данных из первой таблицы.

Накладываем элемент управления полосу прокрутку на передний план на график и наслаждаемся результатом динамического изменения ступеней с показателями значений:

Step Chart.

Скачать динамический ступенчатый график в Excel

Чтобы понять принцип построения ступенчатого графика и чем он отличается от стандартного линейного графика прочитайте статью где описаны его возможности и сфера применения: Пример как построить ступенчатый график в Excel.

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


en ru