При роботі з однотипними даними, розміщеними в різних таблицях, листах або навіть книгах, для зручності сприйняття краще зібрати інформацію воєдино. У Microsoft Excel з цим завданням можна впоратися за допомогою спеціального інструменту, який називається «Консолідація». Він надає можливість зібрати розрізнені дані в одну таблицю. Давайте дізнаємося, як це робиться.
Умови для виконання процедури консолідації
Природно, що не всі таблиці можна консолідувати в одну, а тільки ті, які відповідають певним умовам:- стовпці у всіх таблицях повинні мати однакову назву (допускається лише перестановка стовпців місцями);
- не повинно бути стовпців або рядків з порожніми значеннями;
- шаблони у таблиць повинні бути однаковими.
Створення зведеної таблиці
Розглянемо, як створити консолідовану таблицю на прикладі трьох таблиць, що мають однаковий шаблон і структуру даних. Кожна з них розташована на окремому аркуші, хоча за таким же алгоритмом можна створити консолідовану таблицю з даних розташованих в різних книгах (файлах).
- Відкриваємо окремий лист для зведеної таблиці.
- На що відкрився аркуші відзначаємо осередок, яка буде верхній лівій осередком нової таблиці.
- Перебуваючи у вкладці «Дані» натискаємо на кнопку «Консолідація», яка розташована на стрічці в блоці інструментів «Робота з даними».
- Відкривається вікно настройки консолідації даних.
В поле «Функція» потрібно встановити, яку дію з осередками буде виконуватися при збігу рядків і стовпців. Це можуть бути такі дії:
- сума;
- кількість;
- середнє;
- максимум;
- мінімум;
- твір, добуток;
- кількість чисел;
- зміщене відхилення;
- незміщене відхилення;
- зміщена дисперсія;
- несмещенная дисперсія.
У більшості випадків використовується функція «Сума».
- В поле «Посилання» вказуємо діапазон комірок однієї з первинних таблиць, які підлягають консолідації. Якщо цей діапазон знаходиться в цьому ж файлі, але на іншому аркуші, то тиснемо кнопку, яка розташована праворуч від поля введення даних.
- Переходимо на той лист, де розташована таблиця, виділяємо потрібний діапазон. Після введення даних тиснемо знову на кнопку розташовану праворуч від поля, куди було внесено адресу осередків.
- Повернувшись у вікно налаштувань консолідації, щоб додати вже обрані нами осередки в список діапазонів, тиснемо на кнопку «Додати».
Як бачимо, після цього діапазон додається в список.
Аналогічним чином, додаємо всі інші діапазони, які братимуть участь в процесі консолідації даних.
Якщо ж потрібний діапазон розміщений в іншій книзі (фото), то відразу тиснемо на кнопку «Огляд ...», вибираємо файл на жорсткому диску або знімному носії, а вже потім зазначеним вище способом виділяємо діапазон комірок в цьому файлі. Природно, файл повинен бути відкритий.
- Точно так само можна зробити деякі інші настройки зведеної таблиці.
Для того, щоб автоматично додати назву стовпців в шапку, ставимо галочку біля параметра «Підписи верхнього рядка». Для того, щоб вироблялося підсумовування даних встановлюємо галочку біля параметра «Значення лівого стовпчика». Якщо ви хочете, щоб при оновленні даних в первинних таблицях оновлювалася також і вся інформація в консолідованій таблиці, то обов'язково слід встановити галочку біля параметра «Створити зв'язки з вихідними даними». Але, в цьому випадку потрібно врахувати, що, якщо ви захочете в вихідну таблицю додати нові рядки, то доведеться зняти галочку з даного пункту і перерахувати значення вручну.
Коли всі налаштування виконані, тиснемо на кнопку «OK».
- Консолідований звіт готовий. Як бачимо, дані його згруповані. Щоб подивитися інформацію всередині кожної групи, натискаємо на плюсик зліва від таблиці.
Тепер вміст групи є для перегляду. Аналогічним способом можна розкрити і будь-яку іншу групу.
Як бачимо, консолідація даних в Ексель є дуже зручним інструментом, завдяки якому можна зібрати воєдино інформацію розташовану не тільки в різних таблицях і на різних аркушах, але навіть розміщену в інших файлах (книгах). Робиться це відносно просто і швидко.