Будуть випадки, коли вам потрібно буде аналізувати великі обсяги даних та створювати звіти, які легко читати та розуміти. Зведені таблиці дозволяють аналізувати такі дані та створювати звіти, які відповідають нашим вимогам до бізнес-звітності.
У цьому підручнику ми розглянемо наступні теми;
- Що таке зведена таблиця?
- Покроковий посібник зі створення зведених таблиць
- 2-мірні зведені таблиці
- Візуалізація даних зведеної таблиці за допомогою діаграм
Що таке зведена таблиця?
Зведена таблиця - це підсумок великого набору даних, який зазвичай включає загальні показники, середнє, мінімальне, максимальне і т.д. середні продажі за регіонами, максимальні та мінімальні продажі за регіонами тощо. Зведені таблиці дозволяють аналізувати, узагальнювати та відображати лише відповідні дані у наших звітах.
Покроковий посібник зі створення зведених таблиць
На малюнку нижче показано зразки даних про продажі, зібрані з бази даних доступу до північного вітру.
Ви можете завантажити зразки даних Excel тут.
Завантажте файл даних Excel вище
Як видно з наведеного зображення, наша електронна таблиця містить багато даних. Скажімо, ми хочемо створити підсумок клієнтів, згрупувати всі їх замовлення за товарами та показати кількості, ціну за одиницю та проміжні підсумки для всіх операцій.
- Відкрийте завантажений файл Excel
- Клацніть на вкладці ВСТАВИТИ на стрічці
- Ви отримаєте наступне вікно
- Клацніть на кнопку вибору таблиці / діапазону, як показано на зображенні вище
- Ви отримаєте наступне міні-вікно
- Клацніть на адресу комірки A1
- Натисніть Ctrl + A на клавіатурі, щоб виділити всі комірки даних
- Ваше міні-вікно, яке показано зараз, відображається наступним чином
- Натисніть кнопку Закрити, щоб повернутися до вікна параметрів
- Натисніть кнопку OK
- Виберіть наступні поля
- Назва компанії
- Назва продукту
- Ціна за одиницю
- Кількість
- Підсумкова
- Тепер ваш аркуш повинен виглядати наступним чином
- Зверніть увагу, що вищезазначені дані згруповані за назвою компанії-замовника, назвою товару, ціною одиниці, сумою кількостей та сумою проміжних підсумків.
- Зверніть увагу на спадну кнопку поруч із Етикетки рядків. Ця кнопка дозволяє нам сортувати / фільтрувати наші дані. Припустимо, нас цікавить лише Альфредс Футтеркісте
- Клацніть на випадаючий список Етикетки рядків, як показано нижче
- Видаліть галочку з (Виділити все)
- Виберіть Alfreds Futterkiste
- Натисніть кнопку OK
- Ви отримаєте наступні дані
2-мірні зведені таблиці
Двовимірна зведена таблиця - це таблиця, яка має поля як у рядках, так і в стовпцях. Скажімо, ми хочемо вказати імена співробітників як рядки та використовувати стовпці для представлення імен клієнтів та заповнення комірок із загальним обсягом продажів.
- Активуйте таблицю продажів
- Клацніть на вкладку ВСТАВИТИ
- Клацніть на зведену діаграму та таблицю
- Виберіть усі дані. Тепер Excel повинен запам'ятати попередній діапазон, тому вам просто потрібно натиснути кнопку ОК
- Буде створено новий аркуш за допомогою інструментів зведеної таблиці
- Виберіть поля, як показано на малюнку нижче
Тепер ваша зведена таблиця буде виглядати наступним чином
Візуалізація даних зведеної таблиці за допомогою діаграм
У програмі Excel 2013 вам не потрібно створювати діаграми вручну. Excel створюватиме вам діаграми під час створення зведених таблиць, зміни сукупних функцій, застосування фільтрів тощо.
Наведена нижче діаграма була автоматично створена для нас на основі простої вправи зведеної діаграми, яка фільтрувала дані лише для Альфреда Футтеркісте.
Резюме
Зведені таблиці та діаграми дозволяють узагальнювати та аналізувати великі масиви даних. Ми можемо використовувати агреговані функції, такі як SUM, MINIMUM, MAXIMUM, AVERAGE тощо. Діаграми автоматично створюються та оновлюються для нас за допомогою Excel, коли ми працюємо з даними.