Хитрости »
Основные понятия (23)
Сводные таблицы и анализ данных (9)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (14)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (38)
Баги и глюки Excel (2)

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

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

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


Способ 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 и выше

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

  • 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 Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Надстройки Настройка Печать Поиск данных Политика Конфиденциальности Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных баги и глюки в Excel ссылки
Обсуждение: есть 1 комментарий
  1. Привет:

    Странно, что разработчики Excel не хотят внедрить штатную возможность так соединять отрезки данных

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2018 Excel для всех   Войти