Пов’язані теми
×
Зведені таблиці
Перейти до основного
Підтримка
Вхід
Вхід за допомогою облікового запису Microsoft
Увійдіть або створіть обліковий запис.
Вітаємо,
Виберіть інший обліковий запис.
У вас є кілька облікових записів
Виберіть обліковий запис, за допомогою якого потрібно ввійти.
Зведені таблиці

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

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

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

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

    Примітка.:  Дані слід упорядкувати в стовпці з одним рядком заголовка. Докладні відомості див. в розділі Поради та підказки щодо формату даних.

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

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

  3. Зведена таблиця створюється на основі наявної таблиці або діапазону.
    Зведена таблиця з діапазону або таблиці

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

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

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

Клацнувши стрілку вниз на кнопці, можна вибрати з інших можливих джерел для зведеної таблиці.  Окрім використання наявної таблиці або діапазону, є ще три джерела, з яких можна заповнити зведену таблицю.

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

Примітка.: Залежно від ІТ-параметрів організації ви можете побачити назву організації, включену до списку. Наприклад, "З Power BI (Microsoft)".

Отримати із зовнішнього джерела даних

Зведена таблиця із зовнішнього джерела

Отримати з моделі даних

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

Отримати від Power BI

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

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

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

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

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

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

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

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

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

За замовчуванням поля зведеної таблиці, розміщені в області Значення , відображаються як СУМА. Якщо Excel інтерпретує дані як текст, вони відображаються як КІЛЬКІСТЬ. Ось чому важливо не використовувати різні типи даних у полях значень. Щоб змінити стандартне обчислення, спочатку клацніть стрілку праворуч від імені поля, а потім виберіть параметр Параметри значення поля .

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

Змініть метод обчислення на вкладці Підсумувати значення за. Зверніть увагу, що коли ви змінюєте метод обчислення, Excel автоматично додає його до розділу Настроюване ім'я , наприклад "Сума імені поля", але його можна змінити. Якщо вибрати числовий формат, можна змінити числовий формат для всього поля.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Під час створення даних зведеної таблиці можна змінити джерелоданих.

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

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

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

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

Отримати від Power BI

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

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

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

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

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

Поля зведеної таблиці в вебпрограма Excel

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

За замовчуванням поля зведеної таблиці в області значень відображаються як СУМА. Якщо excel інтерпретує дані як текст, вони відображаються як COUNT. Ось чому важливо не використовувати різні типи даних у полях значень.

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

Підсумувати в вебпрограма Excel

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

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

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

Клацніть правою кнопкою миші будь-яке значення в стовпці, для якого потрібно відобразити значення. У меню виберіть Показати значення як . Відобразиться список доступних значень.

Виберіть потрібний варіант зі списку.

Щоб відобразити як % батьківського підсумку, наведіть вказівник миші на цей елемент у списку та виберіть батьківське поле, яке потрібно використовувати як основу обчислення. 

Відображати як

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

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

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

  • Дані мають бути впорядковані в табличному форматі та не містити пустих рядків або стовпців. Найкраще використовувати таблицю Excel.

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

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

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

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

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

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

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

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

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

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

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

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

    Data tab, Analysis group

  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 (Підсумувати значення за)

    За замовчуванням поля зведеної таблиці, розміщені в області Значення , відображаються як СУМА. Якщо Excel інтерпретує дані як текст, вони відображаються як КІЛЬКІСТЬ. Ось чому важливо не використовувати різні типи даних у полях значень. Щоб змінити обчислення за замовчуванням, спочатку клацніть стрілку праворуч від імені поля, а потім виберіть параметр Параметри поля .

    Змініть метод обчислення на вкладці Summarize by (Підсумувати значення за). Зверніть увагу, що коли ви змінюєте метод обчислення, Excel автоматично додає його до розділу Настроюване ім'я , наприклад "Сума імені поля", але його можна змінити. Якщо вибрати числовий... , можна змінити числовий формат для всього поля.

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

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

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

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

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

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

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

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

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

Зведена таблиця на пристрої iPad доступна для користувачів, які працюють у програмі Excel на пристрої iPad версії 2.82.205.0 і новіших. Щоб отримати доступ до цієї функції, переконайтеся, що програму оновлено до найновішої версії через App Store.

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

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

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

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

  4. Натисніть кнопку Insert (Вставити). 

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

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

Зведена таблиця в списку полів iPad

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

Знімок екрана: оновлення зведеної таблиці на Excel для iPad

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

Поради та підказки щодо форматування даних

  • Використовуйте чисті табличні дані, щоб отримати найкращі результати.

  • Упорядковуйте дані в стовпцях, а не в рядках.

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

    Стовпець Excel 2

  • Відформатуйте дані як таблицю Excel (виберіть будь-де в даних, а потім натисніть кнопку Вставити > таблиця на стрічці).

  • Якщо у вас складні або вкладені дані, скористайтеся Power Query, щоб перетворити їх (наприклад, щоб відкріпити дані), щоб об'єднати їх у стовпці з одним рядком заголовка.

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

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

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

Схожі статті

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

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

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

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

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

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

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

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

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

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

Потрібні додаткові параметри?

Ознайомтеся з перевагами передплати, перегляньте навчальні курси, дізнайтесь, як захистити свій пристрій тощо.

Спільноти допомагають ставити запитання й відповідати на них, надавати відгуки та дізнаватися думки висококваліфікованих експертів.

Чи ця інформація була корисною?

Наскільки ви задоволені якістю мови?
Що вплинуло на ваші враження?
Натиснувши кнопку "Надіслати", ви надасте свій відгук для покращення продуктів і служб Microsoft. Ваш ІТ-адміністратор зможе збирати ці дані. Декларація про конфіденційність.

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

×