Щоб підбити підсумки й узагальнити результати для окремих аркушів, дані з кожного аркуша можна об'єднати в головний аркуш. Аркуші можуть міститися в тій самій книзі, що й головний, або в інших. Під час консолідації ви збираєте дані, тому їх легко оновлювати й агрегувати за потреби.
Наприклад, якщо в кожному з регіональних офісів є власний аркуш витрат, за допомогою консолідації ці дані можна об’єднати на головному аркуші корпоративних витрат. Цей головний аркуш також може містити підсумкові та середні обсяги збуту, дані про поточні запаси та відомості про найпопулярніші продукти для всього підприємства.
Порада.
Якщо ви часто об'єднуєте дані, радимо створювати нові аркуші на основі шаблону аркуша з узгодженим макетом. Докладні відомості про шаблони: Створення шаблону. Це також ідеальний час, щоб настроїти шаблон із таблицями Excel.
Способи консолідації даних
Дані можна об'єднати двома способами: за розташуванням або категорією.
Об'єднання за розташуванням: дані у вихідних областях мають однаковий порядок і підписи. Використовуйте цей метод, щоб об’єднати дані з низки аркушів, створених на основі одного шаблону, наприклад аркушів зі звітами про бюджет підрозділів.
Об’єднання за категорією: дані у вихідних областях не мають розташовуватися в однаковому порядку, але при цьому позначаються однаковими підписами. Використовуйте цей метод, щоб об’єднати дані з низки аркушів із різними макетами, але однаковими підписами даних.
- Об’єднання даних за категорією відбувається так само, як створення зведеної таблиці. Однак за допомогою зведеної таблиці можна легко перевпорядковувати категорії. Радимо створити зведену таблицю , якщо вам потрібні гнучкіші можливості об'єднання за категорією.
Примітка.
Приклади в цій статті створено за допомогою Excel 2016. У разі використання іншої версії Excel подання можуть відрізнятися, але дії однакові.
Як закріпити
Щоб об'єднати кілька аркушів у головний аркуш, зробіть ось що:
Якщо ви ще цього не зробили, настройте дані в кожному установчому аркуші, виконавши такі дії:
- Переконайтеся, що кожен діапазон даних відформатовано як список. Кожен стовпець повинен мати підпис (заголовок) в першому рядку і містити схожі дані. У списку не може бути пустих рядків або стовпців.
- Розташуйте кожен діапазон на окремому аркуші, але нічого не вводьте на головному аркуші, який використовуватиметься для об'єднання даних. Excel зробить це за вас.
- Переконайтеся, що всі діапазони мають однаковий макет.
Клацніть верхню ліву клітинку області на головному аркуші, де потрібно розташувати об’єднані дані.
Примітка.
Щоб уникнути перезаписування наявних даних на головному аркуші, переконайтеся, що праворуч і внизу залишилася достатня кількість клітинок для об'єднаних даних.
Натисніть кнопку "Консолідація даних>" (у групі "Знаряддя даних").
У полі "Функція " виберіть функцію зведення, за допомогою якої потрібно об'єднати дані. Стандартна функція – SUM.
Ось приклад, у якому вибрано три діапазони аркуша:
Виберіть дані.
Після цього в полі "Посилання " натисніть кнопку "Згорнути ", щоб стиснути панель, і виберіть дані на аркуші.
Клацніть аркуш із даними, які потрібно об'єднати, виділіть ці дані та натисніть кнопку " Розгорнути діалогове вікно " праворуч, щоб повернутися до діалогового вікна "Консолідація ".Якщо аркуш із даними, які потрібно об'єднати, розташовано в іншій книзі, натисніть кнопку "Огляд", щоб знайти цей аркуш. Знайшовши шлях до файлу й натиснувши кнопку "OK", Excel введе шлях до файлу в полі "Посилання " та додасть до нього знак оклику. Після цього ви зможете продовжити вибирати інші дані.
Ось приклад, у якому вибрано три діапазони аркуша:
У спливаючому вікні " Консолідація " клацніть "Додати". Повторіть ці дії, щоб додати всі діапазони, які об'єднуєте.
Автоматичне оновлення та оновлення вручну. Щоб налаштувати в Excel автоматичне оновлення таблиці об'єднання після кожного змінення вихідних даних, просто встановіть прапорець Створювати зв'язки з вихідними даними . Якщо цей прапорець не встановлено, параметри об'єднання можна оновити вручну.
Примітка.
- Не можна створювати зв’язки, якщо вихідна та цільова області розташовані на одному аркуші.
- Якщо потрібно змінити екстенцію діапазону або замінити діапазон, клацніть діапазон у спливаючому вікні "Консолідація" й оновіть його, як описано вище. Буде створено нове посилання на діапазон, тому вам потрібно буде видалити попереднє посилання перед повторним об’єднанням. Просто виберіть старе посилання та натисніть клавішу Delete.
Натисніть кнопку OK, і Excel створить об'єднання. За потреби можна застосувати форматування. Його необхідно відформатувати лише один раз, якщо ви не запустите об'єднання повторно.
- Рядки та стовпці, підписи яких не збігаються з підписами інших вихідних аркушів, після об'єднання буде розташовано в окремих рядках або стовпцях.
- Переконайтеся, що категорії, які не слід об'єднувати, мають унікальні підписи, які з'являться лише в одному вихідному діапазоні.
Об’єднання даних за допомогою формули
Дані, які потрібно об'єднати, розташовані в різних клітинках на різних аркушах:
Введіть формулу (окрему для кожного аркуша) з посиланнями на клітинки на інших аркушах. Наприклад, щоб об’єднати дані з аркушів «Продаж» (у клітинці В4), «Кадри» (у клітинці F5) і «Маркетинг» (у клітинці В9), у клітинці А2 на головному аркуші введіть:
Порада.
Введення посилання на клітинку, наприклад "Продаж! B4: у формулі, не набираючи її, введіть текст формули до місця посилання, клацніть вкладку потрібного аркуша, а потім клацніть клітинку. Excel доповнить ім'я аркуша та адресу клітинки. ПРИМІТКА. У таких випадках формули можуть призводити до помилок, оскільки ви можете випадково вибрати не ту клітинку. Крім того, можна не помітити помилку після введення складної формули.
Дані, які слід об'єднати, розташовані в однакових клітинках на різних аркушах:
Введіть формулу з тривимірним посиланням, яка посилається на діапазон назв аркушів. Наприклад, щоб об'єднати дані з клітинок A2 аркушів «Продажі» — «Маркетинг» (включно), на головному аркуші у клітинці E5 введіть:
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті Tech у Excel або отримати підтримку в спільнотах.
Додаткові відомості
Способи уникнення недійсних формул
Виявлення та виправлення помилок у формулах