Лаб.р23(консолідація даних)

Предмет	Інформаційні технології та технічні засоби навчання	Лабораторна робота № 23
Тема заняття	Консолідація даних в таблицях.
Мета заняття	Розглянути поняття консолідації даних, види консолідації, навчити консолідувати дані за розміщеннями та категоріями; сприяти розвитку пам’яті, логічного мислення, уваги; виховувати культуру поведінки та мови.
Вид заняття	Лабораторна робота
Тип заняття	Засвоєння нових знань Формування нових умінь і навичок
Забезпечення заняття	Навчально-методична картка заняття Інструктивна картка виконання лабораторного завдання
Студенти повинні мати уявлення	Поняття про консолідацію даних за розмішенням та категоріями.
Студент повинен вміти	Створювати консолідацію даних за розміщенням та категоріями.
Література	Intel® Навчання для майбутнього. – К.: Видавництво «Нора-прінт», 2005 Морзе Н.В. Основи інформаційно-комунікаційних технологій. – К.: Видавнича група ВНV, 2006. -352с. Інформаційні технології в навчанні. – К.: Видавнича група ВНV, 2006. -240с. Організація служби підтримки в школі. – К.: Видавнича група ВНV, 2006. -224с. Основи комп’ютерних мереж та Інтернету. – К.: Видавнича група ВНV, 2006. -256с. Основи програмування. – К.: Видавнича група ВНV, 2004. -288с.

ХІД ЗАНЯТТЯ

1. Вступна частина

1.1. Перевірка самостійного опрацювання

1.2. Актуалізація системи опорних знань

1.3. Мотивація навчальної діяльності

Дуже часто при роботі з таблицями виникає необхідність об’єднати дані, які знаходяться на різних аркушах або навіть різних книгах. В такому випадку потрібно використовують команду меню Дані – Консолідація. Наприклад, якщо ми маємо аркуші пропуски занять студентами групи, то для отримання підсумкових даних пропусків за семестр, можна використати консолідацію.

1.4. Повідомлення теми та мети заняття

1.5. План

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

2.2.1. Завдання на лабораторне заняття: 2

2. Основна частина

2.1. Сприйняття студентами нового матеріалу

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

Існують два види консолідації даних: за розміщенням та за категоріями.

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

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

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

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

• Можна зв’язати дані в підсумковому аркуші з вихідними даними, щоб наступні зміни в вихідних аркушах відображалися в підсумковому аркуші.

• Можна просто консолідувати дані без створення зв’язків.

Консолідувати дані можна за розташуванням і за категоріями.

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

1. активізувати підсумковий аркуш і виділити в ньому діапазон комірок, куди будуть заноситися дані, що консолідуються (наприклад, B4:F8);

2. виконати команду Консолидация з пункту меню Данные;

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

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

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

6. виділити мишею область для початкового діапазону (B4:F8);

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

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

9. натиснути кнопку ОK.

Excel усереднює підсумкові дані і розташовує їх у підсумковому аркуші.

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

10. виділити у підсумковому аркуші область для розташування даних. Вона повинна містити порожній стовпець A, щоб Excel міг ввести заголовки консолідованих рядків. Ця область повинна складатися із стількох рядків, скільки є унікальних елементів рядків (наприклад, прізвищ працівників) у всіх таблицях;

11. виконати команду Консолидация з пункту меню Данные;

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

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

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

Лист1!$A$4:$F$8,

Лист2!$A$4:$F$8,

Лист3!$A$4:$F$10,

Лист4!$A$4:$F$6;

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

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

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

2.2. Осмислення і усвідомлення студентами нового матеріалу

2.2.1. Завдання на лабораторне заняття:

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

2. Відкрийте книгу Excel з назвою «Лаб.р23(консолідація)

3. Введіть формули для підрахунку пропусків, використовуючи автосуму та функцію «сумесли».

4. Скопіюйте дані на п’ять наступних аркушів, змінюючи назву місяця, на останньому назва «за семестр». Переіменуйте листи книги.

5. Введіть на всіх аркушах дані про пропуски та їх причину.

6. Для підрахунку пропусків за семестр застосуйте консолідацію.

3. Заключна частина:

3.1. Узагальнення і систематизація знань, запам’ятання, закріплення знань

3.2. Підсумки заняття

3.2. Повідомлення завдання самостійного опрацювання

Intel® Навчання для майбутнього. – К.: Видавництво «Нора-прінт», 2005