Як показати маленькі і великі величини на одному графіку? Це один з поширених питань при візуалізації даних. Напевно, ви стикалися з ситуацією, коли при побудові діаграми більшість елементів ряду даних близькі за значенням, але в масиві також присутній пара значень, які на порядок або два відрізняються від всіх інших. В такому випадку побудована діаграма буде не читабельною і зовнішній вигляд її не відображатиме справжнього стану справ. Вийти з ситуації можна, розбивши вісь Y на дві складові, де по одній частині будуть побудовані основні елементи ряду даних, а по інший - пікові значення. Нижче описаний спосіб побудови діаграми в Excel з розірваної віссю.
Кінцевий результат представлений нижче. Тут, нижня частина, яка відображає маленькі значення, побудована по основній осі (з лівого боку), а великі значення побудовані по допоміжної осі (з правого боку). Обидві осі нерозривні і масштабуються від нуля. Таким чином, дану діаграму можна сприймати як два графіка, розташованих один під одним.
Підготовка данних
Перш ніж ми почнемо малювати графік, нам необхідно підготувати дані. Основний фокус буде полягати в тому, що значення перевищують певну межу будуть множать деякий коефіцієнт, який наближатиме великі значення до основного масиву. Припустимо, що у нас є часовий ряд, що відображає щоденні зміни якогось показника, і два значення з цього масиву вибиваються із загального ряду.
Нам необхідно конвертувати їх в, близькі до основного масиву, значення. Для цього скористаємося формулою = ЕСЛИ (C3> 100; 100 + C3 * 100/1000; C3), яка буде множити значення перевищують 100 на 0,1 і додавати до них 100. Таким чином, значення 960 буде переведено в 196 = 100 + 960 * 0,1 і не буде так разюче відрізнятися від загальної маси.
Також додамо стовпець для побудови ряду даних, який буде візуально розмежовувати графік з маленькими значеннями і великими. На прикладі вище - це сіра пунктирна лінія, що проходить посередині діаграми. Цей графік буде будується по нульовій позначці допоміжної осі, тому він заповнений нулями.
побудова діаграми
Щоб побудувати діаграму, утримуючи клавішу CTRL, виділяємо стовпці з заголовками Дата, Перерахунок та Роздільник. У моєму прикладі це діапазони B2: B17 і D2: E17. Переходимо у вкладці Вставка в групу Діаграми і вибираємо Вставити графік -> Графік. У вас повинен вийде наступний графік.
Тепер необхідно налаштувати відображення основної та допоміжної осі. Щоб у нас з'явилася допоміжна вісь, клацаємо правою кнопкою миші по ряду даних Роздільник. У випадаючому меню вибираємо Формат ряду даних. У що з'явилася правій панелі Формат ряду даних переходимо у вкладку Параметри ряду і ставимо маркер Побудувати ряд -> За допоміжної осі. У правій частині діаграми у вас повинна з'явиться допоміжна вісь Y.
Далі налаштовуємо осі так, щоб по основній осі (та що зліва) відображалися значення менше 100 з кроком 20, при цьому значення від 100 і вище не повинні бути видні на осі, а на допоміжній осі відображалися значення від 100 і вище з кроком 200, але нижня половина не була видна. У цій справі нам допоможе призначений для користувача формат. Про все по порядку.
Клацаємо правою кнопкою миші по основній осі, в випадаючому меню вибираємо Формат осі. У що з'явилася правій панелі у вкладці Параметри осі встановлюємо значення Мінімум рівним 0, Максимум рівним 200, Основні ділення рівним 20. У цій же вкладці переходимо до групи Число і в поле Код формату вставляємо призначений для користувача формат [= 0] 0; [<=100]0;. убираем галочку Связь с источником и щелкаем по кнопке Добавить. Верхняя половина основной оси должна исчезнуть, как показано на рисунке:
Далі необхідно налаштувати допоміжну вісь. Для цього клацаємо правою кнопкою миші по осі, в випадаючому меню вибираємо Формат осі. У що з'явилася правій панелі у вкладці Параметри осі встановлюємо значення Мінімум рівним -1000, Максимум рівним 1000, Основні ділення рівним 200. У групі Число вказуємо формат 0 ;;. Це означає, що для всіх позитивних значень буде застосуємо загальний формат, а для 0 і негативних значеньніякої формат застосований не буде.
Нижня частина допоміжної осі повинна зникнути, як показано на малюнку. При цьому ряд даних Роздільник переміститься до центру.
Наступним кроком потрібно візуально розділити верхню частину графіка від нижньої. Для цього клацаємо по ряду даних Роздільник правою кнопкою мили, в випадаючому меню вибираємо Формат ряду даних. У що з'явилася справа панелі переходимо у вкладці Заливка і кордони в групу Лінія і міняємо значення Колір на білий, Прозорість 14%, Ширина рівна 20.
Додамо ще один ряд даних зі значеннями з шпальти Роздільник. щоб мати чітку межу між малими значеннями і великими. Побудуємо його по допоміжної осі і назвемо Разделітель2. Відформатуємо його так, щоб відрізнити від основних рядів даних. Результат відображений на картинці нижче:
Залишилося додати підписи даних і відформатувати наш графік. Виділяємо наш основний ряд даних, переходимо по вкладці Робота з діаграмами -> Конструктор в групу Макети діаграм. Вибираємо Додати елемент діаграми -> Підписи даних -> По центру. Клацаємо правою кнопкою миші по з'явився на графіку підписам, з випадаючого меню вибираємо Формат підписів даних. У що з'явилася справа панелі переходимо у вкладку Параметри підпису. ставимо галочку навпроти пункту Значення з осередків, в діалоговому вікні Діапазон міток даних вказуємо діапазон стовпця Дані (у мене в прикладі це С3: С17) клацаємо ОК і прибираємо всі інші галочки з пункту Включати в підпис.
Залишилося внести корективи в формат нашої діаграми - прибрати легенду, назву діаграми змінити на що-небудь більш осмислене. Підписи даних можна зробити більш контрастними, так як наша діаграма має не зовсім традиційний вид і може ввести в оману користувачів.
Підсумкова діаграма може мати наступний вигляд:
Додамо додатковий стовпець в нашу таблицю, назвемо його Підписи і введемо наступну формулу: = C3 »». яка буде переводити числовий формат в текстовий.
Далі клацаємо правою кнопкою миші по ряду даних Перерахунок, з випадаючого меню вибираємо Вибрати дані. У діалоговому вікні Вибір джерела даних в поле Підписи горизонтальній осі клацаємо Змінити. У наступному вікні вибираємо дані стовпця Підписи. Тиснемо два рази ОК.
Як ви бачите, тепер на горизонтальній осі графіка замість дат стоять значення діапазону.
Залишилося налаштувати відображення горизонтальних осей. Клацаємо правою кнопкою миші по нижній горизонтальній осі, в випадаючому меню вибираємо Формат осі. У вкладці Параметри осі в поле Підписи осі вибираємо значення Ні, клацаємо Закрити.
Знову виділяємо всю діаграму, переходимо у вкладку Робота з діаграмами -> Макет в групу Осі, вибираємо Осі -> Допоміжна горизонтальна вісь -> Зліва направо. На діаграмі повинна з'явитися додаткова горизонтальна вісь, яка за замовчуванням знаходиться у верхній частині графіка. Її необхідно помістити на місце основної осі, для цього клацаємо правою кнопкою миші по допоміжної осі, в випадаючому меню вибираємо Формат осі. У вкладці Параметри осі в поле Основні вибираємо значення Ні, в поле Підписи осі вибираємо Внизу. Переходимо у вкладку Колір лінії, встановлюємо маркер напроти пункту Немає лінії, клацаємо Закрити.
Фактично, щоб відобразити на одній діаграмі в Excel маленькі і великі значення, ми побудували графік із зміненими даними та наклали його на скориговані осі. Такий підхід не стандартний, але як показує практика, особливих складної з розумінням його не виникає.
Щоб краще зрозуміти це підхід, можете завантажити його за прикладом Побудова діаграми з розірваної віссю.