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

Написано співзасновником Каспер Лангманн, Спеціаліст Microsoft Office.

Хто ще хоче обробляти та візуалізувати дані, не потіючи?

Ось чому я написав для вас цей підручник із "Зведених таблиць" у Excel.

Це наш найамбітніший підручник.

Він складається з 7733 слів, містить безкоштовний файл вправ, відео та завдання.

Отже, візьміть чашку кави і починайте!

* Цей посібник призначений для Excel 2019/Microsoft 365 (для Windows). Маєте іншу версію? Немає проблем, ви все одно можете дотримуватися тих самих кроків.

Зміст

Protip:

Миттєво поверніться до цього посібника. Натисніть CTRL + D, щоб додати цю сторінку в закладки.

Що таке зведена таблиця і навіщо її використовувати?

Ах, так, зведена таблиця. Без сумніву, один з найулюбленіших, але ще зловживаних інструментів Excel.

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

Отже, що таке зведена таблиця?

Найкращий спосіб його визначити: це вбудована функція Excel, яка дозволяє брати дані та переупорядковувати їх.

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

Навіщо використовувати зведені таблиці?

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

Отримайте БЕЗКОШТОВНИЙ файл вправ

Перш ніж почати:

У цьому посібнику вам потрібен набір даних для практики.

Я включив для вас один (безкоштовно).

Завантажте його прямо нижче!

Завантажте БЕЗКОШТОВНИЙ файл вправ

Відео: Підготовка даних

Перш ніж створювати зведену таблицю, ваші дані потрібно негайно налаштувати.

У цьому відео я показую вам, як саме це робиться.

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

Добре, ви тут, щоб дізнатися, як створити зведену таблицю.

Слідуйте 6 кроків до зведеної таблиці викладено нижче. За лічені хвилини ви створили свою саму першу зведену таблицю 🙂

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

Давайте розберемося, як створити зведену таблицю.

Про набір даних

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

Як створити зведену таблицю за 6 (простих) кроків

1: Переконайтеся, що всі ваші стовпці даних мають заголовки.

2: Переконайтеся, що ваші дані не містять порожніх рядків

3: Клацніть на будь-яку частину таблиці даних

4: У групі «Таблиці» на вкладці «Вставка» натисніть «Зведена таблиця». «Таблиця/діапазон» за замовчуванням вибрано як ваш суцільний діапазон даних (забезпечується кроками 1 та 2). Залиште значення за замовчуванням вибраними у діалоговому вікні «Створити зведену таблицю».

5: Натисніть «OK», і ваш новий аркуш буде активовано.

6: Виберіть поля, які потрібно додати до зведеної таблиці, за необхідності зі зведеної таблиці «Список полів».

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

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

Як створити зведену таблицю з декількох аркушів

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

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

Тут їхні імена „Чикаго”, „Нашвілл” та „Сан-Франциско”.

Виберемо перший робочий аркуш із трьох, "Чикаго".

Тоді продовжуйте і виконуйте наступні 11 кроків ...

1: Натисніть клавіші Alt + D, а потім натисніть P, щоб з’явилося повідомлення “Майстер зведеної таблиці та зведеної діаграми”.

2: У розділі "Де знаходяться дані, які ви хочете проаналізувати?" виберіть перемикач для «Кілька діапазонів консолідації», а потім натисніть «Далі».

Ви перейдете до "Крок 2" майстра.

3: Виберіть перемикач «Я буду створювати поля сторінки» та натисніть «Далі».

4: Наступне, що вам потрібно зробити, це вибрати свій перший діапазон даних.

У файлі прикладу це буде таблиця даних на вкладці «Чикаго», і вам потрібно вибрати діапазон від A2: H7.

5: Повторіть той самий процес для даних про продажі місцезнаходжень „Нешвілл” та „Сан-Франциско”.

6: Тепер виділіть перший діапазон у розділі «Усі діапазони». Потім натисніть перемикач поруч із «1» у розділі «Скільки полів сторінки ви хочете?».

7: У розділі «Поле перше:» введіть назву для «поля сторінки». У цьому прикладі просто введіть "Чикаго".

8: Тепер повторіть це для решти двох діапазонів. Потім натисніть кнопку «Далі.

9: На кроці 3 майстра залиште вибране значення за замовчуванням «Новий аркуш» і натисніть «Готово».

Отримана зведена таблиця повинна виглядати наступним малюнком.

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

Зауважимо одне - це фільтр у комірці B1. Цей фільтр дозволяє вибрати одне або кілька місць.

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

Чи змінилися вихідні дані для існуючої зведеної таблиці?

Потім вам потрібно оновити діапазон зведеної таблиці.

В інших випадках ви можете додати більше даних.

Щоб включити ці нові дані, вам потрібно змінити джерело даних.

Ось докладніший погляд на ці 2 методи.

Спосіб 1: Зміна джерела даних

Ви хочете додати нові дані до вихідних даних зведеної таблиці?

Потім вам потрібно змінити джерело даних, щоб розширити його до нового діапазону.

Зверніть увагу, що у файлі-прикладі є аркуш із назвою вкладки „Oct Data To To Add”.

Додавши нові дані за жовтень, перейдіть до зведеної таблиці та клацніть будь-яку комірку зведеної таблиці.

Тепер натисніть «Змінити джерело даних» у групі «Дані» на вкладці «Аналіз».

Переконайтеся, що параметр «Таблиця/діапазон» (у вікні «Змінити джерело даних зведеної таблиці») відповідає діапазону, який включає ваші нові дані.

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

Просто клацніть клітинку в даних та скористайтесь комбінацією клавіш Ctrl + A, щоб розширити до нового використовуваного діапазону.

Excel також дозволяє використовувати "динамічні іменовані діапазони" як джерела даних.

Це може спростити процес зміни джерела даних.

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

Ви також повинні знати, як оновити зведену таблицю…

І саме про це ви дізнаєтесь із методу 2 нижче!

Спосіб 2: Оновіть зведену таблицю

Ще один метод, про який ви повинні знати, - це як оновити зведену таблицю.

Скажімо, вам потрібно змінити ціну:

Ви хочете збільшити ціну від $ 275,79 до $ 299,99
Це стосується код товару BP110966-XL

Ви можете знайти це в оригінальних даних для зведеної таблиці.

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

Щоб вирішити цю проблему, відфільтруйте код товару та змініть ціну продажу вручну.

Нова ціна на всі ці рядки даних вплине на суми у стовпці „Усього”.

Тепер знайдіть групу «Дані» та натисніть «Оновити».

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

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

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

Ваша зведна таблиця створена, і ви надзвичайно раді її використовувати.

Але як ви використовуєте зведену таблицю?

Саме про це йдеться в цьому розділі.

Теми:

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

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

«Поле» зведені таблиці називається заголовком у вихідних даних (наприклад, «Місцезнаходження») і містить дані, знайдені в цьому стовпці (наприклад, Сан-Франциско).

Виділяючи дані у відповідні «поля» для використання у зведеній таблиці, Excel дозволяє користувачеві:

Відображення наборів даних у більш логічному виведенні напр. «Загальна кількість проданих одиниць» у Сан-Франциско. Це буде розглянуто більш докладно пізніше, коли ми обговоримо "Налаштування поля значення".
Витяг і відображення відповідних даних організувавши це більш підходящим способом - або в переносному, або в естетичному плані.
Фільтруйте дані відповідно на основі значень, знайдених у вибраному "полі".

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

Давайте зробимо крок назад і створимо зведену таблицю з доданими даними за жовтень:

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

Щоб додати стовпці даних до таблиці, перетягніть потрібне поле в "Мітки стовпців", "Мітки рядків" або "Значення" (ці 3 також будуть описані більш докладно пізніше).

Цей приклад налаштування містив би дані в рядках, розділених „Розташування” та „Елемент”. Стовпці розділені знаком "Місяць".

Це утворює сітку для відображення "Всього проданих одиниць" у кожному "Місцезнаходженні" за "Місяць".

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

Коли ми вибираємо для своїх рядків «Місце» та «Елемент», відбувається щось круте:

Підсумки «Розташування» за кожен місяць тепер розглядаються як проміжні підсумки для кожного місяця.
Розбивка за пунктом "Також" видно в кожному "Місцезнаходження".

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

Це залишає видимими лише проміжні підсумки для цього "Розташування".

Використання фільтрів звітів

З налаштованою зведеною таблицею ви можете бачити розподіл загального обсягу продажів за вибраними діапазонами («Місяць», «Місце розташування» та «Позиція»).

Тут з’являються «фільтри звітів».

Ви перетягуєте «Елемент» у область «Фільтр звітів».

Давайте також встановимо «Рядки» на «Місяць», а «Стовпці» - «Місцезнаходження».

Це дає вам абсолютно новий погляд на дані.

Це додає ще один рядок над зведеною таблицею з вибраним "Полем фільтра".

Існує також спадне вікно, щоб зробити вибір ...

Це дозволяє користувачеві вибрати лише відповідні дані, необхідні для запиту:

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

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

Тут ми виберемо стійку “Автомобіль” та зазначимо зміну значень:

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

У попередньому прикладі ми використовували "Налаштування поля значення суми".

Тут відображається „Загальна кількість проданих одиниць” для кожного „Місяця” та „Розташування”, перелічених у зведеній таблиці.

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

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

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