Об’єднання даних на кількох аркушах

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

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

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

Об'єднати дані можна двома способами: за розташуванням або категорією.

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

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

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

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

Щоб об'єднати кілька аркушів у головний аркуш, виконайте такі дії:

Якщо ви ще цього не зробили, настройте дані на кожному з складових аркушів, виконавши такі дії:
- Переконайтеся, що кожен діапазон даних має формат списку. Кожен стовпець має містити підпис (заголовок) у першому рядку та містити схожі дані. У списку не повинно бути пустих рядків або стовпців.
- Розташуйте кожен діапазон на окремому аркуші, але не вводьте нічого на головному аркуші, де планується об'єднати дані. Excel зробить це за вас.
- Переконайтеся, що кожен діапазон має однаковий макет.
Клацніть верхню ліву клітинку області на головному аркуші, де потрібно розташувати об’єднані дані.

Примітка.: Щоб не перезаписувати наявні дані на головному аркуші, залиште достатньо клітинок праворуч і під цією клітинкою для об'єднаних даних.
Натисніть кнопку Дані>консолідації (у групі Знаряддя даних ).
У полі Функція виберіть функція зведення, яку потрібно використовувати для об’єднання даних. Стандартна функція – SUM.

Ось приклад, у якому вибрано три діапазони аркушів:
Виберіть дані.

Потім у полі Посилання натисніть кнопку Згорнути , щоб стиснути панель і вибрати дані на аркуші.

Клацніть аркуш із даними, які потрібно об’єднати, виділіть ці дані та натисніть кнопку Розгорнути діалогове вікно праворуч, щоб повернутися в діалогове вікно Консолідація.

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

Ось приклад, у якому вибрано три діапазони аркушів:
У спливаючому вікні Консолідація натисніть кнопку Додати. Повторіть цю процедуру, щоб додати всі діапазони, які ви об'єднуєте.
Автоматичне оновлення та оновлення вручну. Якщо потрібно, щоб програма Excel автоматично оновюла таблицю об'єднання, коли змінюються вихідні дані, просто встановіть прапорець Створити зв'язки з вихідними даними . Якщо цей прапорець не знято, об'єднання можна оновити вручну.
Примітки.:
- Не можна створювати зв’язки, якщо вихідна та цільова області розташовані на одному аркуші.
- Якщо потрібно змінити ступінь діапазону або замінити діапазон, клацніть діапазон у спливаючому вікні Консолідація та оновіть його, виконавши наведені вище дії. Буде створено нове посилання на діапазон, тому вам потрібно буде видалити попереднє посилання перед повторним об’єднанням. Просто виберіть старе посилання та натисніть клавішу Delete.
Натисніть кнопку OK, і Excel створить об'єднання для вас. За потреби можна застосувати форматування. Формат потрібно відформатувати лише один раз, якщо ви не повторно не завантажите об'єднання.
- Рядки та стовпці, підписи яких не збігаються з підписами інших вихідних аркушів, після об'єднання буде розташовано в окремих рядках або стовпцях.
- Переконайтеся, що будь-які категорії, які не потрібно об'єднувати, мають унікальні підписи, які відображаються лише в одному вихідному діапазоні.

Якщо дані для консолідації розташовано в різних клітинках на різних аркушах:

Введіть формулу (окрему для кожного аркуша) з посиланнями на клітинки на інших аркушах. Наприклад, щоб об’єднати дані з аркушів «Продаж» (у клітинці В4), «Кадри» (у клітинці F5) і «Маркетинг» (у клітинці В9), у клітинці А2 на головному аркуші введіть:

Посилання на кілька аркушів у формулі Excel

Порада.: Щоб ввести посилання на клітинку, наприклад Продаж! B4 – у формулі, не вводячи текст, введіть формулу до потрібного місця, а потім перейдіть на вкладку аркуша, а потім клацніть клітинку. Excel заповнить ім'я аркуша та адресу клітинки. ПРИМІТКА. Формули в таких випадках можуть бути схильними до помилок, оскільки випадково вибрати неправильну клітинку дуже просто. Також може бути важко помітити помилку після введення складної формули.

Якщо дані для консолідації розташовано в однакових клітинках на різних аркушах:

Введіть формулу з тривимірним посиланням, яка посилається на діапазон назв аркушів. Наприклад, щоб об'єднати дані в клітинках A2 з відділу збуту по маркетингу включно, у клітинці E5 головного аркуша введіть таке:

Тривимірне посилання на аркуші у формулі Excel