Урок виробничого навчання :"Використання консолідації даних в електронних таблицях. Створення зведених таблиць та діаграм"

План уроку виробничого навчання

Тема програми :  Виконання робіт з обробки інформації    

Тема уроку: Використання консолідації даних в електронних таблицях. Створення зведених таблиць та діаграм.

Мета уроку:

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

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

Тип уроку: урок засвоєння нових знань та умінь.

Методи проведення уроку: розповідь-пояснення, самостійна робота.

Матеріально-технічне забезпечення уроку: персональні комп’ютери, програмне забезпечення: програма Microsoft Word, ОС Windows 7.

Дидактичне забезпечення уроку:

1. інструкція для роботи з програмою Microsoft Exsel (https://support.office.com),
2. матеріали для самостійної роботи.

 

Хід уроку виробничого навчання

 І. Організаційна частина

1) Перевірка стану обладнання.

2) Перевірка наявності учнів на уроці.

3) Записи в журналі та зошиті допуску до роботи згідно правил т/б.

ІІ. Вступний інструктаж

1. Повідомлення теми програми і теми уроку.
2. Цільова установка уроку.
3. Викладення нового матеріал (додаток 1).
4. Закріплення нового матеріалу.
5. Пояснення та показ найбільш раціональних прийомів і послідовність виконання завдань
6. Пояснення правил охорони праці, техніки безпеки та електробезпеки (додаток 3).
7. Пояснення характеру та призначення наступної роботи, порядку виконання самостійної роботи.
8. Розподіл учнів за робочими місцями, дозвіл приступити до виконання роботи.
9. Самостійна робота (додаток 2).

Ознайомитися із роздатковим матеріалом для самостійної роботи ( додаток 2).

   ІІІ. Поточний інструктаж.

Цільові обходи:

• простежити за своєчасним початком роботи учнів та організацією робочих місць;
• перевірити правильність виконання трудових прийомів;
• простежити за дотриманням правил безпеки праці;
• звернути увагу на самостійне виконання робіт учнями;
• конкретно з кожним учнем обговорити виконання даної роботи

-     надати допомогу учням, яким важко працювати в даній програмі.

4. Заключний інструктаж:

1. Підведення підсумків за урок:
   • дотримання правил з охорони праці;
   • організація робочих місць;
   • дотримання трудової та виробничої дисципліни;
   • досягнення мети уроку.

 

2. Аналіз робіт, виконаних учнями:
   • відзначити учнів, які найкраще працювали продовж уроку;
   • аналіз допущених помилок;
   • оцінювання навчальних досягнень.

 

3. Домашнє завдання:   

                            Майстер виробничого навчання: Романович О. М.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Додаток 1

Теоретичні відомості

      Зведена таблиця - це інструмент для підсумовування даних у програмах візуалізації даних, до яких можна віднести електронні  таблиці  (наприклад,  Microsoft  Excel  та Openoffice.org) або програми клас бухгалтерського обліку або бізнес-аналітики.

     Зведені таблиці можуть:

•      автоматично сортувати дані;
•      підраховувати кількість даних (записів);
•      підводити підсумки;
•      розраховувати проміжні підсумки на підставі даних, які зберігаються в одній таблиці (електронній таблиці, базі даних, в іншому джерелі);
•      виводити результати в іншій таблиці (яка має назву "зведеної")

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

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

    Зведена (тобто результуюча) таблиця   звичайно містить:

•        поля рядків;
•        поля стовпців;
•        дані, які розташовуються на їх перетинанні.

    Агрегування  даних  може відбуватися  не  тільки  шляхом підсумовування значень, але також шляхом підрахунку кількості значень (записів), розрахунків середнього значення, середньоквадратичного відхилення тощо.

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

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

 

 

 

 

 

 

 

 

 

Додаток 3

Самостійна робота

Завдання 1

1.     Запустіть Excel.
2.     Перейменуйте лист 1 на Завдання 1
3.     Побудуйте таблицю згідно зразка(15 найменувань)

Продаж комп’ютерів Asus, Samsung

Тип	Кількість	Ціна,грн	Вартість
ASUS VivoBook Pro N552VW (N552VW-XS76T)	3	29830,00	89490,00
…….

 

3. Створіть зведену таблицю :

1.     Активізувати будь-яку комірку в базі даних.
2.     У меню Вставка у групі Таблицы обрати команду Сводная таблица. Відкриється діалогове вікно Создание сводной таблицы.

 

3.     Перевірити у полі Таблица или диапазон:заданий діапазон, в якому розміщена база даних, та вказати, що звіт зведеної таблиці треба розмістити на новому аркуші. Натисніть кнопку ОК.
4.     На листі, що з’явився, справа представлено майстер формування зведеної таблиці. Виберіть : Тип, Ціну та Вартість.
5.     На екрані з’явиться зведена таблиця та спеціальна група вкладок для роботи зі зведеними таблицями. Перейти на вкладку Конструктор та обрати стиль зведеної таблиці за Вашим бажанням. Закрити вікно майстра формування зведеної таблиці.

Завдання 2

 

1.     Перейдіть на лист 2 та перейменуйте на «Завдання 2 Діаграми»
2.     На  основі зведеної таблиці створіть діаграму відношення типу до вартості та типу до кількості.

1.     Активізувати будь-яку комірку зведеної таблиці.
2.     У меню Вставка у групі Таблицы обрати команду Сводная диаграма. Відкриється діалогове вікно Создание сводной диаграми.
3.     Вибрати Тип та Ціну; Тип та Вартість
4.     Відредагуйте за бажанням.

 

Завдання 3

1. Перейдіть на лист 3 та перейменуйте на Завдання 3
2. Створіть таблицю за зразком

Назва підприємства	Рік	Доходи за рік, грн	Витрати за рік, грн	Прибуток від продажів, грн	Податок на прибуток, 25%
Будмайстер	2010	250 000,00	150 000,00	75 000,00	18750,00
…….

 

1.     Активізувати будь-яку комірку в базі даних.
2.     У меню Вставка у групі Таблицы обрати команду Сводная таблица. Відкриється діалогове вікно Создание сводной таблицы.
3.     Перевірити у полі Таблица или диапазон:заданий діапазон, в якому розміщена база даних, та вказати, що звіт зведеної таблиці треба розмістити на новому аркуші. Натисніть кнопку ОК.
4.     На листі, що з’явився, справа представлено майстер формування зведеної таблиці. Виберіть :  Назва підприємства, Доходи, Витрати, Прибуток, Податок на прибуток.

 

Завдання 4

1. Перейдіть на лист 4 та перейменуйте Завдання 4 Діаграми
2. На  основі зведеної таблиці створіть діаграму.

1.                    Активізувати будь-яку комірку зведеної таблиці.
2.                   У меню Вставка у групі Таблицы обрати команду Сводная диаграма. Відкриється діалогове вікно Создание сводной диаграми.
3.                    Вибрати Назва підприємства, Доходи, Витрати, Прибуток, Податок на прибуток.
4.                   Відредагуйте за бажанням.

Завдання 5

1. Перейдіть на лист  та перейменуйте Завдання 5
2. Створіть таблицю за зразком (15 найменувань)

Дата
Біржовий курс євро
Комп'ютер Asuss, Комп'ютер Samsung
Комплектуючі	Ціна, євро	Ціна, грн.
Материнська плата	120,00	?
……..

1.     Активізувати будь-яку комірку в базі даних.
2.     У меню Вставка у групі Таблицы обрати команду Сводная таблица. Відкриється діалогове вікно Создание сводной таблицы.
3.     Перевірити у полі Таблица или диапазон:заданий діапазон, в якому розміщена база даних, та вказати, що звіт зведеної таблиці треба розмістити на новому аркуші. Натисніть кнопку ОК.
4.     На листі, що з’явився, справа представлено майстер формування зведеної таблиці. Виберіть : Комплектуючі, Ціна євро, Ціна грн.

 

 

Завдання 6

1. Перейдіть на лист 4 та перейменуйте Завдання 6 Діаграми
2. На  основі зведеної таблиці створіть діаграму.

1.                    Активізувати будь-яку комірку зведеної таблиці.
2.                   У меню Вставка у групі Таблицы обрати команду Сводная диаграма. Відкриється діалогове вікно Создание сводной диаграми.
3.                    Вибрати Назва Відношення всіх комплектуючих до ціни в гривні
4.                   Відредагуйте за бажанням.

Завдання 7

Консолідація – це об’єднання даних з різних діапазонів.

1.                        Додайте Лист  з назвою Консолідація.

Створіть на трьох аркушах три таблиці

Перший аркуш назвіть Продожі Samsung та створіть таблицю згідно зразка

Назва товару	Ціна в січні, грн	Ціна в лютому, грн	Ціна в березні, грн	Ціна в квітні, грн
Телевізори
Холодильники
Пральні машини

 

 

Другий аркуш назвіть Продожі LG та створіть таблицю згідно зразка

Назва товару	Ціна в січні, грн	Ціна в лютому, грн	Ціна в березні, грн	Ціна в квітні, грн
Телевізори
Холодильники
Пральні машини

 

Третій аркуш назвіть Продож Gorenje і  та створіть таблицю згідно зразка

Назва товару	Ціна в січні, грн	Ціна в лютому, грн	Ціна в березні, грн	Ціна в квітні, грн
Телевізори
Холодильники
Пральні машини

 

В пункті меню Данные виберіть Консолидация. В діалоговому вікні Консолидация, що з'явилося, в полі Функция виберіть Минимум. Клацніть в полі Ссылка. Виділіть діапазон клітинок Першого аркушу. Натисніть кнопку Добавить.

 Поверніться до поля Ссылка, очистіть його, виділіть діапазон клітинок другого аркушу. Натисніть кнопку Добавить. Аналогічно додайте значення по постачальнику, що залишився – аркуш третій.

 В полі Использовать в качестве имени поставте прапорець Значения левого столбца. Натисніть кнопку ОК.

 Виділіть отриману таблицю. В пункті меню Формат виберіть команду Ячейки. На вкладці Граница виберіть тип ліній – жирна, виберіть лінії – зовнішні, колір – червоний. Натисніть кнопку ОК.

 

Завдання 8

 

1. Перейдіть на лист  та перейменуйте Завдання 8
2. Використовуючи засіб Подбор параметра та дані таблиці знайдіть, за якого значення терміну вкладу сума виплат стано­витиме 12 000 грн.

 

 

	А	В	С
11		Розрахунок виплат за вкладами
12		Розмір вкладу (грн, V)	4000,00
13		Термін вкладу (роки, Т)	5,00
14		Відсоткова ставка (%, Р)	12,00
15		Коефіцієнт нарощування (k)
16		Сума до виплати (грн, S)

 

 

1. Виділіть комірку, в якій міститься значення суми виплат,
обчислене за формулою:

Для заданої таблиці це буде комірка С16.

2. Меню Дание → Подбор параметра. У діалоговому вікні Подбор параметра потрібно :

а) перевірити, чи задана адреса комірки С16, яка містить значення суми до виплат, у текстовому полі Установить в ячейке;

б) встановити текстовий курсор у текстове віконце Значение і ввести з клавіатури потрібне значення, у цьому прикладі 12 000;

 в) встановити текстовий курсор у текстове віконце Изменяя значение ячейки і клацнути на кнопку комірки, в котрій міс­титься значення терміну вкладу С13;

г) клацнути на кнопку ОК.

 

 

 

 

 

3. Проаналізуйте таблицю. Яке значення терміну вкладу з’явилось у комірці С13?

 

Завдання 9

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

 

	А	В	С
11		Розрахунок виплат за вкладами
12		Розмір вкладу (грн, V)	4000,00
13		Термін вкладу (роки, Т)	5,00
14		Відсоткова ставка (%, Р)	12,00
15		Коефіцієнт нарощування (k)
16		Сума до виплати (грн, S)

 

Розв’язання виконайте самостійно. Використайте наведе­ний для завдання 8 алгоритм, замінюючи в ньому комірку зна­чення терміну вкладу на комірку значення відсоткової ставки.

 

Завдання 10

1. Дайте імена робочим аркушам – Київ, Львів, Суми.

2. Уведіть на кожному аркуші дані, як показано на рисунках

3. На робочому аркуші Консолідація  створіть заголовок для консолідованих даних: Обсяг продажу у 20__році.

4. Виділіть клітинку, починаючи з якої вставлятимуться консолідовані дані.

5. Увійдіть в меню Данные-Консолидация.

6. У діалоговому вікні Консолидация  потрібно:

a. у полі Функция вибрати зі списку Сумма;

b. у полі Ссылка встановити текстовий курсор і перейти на робочий аркуш Київ , виділити на ньому клітинки А2:Е6, після чого натиснути на кнопку Добавить  діалогового вікна Консолидация;

c. повторити дії пункту b для робочого аркуша Львів;

d. повторити дії пункту b для робочого аркуша Суми, але на кнопку Добавить не натискати;

e. встановити прапорці у віконцях індикаторних перемикачів як на зразку вікна, клацнути на кнопку ОК

 

  

                     Діалогове вікно Консолидація

 

7. Надайте виразності отриманій таблиці консолідованих даних. Відтворіть у таблиці дані діапазонів консолідації.

 

 

 

 

 

 

 

 

Додаток 3

Інструкція з техніки безпеки

Вимоги безпеки перед початком роботи

ЗАБОРОНЯЄТЬСЯ

•             заходити в клас у верхньому одязі;

•             приносити на робоче місце особисті речі;
•             класти різні предмети або одяг на ПЕОМ і ін. обладнання;
•             самостійно без вказівки майстра вмикати і вимикати пристрої;
•             чіпати сполучні кабелі, від’єднувати і приєднувати роз'єми;
•             дозволяється працювати тільки за вказівкою майстра в/н.

Вимоги безпеки під час роботи

ПІД ЧАС РОБОТИ ЗАБОРОНЯЄТЬСЯ:

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

ПІД ЧАС РОБОТИ НЕОБХІДНО

•                 акуратно поводитися з апаратурою;
•                 працювати з клавіатурою чистими руками, на клавіші тиснути не сильно, не допускаючи ударів;
•                 у разі виникнення несправності повідомити майстру;
•                   не намагатися самостійно проводити регулювання або усувати несправності

апаратури.

Вимоги безпеки після закінчення роботи

ВИМОГИ БЕЗПЕКИ ПІСЛЯ ЗАКІНЧЕННЯ РОБОТИ

•                Після закінчення роботи про недоліки і несправності, виявлені під час роботи,

необхідно зробити записи у відповідних журналах і повідомити майстра;

•                Після закінчення, на робочому столі не повинні залишатися зайві предмети.