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

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

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

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

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

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

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

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

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

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

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

   2. Натисніть кнопку Настроїти панель швидкого доступу (...) у нижньому лівому куті під стрічкою, а потім виберіть пункт Інші команди.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

   2. Натисніть кнопку Настроїти панель швидкого доступу (...) у нижньому лівому куті під стрічкою, а потім виберіть пункт Інші команди.

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

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

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

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

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

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

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

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

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

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

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

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

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

   2. Натисніть кнопку Настроїти панель швидкого доступу (...) у нижньому лівому куті під стрічкою, а потім виберіть пункт Інші команди.

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

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

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

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

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

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

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

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

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

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

      Приклад

      • Якщо в розділі Скільки полів сторінки вибранозначення 1? виберіть кожен діапазон, а потім введіть інше ім'я в полі Поле 1. Якщо у вас чотири діапазони, кожен із яких відповідає іншому кварталу фінансового року, виберіть перший діапазон, введіть Q1, виділіть другий діапазон, введіть Q2 та повторіть процес для 3 та 4 кварталу.

      • Якщо в розділі Скільки полів сторінки вибрано значення 2?, виконайте такі самі дії, як у попередньому прикладі в полі Поле одне. Потім виберіть два діапазони та введіть у полі Поле два однакові імена, наприклад H1 і H2. Виділіть перший діапазон, введіть H1, виділіть другий діапазон, введіть H1, виділіть третій діапазон, введіть H2, виділіть четвертий діапазон, а потім введіть H2.

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

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