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

В этом руководстве будет показано, как создать ступенчатую диаграмму во всех версиях Excel: 2007, 2010, 2013, 2016 и 2022.

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

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

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

Начиная

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

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

При этом вот примерная таблица, суммирующая эффективность маркетингового канала в 2022 году:

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

Шаг № 1: Клонируйте исходную таблицу данных.

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

Шаг № 2: продублируйте все значения в клонированной таблице.

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

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

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

Шаг № 3: Отсортируйте таблицу по столбцу даты от самого старого к самому новому.

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

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

Шаг №4: Сдвиньте значения стоимости на одну ячейку вниз.

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

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

Шаг № 5: Удалите первую и последнюю строки повторяющейся таблицы.

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

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

Шаг № 6: Создайте пользовательские метки данных.

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

Сначала настройте новую категорию данных под названием «Метка» в столбце рядом со второй таблицей (в нашем случае - столбец F).

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

1	= ЕСЛИ (E2E1; ТЕКСТ (E2, "$ #, ##") & СИМВОЛ (10) & ТЕКСТ (D2, "ммм"), "")

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

1	= IF ({Стоимость в ячейке E2} {Стоимость в ячейке E1}, ТЕКСТ ({Стоимость в ячейке E2}, «{Форматировать значение как валюту}») & CHAR (10) & TEXT ({Дата в ячейке D2 }, "{Отформатируйте значение в формате месяца}"), "")

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

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

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

Шаг № 7: Создайте линейный график.

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

1. Выделите все ячейки, содержащие значения даты и стоимости (D2: E24).
2. Перейти к Вставлять таб.
3. Щелкните значок «Вставить линейную диаграмму или диаграмму с областями" значок.
4. Выбирать "Линия.”

Чудом выскакивает ступенчатая диаграмма! Однако стоит упомянуть один нюанс. Предположим, вы решили анализировать эффективность электронного маркетинга только за три месяца:

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

В появившейся панели задач сделайте следующее:

1. Переключитесь на Параметры оси таб.
2. Под Тип оси, выберите «Ось даты" переключатель.
3. Под Единицы, установить База значение "Дни / Месяц / Год, »В зависимости от ваших фактических данных.

Теперь вернемся к нашей пошаговой диаграмме.

Шаг № 8: Добавьте метки данных.

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

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

Шаг № 9: Добавьте пользовательские метки данных и избавьтесь от значений меток по умолчанию.

Щелкните правой кнопкой мыши любую метку на диаграмме и выберите «Форматирование меток данных.”

Когда появится панель задач, сделайте следующее:

1. Перейти к Параметры метки таб.
2. Проверить "Значение из ячеек » коробка.
3. Выделите все значения из столбца Label (F2: F24).
4. Нажмите «Ok», Чтобы закрыть диалоговое окно.
5. Снимите флажок "Ценить" поле, чтобы удалить метки по умолчанию.

Шаг № 10: Нажмите метки над линией диаграммы.

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

в Форматирование меток данных панель задач под Параметры метки вкладка, прокрутите вниз до Положение ярлыка и выберите вариант «Выше.”

Шаг № 11: Переместите метки, показывающие, что стоимость падает, под линией графика.

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

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

Итак, у вас есть: больше информации, ведущей к лучшим решениям, благодаря вашей шаговой диаграмме!