Як створити зведену таблицю в excel (з ілюстраціями)

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

кроки

Частина 1 з 3:

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

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

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

2. Переконайтеся, що ваші дані відповідають вимогам зведеної таблиці. Зведена таблиця не завжди той інструмент, який вам потрібен. Щоб ви могли отримати вигоду з переваг такої таблиці, ваш лист повинен відповідати кільком базовим критеріям:

Ваш лист повинен включати як мінімум один стовпець з однаковими значеннями. Це означає, що як мінімум в одному стовпці у вас повинні повторюватися дані. У прикладі з наступного розділу в стовпці «Product Type» є два записи: «Table» or «Chair».

Вона повинна містити чисельну інформацію. Це те, що буде порівнюватися і підсумовуватися в таблиці. У прикладі з наступного розділу стовпець «Sales» заповнений числовими даними.

3. Запустіть майстер зведених таблиць. Натисніть на кнопку «Вставити» у верхній частині вікна Excel. Клацніть по кнопці «Зведена таблиця» на лівій стороні стрічки вставки.

Якщо ви використовуєте Excel 2003 або більш ранню версію, клацніть на пункт меню дані і виберіть Зведені таблиці і діаграми...

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

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

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

Коли ви будете задоволені результатом, клікніть OK. Ваша зведена таблиця буде розміщена, а інтерфейс зміниться.

Частина 2 з 3:

Налаштування зведених таблиць

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

Наприклад, ваша компанія продає 2 товара: столи і стільці. У вас є таблиця з кількістю товарів (Sales) кожного типу (Product Type), яке було продано в п`яти ваших магазинах (Store). Ви хочете дізнатися, скільки товарів було продано в кожному магазині.
Перетягніть поле Store зі списку полів в розділ список рядків вашої зведеної таблиці. З`явиться список магазинів. Кожен як окремий рядок.

2. Додавання поля стовпця. Так само, як і рядки, стовпці дозволяють вам сортувати і відображати дані. В наведеному вище прикладі поле Store було додано в секцію полів рядків. Щоб побачити як багато товарів кожного типу було продано, перетягніть поле Product Type в поля стовпців.

3. Додавання поля значення. Тепер, коли структура вибудована, ви можете додати дані, які будуть відображатися в таблиці. Перетягніть поле Sales в секцію поля значень зведеної таблиці. Ви побачите, як у вашій таблиці відобразиться інформація з продажу для обох товарів в кожному з магазинів і стовпець Total (разом) праворуч.

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

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

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

5. Зміна виду відображення даних. Ви можете змінити те, як відображаються значення, клікнувши на значок стрілки поруч із значенням в полі значень. Виберіть «Властивості поля», щоб змінити те, як розраховуються значення. Наприклад, ви можете виводити значення у відсотках замість суми або знаходити середнє значення замість підсумовування.

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

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

Сумма- значення за замовчуванням для полів. Excel обчислить суму всіх значень в обраному полі.

Кількість - обчислює кількість осередків з даними в обраному полі.

Среднее- обчислює середнє значення від всіх значень в поле.

7. Додавання фільтра. Меню «Фільтр звітів» содержітполя, які дозволяють вампролістать зведення даних зі зведеної таблиці, відфільтрувавши набори даних. Вони працюють як фільтри для звіту. Наприклад, налаштувавши поле Store як фільтр замість рядка, ви зможете вибрати кожен магазин і побачити його власні продажі або переглянути кілька магазинів одночасно.

Частина 3 з 3:

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

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

2. Оновлення вашої таблиці. Ваша зведена таблиця буде автоматично оновлюватися при кожній зміні в файлі. Це може допомогти контролювати його і стежити за змінами.

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

Звідси зведена таблиця отримала свою назву. Переміщаючи дані в різні місця, «зводячи» їх, ви міняєте спосіб представлення даних.

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

Поради

Якщо ви використовуєте команду Імпорт даних з меню Дані, вам буде доступний більший вибір опцій, як наприклад підключення до бази даних, файли Excel, бази даних Access, текстові файли, дані стандарту ODBC, веб-сторінки, OLAP і XML / XSL. Ви можете потім використовувати ці дані так само, як список Excel.
Якщо ви використовуєте автофільтрацію (на вкладці «Дані», «Фільтр»), вимкніть її, коли будете створювати зведену таблицю. Після створення таблиці її можна буде знову включити.

попередження

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