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

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

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

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

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

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

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

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

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

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

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

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

Пример как построить ступенчатый график в 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

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

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

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

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

Способ 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)

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

Как создать ступенчатый график с диагональными линиями в MS Excel: шаг за шагом

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

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

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

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

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

Шаг 1: Подготовка данных в таблице Excel

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

Некоторые советы для подготовки данных:

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

Обратите внимание, что Excel предоставляет возможность использовать различные типы данных на оси X, например, текст, числа или даты. Рекомендуется использовать тип данных, который наиболее точно отображает значения.

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

Шаг 2: Выбор типа графика и его настройка

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

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

Для создания диагональных линий на ступенчатом графике необходимо добавить новый ряд данных, который будет выполнять роль линии. Этот ряд должен содержать только значения 0 и 1, где 1 обозначает точку, в которой должна быть нарисована линия, а 0 — точки, где ее быть не должно. Затем необходимо выбрать этот ряд и в меню «Редактирование данных графика» выбрать опцию «Добавить новый ряд данных». Новый ряд появится в таблице и автоматически будет отображен на графике в виде вертикальных линий.

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

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

Шаг 3: Добавление диагональных линий на график

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

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

Затем выберите опцию «Данные линии». В открывшемся диалоговом окне выберите тип линии, а также диапазон данных, на которых должны быть построены линии. Если необходимо, можно добавить несколько наборов линий, чтобы отобразить изменения различных параметров на графике.

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

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

Шаг 4: Редактирование внешнего вида графика и легенды

Редактирование цвета и толщины линий на графике

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

Добавление или удаление легенды на графике

Вы также можете настроить легенду на вашем ступенчатом графике. Если легенды нет, то ее можно добавить, выбрав нужный вариант из меню, которое появляется, когда вы выбираете график. Если легенда уже присутствует, то ее можно удалить, нажав правую кнопку мыши на легенде и выбрав «Удалить».

Настройка шрифта и размера текста

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

Шаг 5: Добавление подписей и сохранение графика

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

Чтобы сохранить ваш график, выберите его и нажмите «Файл» в верхнем левом углу. В выпадающем меню выберите «Сохранить как». Выберите формат, который наиболее удобен для вас (например, JPEG или PNG) и введите имя файла. Нажмите кнопку «Сохранить».

Колонки графика могут быть переименованы, чтобы улучшить читаемость и организованность подписей. Чтобы изменить название столбца, наведите на него и щелкните правой кнопкой мыши. Выберите пункт «Переименовать». Введите новое имя, затем нажмите клавишу «Enter».

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

Вопрос-ответ

Как создать ступенчатый график?

Для создания ступенчатого графика в Excel нужно выбрать диаграмму типа «Диаграмма смешанных типов» и добавить на нее два ряда данных — вертикальные и горизонтальные. Затем необходимо настроить ось горизонтальных данных таким образом, чтобы она не была видима, а вертикальную ось настроить так, чтобы она была видимой. Также нужно выбрать формат столбцов и изменить его на шаги, равные единице.

Как создать диагональные линии на графике?

Для создания диагональных линий на графике в Excel нужно выбрать график, нажать правой кнопкой мыши на область графика и выбрать «Добавить элемент графика». Затем необходимо выбрать «Формы», выбрать кисть и рисовать диагональные линии на графике.

Как изменить цвет ступенчатого графика?

Для изменения цвета ступенчатого графика в Excel нужно выбрать график, нажать правой кнопкой мыши на область графика и выбрать «Форматировать элемент графика». Затем нужно выбрать «Заливка» и изменить цвет графика.

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

Для добавления легенды на ступенчатый график в Excel нужно выбрать график, нажать правой кнопкой мыши на область графика и выбрать «Легенда». Затем нужно выбрать «Расположение», выбрать нужное расположение и изменить название легенды, если нужно.

Можно ли создать ступенчатый график с помощью шаблона графика в Excel?

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