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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільнотах.

Вибір аркушів

Клацнувши вкладки аркушів у нижній Excel, можна швидко виділити один або кілька аркушів. Щоб ввести або змінити дані одночасно на кількох аркушах, можна згрупувати їх, вибравши кілька аркушів. Крім того, можна відформатувати або надрукувати виділені аркуші одночасно.

Виберіть вкладку аркуша, який потрібно відредагувати. Активний аркуш буде іншим кольором, ніж інші аркуші. У цьому випадку вибрано Аркуш4.

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

Два або більше суміжних аркушів

Виберіть вкладку першого аркуша, а потім, утримуючи натиснутою клавішу Shift, виберіть вкладку останнього аркуша, який потрібно виділити.

За допомогою клавіатури: спочатку натисніть клавішу F6, щоб активувати вкладки аркушів. Потім за допомогою клавіш зі стрілками вліво та вправо виберіть потрібний аркуш, а потім натисніть клавіші Ctrl+ПРОБІЛ, щоб вибрати його. Щоб вибрати додаткові аркуші, повторіть кроки зі стрілками та Ctrl+ПРОБІЛ.

Два або більше несуміжних аркушів

Виберіть вкладку першого аркуша, а потім, утримуючи натиснутою клавішу Ctrl, виберіть вкладки інших аркушів, які потрібно виділити.

За допомогою клавіатури: спочатку натисніть клавішу F6, щоб активувати вкладки аркушів. Потім за допомогою клавіш зі стрілками вліво та вправо виберіть потрібний аркуш, а потім натисніть клавіші Ctrl+ПРОБІЛ, щоб вибрати його. Щоб вибрати додаткові аркуші, повторіть кроки зі стрілками та Ctrl+ПРОБІЛ.

Клацніть вкладку аркуша правою кнопкою миші та виберіть команду Вибрати всі аркуші.

ПОРАДА. Якщо вибрати кілька аркушів, у верхній частині аркуша в рядку заголовка з’явиться повідомлення [Група]. Щоб скасувати виділення кількох аркушів у книзі, клацніть будь-який невиділений аркуш. Якщо жодного невиділеного аркуша не видно, клацніть правою кнопкою миші вкладку виділеного аркуша та виберіть у контекстному меню пункт Розгрупувати аркуші.

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

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

Виберіть меню Усі аркуші, а потім виберіть у меню аркуш, щоб відкрити його.

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

Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільнотах.