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

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

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

Наприклад, нижче наведено простий список витрат родини, а також зведену таблицю на основі цього списку.

Дані про витрати родини

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

Зразок витрат родини для створення зведеної таблиці з даними за місяцями, категоріями та сумами



Зразок зведеної таблиці: "Категорії" в розділі "Рядки" та "Місяці" в розділі "Стовпці"

А ось приклад зведеної діаграми:

Приклад зведеної діаграми Excel

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

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

  • Дані потрібно впорядкувати в табличному форматі. Ця таблиця не має містити пусті рядки або стовпці. В ідеалі можна використовувати таблицю Excel, як у прикладі вище.

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

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

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

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

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

Рекомендована зведена таблиця

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

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

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

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

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

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

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

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

    Щоб вставити пусту зведену таблицю, виберіть "Вставлення > Зведена таблиця".

    В Excel 2011 для Mac і попередніх версій кнопку "Зведена таблиця" розташовано на вкладці Дані в групі Аналіз.

    Вкладка "Дані", група "Аналізувати"

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

    Діалогове вікно "Створення зведеної таблиці" в Excel
  4. У розділі Виберіть розташування звіту зведеної таблиці виберіть новий аркуш або наявний аркуш. Якщо вибрано наявний аркуш, потрібно вказати аркуш і клітинку, де слід розмістити зведену таблицю.

  5. Якщо в зведену таблицю потрібно включити кілька таблиць або джерел даних, установіть прапорець Додати дані до моделі даних.

  6. Натисніть кнопку OK. Після цього Excel створить пусту зведену таблицю й відобразить список Поля зведеної таблиці.

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

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

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

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

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



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

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

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

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

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

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

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

    Порада : Якщо змінити метод обчислення на вкладці Підсумувати значення за, зміниться також ім’я поля зведеної таблиці. Тому не радимо перейменовувати поля зведеної таблиці, доки не завершите її налаштування. Щоб одночасно замінити всі значення, а не вводити їх щоразу вручну, виберіть Пошук і заміна (натисніть клавіші Ctrl+H), перейдіть на вкладку Знайти, введіть Сума, а поле Замінити на залишіть пустим.

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

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

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

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

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

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

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

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

Щоб оновити всі зведені таблиці, клацніть у групі "Стрічка > Знаряддя для зведених таблиць > Аналізувати > Дані" стрілку під кнопкою "Оновити" й виберіть "Оновити все".

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

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

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

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

Пов’язані теми

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

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

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

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

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

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

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

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

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

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

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

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×