Створення зведеної таблиці для аналізу даних аркуша
Зведена таблиця – це потужний засіб для обчислення, зведення та аналізу даних, які дають змогу переглядати порівняння, візерунки та тенденції даних.
Створення зведеної таблиці
-
Виберіть клітинки, на основі яких потрібно створити зведену таблицю.
Примітка.: Діапазон даних не має містити пустих рядків або стовпців. Заголовки мають складатися лише з одного рядка.
-
Виберіть Вставлення > Зведена таблиця.
-
У розділі Виберіть дані для аналізу встановіть перемикач Вибрати таблицю або діапазон.
-
У полі Таблиця/Діапазон перевірте діапазон клітинок.
-
У розділі виберіть місце, де потрібно розташувати звіт зведеної таблиці, виберіть елемент створити аркуш , щоб розташувати зведену таблицю на новому аркуші або наявному аркуші , а потім виберіть розташування, у якому має відображатися зведена таблиця.
-
Натисніть кнопку OK.
Розширення зведеної таблиці
-
Щоб додати поле до зведеної таблиці, позначте ім’я поля в області Поля зведеної таблиці.
Примітка.: Вибрані поля додаються до стандартних областей: нечислові поля до області Рядки, значення дати й часу – до області Стовпці та числові значення – до області Значення.
-
Перемістити поле з однієї області до іншої можна його перетягуванням.
Перед початком роботи
-
Ваші дані мають бути організовані в табличному форматі, а також не мають пустих рядків або стовпців. В ідеалі можна використовувати таблицю Excel , як у нашому прикладі вище.
-
Таблиці – це зручне джерело даних зведеної таблиці, оскільки рядки, які додаються до таблиці, автоматично включаються в зведену таблицю, коли ви оновлюєте дані, а будь-які нові стовпці додаються до списку Поля зведеної таблиці. В іншому разі потрібно змінити вихідні дані для зведеної таблиціабо використати динамічну формулу іменованого діапазону.
-
Необхідно, щоб усі дані в стовпцях мали однаковий тип. Наприклад, в одному стовпці не слід використовувати дати й текст.
-
У зведених таблицях використовується знімок даних, який називається кешем, тому фактичні дані не змінюються.
Створення зведеної таблиці
Якщо вам бракує досвіду роботи зі зведеними таблицями або ви не знаєте, з чого почати, радимо використовувати функцію Recommended PivotTable (Рекомендована зведена таблиця). Під час використання цієї функції програма 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. Це не вплине на інші дані, зведені таблиці або діаграми. Якщо зведену таблицю розташовано на окремому аркуші, який не містить інших потрібних даних, можна швидко видалити зведену таблицю, видаливши цей аркуш.
Тепер ви можете вставити зведену таблицю в електронну таблицю в Інтернет-версія Excel.
Увага!: Не радимо створювати зведені таблиці або редагувати їх на аркуші, на якому в цей час працюють інші користувачі.
-
Виберіть таблицю або діапазон клітинок електронної таблиці.
-
Виберіть Insert (Вставлення) > PivotTable (Зведена таблиця).
-
В Excel відкриється діалогове вікно Create PivotTable (Створення зведеної таблиці) з вибраним діапазоном або іменем таблиці.
-
У розділі Choose where you want the PivotTable report to be placed (Виберіть розташування звіту зведеної таблиці) виберіть New Worksheet (Новий аркуш) або Existing Worksheet (Наявний аркуш). Якщо ви вибрли Existing Worksheet (Наявний аркуш), клацніть клітинку, де потрібно вставити зведену таблицю.
Примітка.: Клітинку, на яку ви посилатиметеся, має бути розташовано за межами таблиці або діапазону.
-
Натисніть кнопку OK. Після цього Excel створить пусту зведену таблицю й відобразить список Поля зведеної таблиці.
Робота зі списком "Поля зведеної таблиці"
У області зведеної таблиці у верхній частині вікна установіть прапорець для будь-якого поля, яке потрібно додати до зведеної таблиці. За замовчуванням нечислові поля додаються до області " рядки ", поля дати й часу додаються до області " стовпці ", а числові поля додаються до області значення . Ви також можете вручну перетягувати будь-які доступні елементи до будь-якої з полів зведеної таблиці або якщо ви більше не хочете, щоб елемент у зведеній таблиці, просто перетягніть її зі списку полів або зніміть прапорець. Можливість перевпорядкування елементів полів – це одна з функцій зведеної таблиці, яка дає змогу швидко змінити вигляд.
|
Список "Поля зведеної таблиці" |
Відповідні поля в зведеній таблиці |
|
|
|
Робота зі значеннями зведеної таблиці
-
Підсумувати значення за
За замовчуванням поля зведеної таблиці в області Значення відображатимуться як сума. Якщо Excel інтерпретує дані як текст, вони відображатимуться як кількість. Ось чому важливо не використовувати різні типи даних у полях значень. Щоб змінити стандартний метод обчислення, клацніть стрілку праворуч від імені поля, а потім виберіть Параметри значення поля.
Змініть метод обчислення на вкладці Підсумувати значення за. До речі, якщо змінити метод обчислення, Excel автоматично додасть його до поля Користувацьке ім’я (наприклад, "Сума з Ім’я поля"), але це значення можна змінити. Натиснувши кнопку Формат числа, можна змінити числовий формат для всього поля.
Порада.: Оскільки змінення обчислення в розділі зведення значень за розділом змінить ім'я поля зведеної таблиці, радимо не перейменовувати поля зведеної таблиці, доки не буде виконано настроювання зведеної таблиці. Один трюк – скористатися функцією пошуку & замінити (CTRL + H) >знайдіть, що > "сума", а потім замініть > залишіть пустим, щоб замінити все відразу, а не вводити вручну.
-
Відображати значення як
Замість того щоб обчислювати суму даних, це значення можна відобразити як відсоток від значення поля. У прикладі нижче ми змінили суми витрат родини, які тепер відображатимуться як Частка загальної суми, а не сума значень.
Потрібні параметри можна вибрати в діалоговому вікні Параметри значення поля на вкладці Відображати значення як.
-
Відображення значення як обчислення та відсотка
Просто двічі перетягніть елемент до розділу Значення, а потім установіть для кожного з них параметри Підсумувати значення за та Відображати значення як.
Оновлення зведених таблиць
Якщо ви додали нові дані до джерела даних зведеної таблиці, знадобиться оновити всі зведені таблиці, створені на основі цього джерела. Щоб оновити зведену таблицю, клацніть будь-де в діапазоні даних зведеної таблиці й виберіть Оновити.
Видалення зведеної таблиці
Якщо ви створили зведену таблицю й вирішили, що вона вам більше не потрібна, просто виділіть увесь її діапазон, а потім натисніть клавішу Delete. Це не вплине на інші дані, зведені таблиці або діаграми. Якщо зведену таблицю розташовано на окремому аркуші, який не містить інших потрібних даних, можна швидко видалити зведену таблицю, видаливши цей аркуш.
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті Tech (у розділі Excel), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel User Voice.
Додаткові відомості
Використання роздільників для фільтрування даних зведеної таблиці
Створення часової шкали зведеної таблиці для фільтрування дат
Створення зведеної таблиці з моделлю даних для аналізу даних у кількох таблицях
Використання списку полів для впорядкування полів у зведеній таблиці
Змінення вихідних даних зведеної таблиці
Примітка.: Цю сторінку перекладено за допомогою засобу автоматичного перекладу, тому вона може містити смислові, синтаксичні або граматичні помилки. Ми вважаємо, що цей вміст стане вам у пригоді. Повідомте нас, чи була ця інформація корисною. Для довідки цю статтю можна переглянути англійською мовою.
