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

Консолідація – це об’єднання даних з різних таблиць, розташованих у різних місцях, які мають однотипні дані в одну таблицю. Команда Данныеà Консолидация(Рис.24)може об'єднувати інформацію з вихідних листів (до 255) в один підсумковий лист. Вихідні листи можуть розміщуватись як у тій самій книзі, що й підсумковий лист, так і в інших.

Рис.4.24. Результат консолідації (Excel)

Команду Консолидацияможна використовувати різними способами:

- створити зв'язки даних у підсумковому листі з вихідними даними, щоб наступні зміни у вихідних аркушах відображалися в підсумковому аркуші;

- консолідувати дані без створення зв'язків — за місцем розташування і категоріями. Розглянемо останні два способи консолідації.

Консолідація за місцем розташування.У цьому разі програма збирає інформацію з однаково розміщених клітинок кожного вихідного листа. При консолідації листів використовують функцію, яку можна вибрати зі списку Функція у вікні команди Консолидацпя. За замовчуванням використовується функція Сумма, яка підсумовує дані з кожного аркуша і розміщує результат у підсумковому листі. Для консолідації вихідних листів у підсумковому листі треба виконати такі дії:

- активізувати підсумковий лист і виділити в ньому діапазон клітинок, куди заноситимуть дані, що консолідуються далі виконати Данные àКонсолидация;

- вибрати функцію для опрацювання даних з кожного робочого листа, наприклад Среднее для усереднення значень з кожного робочого листа;

- опції у вікні діалогу залишити невстановленими;

- клацнути лівою кнопкою миші в полі Ссылка і перейти до першого робочого листа;

- виділити за допомогою миші область для початкового діапазону;

- натиснути кнопку Добавить;

- повторити попередні три пункти для кожного робочого листа.

Програма усереднює підсумкові дані й розміщує їх у підсумковому листі.

Консолідація за категоріями.За основу для об'єднання листів використовують заголовки стовпців або рядків. Наприклад, якщо стовпець "Січень" розміщується в одному робочому листі у стовпці В, а в іншому — у стовпці D, то все одно їх можна об'єднати. Зауважимо, що у підсумковому листі не зазначають заголовки рядків. Вони вставляться автоматично. Для консолідації даних, треба виконати такі дії:

- виділити в підсумковому листі область для розміщення даних. Вона повинна містити порожній стовпець А, щоб програма могла ввести заголовки консолідованих рядків. Ця область повинна містити стільки рядків, скільки унікальних елементів рядків (наприклад, прізвищ працівників) міститься в усіх таблицях àДанные àКонсолидация;

- вибрати функцію для опрацювання даних з кожного робочого листа, наприклад Среднее для усереднення значень з кожного робочого листа;

- для консолідації за рядками встановити опцію значения левого столбца в секції Использовать в качестве имен;

- підсумковий лист вже має заголовки стовпців, тому їх можна опустити з посилань на вихідні робочі листи. Проте вихідні посилання повинні містити всі заголовки рядків і стовпці починаючи від стовпця заголовків рядків (наприклад, "Прізвища") і завершуючи стовпцем, що позначений іменем вибраної функції (наприклад, "Середнє"). Тому в полі Спілка необхідно ввести або вказати за допомогою миші потрібні вихідні діапазони і програма заповнить підсумковий аркуш. Тепер підсумковий лист містить рядки, що відповідають унікальним елементам рядків консолідованих робочих листів (у розглядуваному випадку для кожного працівника).

Створення зв'язків з початковими робочими листами.У попередніх прикладах ми консолідували дані за допомогою вибраної функції. У результаті було отримано ряд значень у підсумковому листі. При цьому зміни у вихідних листах не впливатимуть на підсумковий лист доти, поки не буде повторено консолідацію.

Якщо виконати Данные àКонсолидация à опцію Создавать связи с исходными даннымиі виконати консолідацію, то в подальшому в разі зміни даних вихідних листів одразу відбуватимуться зміни в підсумковому листі.

Питання для самоперевірки

1. Для чого призначені сценарії?

2. Як побудувати сценарій?

3. Для розв'язання задач якого типу використовують Поиск решения?

4. Як запустити Поиск решения?

5. Призначення команди Консолидация.

6. Різновиди команди Консолидация.

7. Що таке зведені таблиці?

8. Як формується макет зведеної таблиці?

9. Які операції можна виконувати над даними у зведених таблицях?

10. Як побудувати діаграму на основі зведеної таблиці?