Зведені таблиці Excel

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

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

Що таке зведена таблиця?

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

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

Зміст

Зразки даних
Вставити зведену таблицю
Додати поля
Сортувати за значенням
Оновити дані
Поле другого значення
Застосувати форматування чисел
Групувати за датою
Додайте відсоток від загальної суми
Підсумок переваг
Більше ресурсів

Покроковий посібник

Щоб зрозуміти зведені таблиці, вам потрібно працювати з ними самостійно. У цьому розділі ми покроково створимо кілька зведених таблиць із набору зразків даних. З досвідом, зведені таблиці нижче можна створити приблизно за 5 хвилин.

Зразки даних

Приклади даних містять 452 записи із 5 полями інформації: Дата, Колір, Одиниці, Продажі та Регіон. Ці дані ідеально підходять для зведеної таблиці.

Дані у відповідній таблиці Excel із назвою "Таблиця1". Таблиці Excel - це чудовий спосіб створювати зведені таблиці, оскільки вони автоматично коригуються під час додавання чи видалення даних.

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

Вставити зведену таблицю

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

Зведена таблиця "height =" 400 "src ="/sites/default/files/styles/original_with_watermark/public/images/articles/inline/ept_pivot_table_insert_button.png? Itok = qKlofIZ- "title =" Клацніть на кнопці у вкладці> Зведена таблиця "width =" 700 "/>

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

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

3. Клацніть OK, і Excel будує порожню зведену таблицю, починаючи з комірки H4.

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

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

Щоб побудувати зведену таблицю, перетягніть поля в одну область Стовпці, Рядки або Значення. Область Фільтри використовується для застосування глобальних фільтрів до зведеної таблиці.

Примітка: область полів зведеної таблиці показує, як поля використовувались для створення зведеної таблиці. Навчання "читати" область полів вимагає певної практики. Дивіться нижче, а також тут, щоб побачити більше прикладів.

Додати поля

1. Перетягніть поле Продаж у область Значення.

Excel обчислює загальну суму, 26356. Це сума всіх значень продажів у всьому наборі даних:

2. Перетягніть поле Колір до області Рядки.

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

Зверніть увагу, що загальна сума залишається 26356. Це має сенс, оскільки ми все ще звітуємо про повний набір даних.

Давайте поглянемо на область полів у цій точці. Ви можете побачити Колір - це поле Рядок, а Продажі - поле Значення:

Форматування чисел

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

1. Клацніть правою кнопкою миші будь-який номер продажу та виберіть Формат номера:

2. Застосуйте форматування валюти з нулем десяткових знаків, натисніть кнопку ОК:

У отриманій зведеній таблиці до всіх значень продажів застосовано формат валюти:

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

Сортування за значенням

1. Клацніть правою кнопкою миші будь-яке значення продажів і виберіть Сортувати> Від найбільшого до найменшого.

Від найбільшого до найменшого "height =" 406 "title =" Клацніть правою кнопкою миші та виберіть Сортувати> Від найбільшого до найменшого "width =" 548 "/>

Тепер Excel перелічує найпопулярніші кольори. Цей порядок сортування зберігатиметься при зміні даних або при зміні зведеної таблиці.

Оновити дані

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

1. Виділіть комірку F5 і змініть $ 11,00 на $ 2000.

2. Клацніть правою кнопкою миші в будь-якій точці зведеної таблиці та виберіть "Оновити".

Зверніть увагу, що "Червоний" зараз є найпопулярнішим кольором і автоматично переміщується до самого верхнього:

3. Змініть F5 на $ 11,00 і знову оновіть півот.

Примітка: змінити F5 на 2000 доларів нереально, але це хороший спосіб примусити зміни, які ви легко побачите у зведеній таблиці. Спробуйте змінити наявний колір на щось нове, наприклад "Золотий" або "Чорний". Після оновлення ви побачите новий колір. Ви можете скористатися скасуванням, щоб повернутися до вихідних даних та зробити зведення.

Поле другого значення

Ви можете додати більше одного поля як поле Значення.

1. Перетягніть Одиниці в область Значення, щоб побачити Продажі та Одиниці разом:

Відсоток від загальної суми

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

1. Видаліть Одиниці з області Значення

2. Додайте поле Продаж (знову) до області Значення.

3. Клацніть правою кнопкою миші другий екземпляр і виберіть "% від загальної суми":

відсоток від загальної "висоти =" 402 "title =" Клацніть правою кнопкою миші, виберіть Показати значення як> відсоток від загальної "width =" 659 "/>

Результатом є розбивка за кольорами разом із відсотком від загальної кількості:

Примітка: Формат чисел у відсотках також скоригований, щоб показувати 1 десятковий знак.

Ось область Поля на цьому етапі:

Групувати за датою

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

1. Видаліть друге поле Продаж (Sales2).

2. Перетягніть поле Дата в область Стовпці.

3. Клацніть правою кнопкою миші дату в області заголовка та виберіть "Групувати":

4. Коли з’явиться вікно Групування, групуйте лише за Роками (зніміть прапорець Місяці та Квартали):

Тепер у нас є зведена таблиця, яка групує продажі за кольором та роком:

Зверніть увагу, що продажів срібла в 2016 і 2017 роках немає. Ми можемо здогадатися, що срібло було представлено як новий колір у 2018 році. Зведені таблиці часто виявляють закономірності в даних, які важко побачити в іншому.

Ось область Поля на цьому етапі:

Двосторонній Pivot

Зведені таблиці можуть побудувати дані в різних двовимірних розміщеннях.

1. Перетягніть поле Дата з області стовпців

2. Перетягніть регіон у область Стовпці.

Excel створює двосторонню зведену таблицю, яка розбиває продажі за кольорами та регіонами:

3. Поміняйте місцями регіон і колір (тобто перетягніть регіон до області Рядки та Колір до області Стовпці).

Excel створює ще одну двовимірну зведену таблицю:

Знову зауважте, загальний обсяг продажів (26 356 доларів США) однаковий у всіх зведених таблицях вище. Кожна таблиця представляє різний вигляд одних і тих самих даних, тому всі вони складають однакову сукупність.

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

Основні переваги зведеної таблиці

Простота. Основні зведені таблиці дуже прості в налаштуванні та налаштуванні. Немає необхідності вивчати складні формули.

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

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

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

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

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

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

Діаграми. Отримавши зведену таблицю, ви можете легко створити зведену діаграму.