Тема: Консолідація даних
Мета: Ознайомитися з командою Консолідація даних Навчитися застосовувати консолідацію для одержання підсумкової Інформації
Теоретичні відомості
Команда Консолідація з меню Дат може об'єднувати Інформацію з вихідних листив (до 255) в один підсумковий лист Вихідні листи можуть розміщуватись як у тій самій книзі, що й підсумковий, так І в Інших Наприклад, якщо в різних книгах міститься Інформація про кожне відділення фірми, то для створення підсумкового листа з відповідними підсумками про фірму загалом можна застосувати команду Консолідація
Команду Консолідація можна використовувати різними способами
• створити зв'язки даних у підсумковому листи з вихідними даними, щоб наступні
зміни у вихідних листах відображалися в підсумковому листи,
• консолідувати дані без створення зв'язків — за місцем розташування І категоріями
Розглянемо останні два способи консолідації
Консолідація за місцем розташування. У цьому разі Excel збирає Інформацію з однаково розміщених комірок кожного вихідного листа При консолідації листив використовують функцію, яку можна вибрати зі списку Функція у вікні команди Консолідація За замовчуванням використовується функція Сума, яка підсумовує дані з кожного листа І розміщує результат у підсумковому листи Для консолідації вихідних листив у підсумковому листи треба виконати такі дії
- активізувати підсумковий аркуш І виділити в ньому діапазон комірок, куди
заноситимуться дані, що консолідуються (наприклад, В4 Р8),
- виконати команду Копсодідація з пункту меню Дані,
- вибрати функцію для опрацювання даних з кожного робочого листа, наприклад Середнє
для усереднення значень з кожного робочого листа,
- опції у вікні діалогу залишити невстановленими,
- клацнути лівою кнопкою миші в полі Посилання І перейти до першого робочого листа,
- виділити за допомогою миші область для початкового діапазону (В4 Р8),
- натиснути кнопку Добавити,
- повторити попередні три пункти для кожного робочого листа,
- натиснути кнопку О К
Програма Excel усереднює підсумкові дані й розміщує їх у підсумковому листи
Консолідація за категоріями
За основу для об'єднання листив використовують заголовки стовпців або рядків Наприклад, якщо стовпець "Січень" розміщується в одному робочому листи у стовпці В, а в Іншому — у стовпці D, то все одно їх можна об'єднати Зауважимо, що у підсумковому листи не зазначають заголовки рядків Вони вставляться автоматично Для консолідації даних треба виконати такі дії
- виділити в підсумковому листи область для розміщення даних Вона повинна містити
порожній стовпець А, щоб Excel міг ввести заголовки консолідованих рядків Ця область
повинна містити стільки рядків, скільки унікальних елементів рядків (наприклад, прізвищ
працівників) міститься в усіх таблицях,
- виконати команду Консолідація з пункту меню Дані,
- вибрати функцію для опрацювання даних з кожного робочого листа, наприклад Середнє
для усереднення значень з кожного робочого листа,
- для консолідації за рядками встановити опцію значення лівого стовпця в секції
Використовувати в якості Імен,
- підсумковий аркуш вже має заголовки стовпців, тому їх можна опустити з посилань на
вихідні робочі листи Проте вихідні посилання повинні містити всі заголовки рядків І стовпці
починаючи від стовпця заголовків рядків (наприклад, "Прізвища") І завершуючи стовпцем, що
позначений Іменем вибраної функції (наприклад, "Середнє") Тому в полі Ссылка необхідно
ввести або вказати за допомогою миші потрібні вихідні діапазони, наприклад
Лист1 !$A$4:$F$8,
Лист2!$А$4:$Р$8,
ЛистЗ!$А$4:$Р$10,
Лист4!$А$4:$Р$6;
- натиснути кнопку ОК, і програма Excel заповнить підсумковий лист Тепер підсумковий лист містить рядки, що відповідають унікальним елементам рядків консолідованих робочих листив (у розглядуваному випадку для кожного працівника)
• Створення зв'язків з початковими робочими листами.
У попередніх прикладах ми консолідували дані за допомогою вибраної функції. У результаті було отримано ряд значень у підсумковому листи. При цьому зміни у вихідних листах не впливатимуть на підсумковий лист доти, поки не буде повторено консолідацію.
Якщо у вікні команди Консолідація з пункту менюДая/ встановити опцію Створювати зв 'яз/а/ з вихідними даними і виконати консолідацію, то в подальшому в разі зміни даних вихідних листив одразу відбуватимуться зміни в підсумковому листи.
Індивідуальне завдання Варіант№3
| A | B | C | D | E | F | |
| 6 | Прізвище | Імя | Посада | Вік | Прибутки | Податки |
| 7 | Антонов | Ілля | Директор | 35 | 60000 | 20000 |
| 8 | Архипов | Дмитро | Маркетолог | 32 | 38000 | 11200 |
| 9 | Бабич | Микола | Економіст | 42 | 42000 | 12800 |
| 10 | Буров | Віктор | Бухгалтер | 48 | 45000 | 1400 |
| 11 | Бобир | Семен | Менеджер | 26 | 32000 | 8800 |
| 12 | Доценко | Олег | Менеджер | 25 | 35000 | 10000 |
| 13 | Дамін | Остап | Програміст | 22 | 38000 | 11200 |
| 14 | Климчук | Орест | Менеджер | 24 | 32000 | 8800 |
| 15 | Петренко | Федір | Секретар | 28 | 26000 | 6400 |
| 16 | Семенов | Петро | Водій | 30 | 24000 | 5600 |