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

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

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

Windows macOS Інтернет-версія



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

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

Примітка.: Діапазон даних не має містити пустих рядків або стовпців. Заголовки мають складатися лише з одного рядка.
Виберіть Вставлення > Зведена таблиця.
У розділі Виберіть дані для аналізу встановіть перемикач Вибрати таблицю або діапазон.
У полі Таблиця/Діапазон перевірте діапазон клітинок.
У розділі виберіть місце, де потрібно розташувати звіт зведеної таблиці, виберіть елемент створити аркуш , щоб розташувати зведену таблицю на новому аркуші або наявному аркуші , а потім виберіть розташування, у якому має відображатися зведена таблиця.
Натисніть кнопку OK.

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

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

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

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

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

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

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

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

Рекомендована зведена таблиця

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

Клацніть клітинку в діапазоні вихідних даних або таблиці.
Виберіть Insert (Вставлення) > Recommended PivotTable (Рекомендована зведена таблиця).
Excel проаналізує дані та продемонструє кілька варіантів, як у цьому прикладі з даними про витрати родини.
Виберіть оптимальну зведену таблицю та натисніть кнопку OK. Excel створить зведену таблицю на новому аркуші й відобразить список Поля зведеної таблиці.

Клацніть клітинку в діапазоні вихідних даних або таблиці.
Виберіть Insert (Вставлення) > PivotTable (Зведена таблиця).

В Excel 2011 для Mac і попередніх версій кнопку "Зведена таблиця" розташовано на вкладці Дані в групі Аналіз.
В Excel відкриється діалогове вікно Створення зведеної таблиці з вибраним діапазоном або іменем таблиці. У цьому прикладі ми використовуватимемо таблицю "табл_Витрати_родини".
У розділі Choose where you want the PivotTable report to be placed (Виберіть розташування звіту зведеної таблиці) виберіть New Worksheet (Новий аркуш) або Existing Worksheet (Наявний аркуш). Якщо ви вибрли Existing Worksheet (Наявний аркуш), клацніть клітинку, де потрібно вставити зведену таблицю.
Натисніть кнопку OK. Після цього 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. Це не вплине на інші дані, зведені таблиці або діаграми. Якщо зведену таблицю розташовано на окремому аркуші, який не містить інших потрібних даних, можна швидко видалити зведену таблицю, видаливши цей аркуш.

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

Виберіть таблицю або діапазон в електронній таблиці, а потім виберіть команду вставити > зведену таблицю.

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

Щоб створити зведену таблицю вручну, виберіть команду створити власну зведену таблицю.
Щоб використати рекомендації зведеної таблиці, прокрутіть до потрібного параметра, а потім натисніть кнопку + INSERT (вставити).

Зведена таблиця відображається в полі призначення, і відкриється область " поля зведеної таблиці ".

Змінення вихідних даних або кінцевої точки призначення

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

В області вставлення зведеної таблиці виберіть посилання поруч із елементом джерело або посилання поруч із пунктом призначення. Зміни в області "Вставлення зведеної таблиці":
Виконайте одну з таких дій:

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

Примітка.: Також можна вибрати пусту аркуш, у якому має відображатися зведена таблиця (місце призначення). У цьому випадку або, якщо вибрати неприпустиме джерело, під час вибору елемента вставлення > зведеної таблиці з'явиться область "Вставлення зведеної таблиці" та попросить вказати джерело

Робота з областю "поля зведеної таблиці"

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

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

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

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

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

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

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

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

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

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

Оновлення зведених таблиць

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

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

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