Як зробити зведену таблицю в Excel

Рекламний блок

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

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

Тепер про вимоги, яких необхідно дотримуватися при створенні зведеної таблиці в Ексель. Вихідні дані повинні бути представлені у вигляді таблиці, у якій має бути назва для стовпців, тобто шапка. Для цих цілей відмінно підійде створення розумної таблиці в Excel. Також вона не повинна містити порожні рядки, стовпці, клітинки. Не повинно бути прихованих рядків/стовпців і об'єднаних клітинок.

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

Как сделать сводную таблицу в Excel

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

З'являється наступне діалогове вікно. У ньому потрібно вказати діапазон або назва таблиці з вихідними даними, причому тут є можливість вибрати її з іншого джерела. Потім відзначте маркером, де помістити створюваний звіт. Зробимо його на новому аркуші. Натисніть «ОК».

У відкритій книзі Excel створиться новий аркуш, на якому буде розміщена, поки ще порожня зведена таблиця.

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

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

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

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

В якості рядків зведеної таблиці виберемо «Товари». Аналогічним чином перетягуємо потрібний поле в область Назви рядків».

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

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

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

Далі вибираємо, з якої по яку дату хочемо виконати групування, і з яким кроком.

Таблиця прийме наступний вигляд.

Тепер в область Значень перетягнемо поле «Сума».

Як бачите, відобразилися просто числа, хоча у вихідній таблиці був заданий числовий формат клітинок для даного стовпця, він також може бути грошовий і фінансовий. У колонці «Дата» формат ячеек теж був відповідний – дата.

Щоб це виправити, у зведеній таблиці виділяємо потрібний діапазон клітинок і натискаємо праву кнопку миші. Виберіть із меню «формат».

В наступному вікні вибираємо «Числовий», можете поставити галочку в полі «Роздільник груп розрядів» і натисніть «ОК».

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

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

В області Значень можна налаштувати параметри для поля. У прикладі виводиться сума значень: Рома в лютому продав сорочок на суму 1 800.00. давайте подивимося, скільки це штук. Клікаємо лівою кнопкою мишки по рядку «Сума по полю...» і вибираємо з меню «Параметри полів».

У наступному вікні зі списку вибираємо «Кількість», виберіть те, що підходить конкретно для Вашої таблиці, і натискаємо «ОК».

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

Тепер зробимо, щоб загальний фільтр для таблиці по місяцях. Міняємо області: «Фільтр звіту» перетягуємо поле «Дата», «Назви стовпців» – «Продавець».

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

Зверніть також увагу на стрічку. При виділенні будь осередків з зведеної таблиці, на ній з'являється вкладка «Робота зі зведеними таблицями» з двома подвкладками «Параметри» і «Конструктор».

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

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



Рекламний блок