Як консолідувати дані в Excel

Будь-яка бізнес-операція створює багато даних, навіть у різних формах.

Ручне введення цих даних з одного аркуша Excel на інший і використання формул для об’єднання цих наборів даних може зайняти дуже багато часу.

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

Зміст

Приблизний час читання: 10 хвилин

Що таке консолідація даних в Excel?

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

Функція «Консолідувати» в Excel може бути корисною, коли потрібно узагальнити ті самі дані з кількох джерел. Консолідація даних у Excel дозволяє об’єднувати дані з кількох рядків і діапазонів за допомогою таких функцій, як сума, середнє значення, кількість, мінімум і максимум.

Функція консолідації даних також працює з даними з інших робочих аркушів і книг.

консолідувати дані Excel — Консолідувати

Для чого потрібна консолідація даних в Excel?

Різні системи можуть створювати різні набори даних у різних форматах. У великих організаціях можуть виявитися сотні (якщо не тисячі) файлів CSV і електронних таблиць, що містять дані. Об’єднання цих наборів даних в один у певному форматі є важливим для загального аналізу.

Консолідація даних у Excel є корисною функцією для об’єднання подібних наборів даних із кількох рядків, стовпців, робочих аркушів або робочих книг.

Приклади консолідації даних в Excel

Теоретично консолідувати дані в Excel може здатися простим. Однак це може стати складним під час роботи з наборами даних із кількох робочих аркушів або навіть кількох робочих книг. Нижче наведено п’ять типових прикладів консолідації даних у Excel разом із кроками.

Як консолідувати дані в Excel з кількох рядків

Консолідація рядків у Excel може бути корисною, якщо у вас є кілька рядків з однаковими записами.

Припустімо, у вас є список продажів, здійснених продавцями, із регіоном, галуззю, кодом продукту та продажем.

Тут ви можете використовувати консолідацію даних, щоб отримати вартість продажів за регіоном, продуктом або галуззю.

Щоб консолідувати дані з рядків, виконайте такі дії:

Відкрийте аркуш Excel і виберіть цільову комірку, де ви хочете розмістити зведені рядки.

Натиснувши на кнопку Consolidate меню Data, давайте відкриємо Pop-Up Consolidate

Виберемо, наприклад, відповідну функцію Sumі встановіть контрольний діапазон у полі Reference:

Вибравши діапазон, натисніть кнопку Add щоб додати його як посилання. Виберіть відповідну опцію в «Використовуйте ярлики в” (у цьому прикладі це „Лівий стовпець” для підсумовування кількостей продуктів з тим самим SKU) і натисніть OK продовжити.

Вручну перевірте результати для кількох рядків, щоб переконатися, що рядки були об’єднані правильно:

консолідує вартість продажів — Консолідуйте вартість продажів

Як консолідувати дані в Excel із кількох діапазонів

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

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

консолідується з кількох інтервалів — Консолідація з кількох діапазонів

У цьому випадку вам потрібно вибрати кілька діапазонів і використовувати як «верхній рядок», так і «лівий стовпець» як мітки. Давайте подивимося, як консолідувати дані в Excel із кількох діапазонів.

Виберіть клітинку, у якій потрібно об’єднати дані з кількох діапазонів.
Натиснувши на кнопку Consolidate меню Data, давайте відкриємо Pop-Up Consolidate. Виберіть суму

Тепер виберіть і додайте всі діапазони посилань (не забудьте вибрати діапазони, включаючи рядки заголовків), виберіть «Верхній рядок» і «Лівий стовпець», щоб використовувати їхні відповідні мітки, і натисніть «ОК», щоб продовжити.

Дані будуть консолідовані в комірці призначення.

консолідувати з кількох інтервалів підтвердити

Як консолідувати дані в Excel з кількох аркушів

Ми також можемо використовувати функцію консолідації в Excel, щоб об’єднати дані з кількох аркушів.

Припустімо, у вас є два діапазони даних на різних аркушах, і ви хочете об’єднати дані в Excel.

У цьому випадку ми можемо просто змінити робочий аркуш і вибрати відповідні контрольні діапазони з кількох робочих аркушів для консолідації даних.

Розглянемо кожен окремий крок докладніше:

Виберіть комірку, у якій потрібно об’єднати дані з кількох аркушів.
Натиснувши на кнопку Consolidate меню Data, давайте відкриємо Pop-Up Consolidate. Виберіть суму

Виберіть усі діапазони з кількох аркушів як посилання та додайте їх у поле «Посилання».

дві таблиці дві вкладки консолідує — Об’єднайте дві таблиці з двох вкладок

Після додавання діапазонів виберіть параметри «Верхній рядок» і «Лівий стовпець» для міток і натисніть «ОК», щоб продовжити.

підтвердити об’єднує дві таблиці у дві вкладки

Дані буде успішно консолідовано з кількох робочих аркушів у клітинку призначення.

два столи дві карти об'єднують результат — результат

Як об’єднати дані в Excel із кількох робочих книг

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

Повні кроки для консолідації даних у Excel із кількох файлів робочої книги наведено нижче.

Відкрийте робочий аркуш і виберіть клітинку, у якій потрібно об’єднати дані з кількох робочих книг. (Зберігайте вихідні книги відкритими в кількох вікнах.)
Натиснувши на кнопку Consolidate меню Data, давайте відкриємо Pop-Up Consolidate. Виберіть суму

Тепер у полі посилань клацніть маленьку стрілку вгору, перейдіть до вихідних книг і виберіть діапазони посилань. Це додасть шлях до файлу разом із діапазоном до посилання.

Додайте вибраний діапазон до посилання та повторіть той самий процес, щоб додати більше посилань.
Потім перевірте «Верхній рядок» і «Лівий стовпець» як мітки та натисніть «ОК», щоб продовжити процес консолідації.