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

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

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

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

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

Створення завдання

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  2. Виберіть Insert (Вставлення) > Recommended PivotTable (Рекомендована зведена таблиця).

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

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

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

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

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

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

    Вкладка "Дані", група "Аналізувати"

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

  4. У розділі Choose where you want the PivotTable report to be placed (Виберіть розташування звіту зведеної таблиці) виберіть New Worksheet (Новий аркуш) або Existing Worksheet (Наявний аркуш). Якщо ви вибрли Existing Worksheet (Наявний аркуш), клацніть клітинку, де потрібно вставити зведену таблицю.

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

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

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

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

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



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

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

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

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

    Порада.: Якщо змінити метод обчислення на вкладці Summarize by (Підсумувати значення за), зміниться також ім’я поля зведеної таблиці. Тому не радимо перейменовувати поля зведеної таблиці, доки не завершите її налаштування. Щоб одночасно замінити всі значення, а не вводити їх щоразу вручну, виберіть Replace (Замінити) (у меню редагування) >Find what (Знайти) > "Sum of" (Сума), а поле Replace with (Замінити на) залиште пустим.

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

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

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

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

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

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

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

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

Тепер ви можете вставити зведену таблицю на аркуш в Excel Online.

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

  1. Виберіть таблицю або діапазон клітинок електронної таблиці.

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

  3. В Excel відкриється діалогове вікно Create PivotTable (Створення зведеної таблиці) з вибраним діапазоном або іменем таблиці.

    Створити зведену таблицю
  4. У розділі Choose where you want the PivotTable report to be placed (Виберіть розташування звіту зведеної таблиці) виберіть New Worksheet (Новий аркуш) або Existing Worksheet (Наявний аркуш). Якщо ви вибрли Existing Worksheet (Наявний аркуш), клацніть клітинку, де потрібно вставити зведену таблицю.

    Примітка.: Клітинку, на яку ви посилатиметеся, має бути розташовано за межами таблиці або діапазону.

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

Угорі сторінки в області PivotTable Fields (Поля зведеної таблиці) позначте поля, які потрібно додати до зведеної таблиці. За замовчуванням нечислові поля додаються до області Rows (Рядки), поля дати й часу – до області Columns (Стовпці), а числові поля – до області Values (Значення). Крім того, доступні елементи можна вручну перетягнути до будь-якого поля зведеної таблиці. Якщо котрийсь із них більше не потрібний, просто перетягніть його за межі списку полів або зніміть із нього позначку. Перевпорядкування елементів поля – це одна з функцій зведеної таблиці, що дає змогу швидко змінити її оформлення.

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

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

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



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

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

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

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

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

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

    Порада.: Якщо змінити метод обчислення на вкладці Підсумувати значення за, зміниться також ім’я поля зведеної таблиці. Тому не радимо перейменовувати поля зведеної таблиці, доки не завершите її налаштування. Щоб одночасно замінити всі значення, а не вводити їх щоразу вручну, виберіть Пошук і заміна (натисніть клавіші Ctrl+H), перейдіть на вкладку Знайти, введіть Сума, а поле Замінити на залишіть пустим.

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

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

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

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

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

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

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

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

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

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

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

Див. також

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

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

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

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

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

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

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

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

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

Ця інформація корисна?

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

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×