Відображення різниці на гістограмі і лінійчатої діаграмі з угрупованням в excel

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

Гістограма з угрупованням і з відображенням різниці:

Лінійчата діаграма з угрупованням і з відображенням різниці:

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

Вимоги до даних

Вимоги до діаграми

Для побудови діаграми використовуються два типи графіків: з угрупованням і з накопиченням. Два ряди даних, які ми порівнюємо (план і факт), відображаються на діаграмі з угрупованням, а різниця - на діаграмі з накопиченням.

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

Як це робиться

обчислення даних

Насамперед додаємо в таблицю з даними три стовпці з розрахунками.

Точка отсчётаразніци (Base Variance) - точка відліку для побудови різниці розраховується, як мінімальне значення з двох наборів даних в кожному рядку таблиці.

Так ми отримуємо значення для побудови базової лінії діаграми з накопиченням. Ця частина графіка буде прихована за діаграмою з угрупованням.

Позитивна різниця (Positive Var) - розраховується, як різниця між рядом 1-густо 2 (факт і бюджет), і зображується на графіку, як позитивний результат.

Умова ЯКЩО (IF) повертає порожнє значення, якщо різниця негативна. Пусте значення і підпис для нього не будуть показані на графіку.

Негативна різниця (Negative Var) - таке ж просте обчислення, як і в випадку з позитивної різницею, але для того, щоб отримати позитивне значення при негативній різниці, ми використовували функцію обчислення модуля числа ABS.

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

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

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

1. Насамперед створюємо гистограмму з накопиченням і будуємо в ній п'ять рядів даних.

Ряд 1 (Actual) і ряд 2 (Budget) повинні бути побудовані по допоміжної осі. Клацніть правою кнопкою миші по стовпчику ряду даних Actual на графіку і натисніть Формат ряду даних (Format Data Series).

У розділі налаштувань Параметри ряду (Series Options) поставте прапорець Побудувати ряд (Plot Series On) в положення За допоміжної осі (Secondary Axis).

Повторіть цю операцію для ряду даних Budget.

Для рядів даних 1 і 2 змініть тип діаграми на Гістограма з угрупованням (Clustered Column). Виділіть ряд даних Actual на графіку або в випадаючому списку Елементи діаграми (Chart Elements), який знаходиться на вкладці Макет (Layout) в розділі Поточний фрагмент (Current selection). Діаграма повинна бути виділена, щоб група вкладок Робота з діаграмами (Chart Tools) з'явилася на Стрічці.

На вкладці Конструктор (Design) натисніть кнопку Змінити тип діаграми (Change Chart Type) і виберіть тип Гістограма з угрупованням (Clustered Column).

З цього моменту можна бачити, як наша діаграма починає набувати потрібну форму. Ряди даних Actual (Факт) і Buget (План) тепер показані у вигляді стовпчиків, розташованих поруч для зручності порівняння. Ряд даних, що відображає різницю, видно позаду, як стовпець з накопиченням.

Налаштовуємо формат діаграми. Налаштування форматування, задані за замовчуванням, виглядають не дуже привабливо. Ми можемо дещо виправити, щоб зробити нашу діаграму більш презентабельною:

• Перемістимо легенду в верхню частину області побудови діаграми і видалимо з неї 3 позиції з різницею.
• Додамо назва діаграми.
• Видалимо підписи осей.
• Налаштуємо кольору кордону і заливки для стовпців.
• Видалимо горизонтальні лінії сітки.

Додаємо підписи даних. Стовпці з різницею в таблиці даних відформатовані так, щоб замість нулів відображалася порожня клітинка:

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

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

Підведемо підсумок

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