Пример как построить ступенчатый график в 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 поля. Для каждого имени свое значение:

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

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

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

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

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