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

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

У таких випадках ви можете вибрати запропоновану зведену таблицю, макет якої найближче до того, що ви задумали, або ви можете створити зведену таблицю з нуля (процес, який не є таким складним або займає багато часу).

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

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

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

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

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

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

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

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

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

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

Якщо для створення нової зведеної таблиці вам потрібно використати будь-який з параметрів, запропонованих у цьому діалоговому вікні, вам потрібно створити зведену таблицю за допомогою командної кнопки «Зведена таблиця», а не командної кнопки «Рекомендовані зведені таблиці» на вкладці «Вставка».

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

• ФІЛЬТРИ: ця область містить поля, які дають змогу переглядати підсумки даних, показані в фактичній зведеній таблиці, відфільтровуючи набори даних — вони діють як фільтри для звіту. Наприклад, якщо ви призначите поле Рік зі списку даних як фільтр звіту, ви можете відобразити підсумки даних у зведеній таблиці за окремі роки або для всіх років, представлених у списку даних.

• Стовпці: ця область містить поля, які визначають розташування даних, показаних у стовпцях зведеної таблиці.

• РЯДКИ: Ця область містить поля, які визначають розташування даних, показаних у рядках зведеної таблиці.

• ЗНАЧЕННЯ: ця область містить поля, які визначають, які дані будуть представлені в клітинках зведеної таблиці — це значення, підсумовані в її останньому стовпці (сумарно за замовчуванням).

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

Для цієї зведеної таблиці поле Стать зі списку даних (поле, яке містить F [для жінки] або M [для чоловіка]) призначається для вказівки статі працівника в зоні відкидання ФІЛЬТРИ. Поле Dept (яке містить назви різних відділів компанії) призначається зоні висадки COLUMNS, поле Місцезнаходження (що містить назви різних міст з корпоративними офісами) – зоні ROWS, а поле Зарплата у зону переміщення VALUES.

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

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

• Список полів, щоб приховати та повторно відобразити панель завдань Список полів зведеної таблиці в правій частині області робочого аркуша

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

• Заголовки полів для приховування та повторного відображення полів, призначених міткам стовпців і мітках рядків у зведеній таблиці