Как построить пошаговую диаграмму в Excel

Обновлено: 03.07.2024

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

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

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

Линейная диаграмма против. Пошаговая диаграмма

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

1. Точное время изменения

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

< /p>

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

2. Фактическая тенденция

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

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

3. Постоянные периоды

Линейная диаграмма не может отображать периоды, когда значения были постоянными. Но на пошаговой диаграмме вы можете легко показать постоянный период времени для значений. Посмотрите на обе диаграммы.

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

4. Фактическое количество изменений

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

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

Простые шаги для создания пошаговой диаграммы в Excel

Теперь пришло время создать пошаговую диаграмму, и для этого нам нужно использовать приведенные ниже данные, чтобы создать эту диаграмму.

Это данные о запасах на разные даты, когда произошло их увеличение и уменьшение.

<р> Поздравляем! ваша пошаговая диаграмма готова к работе.

Как это работает?

Я уверен, что вы остались довольны созданием своей первой пошаговой диаграммы. Но пришло время понять всю концепцию, которую вы использовали здесь. Итак, давайте рассмотрим пример с небольшим набором данных.

В приведенной ниже таблице указаны две даты: 01 января 2016 г. и 20 февраля 2016 года, а 20 февраля 2016 года количество товаров увеличилось.

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

Для этого вам необходимо создать новую запись на 20 февраля 2016 года, но со стоимостью акций на 01 января 2016 года. Эта запись поможет вам показать линию, когда увеличения не произошло. Таким образом, когда вы создаете линейную диаграмму с этими данными, она становится ступенчатой диаграммой.

Как создать пошаговую диаграмму без дат

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

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

При создании линейной диаграммы вы можете использовать месяцы или годы (без дат) для текущего периода времени. Но когда вы попытаетесь создать пошаговую диаграмму без дат, она будет выглядеть примерно так, как показано ниже.

Здесь вместо объединения дат Excel разделил месяцы на две разные части. Сначала с января по декабрь, а затем с января по декабрь.

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

Например, вместо января, февраля, марта и т. д. используйте 01 января 2016 года, 01 февраля 2016 года, 01 марта 2016 года и получите месяц из дат с помощью пользовательского форматирования.

А для этого просто создайте пошаговую диаграмму.

ПРОБЛЕМА РЕШЕНА.

Как создать ступенчатую диаграмму без подступенков

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

Эту диаграмму лучше всего использовать, когда ваши значения увеличиваются или уменьшаются после постоянного периода. Например, ставки по ипотечному кредиту, банковские процентные ставки и т. д.

Этапы создания ступенчатой диаграммы без подступенков

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

Шаг 1: Прежде всего, нам нужно создать для этого таблицу.

Скопируйте и вставьте заголовки в новые ячейки.
Теперь в исходной таблице выберите даты, начинающиеся со второй даты (от A3 до A12), и скопируйте ее.
После этого перейдите к новой таблице и вставьте даты под заголовком «Дата» (до D2).
Снова перейдите к исходной таблице, выберите стоимость акций от первого значения до предпоследнего значения (от B2 до B11) и скопируйте его.
Теперь вставьте его под заголовком "Процентная ставка" параллельно датам (вставьте в E2).

Шаг 2. Теперь скопируйте даты от первого до последнего и вставьте их под новой таблицей данных. Не беспокойтесь о пустых ячейках для ставок.

Шаг 3: После этого еще раз скопируйте исходную таблицу данных и вставьте ее ниже новой таблицы данных. Здесь у вас есть данные в трех таких наборах.

Шаг 4. Выберите данные и создайте на их основе линейную диаграмму.

Бум! ваша ступенчатая диаграмма без подступенков готова.

Пример файла

Заключение

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

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

Теперь скажи мне одну вещь.

Как вы думаете, какая диаграмма полезнее: пошаговая или линейная?

Поделитесь своим мнением в разделе комментариев, я буду рад услышать от вас. И не забудьте поделиться этим советом с друзьями.

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

Что такое пошаговая диаграмма?

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

Рассмотрите следующие данные —

Как вы можете заметить, изменения данных происходят через неравные промежутки времени.

Пошаговая диаграмма выглядит так, как показано ниже.

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

Если бы вы отобразили те же данные на линейной диаграмме, это было бы так, как показано ниже.

Различия между линейными и ступенчатыми диаграммами

Вы можете определить следующие различия между линейной диаграммой и ступенчатой диаграммой для одних и тех же данных —

Линейная диаграмма сосредоточена на тренде точек данных, а не на точном времени изменения. Пошаговая диаграмма показывает точное время изменения данных вместе с тенденцией.

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

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

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

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

Преимущества пошаговых диаграмм

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

Процентные ставки в зависимости от времени.
Налоговые ставки и доход.
Плиты платы за электроэнергию в зависимости от используемых единиц.

Подготовка данных

Рассмотрите следующие данные —

Шаг 1. Выберите данные. Скопируйте и вставьте данные под последней строкой данных.

Шаг 2. Скопируйте и вставьте все данные справа от данных. Данные выглядят так, как показано ниже.

Шаг 3. Удалите ячейки, выделенные красным, которые изображены в таблице второго набора данных, приведенного ниже.

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

Шаг 5. Скопируйте второй набор данных и вставьте его справа от него, чтобы получить третий набор данных.

Шаг 6. Выберите третий набор данных. Отсортируйте его от наименьшего к наибольшему значению.

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

Создание пошаговой диаграммы

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

Шаг 1. Выберите третий набор данных и вставьте линейную диаграмму.

Шаг 2 — Отформатируйте диаграмму следующим образом —

Нажмите на диаграмму.

Перейдите на вкладку ДИЗАЙН на ленте.

Нажмите «Выбрать данные» в группе «Данные». Появится диалоговое окно "Выбор источника данных".

Выберите Series1 в разделе Legend Entries (Series).

Нажмите кнопку "Удалить".

Нажмите кнопку "Изменить" в разделе "Ярлыки горизонтальной оси (категории)". Нажмите "ОК".

Появится диалоговое окно "Ярлыки осей".

Шаг 3. Выберите ячейки F2:F10 в диапазоне меток оси и нажмите OK.

Шаг 4. Нажмите «ОК» в диалоговом окне «Выбор источника данных». Ваша диаграмма будет выглядеть так, как показано ниже.

Шаг 5. Как вы можете заметить, некоторые значения (годы) на горизонтальной оси (категории) отсутствуют. Чтобы вставить значения, выполните шаги, указанные ниже.

Как видите, горизонтальная ось (категория) теперь содержит даже отсутствующие годы в значениях категории. Далее, пока не произошло изменение, линия горизонтальна. Когда есть изменение, его величина изображается высотой вертикальной линии.

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

Вот как это выглядит:

Зачем использовать пошаговую диаграмму?

Пошаговые диаграммы показывают изменения, которые происходят время от времени. Например, основная ставка США, которая основана на целевой ставке Федерального резерва, но меняется не так часто. С 2000 года он менялся всего 43 раза, в то время как обычный линейный график может меняться ежеквартально, ежемесячно или даже ежедневно.

К сожалению, в Excel нет стандартного типа пошаговой диаграммы, но мы можем создать его за несколько шагов.

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

и создайте на его основе линейную диаграмму, ваша диаграмма будет выглядеть так:

Это неточная картина наших данных. Например, если вы посмотрите на диаграмму за 2003 год, вы можете предположить, что текущая ставка была ниже 6 процентов, но на самом деле она все еще составляла 6 процентов. Excel предполагает, что наши данные должны быть интерполированы с 6 процентов в 2000 году до 5 процентов в 2004 году. Мы хотим, чтобы это выглядело так, как показано на рисунке вверху этого поста. Итак, приступим к делу…

Разбивка

1) Создать новый диапазон диаграммы

2) Создать линейную диаграмму

3) Изменить тип горизонтальной оси

Шаг за шагом

1) Создать новый диапазон диаграммы

Это первый прием создания типа пошаговой диаграммы в Excel 2013.

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

Обратите внимание, что за 2004 г. есть две точки данных: одна соответствует ставке 6 (предыдущая ставка) и одна соответствует фактической ставке 5 %. Это создаст вертикальную линию в точке 2004 года от 6 до конечной ставки 5%. Вам нужно повторить этот шаг для всех точек данных, как показано на рисунке выше, в диапазоне C2:D13.

2) Создать линейную диаграмму

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

Затем выделите диапазон данных диаграммы от C2:D13.

Затем перейдите на ленту «Вставка» и выберите тип линейной диаграммы в группе диаграмм.

Ваша результирующая диаграмма должна выглядеть следующим образом:

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

3) Изменить тип горизонтальной оси

Это последний шаг в создании пошаговой диаграммы. Нам нужно рассматривать нашу горизонтальную ось как ось дат. Поскольку Excel не распознает числа на горизонтальной оси как годы даты, нам придется принудительно это сделать.

Для этого нам нужно выбрать нашу диаграмму, затем выбрать горизонтальную ось и нажать CTRL+1 или щелкнуть ее правой кнопкой мыши и выбрать «Формат оси…».

Затем в параметрах оси выберите переключатель «Ось дат» в качестве типа оси.

Теперь у вас должна быть окончательная диаграмма Excel, и она будет выглядеть так:

Видеоруководство

*Обратите внимание, что я добавил больше точек данных в видео, это не повредит диаграмме, но нам действительно нужно только тогда, когда точки данных меняются.

Надеюсь, вам понравилось. Убедитесь, что вы усвоили этот совет по составлению пошаговых диаграмм в Excel и вернетесь к пятничному испытанию, так как оно будет включать в себя пошаговые диаграммы. Кроме того, спасибо за то, что вы являетесь поклонником и приглашаете друга и коллегу!

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

Однако, поскольку диаграмма не поддерживается в Excel, вам придется приложить некоторые усилия, чтобы построить ее самостоятельно. Чтобы сэкономить ваше время, мы разработали надстройку Chart Creator — простой в использовании инструмент для создания расширенных диаграмм Excel за считанные секунды.

В этом пошаговом руководстве вы узнаете, как создать простую пошаговую диаграмму с нуля в Excel:

Начало работы

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

Проходя через различные маркетинговые каналы в поисках наиболее эффективного варианта, вы решили построить пошаговую диаграмму, отображающую стоимость привлечения клиентов (CAC) для электронного маркетинга за 2019 год – ключевой показатель, измеряющий затраты на маркетинговые усилия. необходимо привлечь нового клиента.

С учетом этого, вот пример таблицы, обобщающей эффективность маркетингового канала в 2019 году:

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

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

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

Для этого выделите все значения во второй таблице и нажмите Ctrl + C, чтобы скопировать выбранные ячейки. Затем выберите первую пустую ячейку под таблицей (в нашем случае D14) и нажмите Ctrl + V, чтобы вставить данные.

Вот как это выглядит на практике:

Пришло время заложить основу для пользовательских меток данных, которые вы создадите в будущем.

Выделите всю клонированную таблицу (D1:E25).
Перейдите на вкладку "Данные".
В группе "Сортировка и фильтр" нажмите кнопку "Сортировка".
В каждом раскрывающемся меню отсортируйте по следующим параметрам:
1. В поле "Столбец" выберите "Месяц" (столбец D).
2. Для "Сортировать по" выберите "Значения" / "Значения ячеек".
3. В разделе "Порядок" выберите "Старейшие к новейшим".
Этот простой прием избавит вас от необходимости вручную вводить значения для каждой даты.

Выделите все значения из столбца CAC (столбец E) в дублирующейся таблице. Затем сдвиньте все на одну ячейку вниз, нажав Ctrl + X, чтобы вырезать данные, а затем Ctrl + V в следующей ячейке вниз (E3), чтобы вставить их на новое место.

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

Выделите ячейки D2 и E2, щелкните правой кнопкой мыши и выберите "Удалить". В диалоговом окне выберите Сдвинуть ячейки вверх. Сделайте то же самое для ячейки E25.

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

Сначала настройте новую категорию данных под названием "Ярлык" в столбце, примыкающем ко второй таблице (в нашем случае, столбце F).

Оказавшись там, чтобы создать метки, содержащие даты в паре с соответствующими значениями, вам нужно раскрыть всю мощь функций IF, CHAR и TEXT. Этот специальный коктейль из функций Excel поможет нам справиться с задачей:

Для непосвященных вот расшифрованная версия формулы:

Проще говоря, формула сравнивает значения E1 и E2 между собой, и если они не совпадают, ячейка F2 заполняется специальной меткой, отображающей соответствующий месяц и стоимость. Функция CHAR(10) остается константой и обозначает разрыв строки. В противном случае формула возвращает пустое значение.

Вот как это выглядит, когда резина отправляется в путь:

Теперь перетащите маркер заполнения в правом нижнем углу выбранной ячейки до конца столбца F, чтобы выполнить формулу для оставшихся ячеек (F3:F24).

Все препятствия устранены. Пришло время создать простую линейную диаграмму и посмотреть, что получится.
1. Выделите все ячейки, содержащие значения даты и стоимости (D2:E24).
2. Перейдите на вкладку "Вставка".
3. Нажмите значок "Вставить линейную или диаграмму с областями".
4. Выберите «Линия».
Чудесным образом появляется пошаговая диаграмма! Однако стоит упомянуть одно предостережение. Предположим, вы решили проанализировать эффективность электронного маркетинга только за три месяца:

Как вы могли заметить, горизонтальная ось выглядит довольно неряшливо. К счастью, решить проблему проще простого. Сначала щелкните правой кнопкой мыши горизонтальную ось (метки внизу) и выберите «Формат оси».

В появившейся области задач выполните следующие действия:
1. Перейдите на вкладку "Параметры оси".
2. В разделе "Тип оси" выберите переключатель "Ось дат".
3. В разделе "Единицы" установите базовое значение "Дни/месяц/год" в зависимости от ваших фактических данных.
Теперь вернемся к нашей пошаговой диаграмме.

Далее нам нужно добавить на пошаговую диаграмму настраиваемые метки данных, которые вы создали ранее.

Нажмите правой кнопкой мыши на строку, иллюстрирующую колебания стоимости, и выберите "Добавить метки данных".

Нажмите правой кнопкой мыши любую метку на диаграмме и выберите "Форматировать метки данных".

Когда появится панель задач, выполните следующие действия:
1. Перейдите на вкладку "Параметры ярлыка".
2. Установите флажок "Значение из ячеек".
3. Выделите все значения в столбце Метка (F2:F24).
4. Нажмите «ОК», чтобы закрыть диалоговое окно.
5. Снимите флажок "Значение", чтобы удалить ярлыки по умолчанию.
Мы почти закончили! В качестве последнего штриха вам нужно выровнять метки в нужных местах. Метки, обозначающие рост затрат, будут размещены над линией. И наоборот, метки, отображающие снижение стоимости, будут перемещены ниже строки.

На панели задач "Формат меток данных" на вкладке "Параметры метки" прокрутите вниз до "Положение метки" и выберите вариант "Выше".

Наконец, выберите один за другим ярлыки, которые отображают переходы в CAC, и установите для каждого из них значение «Положение ярлыка» на «Ниже».

Последняя корректировка: сделайте метки жирными (вкладка «Главная» > «Шрифт») и измените название диаграммы. Ваша пошаговая диаграмма готова.

Итак, вот и все: больше информации для принятия более эффективных решений благодаря пошаговой диаграмме!

Читайте также: