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