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

1. Призначення зведеної таблиці.

2. Як працює Майстер зведених таблиць?

3. Поясніть призначення всіх областей вікна Макет Майстра зведених таблиць(Страница, Строка, Столбец, Данные).

4. Як коригувати зведену таблицю ?

5. Як в зведеній таблиці створити нове поле, що обчислюється?

5.3 Індивідуальні завдання

Варіант 1

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

Варіант 2

Створити зведену таблицю, в якій для кожного будинку вивести окремо кількість мешканців, що розраховувалися кредиткою, та кіль­кість мешканців, що розраховувалися готівкою.

Варіант 3

Створити зведену таблицю, в якій для кожного магазину ви­вести замовлену кількість книжок кожного видавництва.

Варіант 4

Створити зведену таблицю, в якій для кожного автопарку ви­вести середню відстань поїздок в кожному класі таксі.

Варіант 5

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

Варіант 6

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

Варіант 7

Створити зведену таблицю, в якій для кожної країни – виробни­ка і для кожного з найменувань ліків вивести їх вартість.

Варіант 8

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

Варіант 9

Створити зведену таблицю, в якій для кожної групи товарів вивести останню дату поставки до кожного магазину.

Варіант 10

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

Варіант 11

Створити зведену таблицю, в якій для кожної АТС вивести середню тривалість для кожного типу розмов.

Варіант 12

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

Варіант 13

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

Варіант 14

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

Варіант 15

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

Лабораторна робота № 6

Тема роботи: Консолідація таблиць в Excel

Мета роботи: навчитися обробляти дані, розташовані на різних листах, вибирати підсумкові функції, встановлювати зв'язки.

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

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

Розглянемо це на прикладі аналізу заробітків за 3 місяці. Для цього в робочій книзі, крім існуючого Листа Січень, створимо ще Листи Лютий і Березень.

Скопіюємо з Листа Січень дані на Листи Лютий і Березень. По­тім змінимо в деяких робітників кількість відпрацьованих днів, відпо­відно зміняться і значення стовпчика Одержати. Можна змінити роз­ряд, можна звільнити декілька робітників, тобто вилучити відповідні рядки на Листах Лютий або Березень, можна прийняти на роботу но­вих робітників. Наприклад, з Листів Лютий та Березень вилучимо за­писи про робітника Кирієнко В.Н., а в березні приймемо на роботу но­вого робітника Семенова С.С. (див. рис. 6.1).

При консолідації по категорії всі діапазони повинні задовольня­ти вимогам для списків, а саме:

• перший рядок повинен містити заголовки;

• список не може мати вільних рядків або стовпчиків;

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

Дані для консолідації розташовані на різних Листах, але прізви­ща знаходяться в стовпчику В, а числові дані – в стовпчику Н. Тому змінимо розташування даних на Листах так, щоб стовпчики були по­руч. Вставимо вільний стовпчик праворуч від прізвищ робітників і пе­ретягнемо туди стовпчик Одержати (див. рис. 6.1).

Рисунок 6.1 –Фрагменти даних для консолідації

Створимо новий Лист з ім'ям Квартал1. Встановимо курсор в клітинці А1. Потім в меню Данные виберемо пункт Консолидация. Відкриється вікно Консолидация (див. рис. 6.2).

В полі Функция виберемо функцію Сумма (при консолідації даних можуть використовуватися різні функції: Сумма, Количество, Среднее, Максимум, Минимум, і т.д.). Потім визначимо області – дже­рела, які потрібно консолідувати. Для цього клікнемо мишкою в полі Ссылка, перейдемо на Лист Січень, виділімо клітини В3 : С18 (див. рис. 6.1) і натиснемо кнопку Добавить. Повторимо дії для кожного діапазону. Отримаємо Список диапазонов:

Рисунок 6.2 –Введення діапазонів для консолідації

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

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

Структура дозволяє побачити всі дані, для яких підраховувались підсумки, або сховати їх. Наприклад, на рис. 6.3 видно, що робітники Кирієнко В.Н. та Семенова С.С. працювали по одному місяцю, а Іванов І.І., як і всі інші –3 місяці..

Якщо на всіх Листах прізвища робітників однакові, то можна використовувати консолідацію по розміщенню. Це найпростіший спо­сіб консолідації. Для її виконання можна було б залишити на місці числові дані, тобто в діапазоні H4 : H18. Потім на новому Листі в клітинку А1 ввести заголовок П.І.Б., в клітинку В1 – заголовок Одержати та скопіювати всі прізвища в стовпчик А.

Рисунок 6.3 –Результати консолідації

Встановимо курсор в клітинку В2, тобто позначимо першу клі­тинку області виведення. Потім в меню Данные виберемо пункт Кон­солидация і у вікні Консолидация задамо список діапазонів:

Січень!H4 : H18

Лютий!H4 : H18

Березень!H4 : H18