Как построить ступенчатый график в 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 без сложных настроек в форматировании дизайна диаграмм.
Более того вы имеете возможность переключатся в любой из двух режимов для детального и общего анализа истории изменения показателей.
Требуется на отрезке [-1; 4] построить график функции f(x). Параметры a = 5 и b = 2 необходимо задать в отдельных ячейках.
Решение (1 ряд данных)
Чтобы построить график функции в MS EXCEL можно использовать диаграмму типа График или Точечная.
СОВЕТ : О построении диаграмм см. статью Основы построения диаграмм в MS EXCEL . О различии диаграмм Точечная и График см. статью График vs Точечная диаграмма в MS EXCEL .
Создадим таблицу с исходными данными для x от -1 до 4, включая граничные значения (см. файл примера, лист Ряд1 ):
Шаг по х выберем равным 0,2, чтобы график содержал более 20 точек.
Чтобы построить диаграмму типа Точечная:
- выделите любую ячейку таблицы;
- во вкладке Вставка в группе Диаграммы выберите диаграмму Точечная с прямыми отрезками и маркерами .
Чтобы построить диаграмму типа График:
- выделите любую столбец f(x) вместе с заголовком;
- во вкладке Вставка в группе Диаграммы выберите диаграмму График маркерами .
У обеих диаграмм один общий недостаток - обе части графика соединены линией (в диапазоне х от 1 до 1,2). Из этого можно сделать ошибочный вывод, что, например, для х=1,1 значение функции равно около -15. Это, конечно же, не так. Кроме того, обе части графика одного цвета, что не удобно. Поэтому, построим график используя 2 ряда данных .
Решение (2 ряда данных)
Создадим другую таблицу с исходными данными в файле примера, лист График :
У такой диаграммы имеется недостаток - в диапазоне х от 1 до 1,2 на диаграмме теперь нет вообще значений. Чтобы избежать этого недостатка - построим диаграмму типа Точечная с 3-мя рядами данных.
Решение (3 ряда данных)
Для построения графика используем 2 таблицы с данными для каждого уравнения, см. файл примера, лист График .
Первое значение второго графика возьмем чуть больше 1, например, 1,00001, чтобы как можно ближе приблизиться к значению, в котором происходит разрыв двух графиков. Также для точки со значением х=1 построим на диаграмме одну точку (ряд №3), чтобы показать, что для этого х значение второго уравнения не вычисляется (хотя фактически вычисляется).
Ступенчатая диаграмма применяется для отображения динамики по показателям во временных промежутках не обычным графиком, а как бы ступенями:
Среди предустановленных типов диаграмм в Excel такая диаграмма отсутствует. А для построения такой диаграммы самостоятельно необходимо на основании исходных данных создать новую таблицу и уже на основе её строить диаграмму. Команда Ступенчатая диаграмма сама создаст необходимые данные и построит на их основании диаграмму.
Т.к. сам вид ступенчатой диаграммы специфичен, то есть определенные требования к исходных данным:
- Первой строкой таблицы обязательно должны быть заголовки
- Первый столбец таблицы должен содержать даты. Если первым столбцом будут не даты - результат может быть не таким, как ожидается.
- Начиная со второго столбца должны быть числовые данные. Столбцов данных может быть сколько угодно, но не стоит забывать, что если их много, то ступенчатая диаграмма будет не наглядной и плохочитаемой.
К примеру, для создания диаграммы выше была взята таблица следующего вида:
В данной таблице даты уже расположены по возрастанию, но если в исходных данных даты не отсортированы, то MulTEx сама отсортирует данные в порядке возрастания, после чего создаст дополнительные столбцы данных с нужными вычислениями. И после этого на основании этих данных построит диаграмму.
Таким образом после того, как убедились, что данные соответствуют требованиям, переходим на вкладку MulTEx -группа Ячейки/Диапазоны -Диаграммы -Ступенчатая диаграмма
Построить диаграмму на основании данных диапазона - указываются столбцы исходных данных в таблице. Допускается указать полностью столбцы - в ходе создания диаграммы программа сама определит конец данных на основании первого столбца указанных данных.
Видеоинструкции по использованию надстройки MulTEx
Поделитесь своим мнением
Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Tips_Charts_StepChart.xls (56,0 KiB, 3 416 скачиваний)
Способ 1: Применяем планки погрешностей
Для начала потребуется добавить столбец с формулой для погрешностей. Запишем в ячейку с первым значением(на скрине это C2, напротив 1 апр 2015) значение 0, а в следующую ячейку формулу: = B3 - B2 .
Теперь копируем ячейку с формулой, выделяем данные в столбце С, начиная с С3 и до конца данных в таблице и вставляем скопированную формулу. Или можно просто протянуть эту формулу до конца таблицы.
Теперь выделяем первые два столбца таблицы вместе с заголовками(Дата и Выручка) и вставляем новую диаграмму:
Далее необходимо добавить планки погрешностей:
- 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)
- Excel 2003:
Вставка (Insert) -Диаграмма (Chart) -График (Line) -График (Line) - Excel 2007 и выше:
вкладка Вставка (Insert) -группа Диаграммы (Charts) -График (Line) -График (Line)
Все, график готов. Теперь останется так же как и в первом способе навести при необходимости красоту на свое усмотрение через изменение свойств элементов диаграммы.
Tips_Charts_StepChart.xls (56,0 KiB, 3 416 скачиваний)
Способ 1: Применяем планки погрешностей
Для начала потребуется добавить столбец с формулой для погрешностей. Запишем в ячейку с первым значением(на скрине это C2, напротив 1 апр 2015) значение 0, а в следующую ячейку формулу: = B3 - B2 .
Теперь копируем ячейку с формулой, выделяем данные в столбце С, начиная с С3 и до конца данных в таблице и вставляем скопированную формулу. Или можно просто протянуть эту формулу до конца таблицы.
Теперь выделяем первые два столбца таблицы вместе с заголовками(Дата и Выручка) и вставляем новую диаграмму:
Далее необходимо добавить планки погрешностей:
- 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)
- Excel 2003:
Вставка (Insert) -Диаграмма (Chart) -График (Line) -График (Line) - Excel 2007 и выше:
вкладка Вставка (Insert) -группа Диаграммы (Charts) -График (Line) -График (Line)
Все, график готов. Теперь останется так же как и в первом способе навести при необходимости красоту на свое усмотрение через изменение свойств элементов диаграммы.
Читайте также: