неділя, 17 жовтня 2021 р.

10 клас

08.11

Консолідація даних. Зведені таблиці

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

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

Консолідація даних - це пошук та групування записів за якимись ознаками. Наприклад, є магазин або склад: приходить покупець і купує якісь товари. Потім приходить наступний і купує інші товари. Третій, четвертий ... А тепер запитання: а скільки кожного товару було продано за день? Консолідація як раз і вирішує такі запитання.

Рис. 1. Вихідні дані (зліва) і вихідні дані (праворуч)

Рис. 2. Вибір даних

Перед консолідацією нічого попередньо виділяти не потрібно. Виконайте команду Дані - Об'єднати. Після цього відкривається вікно Об'єднати. Приклад наведено на рисунку (Рис. 1): вихідні дані розташовані в лівій частині робочого аркуша. Натисніть на кнопку Згорнути / Розгорнути (

) праворуч від поля Вихідний діапазон даних. Виділіть комірки A2:B5. Натисніть на кнопку Згорнути / Розгорнути (

) для повернення у вікно Об'єднати. Натисніть на кнопку Додати. Так само додайте діапазони клітинок A7:B10, A12:B15.
Переходимо до вибору діапазону, в який будуть поміщатися результуючі записи. Натисніть на кнопку Згорнути / Розгорнути (

) праворуч від поля Помістити результат у. Виділіть клітинку D2. Може виникнути запитання, чи поміститься у нас все в одній комірці? Ні, ми тільки вказуємо верхню ліву межу вихідного діапазону, тобто клітинку, за якою буде заповнюватися таблиця із результатами. Натисніть на кнопку Згорнути / Розгорнути (

) для повернення у вікно Об'єднати.
Натисніть на кнопку Ще для відкриття панелі з додатковими параметрами. Встановіть прапорці в опції Заголовкам рядків і Заголовкам стовпців. Це потрібно для того, щоб програма взяла з вихідних даних заголовки рядків і стовпців, інакше потім їх доводиться вводити вручну. При бажанні можна пов'язати вихідну таблицю з вихідними даними, встановивши прапорець в опцію Зв'язати із вихідними даними. В цьому випадку, при зміні даних в вихідним таблицях в вихідний таблиці результати також автоматично зміняться. Натисніть на кнопку ОК для розрахунку.

2. Зведені таблиці

Зведена таблиця слугує для об'єднання, порівняння і аналізу великих

об'ємів даних. Можна переглядати різноманітні вибірки по вихідних даних,

відображати детальні відомості з областей, що нас цікавлять, а також

створювати звіти.

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

   Зведена таблиця — це інтерактивна таблиця, за допомогою якої знаходять підсумкові значення для великих обсягів даних, використовуючи обраний формат та метод обчислення. Її використовують для швидкого підведення підсумків або об’єднання великих обсягів даних. Міняючи місцями рядки і стовпці, можна створити нові підсумки вихідних даних; при відображенні різних сторінок можна до того ж здійснити фільтрування даних, а також відобразити детально дані області.
   Зведену таблицю можна створити на основі даних, що знаходяться у списках чи електронних таблицях, однак їх використання є доцільним тоді, коли дані в таких таблицях повторюються, що дозволяє їх групувати на основі використання деякої вбудованої функції, тобто підводити підсумки.
   Для створення зведеної таблиці призначений Майстер зведених таблиць і діаграм, викликати який можна за допомогою вказівки Дані/Зведена таблиця.
   При побудові зведеної таблиці на основі списку, створеного в MS Excel, необхідно задати діапазон даних, що береться за основу, вказати макет та параметри зведеної таблиці.
   Макет зведеної таблиці містить 4 області: Сторінка, Стовпець, Рядок і Дані. Справа розташовані кнопки з категоріями таблиці, які можна переміщувати в потрібні області, при цьому необов’язково використовувати всі категорії вихідної таблиці.

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

   Зведену таблицю можна розмістити на окремому аркуші робочої книги або на існуючому. При роботі зі зведеною таблицею з’являються панель інструментів Зведені таблиці та Список полів зведеної таблиці, за допомогою яких можна вносити зміни до зведених таблиць.
   В середовищі MS Excel не можна редагувати дані безпосередньо у зведених таблицях. Для цього треба змінити дані у вихідній таблиці, але ці зміни автоматично не вносяться в зведену таблицю. Щоб внести зміни, необхідно на панелі інструментів Зведені таблиці натиснути кнопку Оновити дані.
   Всі назви, що використовуються у зведеній таблиці, можна змінити на інші, для цього достатньо в потрібну клітинку ввести нову назву замість старої.

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

   Поле рядка — це поле вихідного списку чи таблиці, яке розміщується до області рядків зведеної таблиці. У цьому прикладі полем рядка є Магазин. Значення цього поля у вихідній таблиці повторюються, тобто в таблиці існують кілька записів з однаковою назвою магазинів: Всесвіт (повторюється 8 разів), Сатурн (повторюється 5 разів), Континент (повторюється 9 разів) та ін. При створенні зведеної таблиці ці дані групуються за значеннями даних, що повторюються, для даного прикладу — за назвами магазинів.
   Поле стовпця — це поле вихідного списку, яке розміщується до області стовпців. У цьому прикладі полем стовпця є Жанр фільму. Значення цього поля у вихідній таблиці також повторюються, в таблиці існують записи з однаковими жанрами, наприклад, фантастика повторюється 13 разів, комедія повторюється 18 разів та ін.
   Областю даних називається частина зведеної таблиці, що містить підсумкові дані. В клітинках області даних відображаються підсумки для елементів полів рядка чи стовпця, за якими відбулося групування даних. Значення в кожній клітинці області даних відповідають вихідним даним. У цьому прикладі в клітинках Н5-Н8 зберігаються загальні суми виручки від продажу фільмів всіх жанрів для кожного окремого магазину; в клітинках В9-G9 — загальні суми виручки від продажу фільмів у всіх магазинах по кожному конкретному жанру; клітинка Н9 містить загальну суму виручки від продажу фільмів усіх жанрів по всіх магазинах.

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

   Для того щоб створити підсумкову таблицю у LibreOffice Calc потрібно:
   1. Встановити курсор в діапазон комірок, що містять значення і заголовки рядків і стовпчиків.
   2. Вибрарти команду Дані - Зведена таблиця - Створити. В діалоговому вікні Вибрати джерело встановити прапорець Поточне виділення і натиснути Гаразд. Заголовки стовпчиків таблиці будуть відображені у вигляді кнопок в діалоговому вікні Зведена таблиця. Їх можна перетягувати в області розмітки: "Поля сторінки", "Поля стовпчика", "Поля Рядки" та "Поля даних".

   Якщо перетягнути кнопку в область Поля сторінки, то виникне кнопка і поле зі списком зверху підсумкової таблиці. Список слугує для фільтрації підсумкової таблиці по змісту вибраного елементу. В підсумковій таблиці можна шляхом перетягування вибирати інше поле сторінки в якості фільтра.
   Якщо перетягнути кнопку в область Поля даних, в ній з'явиться надпис, в котрому показана також формула, що використовується для обчислення даних.
   Подвійне клацання на одному з полів в області Поля даних викликає діалогове вікно Поле даних.
   Діалогове вікно Поле даних слугує для вибору обчислень, котрі будуть використовуватись для даних. Щоб вибрати кілька обчислень, виділяйте їх при натисненій кнопці Ctrl.
   Порядок розташування кнопок можна змінить в будь-який час, переміщуючи їх в інші позиції в області за допомогою лівої кнопки миші.
   Для видалення кнопки перетягніть її назад до інших кнопок в правій части вікна діалогу.

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

Якщо вийти з діалогового вікна "Зведена таблиця", натиснувши кнопку "Гаразд", то з'явиться кнопка Фільтр і кнопка сторінки для кожного поля даних, котрі були переміщені в область Поля сторінок. Нижче буде вставлена підсумкова таблиця.

   3. Зведена діаграма
   Якщо ви вже створили й налаштували зведену таблицю, на її основі можна створити зведену діаграму, що найкраще відповідатиме вашим вимогам.
1. Виберіть будь-яку клітинку в діапазоні зведеної таблиці.
2. Виберіть Знаряддя для зведених таблиць - Аналізувати - Зведена діаграма.
3. Виберіть потрібний тип діаграми й натисніть кнопку OK.
  LibreOffice Calc розташує нову зведену діаграму на тому самому аркуші. У цьому прикладі ми вибрали звичайну стовпчасту діаграму. Якщо ви не впевнені, який тип діаграми потрібен, вибирайте їх по черзі й переглядайте в області "Вставлення діаграми" Calc.

Деякі діаграми не підтримують дані зведеної таблиці, тому, якщо ви виберете одну з них, Calc повідомить про це.

2. Робота в класі

Практична робота №1. Зведені таблиці

Під час роботи дотримуйтесь правил техніки безпеки.

Завдання 1. Зведені таблиці

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

1. Завантаж файл

Автомобілі.xls. Перейди до аркуша Основна таблиця.

2. Проаналізуй таблицю та дай відповіді на такі запитання:

- Скільки стовпців має таблиця?

- Чи повторюється значення деяких стовпців?

- У яких стовпцях дані повторюються?

- Дані якого типу зберігаються в стовпцях?

- За якими ознаками можна згрупувати дані цієї таблиці?

3. Перейди до аркуша з назвою Зведена таблиця:

4. Проаналізуй дані, які відображаються у зведеній таблиці, та дай відповіді на такі запитання:

- За якими ознаками згруповані дані в таблиці?

- Які дані відображаються у стовпці Загальний підсумок? Яка функція застосовувалась до даних цього стовпця?

- Скільки полів відображається?

5. Відкрий список Об'єм двигуна та обери значення 1,6:

- Як змінився зовнішній вигляд таблиці?

- Чому змініилась кількість стовпців та кількість рядків у новому поданні таблиці?

- Спробуй вибрати інше значення для об'єму двигуна автомобіля та проаналізуй зміни у зведеній таблиці.

6. У списку Пробіг обери значення 45, а потім значення 120, змінивши значення в списку Об'єм двигуна на Всі та проаналізуй, як змінився зовнішній вигляд таблиці.

Завдання 2. Створення зведеної таблиці

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

9 клас (ІІ підгрупа)

05.11

Тема: Абсолютні, відносні та мішані посилання

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

розвивати уважності, пам’ять, творчу уяву; образне мислення;

виховувати пізнавальну зацікавленість до предмету, та привчати працювати творчо;

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

Обладнання: дошка, комп’ютери, проектор, підручник Й. Я. Ривкінд, «Інформатика» 8 клас, програмне забезпечення Ms. Excel, презентація, інструктивні картки.

Основні терміни та поняття: посилання, відносні, абсолютні, мішані.

Хід уроку

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

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

ІІ. Актуалізація опорних знань учнів

Сьогодні ми починаємо вивчення нового розділу «Технології опрацювання числових даних у середовищі табличного процесора». У 8 класі на вивчення матеріалу розраховано 10 годин із них 3 практичних роботи.

Повідомлення теми уроку

Розгорніть зошити запишіть сьогоднішнє число і тему уроку

(Учні записують тему в зошити).

Тему «Абсолютні, відносні та мішані посилання».

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

Перед тим як розпочати вивчення нової теми ми маємо пригадати матеріал про «Електронні таблиці в MS Excel».

Робота в групах

Вправа «Асоціативний кущ».

Потрібно скласти на аркуші А3, асоціативний кущ на тему «Електронні таблиці в MS Excel», де в кажете всі відомі вам поняття про електронні таблиці.

Давайте пригадаємо:

1. За допомогою чого здійснюються обчислення в електронних таблицях? (Обчислення в електронних таблицях здійснюються за допомогою формул)

2. Що таке формула? (Формула – це вираз, який задає операції над даними в клітинках електронної таблиці та порядок їх виконання)

3. З чого починається запис формули? ( Запис формули починається із знака дорівнює)

4. Що може містити формула? (Формула може містити числа або текстові значення, посилання на клітинки та діапазони клітинок, знаки математичних дій, дужки та імена функцій.)

5. Які ви знаєте оператори? (Оператори є арифметичні, текстові, адресні та оператори порівняння)

6. Наведіть приклад арифметичних операторів. (Додавання, віднімання, ділення, множення, піднесення до степеня, відсоток)

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

ІІІ. Вивчення нового матеріалу

Давайте з’ясуємо що таке посилання?

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

Давайте за пишемо різновиди посилань

Посилання

Відносні Абсолютні Мішані

А4 $А$4 $A4 або A$4

Давайте опрацюємо разом

Посилання бувають:

Посилання, яке модифікується під час копіювання формули, називається відносним

ü Відносними (А4) – посилання, які при копіюванні коригуються відносно напрямку копіювання (Наприклад, комірка С4 містить формулу А4+В4, при копіюванні А4 зміниться на А5 або А3 в залежності від напрямку копіювання вниз чи вгору, при цьому формула буде справедливою для наступної комірки, С3 = А3+В3, або С5 = А5+В5)

Робота з підручником

На с.230 – 231 прочитайте пошепки, що таке абсолютне посилання? Як записують абсолютне посилання на клітинку А4?

Посилання, яке не модифікується під час копіювання формули, називається абсолютним

Наприклад, комірка С4 містить формулу А4*$В$4, при копіюванні А4 зміниться на А5 або А3 в залежності від напрямку копіювання вниз чи вгору, при цьому В4 залишиться постійною і формула буде справедливою для наступної комірки, С3 = А3*$В$4, або С5 = А5*$В$

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

ü Мішаними ($А4, А$4) – посилання в якому або назва стовпця є абсолютною, а номер рядка ні, або навпаки. (Наприклад, комірка С4 містить формулу А4*$В4, при копіюванні А4 зміниться на А5 або А3 в залежності від напрямку копіювання вниз чи вгору, при цьому В4 не залишиться постійною і формула буде справедливою для наступної комірки, С3 = А3*$В3, або С5 = А5*$В5).

Скажіть будь ласка

- Які види посилань на клітинки можна використовувати у формулах в електронних таблицях?

- Як записують відносне, абсолютне та мішане посилання на клітинку В2?

V. Закріплення нових знань учнів

1. Правила техніки безпеки в комп’ютерному класі

Діти, ми з вами находимось у комп’ютерному кабінеті. Давайте пограємо в гру «Так чи ні» і згадаємо правила поведінки та техніку безпеки в комп’ютерному класі. У вас є сигнальні карточки зеленого та червоного коліру. Зелений колір відповідь «так», червоний колір «ні»

Гра «Так чи ні».

ü В кабінет інформатики потрібно вбігати і при цьому сильно кричати. (Ні)

ü В кабінет інформатики потрібно входити спокійно, в чистому взутті і без верхнього одягу. (Так)

ü Працювати за комп’ютером можна тільки з дозволу вчителя. (Так)

ü Не можна торкатися і висмикувати дроти і розетки. (Так)

ü Працювати на клавіатурі потрібно чистими, сухими руками; легко натискаючи на клавіші, не допускаючи різких ударів. (Так)

ü Приходячи до кабінету, приносьте із собою їжу та напої. (Ні)

ü Не можна торкатися пальцями екрана монітора. (Так).

ü Відстань від очей до екрана повинна бути не меншою ніж 50 см. (Так).

ü Дозволяється працювати за комп’ютером 40 хвилин. (Ні).

2. Працюємо з комп’ютером

Завдання 1. Обчисліть суму шкільних приладів

На аркуші Аркуш 1у клітинку D3 уведіть формулу для знаходження суми (=В3*С3). Скопіюйте формулу з використанням маркера заповнення у клітинки D3:D9

Шкільне приладдя
Назва	Вартість одиниці (грн.)	Кількість (шт.)	Сума (грн.)
Олівець	3	3
Ручка	5	3
Лінійка	3	1
Зошит	10	10
Альбом для малювання	25	5
Пенал	30	2
Фарби	30	2
Приладдя (загальна сума)

Завдання 2.

На аркуші Аркуш 2 у клітинку C2 уведіть формулу (=C2*$C$7). Скопіюйте формулу з використанням маркера заповнення у клітинки С2:С6

Клас	Відмінники	Відсоток
8 – А	3
8 – Б	2
8 – В	4
9 – А	2
9 – Б	5
Всього	16

Гімнастика для очей.

Релаксація (1 хв)

Ваші очі втомилися, тому виконаємо декілька вправ для очей:

- зажмурите сильно очі, відкрийте і подивіться в даль;

- повільно поводіть очима зліва направо і справа наліво 3 рази;

- повільно перевести погляд вгору-вниз, потім навпаки 3 рази;

- кругові оберти очима вліво, а потім вправо 3 рази.

Завдання 3. Заповніть таблицю множення

1. На аркуші Аркуш 3 у клітинку B2 уведіть формулу (=$A2*В$1). Скопіюйте формулу з використанням маркера заповнення у клітинки В2:К11

	1	2	3	4	5	6	7	8	9	10
1
2
3
4
5
6
7
8
9
10

Завдання 4. Переведіть суму в доларах у суму в гривнях

1. На аркуші Аркуш 4 у клітинку С2 уведіть формулу для знаходження суми в гривнях. Скопіюйте формулу з використанням маркера заповнення у клітинки С2:С9

Курс	Сума в доларах	Сума в гривнях
29,32 грн.	125,6
	235
	68
	34
	52,5
	40
	168
	536

VІІ. Домашнє завдання

Відкрийте щоденники запишіть домашнє завдання.

Опрацювати п.7.1 с.230-231, вправа 2, с. 235. Скласти до вивченої теми ребуси, кросворд.

Оцінювання

Ви сьогодні дуже добре працювали, давали цікаві відповіді, робили висновки, брали участь в обговоренні, виконували практичні завдання. Тому отримуєте бали…

VІ. Підсумок уроку

Вільне письмо

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

Хто з вас бажає прочитати своє письмо?

Дякую.

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