Перейти до основного
Office
Зведені таблиці

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

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

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

Ваш браузер не підтримує відео. Інсталюйте Microsoft Silverlight, Adobe Flash Player або Internet Explorer 9.

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

  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 інтерпретує дані як текст, вони відображатимуться як кількість. Ось чому важливо не використовувати різні типи даних у полях значень. Щоб змінити стандартний метод обчислення, клацніть стрілку праворуч від імені поля, а потім виберіть 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. Це не вплине на інші дані, зведені таблиці або діаграми. Якщо зведену таблицю розташовано на окремому аркуші, який не містить інших потрібних даних, можна швидко видалити зведену таблицю, видаливши цей аркуш.

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

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

  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 створить пусту зведену таблицю й відобразить список Поля зведеної таблиці.

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

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

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

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



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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Додаткові відомості

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

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

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

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

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

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

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

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

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

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

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

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

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

×