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

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

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

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

   

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

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

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

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

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

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

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

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

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

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

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

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

   

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  

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

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

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

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

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

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

   

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

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

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

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

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