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

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

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

Підсумковий консолідований звіт зведеної таблиці може містити такі поля в області Список полів зведеної таблиці, які можна додати до зведеної таблиці: "Рядок", "Стовпець" і "Значення". Крім того, звіт може включати до чотирьох полів фільтра сторінок з іменами "Сторінка1", "Сторінка2", "Сторінка3" та "Сторінка4".

Налаштування вихідних даних

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

Зразок джерел даних для консолідації звіту зведеної таблиці

Поля сторінки під час консолідації даних

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

Зразок консолідованого звіту зведеної таблиці з полем сторінки.

Використання іменованих діапазонів

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

Інші способи консолідації даних

Excel також підтримує інші способи консолідації даних у різних форматах і макетах. Наприклад, ви можете створити формули з об’ємними посиланнями або використовувати команду Консолідація (на вкладці Дані в групі Знаряддя даних).

Консолідація кількох діапазонів

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

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

Щоб об’єднати дані з усіх діапазонів і створити консолідований діапазон без полів сторінки, виконайте такі дії:

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

    1. Клацніть стрілку поруч із панеллю інструментів і виберіть Інші команди.

    2. У розділі Вибрати команди виберіть Усі команди.

    3. У списку виберіть Майстер зведених таблиць і діаграм, натисніть кнопку Додати, а потім – OK.

  2. Клацніть пусту клітинку (яка не входить у зведену таблицю) у книзі.

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

  4. На сторінці майстра Крок 1 клацніть кілька діапазонів консолідації, а потім натисніть кнопку Далі.

  5. На сторінці майстра Крок 2а клацніть Створити поля сторінки, а потім натисніть кнопку Далі.

  6. На сторінці майстра Крок 2б зробіть ось що:

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

      Порада : Якщо діапазон клітинок розташовано в іншій книзі, спочатку відкрийте її, щоб полегшити вибір даних.

    2. У розділі Укажіть кількість полів сторінки для зведеної таблиці. клацніть 0, а потім натисніть кнопку Далі.

  7. На сторінці майстра Крок 3 вкажіть, куди слід додати зведену таблицю (на новий або наявний аркуш) і натисніть кнопку Готово.

Консолідація даних із використанням одного поля сторінки

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

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

    1. Клацніть стрілку поруч із панеллю інструментів і виберіть Інші команди.

    2. У розділі Вибрати команди виберіть Усі команди.

    3. У списку виберіть Майстер зведених таблиць і діаграм, натисніть кнопку Додати, а потім – OK.

  2. Клацніть пусту клітинку (яка не входить у зведену таблицю) у книзі.

  3. На сторінці майстра Крок 1 клацніть кілька діапазонів консолідації, а потім натисніть кнопку Далі.

  4. На сторінці майстра Крок 2а клацніть Створити одне поле сторінки та натисніть кнопку Далі.

  5. На сторінці майстра Крок 2б зробіть ось що:

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

      Порада : Якщо діапазон клітинок розташовано в іншій книзі, спочатку відкрийте її, щоб полегшити вибір даних.

  6. Натисніть кнопку Далі.

  7. На сторінці майстра Крок 3 вкажіть, куди слід додати зведену таблицю (на новий або наявний аркуш) і натисніть кнопку Готово.

Консолідація даних із використанням кількох полів сторінки

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

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

    1. Клацніть стрілку поруч із панеллю інструментів і виберіть Інші команди.

    2. У розділі Вибрати команди виберіть Усі команди.

    3. У списку виберіть Майстер зведених таблиць і діаграм, натисніть кнопку Додати, а потім – OK.

  2. Клацніть пусту клітинку (яка не входить у зведену таблицю) у книзі.

  3. На сторінці майстра Крок 1 клацніть кілька діапазонів консолідації, а потім натисніть кнопку Далі.

  4. На сторінці майстра Крок 2а клацніть Створити поля сторінки, а потім натисніть кнопку Далі.

  5. На сторінці майстра Крок 2б зробіть ось що:

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

      Порада : Якщо діапазон клітинок розташовано в іншій книзі, спочатку відкрийте її, щоб полегшити вибір даних.

    2. У розділі Укажіть кількість полів сторінки для зведеної таблиці. виберіть потрібну кількість полів сторінки.

    3. У розділі Укажіть мітки елементів, які будуть позначати виділений діапазон, у кожному полі сторінки. для кожного з полів сторінки виберіть діапазон клітинок, а потім введіть його ім’я.

      Приклад

      • Якщо в розділі Укажіть кількість полів сторінки для зведеної таблиці. вибрано значення 1, виберіть кожен із діапазонів, а потім введіть інше ім’я в полі Перше поле. Якщо у вас чотири діапазони, кожен із яких відповідає кварталу фінансового року, виберіть перший діапазон, введіть ім’я "Кв1", виберіть другий діапазон, введіть ім’я "Кв2" та повторіть цю процедуру для діапазонів "Кв3" й "Кв4".

      • Якщо в розділі Укажіть кількість полів сторінки для зведеної таблиці. вибрано значення 2, введіть у полі Перше поле ті самі дані, що й у попередньому прикладі. Потім виберіть два діапазони та введіть у полі Друге поле однакове ім’я, наприклад "Пр1" і "Пр2". Виберіть перший діапазон і введіть ім’я "Пр1", виберіть другий діапазон і введіть ім’я "Пр1", виберіть третій діапазон і введіть ім’я "Пр2", а потім виберіть четвертий діапазон і введіть ім’я "Пр2".

    4. Натисніть кнопку Далі.

  6. На сторінці майстра Крок 3 вкажіть, куди слід додати зведену таблицю (на новий або наявний аркуш) і натисніть кнопку Готово.

На початок сторінки

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×