Завдання 4.2

1. Скопіюйте таблицю 1 Магазин безмитної торгівлі на новий лист, перейменуйте його на Підсумки. Втаблиці видаліть останній підсумковийрядок Усього ( виділіть рядок – контекстне меню – Удалить).

2. Знайдіть сумарну вартість товарів по кожному найменуванню. Для цього:

- відсортуйте таблицю по полям Назва товару, Вартість, од. (Данные – Сортировка);

- встановіть курсор в будь-яку комірку таблиці і на вкладці Данные в групі Структура оберіть команду Промежуточный итог;

- в діалоговому у вікні Промежуточные итоги вкажіть наступні умови (рис. 14) та натисніть ОК.

Рис. 14. Діалогове вікно Промежуточные итоги

- в результаті отримаєте сумарну вартість товарів по кожному найменуванню (рис. 15).

Рис. 15. Підсумки по вартості товарів

3. На вкладці Данные в групі Структура оберіть команду Промежуточный итог – Убрать все.

4. По кожному найменуванню товару знайдіть середню вартість од. Для цього:

- відсортуйте таблицю по полям Назва товару, Вартість, од. (Данные – Сортировка);

- встановіть курсор в будь-яку комірку таблиці і на вкладці Данные в групі Структура оберіть команду Промежуточный итог;

- в діалоговому у вікні Промежуточные итоги вкажіть наступні умови (рис. 16) та натисніть ОК;

Рис. 16. Діалогове вікно Промежуточные итоги

- в результаті отримаєте середню вартість од. по кожному найменуванню (рис. 17).

Рис. 17. Підсумки по обсягу товарів

5. Консолідація таблиць у Excel.

Одним із способів отримання звітної інформації є консолідація даних. Консолідація – це об’єднання даних, що представлені в певних областях-джерелах (рис. 18) і виведення їх в область призначення.

При консолідації даних можуть використовуватися різноманітні функції: підсумовування, розрахунку середнього арифметичного, підрахунків максимальних і мінімальних значень, тощо.

Області-джерела можуть знаходитись на одному листу, на різних листах, в різних робочих книгах. Всі ці таблиці повинні мати ідентичну структуру. В консолідації можуть приймати участь до 255 областей-джерел.

Результат консолідації знаходиться в області-призначення.

Звітна таблиця буде мати лише 1 рядок з ключовим полем, а числові дані в ній будуть сумами (чи іншими функціями) всіх рядків об’єднаних джерел. Ключовим полем вважається саме ліве поле (колонка) таблиці.

Рис. 18. Представлення про консолідацію даних

Варіанти консолідації даних:

Ø за допомогою формул, де використовуються посилання;

Ø по розташуванню даних для однаково організованих областей-джерел;

Ø по категоріям для різних за структурою даних;

Ø за допомогою зведеної таблиці;

Ø консолідація зовнішніх даних.

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

- всі області на одному листі – в посиланні вказується адреса блоку комірок, наприклад D1:C8;

- області на різних листах – вказується назва листа, діапазон, наприклад лист1!D1:лист 2!C8;

- області в різних книгах, на різних листах – вказується назва книги, назва листа, діапазон, наприклад [книга1] лист1!D1: [книга2] лист 2!C8.

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

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

Виконати команду: Данные – група Работа с данн ыми - Консолидация – вибирається варіант і задаються умови консолідації.

При консолідації по категоріям області-джерела містять однотипні дані, але організовані в різних областях-джерелах неоднаково.

Для консолідації даних використовуються імена рядків та\або стовпців (імена включаються в виділені області-джерела).

Далі виконують команду: Данные – група Работа с данн ыми - Консолидация – вибирають варіант і задають умови консолідації.

Умову консолідації задають в вікні Консолидация, у вікні Функция обирають функцію консолідації даних.

Для кожної області-джерела будують посилання, для чого курсор встановлюють в полі посилання, а потім переходять до області-джерела для виділення блоку комірок та натискують на кнопку Добавить.

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

При консолідації зовнішніх даних в діалоговому вікні Консолідація слід натиснути кнопку Обзор, потім обрати файл, що містить області-джерела для додавання до списку, а потім додати ссылка на ячейку чи вказати ім’я блоку клітинок.

Перемикач Создать связи с исходными данными створює зв’язки області призначення з областями-джерелами. При змінах в області призначення автоматично обновлюються результати консолідації. Але не можна змінювати посилання на області-джерела, якщо встановлений прапорець – перемикач Создать связи с исходными данными.