Хитрости »

Основные понятия (27) Сводные таблицы и анализ данных (10) Графики и диаграммы (5) Работа с VB проектом (12) Power BI и Power Query (23)

Условное форматирование (5) Списки и диапазоны (5) Макросы(VBA процедуры) (68) Разное (43) Баги и глюки Excel (5)

---

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

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

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

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

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

---

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

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

---

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

---

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика