ІК №17

Інструкційно-технологічна картка №17 для Google Таблиць

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

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

Завдання 1: Основи створення формул в Google Таблицях

Мета: Ознайомитися з основами створення формул та використанням основних арифметичних операцій.

Хід роботи:

1.     Відкрийте Google Таблиці через сервіс Google Drive.

2.     Створіть новий документ та назвіть його "Інструкційно-технологічна картка №17".

3.     На першому аркуші створіть таблицю "Бюджет місяця" з такими стовпцями:

• Категорія витрат

• Планові витрати

• Фактичні витрати

• Різниця

• Відсоток виконання

4.     Заповніть таблицю даними для 5-7 категорій витрат (наприклад: продукти, транспорт, розваги, комунальні послуги тощо).

5.     У стовпці "Різниця" створіть формули для обчислення різниці між плановими та фактичними витратами.

• Формула має вигляд: =B2-C2 (де B2 - планові витрати, C2 - фактичні витрати)

6.     У стовпці "Відсоток виконання" створіть формули для обчислення відсотка виконання плану.

• Формула має вигляд: =C2/B2*100 (результат у відсотках)

7.     Відформатуйте стовпець "Відсоток виконання" як відсотки через меню "Формат" → "Число" → "Відсотки".

8.     Додайте в кінці таблиці рядок "Всього" та створіть формули для підрахунку загальних сум у стовпцях.

• Використайте функцію SUM() для підрахунку суми значень.

9.     Змініть ширину стовпців для зручного відображення даних.

10. Застосуйте форматування таблиці для покращення візуального сприйняття (кольори, рамки).

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

Завдання 2: Використання майстра функцій та основні математичні функції

Мета: Навчитися використовувати майстер функцій та застосовувати основні математичні функції.

Хід роботи:

1.     Створіть на другому аркуші таблицю "Аналіз успішності групи" з такими стовпцями:

• ПІБ учня

• Оцінки за 5 контрольних робіт (5 окремих стовпців)

• Середній бал

• Найвища оцінка

• Найнижча оцінка

• Медіана

2.     Внесіть дані для 10 учнів, використовуючи оцінки від 1 до 12.

3.     Для розрахунку середнього балу використайте функцію AVERAGE:

• Встановіть курсор у відповідну клітинку

• Відкрийте майстер функцій, натиснувши на кнопку "∑" на панелі інструментів

• Виберіть функцію "AVERAGE"

• Виділіть діапазон з оцінками учня

• Натисніть Enter

4.     Для знаходження найвищої оцінки використайте функцію MAX через майстер функцій.

5.     Для знаходження найнижчої оцінки використайте функцію MIN через майстер функцій.

6.     Для обчислення медіани використайте функцію MEDIAN через майстер функцій.

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

8.     Додайте в кінці таблиці рядок "Середнє по групі" та обчисліть середні значення для кожного стовпця з оцінками.

9.     Застосуйте умовне форматування до стовпця "Середній бал":

• Червоний колір для значень менше 7

• Жовтий колір для значень від 7 до 9

• Зелений колір для значень вище 9

Очікуваний результат: Таблиця аналізу успішності з коректно працюючими функціями AVERAGE, MAX, MIN, MEDIAN та умовним форматуванням.

Завдання 3: Статистичні та логічні функції

Мета: Опанувати використання статистичних та логічних функцій для аналізу даних.

Хід роботи:

1.     Створіть на третьому аркуші таблицю "Аналіз продажів" з такими стовпцями:

• Товар

• Ціна

• Кількість

• Сума продажу

• Виконання плану (кількість)

• План виконано (так/ні)

• Рейтинг ефективності

2.     Заповніть таблицю даними для 10-12 товарів.

3.     У колонці "Сума продажу" створіть формулу для розрахунку суми (ціна * кількість).

4.     Додайте колонку "План продажів" та заповніть її плановими значеннями.

5.     У колонці "Виконання плану" створіть формулу для розрахунку відсотка виконання плану:

• =D2/H2*100 (де D2 - кількість, H2 - план продажів)

6.     У колонці "План виконано" використайте логічну функцію IF:

• Клацніть по клітинці і виберіть "Функції" → "Логічні" → "IF"

• Задайте умову: IF(Виконання плану >= 100, "Так", "Ні")

7.     У колонці "Рейтинг ефективності" використайте функцію VLOOKUP або вкладені IF:

• Використайте вкладені функції IF:

• =IF(F2>=120, "Відмінно", IF(F2>=100, "Добре",   IF(F2>=80, "Задовільно", "Незадовільно")))

8.     Використайте функцію COUNT для підрахунку кількості товарів.

9.     Використайте функцію COUNTIF для підрахунку кількості товарів з виконаним планом:

• =COUNTIF(G2:G13, "Так")

10. Обчисліть загальну суму продажів, середню ціну товару та стандартне відхилення цін, використовуючи функції SUM, AVERAGE, STDEV.

Очікуваний результат: Таблиця аналізу продажів з використанням логічних та статистичних функцій, включаючи IF, COUNTIF, SUM, AVERAGE, STDEV.

Завдання 4: Текстові та дата/час функції

Мета: Опанувати використання текстових функцій та функцій для роботи з датами і часом.

Хід роботи:

1.     Створіть на четвертому аркуші таблицю "Список замовлень" з такими колонками:

• Номер замовлення

• ПІБ клієнта

• Дата замовлення

• Час замовлення

• Сума замовлення

• Статус

• Дата виконання

2.     Заповніть таблицю даними для 8-10 замовлень.

3.     Додайте колонку "Ім'я клієнта" та використайте функцію LEFT або SPLIT для виділення імені з ПІБ:

• =SPLIT(B2, " ")

• Або =LEFT(B2, FIND(" ", B2)-1)

4.     Додайте колонку "Тривалість виконання (дні)" та обчисліть різницю між датою виконання та датою замовлення:

• =G2-C2

5.     Додайте колонку "Місяць замовлення" та використайте функцію MONTH:

• =MONTH(C2)

6.     Додайте колонку "Назва місяця" та використайте функцію TEXT:

• =TEXT(C2, "MMMM")

7.     Додайте колонку "День тижня" для дати замовлення, використовуючи функцію WEEKDAY:

• =WEEKDAY(C2, 2) (2 означає, що понеділок - це 1)

8.     Додайте колонку "Назва дня тижня" та використайте функцію CHOOSE для перетворення числа у назву:

• =CHOOSE(WEEKDAY(C2, 2), "Понеділок", "Вівторок", "Середа", "Четвер", "П'ятниця", "Субота", "Неділя")

9.     Створіть колонку "Клієнт-Дата" та об'єднайте ім'я клієнта та дату замовлення, використовуючи функцію CONCATENATE або оператор &:

• =B2 & " - " & TEXT(C2, "dd.mm.yyyy")

10. Додайте колонку "Термін виконання" та обчисліть дату, яка настає через 5 робочих днів після дати замовлення:

• =WORKDAY(C2, 5)

Очікуваний результат: Таблиця зі списком замовлень та використанням текстових функцій і функцій для роботи з датами і часом, включаючи LEFT, SPLIT, MONTH, TEXT, WEEKDAY, CHOOSE, CONCATENATE, WORKDAY.

Завдання 5: Робота з даними з різних аркушів

Мета: Навчитися виконувати обчислення з даними, розташованими на різних аркушах.

Хід роботи:

1.     Створіть на п'ятому аркуші таблицю "Зведений аналіз" з такими стовпцями:

• Показник

• Значення

• Джерело даних

2.     Заповніть таблицю наступними показниками:

• Загальна сума бюджету (планові витрати)

• Загальна сума фактичних витрат

• Середній відсоток виконання бюджету

• Кількість учнів з середнім балом вище 9

• Загальна сума продажів

• Кількість виконаних планів продажів

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

3.     Для кожного показника створіть формулу, яка звертається до відповідного аркушу:

• Для суми бюджету: ='Завдання 1'!B11 (де B11 - клітинка з загальною сумою планових витрат)

• Для суми фактичних витрат: ='Завдання 1'!C11

• Для середнього відсотка виконання бюджету: =AVERAGE('Завдання 1'!E2:E10)

• Для кількості учнів з середнім балом вище 9: =COUNTIF('Завдання 2'!G2:G11, ">9")

• Для загальної суми продажів: =SUM('Завдання 3'!D2:D13)

• Для кількості виконаних планів: =COUNTIF('Завдання 3'!G2:G13, "Так")

• Для середньої тривалості виконання замовлень: =AVERAGE('Завдання 4'!H2:H11)

4.     У колонці "Джерело даних" вкажіть назву аркуша, з якого взяті дані.

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

• Планових та фактичних витрат (у вигляді кругової діаграми)

• Виконання плану продажів по товарах (у вигляді гістограми)

6.     Для створення діаграм:

• Виділіть потрібні дані

• Виберіть "Вставка" → "Діаграма"

• Виберіть тип діаграми та налаштуйте її

7.     Додайте формули для розрахунку відсоткового співвідношення між показниками з різних аркушів.

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

Завдання 6: Використання складних функцій та умовного форматування

Мета: Навчитися використовувати складні функції, такі як SUMIFS, COUNTIFS, VLOOKUP, та застосовувати складне умовне форматування.

Хід роботи:

1.     Створіть на шостому аркуші таблицю "Розширений аналіз" з такими стовпцями:

• Категорія

• Підкатегорія

• Сума

• Квартал

• Відповідальний

2.     Заповніть таблицю даними для 15-20 рядків, використовуючи 3-4 категорії, 2-3 підкатегорії для кожної категорії, 4 квартали та 3-4 відповідальних осіб.

3.     Створіть допоміжну таблицю "Зведені дані":

• По категоріях

• По кварталах

• По відповідальних

4.     Використайте функцію SUMIFS для підрахунку сум з кількома умовами:

• Сума по категорії та кварталу: =SUMIFS(C2:C21, A2:A21, "Категорія1", D2:D21, "Q1")

• Сума по відповідальному та категорії: =SUMIFS(C2:C21, E2:E21, "Петров", A2:A21, "Категорія1")

5.     Використайте функцію COUNTIFS для підрахунку кількості записів з кількома умовами:

• Кількість записів по категорії та кварталу: =COUNTIFS(A2:A21, "Категорія1", D2:D21, "Q1")

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

7.     Використайте функцію VLOOKUP для пошуку планових показників:

• =VLOOKUP(A2, $I$2:$J$6, 2, FALSE) (де I2:J6 - діапазон з плановими показниками)

8.     Додайте колонку "Відхилення від плану" та розрахуйте різницю між фактичною сумою та плановим показником.

9.     Застосуйте складне умовне форматування:

• Використайте формулу для виділення кольором рядків з найбільшими сумами в кожній категорії

• Використайте формулу для виділення кольором рядків з найменшими сумами в кожній категорії

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

• Виділіть всю таблицю з даними

• Виберіть "Дані" → "Зведена таблиця"

• Виберіть рядки: Категорія, Підкатегорія

• Виберіть стовпці: Квартал

• Виберіть значення: Сума (Сума)

Очікуваний результат: Таблиця з розширеним аналізом, яка містить складні функції SUMIFS, COUNTIFS, VLOOKUP та складне умовне форматування, а також зведену таблицю.

Підсумкове завдання

1.     Переконайтесь, що всі аркуші правильно названі відповідно до номерів завдань.

2.     Перевірте роботу всіх формул та функцій.

3.     Перевірте форматування всіх таблиць для зручного читання.

4.     Додайте на першому аркуші (перед таблицею з завданням 1) інформацію про виконавця:

• ПІБ

• Група

• Дата виконання

5.     Поділіться документом з викладачем:

• Натисніть кнопку "Поділитись" у правому верхньому куті

• Введіть електронну адресу: serdech@gmail.com

• Встановіть права доступу "Редактор"

• Додайте повідомлення з вашим ПІБ та номером групи

• Натисніть "Надіслати"