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

By Mirko Peters · 2026-11-12

Як створити зведену таблицю в Excel: підготуйте дані, скористайтеся командою Вставлення > Зведена таблиця, перетягніть поля, додайте зрізи, а потім оновіть таблицю й виправте проблеми. Кроки для початківців.

Automatically translated with AI from the original post. Read the English original

Щоб створити зведену таблицю в Excel, клацніть будь-яку комірку в своїх даних, перейдіть до Вставлення > Зведена таблиця, виберіть, де має розміщуватися таблиця (Новий аркуш або Наявний аркуш), і натисніть OK. Потім позначте або перетягніть поля в області Поля зведеної таблиці, щоб побудувати підсумок. Ця стаття призначена для користувачів Excel, які мають довгий список рядків, наприклад продажі, заявки або години, і хочуть отримати підсумки за клієнтами, регіонами чи місяцями без жодної формули. Ви підготуєте дані, побудуєте таблицю, змініть спосіб розрахунку чисел, фільтруватимете зрізами, підтримуватимете таблицю в актуальному стані та виправите типові помилки.

Головне

• Щоб створити зведену таблицю, виберіть дані, перейдіть до Вставлення, далі Зведена таблиця, виберіть новий або наявний аркуш і натисніть OK.
• Насамперед потрібні чисті дані: один рядок заголовків, унікальний непорожній заголовок у кожному стовпці, без об'єднаних комірок і без проміжних підсумків у джерелі.
• Перетягніть поля в Рядки, Стовпці та Значення. Числові поля за замовчуванням використовують Суму, але стовпець, який Excel сприймає як текст, отримує Кількість.
• Зведена таблиця не завжди оновлюється сама. Після зміни вихідних даних клацніть її правою кнопкою миші та виберіть Оновити або натисніть Alt+F5.
• Оформіть джерело як таблицю Excel, щоб нові рядки враховувалися під час оновлення зведеної таблиці.

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

Підготуйте вихідні дані

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

• Один рядок заголовків, і кожен стовпець має унікальний непорожній заголовок.
• Без подвійних рядків заголовків і без об'єднаних комірок.
• Без проміжних підсумків у джерелі. В огляді зведених таблиць Microsoft радить видалити наявні проміжні підсумки before створення зведеної таблиці.
• Без порожніх рядків або стовпців усередині списку.
• Один тип даних у кожному стовпці. Не змішуйте числа й текст.

Останній пункт має реальний вплив на результати. Microsoft пояснює, що числові поля значень за замовчуванням використовують Суму, але якщо Excel сприймає поле як текст, за замовчуванням застосовується Кількість. Досить однієї комірки зі значенням «N/A» або «n/a» у стовпці сум, щоб це сталося. Якщо зведена таблиця показує кількість там, де ви очікували підсумки, спочатку перевірте вихідний стовпець.

Далі перетворіть список на таблицю Excel: клацніть комірку в даних і натисніть Ctrl+T. Microsoft рекомендує використовувати таблицю як джерело, а на сторінці огляду зазначено, що таблиці підхоплюють нові дані під час оновлення зведеної таблиці. Звичайний діапазон зупиняється на останньому рядку, з яким був створений. Динамічний іменований діапазон також може розширюватися, згідно з тією самою сторінкою, але таблицю простіше налаштувати й легше зрозуміти наступному користувачеві.

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

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

Ці кроки стосуються Excel для Windows, як описано на сторінці Microsoft.

1. Виділіть клітинки, які хочете використати, або клацніть одну клітинку всередині своєї таблиці чи списку. Excel сам визначить навколишній блок даних.
2. Перейдіть на вкладку Вставлення і виберіть Зведена таблиця.
3. У діалоговому вікні Створення зведеної таблиці перевірте таблицю або діапазон, зазначений у полі «Таблиця або діапазон». Якщо там помилка, виправте її.
4. Виберіть, де розмістити звіт: На новому аркуші або На існуючому аркуші. Новий аркуш відокремлює джерело від підсумків, що зручніше для читання й менш схильне до випадкових змін.
5. Натисніть OK. Excel відкриє панель Поля зведеної таблиці праворуч. Доки ви не додасте поля, область зведеної таблиці на аркуші буде порожньою. Це нормально.

У діалоговому вікні є необов'язковий прапорець Додати ці дані до моделі даних. Для першої зведеної таблиці залиште його вимкненим. Він призначений для випадків, коли ви поєднуєте кілька таблиць або працюєте з дуже великими даними. Стрілка на кнопці «Зведена таблиця» також пропонує Отримати із зовнішнього джерела даних, Отримати з моделі даних і Отримати з Power BI. Якщо дійдете до потреби в зв'язках між таблицями, перегляньте сторінку Power Pivot.

Дозвольте Excel запропонувати макет

Якщо ви не знаєте, що побудувати, команда Вставлення > Рекомендовані зведені таблиці покаже кілька готових макетів для ваших даних. На сторінці Microsoft про зведені таблиці, яку я відкривав, ця функція не описана покроково, але там зазначено, що рекомендації щодо зведених таблиць належать до пов'язаних функцій Microsoft 365 і використовують служби ШІ для аналізу даних. Якщо в організації вимкнено пов'язані функції, рекомендацій не буде. Ручний спосіб, описаний вище, працює завжди.

Побудуйте макет на панелі «Поля зведеної таблиці»

На панелі кожен заголовок стовпця з вашого джерела відображається як поле. Можна встановити прапорець поля або перетягнути його в одну з областей. Ось типові значення Microsoft під час установлення прапорця:

• Нечислові поля потрапляють до Рядків.
• Поля дати й часу потрапляють до Стовпців як ієрархія.
• Числові поля потрапляють до Значень.

Будь-яке поле можна згодом перетягнути в іншу область. Простий приклад: для списку продажів встановіть прапорець Регіон (потрапить до Рядків) і Сума (потрапить до Значень), і ви отримаєте підсумок за регіонами. Перетягніть Товар до Стовпців, і ті самі підсумки розіб'ються за товарами.

Ось що роблять області:

• Рядки перелічують елементи зліва, по одному рядку для кожного унікального значення.
• Стовпці перелічують елементи вгорі.
• Значення містять числа, які підсумовуються, підраховуються або усереднюються.
• Фільтри розміщують поле над таблицею, щоб ви могли показувати по одному значенню за раз. На окремій сторінці Microsoft про фільтрування зведених таблиць це описано як фільтр звіту.

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

Змініть обчислення

Щоб змінити спосіб обчислення значення, клацніть стрілку біля поля в області «Значення» і виберіть Параметри полів значень. На вкладці Підсумувати значення за можна перемикатися між Сума, Кількість, Среднє, Максимум, Мінімум та іншими варіантами, які там перелічені. Excel автоматично оновлює власну назву, і її можна редагувати. Назва на кшталт «Сума за полем Сума» годиться для вас самих, але якщо ви ділитеся звітом, перейменуйте його на зрозумілу. Microsoft радить перейменовувати лише після завершення налаштування, адже зміна обчислення знову змінює назву.

Кнопка Number Format у тому самому діалоговому вікні задає формат для всього поля, наприклад валюту або фіксовану кількість десяткових знаків. Зробіть це один раз тут, а не форматуйте комірки вручну, адже ручне форматування може зникнути після зміни макета. Якщо ви хочете зберегти своє форматування, відкрийте Options на вкладці PivotTable Analyze і в розділі Layout & Format увімкніть Preserve cell formatting on update. На тій самій вкладці є параметр Autofit column widths on update, який варто вимкнути, якщо ширина стовпців постійно змінюється.

Відображення значень у відсотках

Вкладка Show Values As у параметрах поля значень змінює те, що означає число. Вона може показувати кожне значення як частку від загальної суми, наприклад % of Grand Total. Щоб вивести суму й відсоток поруч, перетягніть те саме поле в Values двічі і задайте для кожної копії власний параметр. Це зручний прийом для пари «Revenue» і «Share of revenue».

Фільтрування за допомогою сегментів і фільтрів звіту

Коли таблиця працює, наступне питання зазвичай таке: як переглянути окрему її частину. Є три інструменти, і всі вони описані на сторінках Microsoft.

Slicers — це кнопки, які залишаються видимими поруч із таблицею. Клацніть будь-де в зведеній таблиці, перейдіть до Insert > Slicer, позначте потрібні поля в діалоговому вікні Insert Slicers і натисніть OK. Для кожного поля отримаєте окремий сегмент. Клацніть кнопку, щоб відфільтрувати, утримуйте Ctrl і клацайте, щоб вибрати кілька елементів, а Clear Filter скидає фільтр. На сторінці Microsoft про використання сегментів для фільтрування даних є корисна деталь: один сегмент може керувати кількома зведеними таблицями, але лише тоді, коли вони мають спільне джерело даних. Виберіть сегмент, перейдіть на вкладку Slicer, натисніть Report Connections і позначте потрібні зведені таблиці.

Випадні списки фільтрів. Стрілка в мітці рядка або стовпця відкриває меню з пунктами Label Filters і Value Filters. У розділі Value Filters знайдете Top 10, який показує найвищі або найнижчі елементи за числом, відсотком або сумою. У списку елементів зніміть позначку з Select All і позначте лише потрібні, або введіть текст у поле Search.

Фільтрування через контекстне меню. Виділіть елементи в таблиці, клацніть правою кнопкою миші, виберіть Filter, а потім Keep Only Selected Items або Hide Selected Items. Clear Filter видаляє фільтр.

Фільтри звіту (область Filters) мають корисну додаткову можливість: Show Report Filter Pages створює окремий аркуш для кожного значення поля фільтра. Так можна швидко отримати окремий аркуш для кожного регіону.

Оновлення зведеної таблиці після зміни даних

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

1. Оновлення однієї таблиці. Виділіть зведену таблицю, перейдіть на вкладку PivotTable Analyze і натисніть Refresh у групі Data або натисніть Alt+F5. Також спрацює клацання правою кнопкою миші по таблиці та вибір Refresh.
2. Оновлення всього. Відкрийте стрілку під Refresh і виберіть Refresh All. Це оновить усі зведені таблиці в книзі.
3. Оновлення під час відкриття. Виділіть таблицю, відкрийте Options на вкладці PivotTable Analyze, перейдіть на вкладку Data і позначте Refresh data when opening the file.

На сторінці Microsoft також описано параметр Auto Refresh, за якого зведені таблиці, побудовані на даних книги, оновлюються після зміни джерела. Там зазначено, що наразі ця функція доступна лише учасникам програми Microsoft 365 Insider, а старіші версії Excel не оновлюються автоматично, хоча можуть оновлюватися під час відкриття. Тож не покладайтеся на неї, доки не переконаєтеся, що ваша збірка її підтримує. До того часу безпечним варіантом є ручне оновлення або оновлення під час відкриття.

А що, як нові рядки все одно не відображаються?

Ймовірно, вихідний діапазон занадто малий. Якщо ваші дані оформлено як таблицю Excel, нові рядки автоматично враховуються під час оновлення. Якщо це звичайний діапазон, зведена таблиця все ще посилається на старі клітинки. Виправте це так, як Microsoft описує у статті про зміну джерела даних для зведеної таблиці: виберіть зведену таблицю, перейдіть на вкладку Аналіз, двічі виберіть Змінити джерело даних (спočатку кнопку, потім пункт меню), виберіть Вибрати таблицю або діапазон, введіть діапазон у полі «Таблиця/діапазон» і натисніть OK. Microsoft додає, що якщо дані часто змінюються, наприклад додаються або видаляються стовпці, нова зведена таблиця зазвичай є зручнішим вибором.

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

Видалення або очищення зведеної таблиці

У статті Microsoft про видалення зведеної таблиці описано два способи. По-перше, виберіть всю зведену таблицю і натисніть Delete. Якщо ви бачите повідомлення «Не можна змінити цю частину звіту зведеної таблиці», натисніть Ctrl+A, щоб вибрати все, і знову натисніть Delete. По-друге, клацніть будь-яку клітинку зведеної таблиці, перейдіть до Аналіз зведеної таблиці > Вибрати > Вся зведена таблиця і видаліть її звідти. Якщо зведена таблиця розташована окремо на аркуші, можна видалити весь аркуш.

Будьте обережні з останнім способом, якщо на тому самому аркуші є ваші вихідні дані. На сторінці Microsoft не сказано, що станеться з джерелом, тож перевірте, що дані розташовані на іншому аркуші, перш ніж щось видаляти. Зрізи — це окремі об’єкти. Щоб видалити зріз, виберіть його і натисніть Delete.

Коли зведена таблиця дає неправильні результати

• Кількості замість сум. У стовпці значень є текстова клітинка. Очистіть дані або встановіть для параметра «Підсумувати значення за» значення «Сума», а потім переконайтеся, що жодна клітинка не відображає числа як текст.
• Поля немає у списку. Стовпець може не мати заголовка або діапазон може його не охоплювати. Перевірте перший рядок вихідних даних і поле «Таблиця/діапазон» у вікні «Змінити джерело даних».
• Застарілі числа. Ви не оновили таблицю. Скористайтеся сполученням Alt+F5.
• Дати поводяться як текст. Якщо стовпець із датами збережено як текст, Excel не може розпізнати його як дати. Перетворіть його на справжні дати у джерелі та оновіть таблицю.
• Макет виглядає перемішаним. Перевірте джерело на наявність об’єднаних клітинок або другого рядка заголовка. Вимоги Microsoft до даних виключають і те, і інше.

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

Типові помилки

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

Пов’язані теми для вивчення

• Групування дат за місяцями або кварталами. Типовий наступний крок для даних про продажі та часових даних.
• Зрізи для кількох таблиць. Під’єднайте один зріз до групи зведених таблиць для простої панелі керування.
• Зведені діаграми. Діаграма, пов’язана зі зведеною таблицею, яка змінюється разом із нею.
• Power Query перед зведеною таблицею. Спочатку перетворіть або об’єднайте дані за допомогою Power Query, якщо ваш експорт має один стовпець на місяць.
• Модель даних і показники. Об’єднуйте кілька таблиць за допомогою Power Pivot, коли одного списку вже недостатньо.

Наступний крок

Візьміть один зі своїх списків, натисніть Ctrl+T, щоб перетворити його на таблицю, і створіть із неї зведену таблицю лише з одним полем у рядках і одним у значеннях. Коли це працює, додайте зріз і оновіть таблицю один раз після зміни числа в джерелі. Це весь цикл створення зведеної таблиці в Excel: підготовка, побудова, фільтрування та оновлення.