Як створити зведену таблицю в Excel: підручник для початківців
Шарлотта Міллер
⚡ Розумний підсумок
Зведені таблиці в Excel підсумовують великий набір даних у компактний звіт із підсумками, середніми значеннями та іншими агрегатами. На цій сторінці пояснюється, що таке зведена таблиця, крок за кроком створюється на основі даних про продажі, створюється двовимірна зведена таблиця та генерується зведена діаграма.
Буватимуть часи, коли вам потрібно буде аналізувати великі обсяги даних і складати звіти, які легко читати та розуміти. Зведені таблиці дозволяють нам аналізувати такі дані та створювати звіти, які відповідають нашим вимогам до бізнес-звітності.
Що таке зведена таблиця?
Зведена таблиця – це підсумок великого набору даних, який зазвичай включає загальні цифри, середнє, мінімальне, максимальне тощо. Скажімо, у вас є дані про продажі для різних регіонів, за допомогою зведеної таблиці ви можете підсумувати дані за регіонами та знайти середні продажі на регіон, максимальний і мінімальний продаж на регіон тощо. Зведені таблиці дозволяють нам аналізувати, узагальнювати та відображати в наших звітах лише релевантні дані.
Чотири області зведеної таблиці
Кожна зведена таблиця створюється шляхом перетягування полів вихідних даних в одну з чотирьох областей у списку полів. Розуміння цих областей полегшує уявлення про наступні кроки.
| Область | Що вона робить | Приклад |
|---|---|---|
| ряди | Перелічує категорії зліва, по одній у рядку | Назва компанії |
| Колони | Поширює другу категорію зверху | Ім'я клієнта |
| Цінності | Числа, що агрегуються за допомогою функцій SUM, AVERAGE, MIN або MAX | Сума проміжного підсумку |
| Фільтри | Обмежує всю таблицю вибраним значенням | Показати лише один товар |
💡 Порада: Поле, розміщене як у рядках, так і в стовпцях, створює двовимірну таблицю, показану пізніше. Змініть агрегат в області «Значення», клацнувши поле та вибравши «Налаштування поля значення», а потім виберіть «Середне», «Кількість», «Максимум» або «Мінімум».
Покроковий посібник зі створення зведених таблиць
На зображенні нижче показано зразки даних про продажі, зібрані з бази даних доступу North wind.
Ви можете завантажити зразок даних Excel тут.
Завантажте наведений вище файл даних Excel
Як ви можете бачити на зображенні вище, наша електронна таблиця містить багато даних. Припустімо, ми хочемо створити зведення клієнтів, згрупувати всі їхні замовлення за продуктами та показати кількість, ціну за одиницю та проміжні підсумки для всіх транзакцій.
- Відкрийте файл Excel, який ви завантажили
- Натисніть вкладку ВСТАВИТИ на стрічці
- Ви отримаєте таке вікно
- Натисніть кнопку вибору таблиці/діапазону, як показано на зображенні вище
- Ви отримаєте таке міні-вікно
- Натисніть адресу комірки A1
- Натисніть Ctrl + A на клавіатурі, щоб вибрати всі комірки даних
- Показане зараз міні-вікно виглядає наступним чином
- Натисніть кнопку Закрити, щоб повернутися до вікна параметрів
- Натисніть кнопку OK
-
Виберіть наступні поля
- Назва компанії
- Назва Продукту
- Ціна за одиницю
- Кількість
- Підсумкова
- Тепер ваш аркуш має виглядати наступним чином
- Зверніть увагу, що наведені вище дані згруповано за назвою компанії клієнта, назвою продукту, ціною за одиницю, сумою кількостей і сумою проміжних підсумків.
- Зверніть увагу на кнопку спадного меню поруч із «Мітки рядків». Ця кнопка дозволяє сортувати/фільтрувати наші дані. Припустімо, що нас цікавить лише Alfreds Futterkiste
- Клацніть спадний список Мітки рядків, як показано нижче
- Зніміть галочку з (Вибрати все)
- Виберіть Alfreds Futterkiste
- Натисніть кнопку OK
- Ви отримаєте наступні дані
Двовимірні зведені таблиці
Двовимірна зведена таблиця – це таблиця, яка містить поля як у рядках, так і в стовпцях. Припустімо, ми хочемо вказати імена співробітників у вигляді рядків і використати стовпці для представлення імен клієнтів і заповнити клітинки загальним обсягом продажів.
- Активуйте таблицю даних про продажі
- Натисніть вкладку ВСТАВИТИ
- Натисніть кнопку «Зведена діаграма та таблиця».
- Виберіть усі дані. Тепер Excel повинен запам’ятати попередній діапазон, тому вам потрібно просто натиснути кнопку OK
- За допомогою інструментів зведеної таблиці буде створено новий аркуш
- Виберіть поля, як показано на зображенні нижче
Тепер ваша зведена таблиця виглядатиме наступним чином
Візуалізація даних зведеної таблиці за допомогою діаграм
З Excel 2013 вам не потрібно створювати діаграми вручну. Excel створить графіки для вас, коли ви створюєте свої зведені таблиці, змінюєте агрегатні функції, застосовуєте фільтри тощо.
Наведену нижче діаграму було автоматично створено для нас із простої вправи зведеної діаграми, для якої відфільтровано дані Alfreds Futterkiste тільки.
