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

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

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

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

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

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

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

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

Примітка : Приклади в цій статті стосуються Excel 2016, тому подання у вашій версії може трішки відрізнятися. Однак послідовність дій однакова в усіх версіях.

Дії з об’єднання

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

    • Переконайтеся, що кожен діапазон даних відформатовано як список (тобто всі стовпці мають підпис у першому рядку та містять схожі дані) і в списку немає пустих рядків або стовпців.

    • Розташуйте кожен діапазон на окремому аркуші. Нічого не вводьте на головному аркуші, який використовуватиметься для об’єднання: його буде заповнено автоматично.

    • Переконайтеся, що всі діапазони мають однаковий макет.

  2. Клацніть верхню ліву клітинку області на головному аркуші, де потрібно розташувати об’єднані дані.

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

  3. На вкладці Дані в групі Знаряддя даних натисніть кнопку Консолідація.

    Зображення стрічки у програмі Excel

  4. У полі Функція виберіть функцію, яку потрібно використовувати для об’єднання даних. Стандартна функція – SUM.

    Нижче наведено приклад із трьома вибраними діапазонами аркуша.

    Діалогове вікно "Консолідація даних"

  5. Виберіть дані.

    • Якщо аркуш із даними, які потрібно об’єднати, розташовано в іншій книзі, спочатку натисніть кнопку Огляд, знайдіть потрібний файл і натисніть кнопку OK, щоб закрити діалогове вікно Огляд. Шлях до файлу буде введено в полі Посилання, а після нього буде вставлено знак оклику. Ви можете продовжити вибір даних.

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

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

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

  6. У діалоговому вікні Консолідація натисніть кнопку Додати, а потім повторіть дії, щоб додати всі потрібні діапазони.

  7. Автоматичне оновлення та оновлення вручну. Щоб налаштувати в Excel автоматичне оновлення таблиці об’єднання після кожного змінення вихідних даних, установіть прапорець Створювати зв’язки з вихідними даними. Якщо цей прапорець знято, параметри об’єднання можна оновити вручну.

    Примітки : 

    • Не можна створювати зв’язки, якщо вихідна та цільова області розташовані на одному аркуші.

    • Щоб змінити додані діапазони, клацніть кожен із них у діалоговому вікні "Консолідація", оновіть їх у полі "Посилання", а потім натисніть кнопку "Додати". Буде створено нове посилання на діапазон, тому вам потрібно буде видалити попереднє посилання перед повторним об’єднанням. Просто виберіть старе посилання та натисніть клавішу Delete.

  8. Натисніть кнопку OK, щоб створити об’єднання. Його не буде відформатовано, але ви можете зробити це самі. Це достатньо зробити всього один раз, якщо ви знову не запустите об’єднання.

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

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

Об’єднання даних за допомогою формули

  • Дані, які потрібно об’єднати, розташовані в різних клітинках на різних аркушах

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

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

    Порада : Щоб ввести посилання на клітинку, наприклад "Продажі!B4", у формулу, не набираючи його, введіть тіло формули до місця посилання, клацніть вкладку потрібного аркуша, а потім клацніть потрібну клітинку. Ім’я аркуша й адресу клітинку буде заповнено автоматично. Просто зауважте, що такі формули можуть призводити до помилок, тому що ви можете випадково вибрати не ту клітинку. Крім того, після введення формули можна не помітити помилку.

  • Дані, які слід об’єднати, розташовані в однакових клітинках на різних аркушах

    Введіть формулу з тривимірним посиланням, яке вказує на діапазон назв аркушів. Наприклад, щоб об’єднати дані з клітинок A2 аркушів із "Продажі" по "Маркетинг" (включно), на головному аркуші в клітинці E5 введіть:

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

Цікавий факт

Якщо у вас немає передплати на Office 365 або останньої версії Office, можна ознайомитися з ними зараз:

Спробуйте Office 365 або найновішу версію Excel

Відповіді на запитання щодо конкретних функцій

Опублікувати запитання на форумі спільноти Excel

Допомога в удосконаленні програми Excel

Маєте пропозиції щодо того, як покращити наступну версію програми Excel? Тоді перегляньте розділи на веб-сайті Excel User Voice

Див. також

Огляд формул у програмі Excel

Способи уникнення недійсних формул

Пошук і виправлення помилок у формулах

Сполучення клавіш і функціональні клавіші в програмі Excel

Функції Excel (за алфавітом)

Функції Excel (за категоріями)

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

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

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

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

×