Як в excel використовувати консолідацію для об’єднання даних з різних книг - трюки і прийоми в

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

Ось два способи об'єднання даних, що застосовуються в Excel:

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

Об'єднання по позиціях

Якщо книги, з якими ви працюєте, мають однакову структуру побудови, об'єднання по позиціях - це найбільш правильний спосіб об'єднання даних. Наприклад, зверніть увагу на три створені книги - Баланс1 Баланс2 і Баланс3 на рис. 3.7.

Мал. 3.7. Вихідні книги для об'єднання по позиціях

Як ви бачите, все три балансові книги (які, наприклад, можуть являти собою звіт від трьох різних магазинів однієї фірми) мають однакову структуру і розташування даних. Таким чином, вони ідеальні для об'єднання по позиціях.

Мал. 3.8. Книга-шаблон для майбутнього об'єднання

Отже, дане об'єднання слід почати з створення нової книги з таким же дизайном та розташуванням елементів (див. Рис. 3.8). Ви можете зробити це, просто зберігши під новим ім'ям будь-яку з книг для об'єднання, видаливши попередньо всі дані (при цьому формули для розрахунку всіх сум і балансів видаляти не слід). В даному прикладі ми об'єднуємо дані з трьох книг. Діапазони значень для продажів будуть виглядати наступним чином:

[Баланс1.хlsx] Лист1! B3: M5
[Баланс2.хlsx] Лист1! B3: M5
[Баланс3.хlsx] Лист1! B3: M5

Маючи це на увазі, виконайте наступні кроки для об'єднання даними способом:

1. Виберіть верхній лівий кут діапазону, куди будуть занесені дані. В даному прикладі (див. Рис. 3.8) це буде осередок B3.
2. Перейдіть на вкладку Дані стрічки інструментів Excel, потім в групі Робота з даними натисніть кнопку Консолідація. В результаті на екрані з'явиться діалогове вікно Консолідація - см. Рис. 3.9.

Мал. 3.9. Вікно «Консолідація»

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

Натисніть на кнопку Додати. Excel занесе введені вами дані до Списку діапазонів (див. Рис. 3.9).

Повторіть кроки 4-5, додаючи всі необхідні вам книги і дані.

Якщо ви хочете, щоб дані в об'єднаній книзі змінювалися в міру змін в книгах-джерелах, поставте галочку біля пункту Створювати зв'язку з вихідними даними.

Натисніть ОК. Excel запросить необхідні дані і занесе їх в об'єднану книгу (див. Рис. 3.10).

Мал. 3.10. Об'єднана по позиціях книга

Якщо ви не створите зв'язок з вихідними даними. Excel просто одноразово внесе дані з книг. При створенні ж зв'язків відбудеться наступне:

1. Додадуться зв'язку до всіх осередків.
2. Об'єднання даних буде відбуватися за допомогою функції СУММ ().
3. Відбудеться приховування осередків зі зв'язками, як ви можете бачити на рис. 3.10.

Як ви можете бачити, в Баланс1 знаходиться інформація від якогось відділу з продажу Дисков і Книг, в Баланс2 - Морозива, Книг і Кассет, а в Баланс3 - Дисков, Морозива, Кассет і Приборов. Далі виконайте наступні операції: