Зведені таблиці в Excel – це ефективний та зручний інструмент для аналізу даних. Зазвичай їх використовують для підсумовування інформації, але вони також можуть обчислювати відсоткові зміни між різними значеннями, і це доволі легко зробити.
Цей метод можна застосовувати для різноманітних цілей, зокрема для порівняння різних показників. Розглянемо приклад розрахунку та відображення відсотка зміни загальної вартості продажів за місяцями.
Для ілюстрації використаємо наступний набір даних:
Типовий приклад таблиці продажів, де вказано дату замовлення, ім’я клієнта, торгового представника, загальну вартість продажів та інші дані.
Спочатку необхідно відформатувати цей діапазон даних як таблицю Excel. Далі, ми створимо зведену таблицю, яка дозволить розрахувати та відобразити відсоткові зміни.
Форматування діапазону як таблиці
Якщо ваші дані ще не представлені у вигляді таблиці, рекомендуємо це зробити. Таблиці в Excel мають значні переваги над звичайними діапазонами клітинок, особливо при роботі зі зведеними таблицями (додаткова інформація про переваги використання таблиць).
Щоб перетворити діапазон на таблицю, виділіть необхідні клітинки та оберіть “Вставити” > “Таблиця”.
Переконайтеся, що діапазон виділено коректно і що перший рядок містить заголовки. Натисніть “ОК”.
Після цього діапазон буде перетворено на таблицю. Наявність назви таблиці значно полегшить її використання при створенні зведених таблиць, діаграм та формул.
Перейдіть до вкладки “Конструктор” у розділі “Робота з таблицями” та введіть назву таблиці в відповідне поле. В нашому випадку таблицю названо “Продаж”.
За бажанням, ви також можете змінити стиль таблиці.
Створення зведеної таблиці для відображення відсоткової зміни
Тепер перейдемо до створення зведеної таблиці. На новій вкладці натисніть “Вставити” > “Зведена таблиця”.
З’явиться вікно “Створення зведеної таблиці”, де автоматично буде визначено таблицю. На цьому етапі ви можете вибрати іншу таблицю або діапазон, якщо необхідно.
Групування дат за місяцями
Перетягніть поле з датою, за якою буде відбуватися групування, у розділ рядків зведеної таблиці. У нашому випадку це поле “Дата замовлення”.
Починаючи з версії Excel 2016, дати автоматично групуються за роками, кварталами та місяцями.
Якщо у вашій версії Excel це не відбувається автоматично, або ви бажаєте змінити групування, клацніть правою кнопкою миші на комірці зі значенням дати та оберіть “Групувати”.
Оберіть бажані параметри групування. У цьому прикладі обрано групування за роками та місяцями.
Тепер рік та місяць стали окремими полями, які можна використовувати для аналізу. При цьому, місяці все ще відображаються під назвою “Дата замовлення”.
Додавання полів значень до зведеної таблиці
Перемістіть поле “Рік” з області рядків до області фільтрів. Це дозволить фільтрувати дані зведеної таблиці за роком, не перевантажуючи її зайвою інформацією.
Двічі перетягніть поле зі значеннями (у цьому випадку “Загальна вартість продажів”), для яких необхідно розрахувати відсоткову зміну, до області “Значення”.
Наразі це не дуже інформативно, але скоро все зміниться.
Обидва поля значень за замовчуванням відображають суми, і наразі вони не відформатовані.
Значення у першому стовпці ми залишимо як підсумкові, але нам потрібно їх відформатувати.
Клацніть правою кнопкою миші на число у першому стовпці та оберіть “Формат чисел” з контекстного меню.
У діалоговому вікні “Формат клітинок” оберіть “Грошовий формат” з 0 знаками після коми.
Тепер зведена таблиця виглядає наступним чином:
Створення стовпця відсоткової зміни
Клацніть правою кнопкою миші на значення у другому стовпці, наведіть курсор на “Показати значення як”, а потім оберіть “% від різниці”.
Оберіть “(Попередній)” як базовий елемент. Це означає, що значення поточного місяця завжди буде порівнюватися зі значенням попереднього місяця (поле “Дата замовлення”).
Тепер зведена таблиця відображає як значення, так і їх відсоткову зміну.
Клацніть на клітинку з мітками рядків та введіть “Місяць” як заголовок для цього стовпця. Далі клацніть на клітинку заголовка другого стовпця значень та введіть “Відхилення”.
Додавання стрілок для візуалізації відхилень
Для поліпшення візуального сприйняття зведеної таблиці, додамо стрілки зеленого та червоного кольорів для відображення позитивних та негативних відхилень.
Це дозволить легко ідентифікувати позитивні та негативні зміни.
Клацніть на будь-яке значення у другому стовпці, а потім оберіть “Головна” > “Умовне форматування” > “Створити правило”. У вікні “Змінити правило форматування”, виконайте наступні дії:
Виберіть “Усі клітинки, які показують значення “Відхилення” для дати замовлення”.
Оберіть “Набори значків” зі списку “Стиль формату”.
Виберіть червоні, жовті та зелені трикутники зі списку “Стиль значка”.
У стовпці “Тип” змініть значення зі списку на “Число” замість “Відсоток”. Це змінить значення стовпця “Значення” на 0. Саме те, що нам потрібно.
Натисніть “ОК” і умовне форматування буде застосовано до зведеної таблиці.
Зведені таблиці – це потужний інструмент і один з найпростіших способів для відображення відсоткової зміни значень з часом.