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

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

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

   Примітка.

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

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

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

   Примітка.

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

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

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

Зведені таблиці з інших джерел

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

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

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

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

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

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

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

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

   Примітка.

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

   

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

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

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

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

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

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

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

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

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

, .

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

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

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

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

1. Відкрийте електронну таблицю в вебпрограма Excel.
2. Виберіть таблицю або діапазон даних на аркуші, а потім натисніть кнопку "Вставити > зведену таблицю ", щоб відкрити область "Вставлення зведеної таблиці ".
3. Ви можете створити власну зведену таблицю вручну або вибрати рекомендовану зведену таблицю . Виконайте одну з таких дій:

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

Змінення вихідних даних

Коли ви створюєте зведену таблицю, її джерело можна змінювати.

1. В області "Вставлення зведеної таблиці " виберіть текстове поле в розділі "Джерело". Якщо змінити джерело, картки в цій області будуть недоступні.
2. Виділіть дані в сітці або введіть діапазон у текстовому полі.
3. Натисніть клавішу Enter на клавіатурі або кнопці, щоб підтвердити вибір. Область оновиться новими рекомендованими зведеними таблицями на основі нового джерела даних.

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

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

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

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

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

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

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

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

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

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

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

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

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

Зробіть свій вибір зі списку.

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

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

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

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

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

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

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

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

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

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

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

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

   

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

   

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

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

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

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

   

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 автоматично додасть його до розділу " Користувацьке ім'я ", наприклад "Сума з Ім'я поля", але це значення можна змінити. Якщо вибрано варіант "Число... ", можна змінити числовий формат для всього поля.

  Порада.

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

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

  

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

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

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

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

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

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

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

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

1. Виберіть клітинку в діапазоні вихідних даних або таблиці.
2. Перейдіть до розділу "Вставлення > зведеної таблиці".
   
3. Виберіть розташування, у якому потрібно розмістити зведену таблицю. Виберіть команду "Вставити на новий аркуш ", щоб розмістити зведену таблицю на новому аркуші, або виберіть клітинку, де потрібно вставити нову зведену таблицю в поле призначення.
4. Натисніть кнопку Insert (Вставити).

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

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

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

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

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

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