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

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

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

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

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

Упорядкуйте свої дані

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

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

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

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

Спочатку виділіть усі клітинки, які містять дані, перейдіть на вкладку «Вставка» та натисніть «Зведена діаграма». Потім виберіть у спадному меню параметр «Зведена діаграма та зведена таблиця».

Відкриється діалогове вікно Створення зведеної таблиці. Excel автоматично визначить та заповнить правильний діапазон у полі «Таблиця/діапазон», інакше виберіть правильну таблицю або діапазон комірок. Потім вкажіть цільове розташування для вашої зведеної таблиці Excel, це може бути «Новий аркуш» або «Існуючий аркуш» і натисніть «ОК».

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

Створіть свою зведену таблицю

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

Панель «Поля зведеної таблиці» розділена на дві горизонтальні частини: розділ «Поля» (верхня частина панелі) і розділ «Макет» (нижня частина панелі).

  • The Польова секція перелічує всі поля (стовпці), які ви додали до таблиці. Ці назви полів – це імена всіх стовпців із вихідної таблиці.
  • The Розділ макета має 4 області, тобто фільтри, стовпці, рядки та значення, за допомогою яких ви можете впорядковувати та перевпорядковувати поля.

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

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

Додати рядки

Ми почнемо з додавання поля «Компанія» до розділу «Рядки». Зазвичай нечислові поля додаються до області рядків макета. Просто перетягніть і опустіть поле «Компанія» в область «Рядок».

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

Додайте цінності

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

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

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

Тепер у нас є одновимірна таблиця компаній (мітки рядків) разом із сумою доходів.

Додати стовпець

Двовимірна таблиця

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

Коли ви додаєте поле «Дата» до області «Стовпець», Excel автоматично додає «Квартально» та «Роки» до полів стовпців, щоб обчислити та краще узагальнити дані.

Тепер у нас є двовимірна таблиця з тривимірними значеннями.

Додати фільтри

Якщо ви хочете відфільтрувати дані за «Регіон», ви можете перетягнути поле «Регіон» до області фільтра.

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

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

Результат:

Сортування

Якщо ви хочете відсортувати значення таблиці в порядку зростання або спадання, клацніть правою кнопкою миші будь-яку клітинку в стовпці «Сума доходу», потім розгорніть «Сортувати» та виберіть порядок.

Результат:

Групування

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

Спочатку виділіть стовпці та клацніть на них правою кнопкою миші. Потім виберіть опцію «Група» зі спадного меню.

У вікні групування виберіть «Квартали» та «Роки», оскільки ми хочемо, щоб вони були організовані за фінансовими кварталами кожного року. Потім натисніть «ОК».

Тепер ваші дані організовані за фінансовими кварталами кожного року.

Параметри поля значення

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

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

Крім того, ви можете натиснути стрілку вниз поруч із «Сума ..» в області значень розділу поля та вибрати «Параметри поля значення».

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

Результат:

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

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

Коли ми вибираємо «% від загальної кількості стовпців», результат буде таким:

Оновіть зведену таблицю

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

Клацніть будь-де в зведеній таблиці та перейдіть на вкладку «Аналіз», натисніть кнопку «Оновити» в групі «Дані». Щоб оновити поточну зведену таблицю на аркуші, натисніть опцію «Оновити». Якщо ви хочете оновити всі зведені таблиці в книзі, натисніть «Оновити все».

Крім того, ви можете клацнути таблицю правою кнопкою миші та вибрати опцію «Оновити».

Це воно. Сподіваємося, що ця стаття дасть вам детальний огляд зведених таблиць Excel і допоможе вам створити їх.