Як створити покрокову діаграму в Excel

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

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

Однак, оскільки діаграма не підтримується в Excel, вам доведеться докласти певних зусиль, щоб побудувати її самостійно. Щоб заощадити ваш час, ми розробили надбудову Chart Creator-простий у використанні інструмент для створення розширених діаграм Excel за лічені секунди.

У цьому покроковому посібнику ви дізнаєтесь, як побудувати просту поетапну діаграму з нуля в Excel:

Починаємо

Для ілюстрації припустимо, що ви збираєтесь розпочати масштабну маркетингову кампанію для свого бренду, і вам потрібно з’ясувати, куди вкласти рекламні долари у 2022 році.

Коли ви пробираєте різні маркетингові канали в пошуках найбільш ефективного варіанту, ви вирішуєте скласти покрокову діаграму, яка відображатиме вартість вашої залучення клієнтів з електронного маркетингу (CAC) з 2022 року - ключовий показник, який вимірює вартість маркетингових зусиль, необхідних для залучення нового клієнта.

З урахуванням сказаного, ось зразкова таблиця, що підсумовує результати маркетингового каналу в 2022 році:

У бізнесі час - це гроші, тож почнемо.

Крок №1: клонуйте вихідну таблицю даних.

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

Крок 2: Дублюйте всі значення в клонованій таблиці.

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

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

Ось як це виглядає на практиці:

Крок №3: Сортуйте таблицю за стовпцем дати від найдавнішої до найновішої.

Настав час закласти основи для власних міток даних, які ви створите по дорозі.

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

Крок №4: Зсуньте значення вартості на одну клітинку вниз.

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

Виділіть усі значення зі стовпця CAC (колонка Е) у дублікаті таблиці. Потім пересуньте все на одну клітинку вниз, натиснувши Ctrl + X щоб потім обрізати дані Ctrl + V у наступній клітині вниз (E3), щоб вставити його на нове місце.

Крок №5: Видаліть перший та останній рядки повторюваної таблиці.

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

Виділити клітинки D2 та E2, клацніть правою кнопкою миші та виберіть Видалити. У діалоговому вікні виберіть Зсунути клітинки вгору. Зробіть те ж саме для клітини E25.

Крок 6: Створіть спеціальні мітки даних.

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

Спочатку встановіть нову категорію даних під назвою «Мітка» у стовпці, що прилягає до другої таблиці (у нашому випадку стовпець F).

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

1	= IF (E2E1, TEXT (E2, "$#, ##") & CHAR (10) & TEXT (D2, "ммм"), "")

Для непосвячених ось декодована версія формули:

1	= IF ({Вартість у комірці E2} {Вартість у комірці E1}, TEXT ({Вартість у комірці E2}, "{Форматувати значення як валюту}") & CHAR (10) & TEXT ({Дата у комірці D2 }, "{Форматувати значення у форматі місяця}"), "")

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

Ось як це виглядає, коли гума виходить на дорогу:

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

Крок 7: Створіть лінійну діаграму.

Тепер усі перешкоди усунено. Настав час створити просту лінійну діаграму і подивитися, що станеться.

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

Дивом спливає діаграма ступенів! Однак варто згадати одне застереження. Припустимо, ви вирішили аналізувати ефективність маркетингу електронною поштою лише протягом трьох місяців:

Як ви могли помітити, горизонтальна вісь виглядає досить безладно. На щастя, вирішити проблему так само легко, як і очистити горох. Спочатку клацніть правою кнопкою миші по горизонтальній осі (мітки внизу) і виберіть "Формат осі.”

У вікні завдань, що з’явиться, виконайте наведені нижче дії.

1. Перейдіть до Параметри осі вкладка.
2. Під Тип осі, виберіть “Вісь дати" радіо-кнопка.
3. Під Одиниці, встановити База значення "Дні/місяць/рік", Залежно від ваших фактичних даних.

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

Крок #8: Додайте мітки даних.

Далі нам потрібно додати до покрокової діаграми спеціальні мітки даних, які ви раніше створили.

Клацніть правою кнопкою миші на рядку, що ілюструє коливання вартості, і виберіть «Додати мітки даних.”

Крок 9: Додайте власні мітки даних та позбудьтеся значень міток за замовчуванням.

Клацніть правою кнопкою миші будь-яку підпис на діаграмі та виберіть "Форматувати мітки даних.”

Після появи панелі завдань виконайте наведені нижче дії.

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

Крок 10: Натисніть мітки над лінією діаграми.

Ми майже закінчили! Останній штрих: етикетки потрібно розташувати в потрібних місцях. Мітки, що представляють підвищення вартості, будуть розміщені над лінією. І навпаки, мітки, що відображають зниження вартості, будуть переміщені нижче рядка.

В Форматувати мітки даних на панелі завдань під Параметри міток вкладку, прокрутіть униз до Положення мітки і виберіть опцію «Вище.”

Крок #11: Перемістіть мітки, що показують, що вартість падає нижче рядка діаграми.

Нарешті, виберіть мітки по черзі, які відображають падіння в CAC, і встановіть Положення мітки значення для кожного до "Нижче.”

Для останнього регулювання зробіть етикетки жирним шрифтом (Вкладка Домашня сторінка> Шрифт) і змінити назву діаграми. Ваша крокова діаграма готова.

Ось вам: більше інформації, що допомагає приймати більш прийнятні рішення, все завдяки вашій кроковій діаграмі!