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

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

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

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

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

Існує два способи об'єднувати дані, розташування або категорії.

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

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

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

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

Виконайте ці кроки, щоб об'єднувати кілька аркушів, на головному аркуші.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Примітки.: 

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

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

  8. Натисніть кнопку OKа Excel створить об'єднання для вас. За потреби можна застосувати форматування. Це лише необхідні, щоб відформатувати один раз, якщо ви повторно об'єднання.

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

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

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

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

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

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

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

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

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

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

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

Див. також

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

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

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

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

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

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

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

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

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

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

×