Консолідація даних

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

Розглянемо це на прикладі аналізу заробітків за 3 місяці. Для цього в робочій книзі, крім існуючого Листа Січень, створимо ще Листи Лютий і Березень.

Скопіюємо з Листа Січень дані на Листи Лютий і Березень. По­тім змінимо в деяких робітників кількість відпрацьованих днів, відпо­відно зміняться і значення стовпчика Одержати. Можна змінити роз­ряд, можна звільнити декілька робітників, тобто вилучити відповідні рядки на Листах Лютий або Березень, можна прийняти на роботу но­вих робітників. Наприклад, з Листів Лютий та Березень вилучимо за­писи про робітника Кирієнко В.Н., а в березні приймемо на роботу но­вого робітника Семенова С.С. (див. рис. 6.1).

При консолідації по категорії всі діапазони повинні задовольня­ти вимогам для списків, а саме:

ü перший рядок повинен містити заголовки;

ü список не може мати вільних рядків або стовпчиків;

ü підписи рядків та стовпчиків, які консолідуються, повинні співпадати з урахуванням регістру.

Дані для консолідації розташовані на різних Листах, але прізви­ща знаходяться в стовпчику В, а числові дані – в стовпчику Н. Тому змінимо розташування даних на Листах так, щоб стовпчики були по­руч. Вставимо вільний стовпчик праворуч від прізвищ робітників і пе­ретягнемо туди стовпчик Одержати (див. рис. 6.1).

Рисунок 6.1 – Фрагменти даних для консолідації

Створимо новий Лист з ім'ям Квартал1. Встановимо курсор в клітинці А1. Потім в меню Данные виберемо пункт Консолидация. Відкриється вікно Консолидация (див. рис. 6.2).

В полі Функция виберемо функцію Сумма (при консолідації даних можуть використовуватися різні функції: Сумма, Количество, Среднее, Максимум, Минимум, і т.д.). Потім визначимо області – дже­рела, які потрібно консолідувати. Для цього клікнемо мишкою в полі Ссылка, перейдемо на Лист Січень, виділімо клітини В3:С18 (див. рис. 6.1) і натиснемо кнопку Добавить. Повторимо дії для кожного діапазону. Отримаємо Список диапазонов:

Рисунок 6.2 – Введення діапазонів для консолідації

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

Якщо встановити прапорець Создавать связи с исходными дан­ными, то результати консолідації будуть автоматично поновлюватися у разі внесення змін в областях – джерелах. При цьому ще й ство­рюється структура (див. рис. 6.3).

Структура дозволяє побачити всі дані, для яких підраховувались підсумки, або сховати їх. Наприклад, на рис. 6.3 видно, що робітники Кирієнко В.Н. та Семенова С.С. працювали по одному місяцю, а Іванов І.І., як і всі інші –3 місяці..

Якщо на всіх Листах прізвища робітників однакові, то можна використовувати консолідацію по розміщенню. Це найпростіший спо­сіб консолідації. Для її виконання можна було б залишити на місці числові дані, тобто в діапазоні H4:H18. Потім на новому Листі в клітинку А1 ввести заголовок П.І.Б., в клітинку В1 – заголовок Одержати та скопіювати всі прізвища в стовпчик А.

Рисунок 6.3 – Результати консолідації

Встановимо курсор в клітинку В2, тобто позначимо першу клі­тинку області виведення. Потім в меню Данные виберемо пункт Кон­солидация і у вікні Консолидация задамо список діапазонів:

Січень!H4:H18

Лютий!H4:H18

Березень!H4:H18