Як створити зведену таблицю в Excel: підручник для початківців

Буватимуть часи, коли вам потрібно буде аналізувати великі обсяги даних і складати звіти, які легко читати та розуміти. Зведені таблиці дозволяють нам аналізувати такі дані та створювати звіти, які відповідають нашим вимогам до бізнес-звітності.

Що таке зведена таблиця?

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

Покроковий посібник зі створення зведених таблиць

На зображенні нижче показано зразки даних про продажі, зібрані з бази даних доступу North wind.

Ви можете завантажити зразок даних Excel тут.

Завантажте наведений вище файл даних Excel

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

• Відкрийте файл Excel, який ви завантажили
• Натисніть вкладку ВСТАВИТИ на стрічці

• Ви отримаєте таке вікно

• Натисніть кнопку вибору таблиці/діапазону, як показано на зображенні вище
• Ви отримаєте таке міні-вікно

• Натисніть адресу комірки A1
• Натисніть Ctrl + A на клавіатурі, щоб вибрати всі комірки даних
• Показане зараз міні-вікно виглядає наступним чином

• Натисніть кнопку Закрити, щоб повернутися до вікна параметрів
• Натисніть кнопку OK

• Виберіть наступні поля
  • Назва компанії
  • Назва Продукту
  • Ціна за одиницю
  • Кількість
  • Підсумкова
• Тепер ваш аркуш має виглядати наступним чином

• Зверніть увагу, що наведені вище дані згруповано за назвою компанії клієнта, назвою продукту, ціною за одиницю, сумою кількостей і сумою проміжних підсумків.
• Зверніть увагу на кнопку спадного меню поруч із «Мітки рядків». Ця кнопка дозволяє сортувати/фільтрувати наші дані. Припустімо, що нас цікавить лише Alfreds Futterkiste
• Клацніть спадний список Мітки рядків, як показано нижче

• Зніміть галочку з (Вибрати все)
• Виберіть Alfreds Futterkiste
• Натисніть кнопку OK
• Ви отримаєте наступні дані

Двовимірні зведені таблиці

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

• Активуйте таблицю даних про продажі
• Натисніть вкладку ВСТАВИТИ
• Натисніть кнопку «Зведена діаграма та таблиця».
• Виберіть усі дані. Тепер Excel повинен запам’ятати попередній діапазон, тому вам потрібно просто натиснути кнопку OK
• За допомогою інструментів зведеної таблиці буде створено новий аркуш
• Виберіть поля, як показано на зображенні нижче

Тепер ваша зведена таблиця виглядатиме наступним чином

Візуалізація даних зведеної таблиці за допомогою діаграм

З Excel 2013 вам не потрібно створювати діаграми вручну. Excel створить графіки для вас, коли ви створюєте свої зведені таблиці, змінюєте агрегатні функції, застосовуєте фільтри тощо.

Наведену нижче діаграму було автоматично створено для нас із простої вправи зведеної діаграми, для якої відфільтровано дані Alfreds Futterkiste тільки.

Підсумки

Зведені таблиці та діаграми дозволяють нам узагальнювати та аналізувати великі набори даних. Ми можемо використовувати такі агрегатні функції, як СУМА, МІНІМУМ, МАКСИМУМ, СЕРЕДНЄ тощо. Діаграми автоматично створюються та оновлюються для нас у програмі Excel під час роботи з даними.