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

а

б

Мал. 9.5. Дані про продаж: а – магазин «Гейм»; б – магазин «Обрій»

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

Звернемося знову до списку продажу ігор у двох магазинах, але замість загального списку (мал. 9.3) сформуємо два списки на окремих робочих аркушах (мал. 9.5,а,б). Надайте цим аркушам імена, наприклад, назви магазинів «Гейм» і «Обрій». Нас цікавитимуть консолідовані дані – це загальні підсумки продажу в двох магазинах.

Для консолідації даних виконайте такі операції:

· Створіть окремий аркуш для консолідованих даних і активізуйте верхню ліву комірку ділянки, призначеної для розміщення цих даних (наприклад, А1).

· Оберіть команду меню Данные→Консолидация й у діалозі Консолидация (мал. 9.6) у полі Функція задайте операцію для підбиття підсумків. У нашому прикладі буде встановлена за замовчуванням Сумма.

· У поле Ссылка введіть першу частину початкових даних, що консолідуються (у даному прикладі – це діапазон для списку на мал. 9.5,а). Цю частину можна задати набором із клавіатури, але простіше – прийомом виділення. Для цього натисніть кнопку праворуч від поля Ссылки, – діалог Консолидация згортається до розміру однорядкового поля введення, звільняючи робочу частину аркуша. Обведіть мишею комірки, що консолідуються, включаючи назви стовпців і натисніть на кнопку справа від поля введення. У результаті в полі Ссылка діалогу Консолидация з’явиться запис посилання (у ньому зазначене ім’я аркуша, а потім через знак «!» – абсолютні адреси комірок діапазону). Натисніть кнопку Добавить.

· Аналогічно задайте другу частину початкових даних, що розміщається на аркуші «Обрій» (мал. 9.5,б). Оскільки діапазони комірок на двох аркушах розташовані однаково, виділяти діапазон на другому аркуші не потрібно. Досить перейти на аркуш «Обрій», а потім при поверненні в діалог Консолидация натиснути кнопку Добавить.

· Для визначення імен даних, що консолідуються, установіть опцію Значения левого столбца (мал. 9.6).

Мал. 9.6. Діалог Консолидация

· Встановіть опцію Создавать звязи с исходными данными. Тоді дані в консолідованій таблиці оновлюватимуться при зміні вихідних даних.

· На завершення натисніть кнопку Ok.

На зазначеному вами аркуші буде відображена таблиця з консолідованими даними (мал. 9.7). Ця таблиця буде структурована й у ній з’явиться додатковий стовпець В з назвою робочої книги. Якщо клацнути по будь-якій із кнопок розгортання структури (кнопки «+»), з’являться додаткові рядки, що відображають початкові дані для кожного з магазинів (мал. 9.7).

Зазначимо, що після операції консолідації первинних таблиць, показаних на мал. 9.5, у підсумковій таблиці з’являється стовпець, що відповідає цінам комп’ютерних ігор. Оскільки додавання цін не має значення, цей стовпець потрібно видалити окремою командою (Правка→Удалить).

Можете переконатися у наявності зв’язку з початковими даними. Для цього перейдіть на аркуш магазина «Гейм» або «Обрій» і змініть будь-які числа в стовпці Продано. Потім поверніться на аркуш із консолідованими даними, – ви побачите, що підсумкові дані обновилися.

Мал. 9.7. Таблиця консолідованих даних

Контрольні запитання

1. Що таке структура списку?

2. Як створити структуру? Які елементи має структура?

3. Як виконати підбиття проміжних підсумків?

4. Як видалити проміжні підсумки?

5. Що розуміють під консолідацією даних?

6. Чим консолідація відрізняється від створення проміжних підсумків?

7. Як виконати консолідацію даних кількох таблиць?

Практикум. Таблиця продажу комп’ютерних ігор

Для ілюстрації процедур обробки даних у ЕТ нам знадобиться таблиця-приклад. Звернемося до списку, який ви створили раніше, вивчаючи процеси упорядкування і фільтрації (мал. 7.1). На основі цього списку складемо нову таблицю продажу комп’ютерних ігор у магазинах «Гейм» і «Обрій» (мал. 9.8).

Створіть у робочій книзі новий аркуш, якому надайте ім’я «Продаж». Потім виконайте такі дії:

1. Скопіюйте всю таблицю ігор (мал. 7.1) на новий аркуш через буфер обміну.

2. Приберіть стовпець Жанр і додайте стовпці: Магазин, Продано і Сума. Зменшіть розмір списку, видаливши деякі рядки. У нашому випадку на мал. 9.8 залишено лише 6 позицій (Атлантида 2 … Космічні війни).

Мал. 9.8. Таблиця продажу ігор у магазинах «Гейм» і «Обрій»

3. Скопіюйте рядки 2 – 7 у рядки 8 – 13. Введіть назви магазинів до комірок А2 і А8 і скопіюйте їх до нижніх комірок перетягуванням маркера заповнення.

4. Заповніть стовпець Продано числами, а до комірки Е2 стовпця Сума введіть формулу =C2*D2. Скопіюйте цю формулу з нижньої комірки стовпця перетягуванням маркера заповнення.

У результаті ви отримаєте таблицю, показану на мал. 9.8. У ній стовпець Сума є добутком стовпців Ціна і Продано.