Консолідація даних засобами ЕТ

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

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

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

Консолідація даних по розташуванню

Діапазони, дані яких консолідуються і поміщаються у вказаний кінцевий діапазон, називаються вихідними областями. Вихідні області можуть розташовуватися на будь-якому аркуші або книзі, на інших відкритих листах або книгах, а також на листах Lotus 1-2-3.

Якщо у всіх відомостях кількість упаковок Рибні продукти наводиться у вічку F3, то при консолідації в підсумковій ведомости будуть узагальнені значення, що зберігаються в цьому вічку у всіх відомостях. Області консолідації

Мал. 18.15 Діалогове вікно Консолідація можна задати або тривимірними формулами, або в полі Заслання (Reference) (мал. 18.15).-

У діалоговому вікні Консолідація в списку, що розкривається Функція (Function) виберіть підсумкову функцію, яку слід використовувати для обробки даних, наприклад Сума (Sum) якщо підсумовуватимуться значення. Опис функцій, приведених в списку, даний в таблиці 18.3.

Таблиця 18.3. Підсумкові функції, використовувані при побудові звідних таблиць і звідних діаграм

Операція

Призначення

Сума (Sum)

Обчислення суми чисел, що зберігаються у вихідних вічках. Ця операція використовується за умовчанням для підведення підсумків по числових полях

Кількість (Count)

Кількість записів або рядків даних. Ця операція використовується за умовчанням для підведення підсумків по нечислових полях

Середнє (Average)

Обчислення середнього числа за даними, що зберігаються у вихідних вічках

Максимум (Мах)

Визначення максимального числа за даними, що зберігаються у вихідних вічках

Мінімум (Min)

Визначення мінімального числа за даними, вч, що зберігається, вихідних вічках

Твір (Product)

Обчислення твір чисел, що зберігаються у вихідних вічках

Кількість чисел (Count Nums)

Кількість записів або рядків, що містять числа

Зміщене відхилення (StdDev)

Зміщена оцінка стандартного відхилення генеральної сукупності по вибірці даних

Незміщене відхилення (StdDevp)

Обчислення стандартного відхилення генеральної сукупності по вибірці даних, що зберігаються у вихідних вічках

Зміщена дисперсія (Var)

Зміщена оцінка дисперсії генеральної сукупності по вибірці даних

Незміщена дисперсія (Varp)

Незміщена оцінка дисперсії генеральної сукупності по вибірці даних

Після установки курсора в полі Заслання (Reference) введіть заслання на перший діапазон даних, який консолідується у вказаний кінцевий діапазон. Заслання введіть уручну або, якщо аркуш, що містить нову вихідну область, є поточним, виділите на нім вихідну область. Якщо вихідні області і область призначення знаходяться на різних листах, використовуйте ім'я аркуша і ім'я або заслання на діапазон. Аби прибрати діалогове вікно Консолідація (Consolidate) на час вибору вихідної області, натискуйте кнопку Скрутити діалогове вікно (Collapse dialog) у правій частині поля. Повторне натиснення на цю кнопку відновлює вікно.

Якщо вихідні дані знаходяться в іншій книзі, яка в даний момент закрита, натискуйте кнопку Огляд (Browse) і виберіть книгу. Дорога до вибраного заслання відображуватиметься в полі Заслання (Reference). Потім додайте заслання.

Натискуйте кнопку Дoбaвum'(Add). Введене заслання відображуватиметься у вікні Список діапазонів (All references). Повторите цю операцію для всіх вихідних областей, що консолідуються.

Аби автоматично оновлювати підсумкову таблицю при зміні джерел даних, встановите прапорець Створювати зв'язки з вихідними даними (Create links to source data). Зв'язку не можна використовувати, якщо вихідна область і область призначення знаходяться на одному аркуші. Після установки зв'язків не можна додавати нові вихідні області і змінювати вихідні області, що вже входять в консолідацію.

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

Консолідація даних по категоріях

Виберіть із списку, що розкривається Функція (Function) функцію, яку слід використовувати для обробки даних (мал. 18.15). Використовувані підсумкові функції приведені в таблиці 18.3. Введіть вихідну область даних, що консолідуються, в поле Заслання (Reference). Переконаєтеся, що вихідна область має заголовок.

Натискуйте кнопку Додати (Add) для додавання діапазону до списку вихідних діапазонів консолідації. Повторите цю операцію для всіх вихідних областей, що консолідуються. У наборі прапорців Використовувати як імена (Use labels in) встановите прапорці, відповідні розташуванню у вихідної області заголовків: у верхньому рядку, в лівому стовпці або у верхньому рядку і в лівому стовпці одночасно.