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

Как построить динамический ступенчатый график в 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 – это ссылка на ячейку где хранится переменное значение для аргументов формул во второй таблице обработки исходных данных из первой таблицы.

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

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

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Пример как построить ступенчатый график в Excel скачать шаблон

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

Что такое ступенчатый график и в чем его преимущество?

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

Сначала заполните небольшую табличку с исходными данными:

Теперь на основе данной таблицы построим обычный линейный график. Для этого выберите выделите диапазон ячеек A2:B8 и выберите инструмент: «ВСТАВКА»-«Диаграммы»-«Вставить график». В результате получаем следующую картинку:

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

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

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

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

Будем использовать те же исходные данные. Сначала скопируем диапазон ячеек A2:B8 и вставим его ниже в область A9:B15:

Теперь делаем «ход конем:)» выделяем диапазон ячеек B2:B15 наводим курсор мышки на рамку выделения и удерживая левую клавишу смещаем данный диапазон на 1-ну ячейку вниз. После чего выделяем диапазон ячеек A9:A15 и таким же образом также смещаем выделенный диапазон на одну ячейку вниз:

После чего удаляем лишние строки листа из таблицы: строка 9 и строка 2. В результате получаем новую таблицу исходных данных для создания ступенчатого графика в Excel. Для этого выделяем диапазон значений новой таблицы A2:B14 и строим по ней обычный линейный график, который примет форму ступенчатого. Снова выбираем инструмент: «ВСТАВКА»-«Диаграммы»-«Вставить график».

Переключение между ступенчатым и линейным графиками

Для решения данной задачи нам нужно будет сначала сделать на отдельном листе Excel (назовем его «ВЫБОР») 2 таблицы для первого – линейного и второго – ступенчатого графика:

А также потребуется создать элемент управления нашим «отчетом» для визуального анализа. В данном примере управлять переключателем будем с помощью выпадающего списка. Чтобы сделать выпадающий список в Excel перейдите в ячейку M2 и выберите инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных»:

В появившемся диалоговом окне «Проверка вводимых значений» на вкладке «Параметры» в разделе опций «Условие проверки» из выпадающего списка «Типы данных:» выбреете опцию «Список». А в поле ввода «Источник:» укажите следующее текстовое значение: Линейный;Ступенчатый.

Теперь придадим функционал для элемента управления. Для этого будем использовать в рядах и подписях значений графика имена с формулами. Сначала создадим 2 имени для осей X и Y. Выберите инструмент: «ФОРМУЛЫ»-«Определенные имена»-«Диспетчер имен» (иле нажмите комбинацию горячих клавиш CTRL+F3):

В появившемся диалоговом окне нажмите на кнопку «Создать» и заполните 2 поля. Для каждого имени свое значение:

1. «Имя:» X. «Диапазон:» =ЕСЛИ(ВЫБОР!$M$2=»Линейный»;ВЫБОР!$A$2:$A$8;ВЫБОР!$D$2:$D$14).
2. «Имя:» Y. «Диапазон:» =ЕСЛИ(ВЫБОР!$M$2=»Линейный»;ВЫБОР!$B$2:$B$8;ВЫБОР!$E$2:$E$14).

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

В появившемся диалоговом окне «Выбор источника данных» в левой секции «Элементы легенды (ряды)» нажмите на кнопку «Изменить» чтобы указать новую ссылку с именем Y в поле «Значение:» =ВЫБОР!Y. Такие же самые действия выполняем и в правой секции «Подписи горизонтальной оси (категории)», только со ссылкой на другое имя =ВЫБОР!X. После чего нажимаем ОК на всех открытых окнах. Теперь при изменении значения в ячейке M2 с помощью выпадающего списка автоматически меняются ссылки на ряды (в оси Y) и подписи (в оси X) для ступенчатого и линейного графика:

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

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

Ступенчатый график в EXCEL

Для ступенчатого графика в MS EXCEL (Step Chart) нет типовой диаграммы. Но ее можно построить на основе диаграммы «Точечная с прямыми отрезками».

Будем строить вот такой график (см. файл примера ).

В качестве иходных данных возьмем вот такую таблицу.

Прежде чем строить график, преобразуем наши данные к следующему виду:

Т.е. для каждого значения из исходной таблицы, кроме первого и последнего, у нас теперь 2 значения Х. Это необходимо, чтобы получить «ступеньки», а не просто соединение точек прямыми линиями.

Результат достигнут 2-мя формулами.

Для Х: =ЦЕЛОЕ((СТРОКА()-СТРОКА(D$6))/2)+1

Для Y, начиная со второго значения: =ИНДЕКС(B$7:B$17;$D7)

Данные для диаграммы выглядят так:

СОВЕТ : Для начинающих пользователей EXCEL советуем прочитать статью Основы построения диаграмм в MS EXCEL , в которой рассказывается о базовых настройках диаграмм, а также статью об основных типах диаграмм .

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

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

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

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

Tips_Charts_StepChart.xls (56,0 KiB, 3 846 скачиваний)

Способ 1: Применяем планки погрешностей
Для начала потребуется добавить столбец с формулой для погрешностей. Запишем в ячейку с первым значением(на скрине это C2, напротив 1 апр 2015) значение 0, а в следующую ячейку формулу: = B3 — B2 .

Теперь копируем ячейку с формулой, выделяем данные в столбце С, начиная с С3 и до конца данных в таблице и вставляем скопированную формулу. Или можно просто протянуть эту формулу до конца таблицы.
Теперь выделяем первые два столбца таблицы вместе с заголовками(Дата и Выручка) и вставляем новую диаграмму:

• Excel 2003:
  Вставка (Insert) —Диаграмма (Chart) —Точечная (Scatter) —С прямыми отрезками (Scatter with straight lines)
• Excel 2007 и выше:
  вкладка Вставка (Insert) -группа Диаграммы (Charts) —Точечная (Scatter) —С прямыми отрезками (Scatter with straight lines) :

Далее необходимо добавить планки погрешностей:

• Excel 2007-2010:
  вкладка Макет (Layout) —Предел/Планки погрешностей (Error Bars) —Дополнительные параметры планок погрешностей (More Error Bars Options. )
• Excel 2013
  жмем справа от диаграммы кнопку со знаком «плюс» и ставим флажок Предел погрешностей (Error Bars)

Осталось дело за малым: на вкладке Макет (Layout) -группа кнопок Текущий фрагмент (Current Selection) выбираем Планки погрешностей по оси X (X Error Bars) -и сразу жмем там же кнопку Формат выделенного (Format Selection) (расположена сразу под вып.списком).
Указываем следующие параметры:

• Направление (Display) —Плюс (Plus) ;
• Конечный стиль (End Style) —Без точки (No Cap) ;
• Величина погрешности (Error Amount) —фиксированное значение (Fixed value) — 1 С величиной погрешности для горизонтальных планок чуть подробнее: 1 выбираем, т.к. у нас данные указаны в таблице ежедневные. Т.е. шаг оси между данными получается 1(один день). Если бы данные поступали каждые 20 дней и в таблице они были бы занесены тоже с промежутком через каждые 20 дней — то фиксированное значение необходимо было бы указать 20.

Далее, не закрывая окно свойств ряда идем на вкладку Макет (Layout) -группа кнопок Текущий фрагмент (Current Selection) —Планки погрешностей по оси Y (Y Error Bars) . Здесь указываем:

• Направление (Display) —Минус (Minus) ;
• Конечный стиль (End Style) —Без точки (No Cap) ;
• Величина погрешности (Error Amount) —пользовательская (Custom) . Жмем Укажите значения (Specify Value) и в появившемся окне для Отрицательные значения ошибки (Negative Error Value) указываем столбец с теми формулами, которые записаны у нас в столбце С (в примере C2:C23). Ок. Закрыть.

И пара последних косметических штришков:

• Убираем «лишнюю» линию графика: выделяем Ряд «Выручка»(это наша основная линия после создания графика) -правая кнопка мыши —Формат ряда данных (Format Data Series) . Переходим к свойствам Цвет линии (Line Color) и ставим Нет линий (No line) :
  
• Т.к. тип диаграммы Точечная строится по своим законам, то на диаграмме скорее всего перед данными и после будут пропуски:
  
  Происходит это потому, что шаг в таких диаграммах выбирается автоматически и «с запасом». Чтобы убрать эти пропуски надо посмотреть значение самой первой даты исходных данных и самой последней. Запомнить эти значения. Далее в диаграмме на оси с датами щелкнуть правой кнопкой мыши —Формат оси (Format axis) —Формат оси (Axis options) -выставляем для Минимум (Minimum) и Максимум (Maximum) значение первой и последней даты. Теперь пропуски «исчезнут».

Вот график и построен. Остается лишь навести красоту. Например, увеличить ширину линий, изменить цвет. Чтобы увеличить ширину линий можно сразу при установке планок погрешностей после установления основных параметров перейти к свойствам Цвет линии (Line Color) (для задания нужного цвета) и Тип линии (Line Style) (для задания нужной ширины).

Если же не сделали этого сразу, то это можно сделать в любой момент: вкладка Макет (Layout) -группа кнопок Текущий фрагмент (Current Selection) —Планки погрешностей по оси X (X Error Bars) . И так для любого ряда.
Так же можно изменить форматы для других элементов диаграммы: область построения, подписи данных и т.д. Сделать это можно, выделив любой из элементов -правая кнопка мыши —Формат «имя элемента» (Format «имя элемента»)
Пример результата графика через погрешности приведен в самом начале статьи.

Способ 2: «Растягиваем» данные

Этот прием основан на том, что стандартные графики строятся на перепадах данных и если значения будут одинаковые — то линия графика будет горизонтальная. Однако нужна и вертикальная и тут как раз и хитрость: мы для каждого дня будем записывать ДВА значения сумм выручки, вместо одного. Тогда мы получим желаемое.
Для этого надо будет выделить два отдельных столбца. В приложенном к статье примере это столбцы D и E. Копируем заголовки и в столбец D(начиная с ячейки D2) записываем формулу:
=ИНДЕКС( $A$2:$B$23 ;ЦЕЛОЕ(СТРОКА()-СТРОКА( A2 )/2);1)
=INDEX($A$2:$B$23,INT(ROW()-ROW(A2)/2),1)
в столбец E так же прописываем формулу, но чуть другую:
=ИНДЕКС( $A$2:$B$23 ;ЦЕЛОЕ(СТРОКА( A1 )-СТРОКА( B1 )/2)+1;2)
=INDEX($A$2:$B$23,INT(ROW(A1)-ROW(B1)/2)+1,2)

Эти формулы надо будет скопировать на количество строк, большее в два раза, чем исходные данные. Как вариант можно протягивать формулу до тех пор, пока формула не вернет значение ошибки #ССЫЛКА! (#REF!) . А теперь останется только вставить на основании этих данных диаграмму типа График:

• Excel 2003:
  Вставка (Insert) —Диаграмма (Chart) —График (Line) —График (Line)
• Excel 2007 и выше:
  вкладка Вставка (Insert) -группа Диаграммы (Charts) —График (Line) —График (Line)

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

Статья помогла? Поделись ссылкой с друзьями!