Аналіз даних Excel: створення зведеної таблиці Excel

Зведені таблиці Excel дуже універсальні, оскільки вони дозволяють легко аналізувати підсумки великих обсягів даних за допомогою різноманітних зведених функцій (хоча підсумки, створені за допомогою функції SUM, ймовірно, залишаться вашим колишнім режимом очікування). Налаштовуючи оригінальну зведену таблицю Excel, ви приймаєте кілька рішень: яку функцію підсумовування використовувати, до яких стовпців (полів) застосовувати підсумкову таблицю та з якими стовпцями (полями) ці обчислення зводяться в таблицю.

Зведені таблиці Excel ідеально підходять для перехресного табуляції одного набору даних у вашому списку даних з іншим. Наприклад, ви можете створити зведену таблицю з таблиці бази даних співробітників, яка містить загальну суму заробітної плати для кожної категорії посад, перехресну таблицю (впорядковану) за відділом або місцем роботи.

Створення зведених таблиць Excel за рекомендацією

Якщо створення нової зведеної таблиці за допомогою інструмента швидкого аналізу є для вас надто складною роботою, ви створюєте її миттєво за допомогою командної кнопки «Рекомендовані зведені таблиці». Щоб скористатися цим методом, виконайте такі прості кроки:

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

Виберіть командну кнопку «Рекомендовані зведені таблиці» на вкладці «Вставка» стрічки або натисніть Alt+NSP.
Excel відображає діалогове вікно Рекомендовані зведені таблиці. Це діалогове вікно містить вікно зі списком ліворуч, яке показує зразки всіх запропонованих зведених таблиць, які Excel 2019 може створити з даних у вашому списку.

Створення нової зведеної таблиці зі зразків зведених таблиць, відображених у діалоговому вікні «Рекомендовані зведені таблиці».

Виберіть зразок зведеної таблиці, яку потрібно створити, у списку ліворуч, а потім натисніть кнопку OK.

Щойно ви натиснете кнопку «ОК», Excel створює нову зведену таблицю за вибраним зразком на власному аркуші (Аркуш1), вставленому перед іншими у вашій книзі. Цю зведену таблицю вибирають на новому аркуші, щоб панель завдань «Поля зведеної таблиці» відображалася в правій частині вікна аркуша Excel, а контекстна вкладка «Інструменти зведеної таблиці» відображалася на стрічці. Ви можете використовувати параметри на цій панелі завдань і контекстній вкладці, щоб потім налаштувати нову зведену таблицю Excel.

Створення зведених таблиць Excel вручну

Іноді жодна зі зведених таблиць, які Excel 2019 пропонує під час створення нової таблиці за допомогою інструмента швидкого аналізу або командної кнопки «Рекомендовані зведені таблиці», не відповідає типу зведених даних, які ви маєте на увазі. У таких випадках ви можете вибрати запропоновану зведену таблицю, макет якої найближче до того, що ви задумали, або ви можете створити зведену таблицю з нуля (процес, який не є таким складним або займає багато часу).

Щоб вручну створити нову зведену таблицю на аркуші з даними для аналізу, розмістіть покажчик комірки десь у клітинках цього списку, а потім натисніть кнопку команди «Зведена таблиця» на вкладці «Вставка» стрічки або натисніть Alt+NV.

Потім Excel відкриває діалогове вікно Створення зведеної таблиці та вибирає всі дані у списку, що містить курсор комірки (позначений рамкою навколо діапазону комірок). Потім ви можете налаштувати діапазон комірок у текстовому полі «Таблиця/діапазон» під кнопкою «Вибрати таблицю або діапазон», якщо область не включає всі дані для підсумовування в зведеній таблиці.

За замовчуванням Excel створює нову зведену таблицю на новому аркуші, який додає до книги. Однак якщо ви хочете, щоб зведена таблиця відображалася на тому самому аркуші, натисніть кнопку Існуючий робочий лист, а потім вкажіть розташування першої клітинки нової таблиці в текстовому полі «Розташування». (Просто переконайтеся, що ця нова зведена таблиця не перекриватиме існуючі таблиці даних.)

Вкажіть джерело даних і розташування зведеної таблиці в діалоговому вікні «Створення зведеної таблиці».

Якщо джерелом даних для вашої зведеної таблиці є зовнішня таблиця бази даних, створена за допомогою окремої програми керування базою даних, наприклад Access, вам потрібно натиснути кнопку Використовувати зовнішнє джерело даних, натиснути кнопку Вибрати підключення, а потім клацнути назву підключення у діалоговому вікні Існуючі з’єднання. Крім того, Excel 2019 підтримує аналіз даних із кількох пов’язаних таблиць на робочому аркуші (іменується моделлю даних). Якщо дані в новій зведеній таблиці Excel, яку ви створюєте, потрібно аналізувати разом з іншою існуючою зведеною таблицею, обов’язково встановіть прапорець Додати ці дані до моделі даних.

Якщо ви вкажете новий робочий аркуш як місце для нової зведеної таблиці в діалоговому вікні «Створення зведеної таблиці», коли ви натиснете кнопку «ОК», програма вставить новий аркуш у передній частині книги з порожньою сіткою для нової зведеної таблиці. Він також відкриває панель завдань «Поля зведеної таблиці» у правій частині області робочого аркуша та додає контекстну вкладку «Інструменти зведеної таблиці» на стрічку. Панель завдань «Поля зведеної таблиці» розділена на дві області: список «Виберіть поля для додавання до звіту» з іменами всіх полів у списку даних, які ви можете вибрати як джерело таблиці, перед якими стоять порожні прапорці, і поле «Перетягнути поля». між Областями Нижнього розділу, розділеного на чотири зони вилучення (ФІЛЬТРИ, Стовпці, РЯДКИ та ЗНАЧЕННЯ).

Завершена зведена таблиця Excel після додавання полів зі списку даних про співробітників до різних зон вилучення.

Ви також можете вставити новий аркуш із порожньою сіткою зведеної таблиці, вибравши кнопку Пуста зведена таблиця в діалоговому вікні «Рекомендована зведена таблиця» або палітрі параметрів інструмента «Швидкий аналіз» (відображається лише тоді, коли «Швидкий аналіз» не може запропонувати зведені таблиці для ваших даних). Майте на увазі, що коли ви вибираєте кнопку Пуста зведена таблиця в цьому діалоговому вікні або палітрі, Excel 2019 спочатку не відкриває діалогове вікно Створення зведеної таблиці. Якщо для створення нової зведеної таблиці вам потрібно використати будь-який з параметрів, запропонованих у цьому діалоговому вікні, вам потрібно створити зведену таблицю за допомогою командної кнопки «Зведена таблиця», а не командної кнопки «Рекомендовані зведені таблиці» на вкладці «Вставка».

Щоб завершити створення нової зведеної таблиці Excel, все, що вам потрібно зробити, це призначити поля в області завдань Поля зведеної таблиці різним частинам таблиці. Ви можете зробити це, перетягнувши ім’я поля зі списку «Вибрати поля для додавання до звіту» та перетягнувши його в одну з чотирьох областей нижче, які називаються зонами вилучення:

• ФІЛЬТРИ: ця область містить поля, які дають змогу переглядати підсумки даних, показані в фактичній зведеній таблиці, відфільтровуючи набори даних — вони діють як фільтри для звіту. Наприклад, якщо ви призначите поле Рік зі списку даних як фільтр звіту, ви можете відобразити підсумки даних у зведеній таблиці за окремі роки або для всіх років, представлених у списку даних.
• Стовпці: ця область містить поля, які визначають розташування даних, показаних у стовпцях зведеної таблиці.
• РЯДКИ: Ця область містить поля, які визначають розташування даних, показаних у рядках зведеної таблиці.
• ЗНАЧЕННЯ: ця область містить поля, які визначають, які дані будуть представлені в клітинках зведеної таблиці — це значення, підсумовані в її останньому стовпці (сумарно за замовчуванням).

Щоб зрозуміти, як ці різні зони пов’язані зі зведеною таблицею, перегляньте готову зведену таблицю Excel.

Для цієї зведеної таблиці ви призначаєте поле Стать зі списку даних (поле, яке містить F (для жінок) або M (для чоловіків), щоб вказати стать співробітника в зоні FILTERS. Ви також призначаєте поле Dept (що містить назви різних відділів компанії) у зону вилучення COLUMNS, поле Місцезнаходження (що містить назви різних міст із корпоративними офісами) у зону ROWS, а поле Зарплата у зону вилучення VALUES. У результаті ця зведена таблиця тепер відображає суму заробітної плати як чоловіків, так і жінок у кожному відділі (по стовпцях), а потім подає ці суми за місцем розташування компанії (у кожному рядку).

Щойно ви додаєте поля до нової зведеної таблиці (або виділяєте клітинку наявної таблиці на аркуші), Excel вибирає вкладку «Аналіз» контекстної вкладки «Інструменти зведеної таблиці», яка автоматично з’являється на стрічці. Серед багатьох груп на цій вкладці ви знайдете групу Показати в кінці, яка містить такі корисні командні кнопки:

• Список полів, щоб приховати та повторно відобразити панель завдань Список полів зведеної таблиці в правій частині області робочого аркуша
• +/– Кнопки для приховування та повторного відображення кнопок розгортання (+) і згортання (-) перед окремими полями стовпців або полями рядків, які дають змогу тимчасово видалити, а потім знову відобразити їхні конкретні підсумовані значення в зведеній таблиці
• Заголовки полів для приховування та повторного відображення полів, призначених міткам стовпців і мітках рядків у зведеній таблиці