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

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

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

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

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

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

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

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

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

Виконайте наведені нижче дії, щоб об'єднати кілька аркушів на зразок аркуша.

  1. Якщо ви ще не зробили цього, налаштуйте дані в кожному установчому аркуші, виконавши такі дії:

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

    • Помістіть кожний діапазон на окремому аркуші, але не вводьте нічого в головному аркуші, де ви плануєте закріпити дані. Програма Excel зробить це за вас.

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

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

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

  3. Виберіть елемент консолідаціяданих>(у групі Знаряддя даних ).

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

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

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

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

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

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

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

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

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

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

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

  6. У спливаючому вікні об'єднання натисніть кнопку Add (додати). Повторіть цю дію, щоб додати всі діапазони, які потрібно об'єднати.

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

    Примітки.: 

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

    • Якщо потрібно змінити масштаб діапазону або замінити діапазон, клацніть діапазон у спливаючому меню "Консолідація", а потім оновіть її, виконавши наведені вище дії. Буде створено нове посилання на діапазон, тому вам потрібно буде видалити попереднє посилання перед повторним об’єднанням. Просто виберіть старе посилання та натисніть клавішу DELETE.

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

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

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

Якщо дані, які потрібно об'єднати, розташовані в різних клітинках на різних аркушах:

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

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

Порада.: Щоб увести посилання на клітинку (наприклад, "продажі!") B4 – у формулі без введення тексту введіть формулу до точки, у якій потрібно створити посилання, а потім перейдіть на вкладку аркуш, а потім клацніть клітинку. Програма Excel завершуватиме ім'я аркуша та адресу для вас. Примітка: формули в таких випадках можуть бути схильні до помилок, оскільки випадково вибрати неправильну. Після введення складної формули може бути складно помітити помилку.

Якщо дані, які потрібно об'єднати, розташовані в одних і тих самих клітинках на різних аркушах:

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

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

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

Ви завжди можете поставити запитання експерту в спільноті Tech (у розділі Excel), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel User Voice.

Див. також

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

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

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

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

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

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

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

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

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

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

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

×