Консолідація даних в програмі Microsoft Excel

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

Умови для виконання процедури консолідації

Природно, що не всі таблиці можна консолідувати в одну, а тільки ті, які відповідають певним умовам:

• стовпці у всіх таблицях повинні мати однакову назву (допускається лише перестановка стовпців місцями);
• не повинно бути стовпців або рядків з порожніми значеннями;
• шаблони у таблиць повинні бути однаковими.

Створення зведеної таблиці

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

1. Відкриваємо окремий лист для зведеної таблиці.
2. На що відкрився аркуші відзначаємо осередок, яка буде верхній лівій осередком нової таблиці.
3. Перебуваючи у вкладці «Дані» натискаємо на кнопку «Консолідація», яка розташована на стрічці в блоці інструментів «Робота з даними».
4. Відкривається вікно настройки консолідації даних.

   В поле «Функція» потрібно встановити, яку дію з осередками буде виконуватися при збігу рядків і стовпців. Це можуть бути такі дії:

   • сума;
   • кількість;
   • середнє;
   • максимум;
   • мінімум;
   • твір;
   • кількість чисел;
   • зміщене відхилення;
   • незміщене відхилення;
   • зміщена дисперсія;
   • несмещенная дисперсія.

   У більшості випадків використовується функція «Сума».

5. В поле «Посилання» вказуємо діапазон комірок однієї з первинних таблиць, які підлягають консолідації. Якщо цей діапазон знаходиться в цьому ж файлі, але на іншому аркуші, то тиснемо кнопку, яка розташована праворуч від поля введення даних.
6. Переходимо на той лист, де розташована таблиця, виділяємо потрібний діапазон. Після введення даних тиснемо знову на кнопку розташовану праворуч від поля, куди було внесено адресу осередків.
7. Повернувшись у вікно налаштувань консолідації, щоб додати вже обрані нами осередки в список діапазонів, тиснемо на кнопку «Додати».

   Як бачимо, після цього діапазон додається в список.

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

   Якщо ж потрібний діапазон розміщений в іншій книзі (фото), то відразу тиснемо на кнопку «Огляд ...», вибираємо файл на жорсткому диску або знімному носії, а вже потім зазначеним вище способом виділяємо діапазон комірок в цьому файлі. Природно, файл повинен бути відкритий.

8. Точно так само можна зробити деякі інші настройки зведеної таблиці.

   Для того, щоб автоматично додати назву стовпців в шапку, ставимо галочку біля параметра «Підписи верхнього рядка». Для того, щоб вироблялося підсумовування даних встановлюємо галочку біля параметра «Значення лівого стовпчика». Якщо ви хочете, щоб при оновленні даних в первинних таблицях оновлювалася також і вся інформація в консолідованій таблиці, то обов'язково слід встановити галочку біля параметра «Створити зв'язки з вихідними даними». Але, в цьому випадку потрібно врахувати, що, якщо ви захочете в вихідну таблицю додати нові рядки, то доведеться зняти галочку з даного пункту і перерахувати значення вручну.

   Коли всі налаштування виконані, тиснемо на кнопку «OK».

9. Консолідований звіт готовий. Як бачимо, дані його згруповані. Щоб подивитися інформацію всередині кожної групи, натискаємо на плюсик зліва від таблиці.

   Тепер вміст групи є для перегляду. Аналогічним способом можна розкрити і будь-яку іншу групу.

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

• ← Конвертація файлів Word в Microsoft Excel
• Регресійний аналіз в Microsoft Excel →