Консолідація даних
Мета:Ознайомитися з командою Консолідація даних Навчитися застосовувати консолідацію для одержання підсумкової Інформації
Теоретичні відомості
Команда Консолідація з меню Дат може об'єднувати Інформацію з вихідних листив (до 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 | Бабич | ht: none; padding-top: 0in; padding-bottom: 0in; padding-left: 0.01in; padding-right: 0in;">Микола | Економіст | 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 |