Консолідація Excel (Зміст)
- Вступ до консолідації в Excel
- Як консолідувати дані в декількох робочих аркушах?
Консолідуйте дані в Excel
Консолідація в цілому означає збір або збирання деяких спільних речей в одному місці. Це можуть бути будь-які дані або щось на зразок інформації чи кількості. Консолідація в Excel відноситься до функції, яка поєднує інформацію або дані з різних робочих книжок / робочих таблиць в один робочий лист / робочу книгу. Іншими словами, ця функція бере дані з різних робочих аркушів, щоб об’єднати їх в єдиний робочий аркуш, який можна легко оновлювати.
Наприклад, якщо у вас є чотири робочі аркуші. Кожен аркуш має витрати на чверть. Можливо, вам знадобиться один аркуш із витратами за всі чотири квартали. Це коли грає функція консолідації. Коли дані на робочих аркушах знаходяться в одному положенні, що означає під тими ж мітками і в тому ж порядку. Ми використовуємо "Консолідація за позицією". І коли дані на робочих аркушах знаходяться не під одними мітками і не в тому самому порядку. Потім ми використовуємо "Консолідація за категоріями". Це так само, як створити таблицю зведення.
Як консолідувати дані в декількох робочих аркушах?
Давайте розберемося, як об’єднати дані в декілька робочих аркушів за допомогою декількох прикладів.
Приклад №1 - Консолідуйте дані в тій же робочій книжці
Скажімо, у нас є робочий аркуш, в якому ми маємо дані про продаж із чотирма аркушами, позначеними відповідно до їх регіону як Північ, Південь, Схід та Захід. Тепер ми хотіли б об'єднати дані в одне місце, а не на аркуші в межах однієї робочої книги. Існує п'ятий аркуш з назвою зведений файл.
Цей приклад покаже консолідовані продажі для всіх регіонів. Ось файли зразків даних. Тут ви можете побачити різні назви робочого аркуша та останній зведений файл, над яким ми маємо працювати.
Це наш шаблон на аркуші «консолідований файл», і тепер ми почнемо консолідувати дані з робочих аркушів.
Зараз ми натиснемо на клітинку B3.
Там, де ми хочемо, щоб функція «Консолідація» вставляла дані з інших аркушів. Як ми бачимо вище, вибирається клітинка B3, і тепер ми перейдемо до вкладки «Дані» на стрічці Excel і перейдемо до «Консолідація».
Як тільки ми натиснемо на консолідацію, нижче з'явиться вікно:
Перше, на що ми звернемо увагу, - це спадне меню «Функція», яке показує безліч арифметичних функцій, таких як сума, підрахунок, макс, середнє значення тощо. Оскільки ми хочемо суму продажів, то у спадному меню виберемо «Сума». Тепер ми перейдемо на вкладку довідки, щоб додати посилання на наші дані з різних робочих таблиць. Потім ми перейдемо до нашого першого аркуша, який містить дані про продажі для North. Нам залишається лише вибирати дані про продажі, а не заголовки та рядки. Це показано нижче.
Посилання відображається у вікні «Довідник», як це.
Натисніть "Додати", і посилання буде додано у поле "Всі довідки".
Таким же чином ми тепер додамо посилання з усіх інших аркушів, таких як Північ, Південь, Схід та Захід. Після завершення додавання посилань натисніть «ОК».
Тепер ми консолідували дані щодо продажів керівників щомісяця на національному рівні.
Приклад №2 - Консолідуйте щорічний мудрий продукт продажів
У цьому ми маємо чверть розумних продажів товарів A, B, C і D, і ми хочемо, щоб консолідований річний обсяг продажів продукту.
Тут ми маємо дані на чотирьох аркушах, на першому аркуші з даними про продажі за 1 квартал, у наступному - дані за Q2, а потім за 3 та Q4. Зараз ми перейдемо на вкладку довідки, щоб додати посилання на наші дані з різних робочих таблиць. Ми перейдемо до нашого першого аркуша, який містить дані про продажі за 1 квартал. Ми виберемо дані, як показано нижче.
Тепер ми перейдемо на вкладку «Дані» і натиснемо «Консолідувати», і з’явиться нижнє вікно.
Ми натиснемо «Додати», і посилання буде додано у поле «Усі довідки».
Ми натиснемо «Додати», і посилання буде додано у поле «Усі довідки».
Ми натиснемо «Додати», і посилання буде додано у поле «Усі довідки».
Нижче наш шаблон для зведеної таблиці даних. Тепер ми виберемо клітинку B2, щоб отримати загальні дані про продажі з інших аркушів.
Ми виберемо зі спадного меню функцію «Сума». Так само ми додамо посилання з аркушів Q2, Q3 та Q4. Виявиться так. Усі посилання з усіх аркушів тепер додані. Натисніть «ОК»
Зараз у нас є консолідовані щорічні дані про продажі із загальною сумою для кожного продукту.
У випадку, якщо ми хочемо вставити зведену таблицю в порожній робочий аркуш замість того, щоб робити таблицю шаблонів. Ми також можемо це зробити. Взявши наші попередні зразкові дані, ми зробимо консолідацію нижче.
У нас немає шаблону для таблиці консолідації, і ми хочемо отримати консолідовані дані на чистому робочому аркуші з мітками рядків та стовпців. Ми додамо новий робочий аркуш, в нашому випадку це "Консолідований файл".
Тепер, як раніше, ми перейдемо на вкладку Дані Тепер ми виберемо посилання з наших даних. Ми почнемо це з аркуша "Північ", а потім продовжимо з аркушами "Південь", "Схід" та "Захід". Ми виберемо дані, як показано нижче, що включає мітки рядків та стовпців. Потім ми додамо посилання у поле "Усі посилання": Тепер додайте всі посилання однаково з усіх таблиць даних. Тепер ми перевіримо і «верхній рядок», і «лівий стовпець», а потім натиснемо кнопку «ОК». Зараз ми бачимо зведені дані про продажі з мітками рядків та стовпців. Якщо ви хочете оновити консолідовані дані, коли окремий аркуш оновлюється, натисніть поле "Створити посилання для створення даних". Якщо ви хочете оновити дані вручну, не встановіть прапорець і натисніть кнопку ОК. Осередки зведеної таблиці тепер містять формулу суми, як показано нижче. Це робить його динамічним за своєю суттю. Це динамічна таблиця консолідації. Якщо у нас є дані в різних робочих зошитах і ми хочемо об'єднати їх в єдиний робочий зошит. Ми також можемо це зробити. Це дає нам велику гнучкість та легкість. У різних робочих книжках, як показано нижче, у нас є квартальні дані про продажі різних продуктів: A, B, C і D. Після того, як ми відкриємо всі робочі зошити, ми перейдемо до нової робочої книги та натиснемо на комірку B3. Додайте посилання з усіх робочих таблиць нижче. Ми об’єднали дані з усіх робочих книжок у нову робочу книжку. Крім того, будь-які зміни даних у будь-яких робочих книгах також будуть оновлені в новій консолідованій робочій книжці даних. Таким чином ми дізналися, як використовувати функцію консолідації в Excel за допомогою прикладів. Це дуже корисна функція при об'єднанні або збиранні даних на одному аркуші з різних робочих аркушів або робочих зошитів. Це посібник з консолідації в Excel. Тут ми обговорюємо, як консолідувати дані в декількох робочих аркушах разом з практичними прикладами та шаблоном Excel, який можна завантажити. Ви також можете ознайомитись з іншими нашими пов’язаними статтями -
Приклад №3 - Консолідація даних з різних робочих книжок
Що слід пам’ятати про консолідацію в Excel
Рекомендовані статті