Зміст
×

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

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

Ваш браузер не підтримує відео.

Створення зведеної таблиці в Excel для Windows

  1. Виберіть клітинки, на основі яких потрібно створити зведену таблицю.

    Примітка.:  Діапазон даних не має містити пустих рядків або стовпців. Заголовки мають складатися лише з одного рядка.

  2. Виберіть Вставлення > Зведена таблиця.

    Зведена таблиця

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

    Зведена таблиця

  4. У полі Таблиця/Діапазон перевірте діапазон клітинок.

  5. У розділі Виберітьрозташування звіту зведеної таблиці виберіть новий аркуш, щоб розташувати зведену таблицю на новому аркуші, або Наявний аркуш, а потім виберіть розташування, у якому має відображатися зведена таблиця.

  6. Натисніть кнопку OK.

Розширення зведеної таблиці

  1. Щоб додати поле до зведеної таблиці, позначте ім’я поля в області Поля зведеної таблиці.

    Примітка.: Вибрані поля додаються до стандартних областей: нечислові поля до області Рядки, значення дати й часу – до області Стовпці та числові значення – до області Значення.

    Зведена таблиця

  2. Перемістити поле з однієї області до іншої можна його перетягуванням.

Перед початком роботи

  • Дані має бути впорядковано в табличному форматі без пустих рядків або стовпців. В ідеалі можна використовувати таблицю Excel, як у нашому прикладі вище.

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

  • Необхідно, щоб усі дані в стовпцях мали однаковий тип. Наприклад, в одному стовпці не слід використовувати дати й текст.

  • У зведених таблицях використовується знімок даних, який називається кешем, тому фактичні дані не змінюються.

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

Крім того, ви можете завантажити наш інтерактивний посібник Створення першої зведеної таблиці.

1. Клацніть клітинку у вихідних даних або діапазоні таблиці.

2. Перейдіть до вкладки Вставлення > Рекомендована зведена таблиця.

Щоб Excel автоматично створила зведену таблицю, виберіть "Вставлення > Рекомендовані зведені таблиці".

3. Excel проаналізувати дані та пропонують кілька варіантів, як у цьому прикладі з даними про витрати родини.

Діалогове вікно "Рекомендовані зведені таблиці" в Excel

4. Виберіть оптимальну зведену таблицю та натисніть кнопку OK. Excel створить зведену таблицю на новому аркуші й відобразить список Поля зведеної таблиці.

1. Клацніть клітинку у вихідних даних або діапазоні таблиці.

2. Перейдіть до вкладки Вставлення > зведеної таблиці.

В Excel 2011 для Mac і попередніх версій кнопку "Зведена таблиця" розташовано на вкладці Дані в групі Аналіз.

Data tab, Analysis group

3. Excel відкриється діалогове вікно Створення зведеної таблиці з вибраним діапазоном або іменем таблиці. У цьому прикладі ми використовуватимемо таблицю "табл_Витрати_родини".

4. У розділі Виберіть розташування звіту зведеної таблиці виберіть Новий аркуш абоНаявний аркуш. Якщо ви вибрли Existing Worksheet (Наявний аркуш), клацніть клітинку, де потрібно вставити зведену таблицю.

5. Натисніть кнопку OK, Excel створити пусту зведену таблицю та відобразити список Поля зведеної таблиці.

Список полів зведеної таблиці

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

Список "Поля зведеної таблиці"

Діалогове вікно "Поля зведеної таблиці" в Excel

  • Summarize by (Підсумувати значення за)

    За замовчуванням поля зведеної таблиці в області Values (Значення) відображатимуться як SUM (сума). Якщо Excel інтерпретує дані як текст, вони відображатимуться як кількість. Ось чому важливо не використовувати різні типи даних у полях значень. Щоб змінити стандартний метод обчислення, клацніть стрілку праворуч від імені поля, а потім виберіть Field Settings (Параметри поля).

    Змініть метод обчислення на вкладці Summarize by (Підсумувати значення за). До речі, якщо змінити метод обчислення, Excel автоматично додасть його до поля Custom Name (Користувацьке ім’я) (наприклад, "Sum of FieldName" (Сума з Ім’я поля)), але це значення можна змінити. Натиснувши кнопку Number... (Число...), можна змінити числовий формат для всього поля.

    Порада.: Якщо змінити обчислення в розділі Підсумувати значення за, ім'я поля зведеної таблиці зміниться, тому не варто перейменовувати поля зведеної таблиці, доки не завершите настроювати зведену таблицю. Один раз хитрість полягає в тому, що натиснути кнопку Замінити ( у меню Редагування)>Знайти >"Сума ", а потім замінити на > залиште пустими, щоб одночасно замінити всі дані замість того, щоб вводити їх вручну.

  • Show data as (Відображати дані як)

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

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

    Потрібні параметри можна вибрати в діалоговому вікні Field Settings (Параметри поля) на вкладці Show data as (Відображати дані як).

  • Відображення значення як обчислень і відсотків

    Просто двічі перетягніть елемент до розділу Values (Значення), клацніть його правою кнопкою миші й виберіть Field Settings (Параметри поля), а потім установіть для кожного з них параметри Summarize by (Підсумувати значення за) і Show data as (Відображати значення як).

Якщо до джерела даних зведеної таблиці додано нові дані, усі створені на його основі зведені таблиці необхідно оновити. Щоб оновити тільки одну зведену таблицю, клацніть правою кнопкою миші будь-де в діапазоні зведеної таблиці й виберіть Refresh (Оновити). Якщо у вас кілька зведених таблиць, спочатку виберіть в одній із них будь-яку клітинку, а потім на стрічці натисніть кнопку PivotTable Analyze (Аналіз зведеної таблиці) > клацніть стрілку під кнопкою Refresh (Оновити) і виберіть Refresh All (Оновити все).

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

Ваш браузер не підтримує відео.

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

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

  2. Ви можете вручну створити власну зведену таблицю або вибрати рекомендовану зведену таблицю, створену автоматично. Виконайте одну з таких дій:

Примітка.: Рекомендовані зведені таблиці доступні лише Microsoft 365 абонентам.

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

  • У рекомендованій зведеній таблиці виберіть Новий аркуш або Наявний аркуш, щоб вибрати місце призначення зведеної таблиці.

В області Вставлення зведеної таблиці можна вказати вихідний текст, місце призначення та інші параметри зведеної таблиці.

За потреби можна змінити джерело даних зведеної таблиці, перш ніж створювати його.

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

  2. Виберіть дані в сітці або введіть діапазон у текстове поле. 

  3. Натисніть клавішу Enter на клавіатурі або кнопку, щоб підтвердити вибір. Ми оновимо цю область новими рекомендованими зведеними таблицями на основі нового джерела даних.

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

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

За замовчуванням нечислові поля додаються до області Рядки, поля дати й часу – до області Стовпці, а числові поля – до області Значення.

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

Відповідні поля у зведеній таблиці:

Зведена таблиця з частинами, позначеними (стовпці, рядки, значення).

Щоб вибрати поля у зведеній таблиці, скористайтеся областю "Поля зведеної таблиці".

Підсумувати значення за

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

Діалогове вікно "Параметри значення поля" в Excel

Змініть метод обчислення на вкладці Підсумувати значення за. До речі, якщо змінити метод обчислення, Excel автоматично додасть його до поля Користувацьке ім’я (наприклад, "Сума з Ім’я поля"), але це значення можна змінити. Натиснувши кнопку Формат числа, можна змінити числовий формат для всього поля.

Порада.: Якщо змінити обчислення в розділі Підсумувати значення за, зміниться ім'я поля зведеної таблиці, тому не варто перейменовувати поля зведеної таблиці, доки не завершите настроювати зведену таблицю. Один раз хитрість полягає у використанні функції "Знайти & заміна"(Ctrl+H)>> "Сума", а потім "Замінити на > залиште пустими, щоб заміняти всі дані одночасно, а не вводити їх вручну.

Діалогове вікно "Параметри значення поля" в Excel із параметрами на вкладці "Підсумувати значення за"

Відображати значення як

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

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

Потрібні параметри можна вибрати в діалоговому вікні Параметри значення поля на вкладці Відображати значення як.

Відображення значення як обчислення та відсотка

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

Діалогове вікно "Параметри значення поля зведеної таблиці", вкладка "Відображати значення як"

Якщо ви додали нові дані до джерела даних зведеної таблиці, знадобиться оновити всі зведені таблиці, створені на основі цього джерела. Щоб оновити зведену таблицю, клацніть будь-де в діапазоні даних зведеної таблиці й виберіть Оновити.

Оновити зведену таблицю

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

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільноті Answers.

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

Схожі статті

Створення зведеної діаграми

Використання роздільників для фільтрування даних зведеної таблиці 

Створення часової шкали зведеної таблиці для фільтрування дат

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

Створення зведеної таблиці, підключеної до набірок даних Power BI

Використання списку полів для впорядкування полів у зведеній таблиці

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

Обчислення значень у зведеній таблиці

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

Потрібна додаткова довідка?

Отримуйте нові функції раніше за інших
Приєднатися до Microsoft оцінювачів

Чи були ці відомості корисні?

Наскільки ви задоволені якістю перекладу?
Що вплинуло на ваші враження?

Дякуємо за ваш відгук!

×