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

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

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

Підсумковий консолідований звіт зведеної таблиці може містити такі поля в області Список полів зведеної таблиці, які можна додати до зведеної таблиці: "Рядок", "Стовпець" і "Значення". Крім того, звіт може включати до чотирьох полів фільтра сторінок з іменами "Сторінка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 квартал, виберіть другий діапазон, уведіть Q2, а потім повторіть процес для 3-го та 4-го діапазону.

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

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

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

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

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

Залишайтеся попереду з Microsoft 365

Потрібна додаткова довідка?

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

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

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

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

×