Інструкційно-технологічна картка №16

Тема: Робота з електронними таблицями, введення даних за типами, форматування комірок та діапазонів комірок, робота зі списками

Мета роботи:

Навчитися працювати з Google Таблицями, створювати різні типи таблиць, вводити дані різних типів, форматувати комірки та діапазони комірок, працювати зі списками.

Необхідні матеріали:

• Комп'ютер з доступом до Інтернету

• Браузер

• Обліковий запис Google

Хід роботи:

Таблиця 1: Фінансовий облік

Завдання 1.1: Створення та форматування таблиці фінансового обліку

1. Відкрийте новий документ Google Таблиці.

2. Створіть таблицю з наступними колонками: "Дата", "Категорія", "Опис", "Сума доходу", "Сума витрат", "Баланс".

3. Заголовки колонок розташуйте в рядку 1, починаючи з комірки A1.

4. Застосуйте форматування до заголовків: жирний шрифт, розмір 12пт, колір фону лавандовий.

5. Заповніть таблицю 10 рядками з різними фінансовими операціями.

6. Для колонки "Дата" встановіть формат "Дата" з шаблоном "ДД.ММ.РРРР".

7. Для колонок "Сума доходу", "Сума витрат" та "Баланс" встановіть грошовий формат з двома десятковими знаками та символом грошової одиниці ₴.

Завдання 1.2: Умовне форматування

1. Виділіть діапазон комірок колонки "Баланс".

2. Натисніть Формат → Умовне форматування.

3. Створіть правило для значень менше 0 (колір тексту червоний).

4. Створіть правило для значень більше 1000 (колір тексту зелений).

5. Перевірте роботу умовного форматування, змінивши деякі значення.

Завдання 1.3: Обчислення балансу

1. В першій комірці колонки "Баланс" (F2) введіть формулу: =D2-E2

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

3. Перевірте правильність розрахунків.

Завдання 1.4: Додавання підсумкового рядка

1. В кінці таблиці додайте рядок "Всього".

2. В комірку під колонкою "Сума доходу" введіть формулу =SUM(D2:D11).

3. В комірку під колонкою "Сума витрат" введіть формулу =SUM(E2:E11).

4. В комірку під колонкою "Баланс" введіть формулу =SUM(F2:F11).

5. Виділіть підсумковий рядок жирним шрифтом та кольором фону ціано-блакитний.

Завдання 1.5: Створення діаграми

1. Виділіть діапазон комірок з категоріями та сумами витрат.

2. Натисніть "Вставка" → "Діаграма".

3. Виберіть тип діаграми "Кругова".

4. Налаштуйте заголовок діаграми "Розподіл витрат за категоріями".

5. Змініть кольори сегментів на власний смак.

Завдання 1.6: Фільтрація даних

1. Виділіть всю таблицю з даними (з заголовками).

2. Натисніть "Дані" → "Створити фільтр".

3. Використовуйте фільтр у колонці "Категорія", щоб відобразити лише певну категорію.

4. Скасуйте фільтрацію, щоб знову побачити всі дані.

Завдання 1.7: Сортування даних

1. Виділіть всю таблицю з даними (з заголовками).

2. Натисніть "Дані" → "Сортувати діапазон".

3. Відсортуйте дані за датою від найстарішої до найновішої.

4. Тепер відсортуйте за сумою витрат від найбільшої до найменшої.

Завдання 1.8: Використання функції AVERAGEIF

1. Під таблицею створіть новий рядок з назвою "Середні витрати на категорію".

2. Поруч введіть назву певної категорії, наприклад "Продукти".

3. У наступній комірці введіть формулу =AVERAGEIF(B2:B11;"Продукти";E2:E11).

4. Інтерпретуйте отриманий результат.

Завдання 1.9: Використання функції COUNTIF

1. Під таблицею створіть новий рядок з назвою "Кількість операцій у категорії".

2. Поруч введіть назву певної категорії, наприклад "Комунальні послуги".

3. У наступній комірці введіть формулу =COUNTIF(B2:B11;"Комунальні послуги").

4. Інтерпретуйте отриманий результат.

Завдання 1.10: Захист даних

1. Виділіть діапазон комірок з формулами.

2. Натисніть правою кнопкою миші та виберіть "Захистити діапазон".

3. Створіть опис для захищеного діапазону.

4. Встановіть дозвіл "Показувати попередження при редагуванні цього діапазону".

5. Спробуйте змінити захищений діапазон і переконайтеся, що з'являється попередження.

Таблиця 2: Облік товарів на складі

Завдання 2.1: Створення та форматування таблиці товарів

1. Створіть новий аркуш і назвіть його "Склад".

2. Створіть таблицю з колонками: "Код товару", "Найменування", "Категорія", "Одиниця виміру", "Кількість", "Ціна за одиницю", "Загальна вартість".

3. Заголовки колонок розташуйте в рядку 1, починаючи з комірки A1.

4. Застосуйте форматування до заголовків: жирний шрифт, розмір 12пт, вирівнювання по центру, перенесення тексту.

5. Заповніть таблицю 10 рядками з різними товарами.

6. Налаштуйте ширину колонок для оптимального відображення.

Завдання 2.2: Автоматичне обчислення загальної вартості

1. В першій комірці колонки "Загальна вартість" (G2) введіть формулу: =E2*F2

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

3. Відформатуйте колонки "Ціна за одиницю" та "Загальна вартість" як грошовий формат з символом ₴.

Завдання 2.3: Використання випадаючих списків для категорій

1. Створіть на аркуші новий діапазон (наприклад, J1:J5) і введіть в нього 5 різних категорій товарів.

2. Виділіть діапазон комірок у колонці "Категорія" (C2:C11).

3. Натисніть "Дані" → "Перевірка даних".

4. У типі перевірки виберіть "Список елементів з діапазону" і вкажіть діапазон з категоріями (J1:J5).

5. Натисніть "Зберегти" і перевірте, як працює випадаючий список у комірках категорій.

Завдання 2.4: Використання функції SUMIF

1. Під таблицею створіть новий рядок з назвою "Загальна вартість категорії".

2. Поруч введіть назву певної категорії з вашого списку.

3. У наступній комірці введіть формулу =SUMIF(C2:C11;"Категорія";G2:G11), де "Категорія" - назва однієї з введених вами категорій.

4. Повторіть для інших категорій.

Завдання 2.5: Підсвічування товарів з малою кількістю

1. Виділіть діапазон комірок колонки "Кількість" (E2:E11).

2. Натисніть Формат → Умовне форматування.

3. Створіть правило для значень менше 5 (колір фону світло-червоний).

4. Додайте опис у сусідній колонці "Потрібно замовити!" для товарів з малою кількістю, використовуючи функцію =IF(A2<=5;"Замовити товар";"Не потрібно замовляти").

Завдання 2.6: Створення текстових ідентифікаторів

1. Додайте нову колонку "Повний код" після "Коду товару".

2. В першому рядку введіть формулу =CONCATENATE(A2;"-";C2), щоб об'єднати код товару і категорію.

3. Скопіюйте формулу для всіх рядків.

4. Перевірте результат об'єднання.

Завдання 2.7: Створення статистичної таблиці

1. У вільній області створіть невелику таблицю статистики.

2. Додайте рядки: "Всього найменувань", "Загальна вартість", "Середня вартість одиниці", "Найдорожчий товар", "Найдешевший товар".

3. Використовуйте відповідні функції: COUNT, SUM, AVERAGE, MAX, MIN для заповнення даних.

4. Відформатуйте таблицю для виділення.

Завдання 2.8: Використання автофільтрів

1. Виділіть всю таблицю з даними (з заголовками).

2. Натисніть "Дані" → "Створити фільтр".

3. Створіть користувацький фільтр у колонці "Ціна за одиницю" для відображення товарів у певному ціновому діапазоні.

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

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

1. Виділіть всю таблицю з даними (з заголовками).

2. Натисніть "Вставити" → "Зведена таблиця".

3. Налаштуйте зведену таблицю з рядками за "Категорією" і значеннями "Кількість" та "Загальна вартість".

4. Для значень встановіть сумування за категоріями.

5. Проаналізуйте отримані результати.

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

1. Виділіть діапазон комірок з категоріями та кількістю.

2. Натисніть "Вставка" → "Діаграма".

3. Виберіть тип діаграми "Гістограма".

4. Налаштуйте заголовок діаграми "Кількість товарів за категоріями".

5. Змініть кольори стовпців та налаштуйте легенду.

Таблиця 3: Розрахунок заробітної плати

Завдання 3.1: Створення та форматування таблиці розрахунку заробітної плати

1. Створіть новий аркуш і назвіть його "Заробітна плата".

2. Створіть таблицю з колонками: "ID працівника", "ПІБ", "Посада", "Оклад", "Премія", "Податок", "До виплати".

3. Заголовки колонок розташуйте в рядку 1, починаючи з комірки A1.

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

5. Заповніть таблицю 10 рядками з різними працівниками.

6. Для колонок "Оклад", "Премія", "Податок" та "До виплати" встановіть грошовий формат.

Завдання 3.2: Розрахунок податку

1. Припустимо, що податок становить 18% від суми окладу та премії.

2. В першій комірці колонки "Податок" (F2) введіть формулу: =(D2+E2)*0.18

3. Скопіюйте формулу для всіх рядків таблиці.

4. Перевірте правильність розрахунків.

Завдання 3.3: Розрахунок суми до виплати

1. В першій комірці колонки "До виплати" (G2) введіть формулу: =D2+E2-F2

2. Скопіюйте формулу для всіх рядків таблиці.

3. Перевірте правильність розрахунків.

Завдання 3.4: Підсвічування високих окладів

1. Виділіть діапазон комірок колонки "Оклад" (D2:D11).

2. Натисніть Формат → Умовне форматування.

3. Створіть правило для значень більше 20000 (колір фону світло-зелений).

4. Перевірте, як працює умовне форматування.

Завдання 3.5: Використання функції VLOOKUP

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

2. Заповніть таблицю різними посадами та відповідними сумами стандартних премій.

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

4. Формула повинна мати вигляд: =VLOOKUP(C2,посилання_на_діапазон_допоміжної_таблиці,2,FALSE)

5. Перевірте правильність роботи функції.

Завдання 3.6: Додавання прогресивної шкали податку

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

   • До 10000 – 15%

   • 10001-20000 – 18%

   • Понад 20000 – 20%

2. Замініть формулу в колонці "Податок" на формулу з використанням IF або VLOOKUP для визначення ставки податку залежно від суми окладу та премії.

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

Завдання 3.7: Створення графіка розподілу зарплат

1. Виділіть діапазон комірок з ПІБ та сумами до виплати.

2. Натисніть "Вставка" → "Діаграма".

3. Виберіть тип діаграми "Гістограма".

4. Налаштуйте заголовок діаграми "Розподіл заробітної плати".

5. Відсортуйте дані на графіку за зменшенням.

Завдання 3.8: Використання функції RANK

1. Додайте нову колонку "Ранг за окладом".

2. В першому рядку введіть формулу =RANK(D2,$D$2:$D$11,0), щоб визначити ранг співробітника за розміром окладу (0 - за спаданням).

3. Скопіюйте формулу для всіх рядків.

4. Перевірте правильність рангів.

Завдання 3.9: Використання функції COUNTIFS для аналізу даних

1. Під таблицею створіть новий рядок з назвою "Кількість працівників з окладом більше X і премією більше Y".

2. Введіть значення X та Y в окремі комірки.

3. У наступній комірці введіть формулу =COUNTIFS(D2:D11;">"&X,E2:E11;">"&Y).

4. Змініть значення X та Y і спостерігайте, як змінюється результат.

Завдання 3.10: Створення зведеної таблиці за посадами

1. Виділіть всю таблицю з даними (з заголовками).

2. Натисніть "Дані" → "Зведена таблиця".

3. Налаштуйте зведену таблицю з рядками за "Посадою" і значеннями "Оклад", "Премія" та "До виплати".

4. Для значень встановіть сумування та середнє значення.

5. Проаналізуйте отримані результати.

Таблиця 4: Планування проєктів

Завдання 4.1: Створення та форматування таблиці проєктів

1. Створіть новий аркуш і назвіть його "Проєкти".

2. Створіть таблицю з колонками: "ID проєкту", "Назва проєкту", "Виконавець", "Дата початку", "Дата завершення", "Тривалість (днів)", "Статус", "Пріоритет", "Бюджет".

3. Заголовки колонок розташуйте в рядку 1, починаючи з комірки A1.

4. Застосуйте форматування до заголовків: жирний шрифт, колір фону powderblue, вирівнювання по центру.

5. Заповніть таблицю 10 рядками з різними проєктами.

6. Для колонок "Дата початку" та "Дата завершення" встановіть формат "Дата".

7. Для колонки "Бюджет" встановіть грошовий формат.

Завдання 4.2: Автоматичний розрахунок тривалості проєкту

1. В першій комірці колонки "Тривалість (днів)" (F2) введіть формулу: =E2-D2

2. Скопіюйте формулу для всіх рядків таблиці.

3. Перевірте правильність розрахунків.

Завдання 4.3: Використання випадаючих списків для статусу та пріоритету

1. Створіть на аркуші нові діапазони для статусів (наприклад: "Не розпочато", "В процесі", "Завершено", "Відкладено") та пріоритетів (наприклад: "Низький", "Середній", "Високий", "Критичний").

2. Виділіть діапазон комірок у колонці "Статус" (G2:G11).

3. Натисніть "Дані" → "Перевірка даних".

4. У типі перевірки виберіть "Список елементів з діапазону" і вкажіть діапазон зі статусами.

5. Повторіть аналогічні дії для колонки "Пріоритет".

6. Перевірте, як працюють випадаючі списки.

Завдання 4.4: Умовне форматування для статусів

1. Виділіть діапазон комірок колонки "Статус" (G2:G11).

2. Натисніть Формат → Умовне форматування.

3. Створіть правила для різних статусів з різними кольорами фону:

   • "Не розпочато" - світло-сірий

   • "В процесі" - світло-жовтий

   • "Завершено" - світло-зелений

   • "Відкладено" - світло-червоний

4. Перевірте, як працює умовне форматування.

Завдання 4.5: Використання функції TODAY для відстеження термінів

1. Додайте нову колонку "Днів до завершення".

2. В першому рядку введіть формулу =E2-TODAY(), щоб розрахувати кількість днів до дати завершення.

3. Скопіюйте формулу для всіх рядків.

4. Створіть умовне форматування для цієї колонки:

   • Менше 0 (прострочено) - червоний текст

   • 0-7 днів - жовтий текст

   • Більше 7 днів - зелений текст

Завдання 4.6: Створення діаграми Ґанта

1. Виділіть діапазон комірок з назвами проєктів, датами початку та завершення.

2. Натисніть "Вставка" → "Діаграма".

3. Виберіть тип діаграми "Стовпчаста".

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

5. Змініть кольори смуг відповідно до пріоритетів проєктів.

Завдання 4.7: Фільтрація та сортування проєктів

1. Виділіть всю таблицю з даними (з заголовками).

2. Натисніть "Дані" → "Створити фільтр".

3. Використовуйте фільтр у колонці "Статус", щоб відобразити лише проєкти "В процесі".

4. Відсортуйте відфільтровані дані за "Датою завершення" від найближчої до найдальшої.

5. Зробіть висновки щодо пріоритетності роботи над проєктами.

Завдання 4.8: Використання функції SUMIF для аналізу бюджету

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

2. Створіть окремі рядки для кожного статусу проєкту.

3. У кожному рядку використовуйте формулу =SUMIF(G2:G11;"Статус";I2:I11), де "Статус" - один із можливих статусів проєкту.

4. Відформатуйте результати як грошові значення.

5. Створіть кругову діаграму на основі отриманих даних.

Завдання 4.9: Використання функції COUNTIFS для комплексного аналізу

1. Під таблицею створіть нову секцію "Аналіз проєктів за пріоритетом та статусом".

2. Створіть таблицю зі статусами у рядках та пріоритетами у стовпцях.

3. У кожній комірці використовуйте формулу =COUNTIFS(G2:G11;"Статус";H2:H11;"Пріоритет"), де "Статус" та "Пріоритет" - відповідні значення.

4. Додайте підсумкові рядки та стовпці з використанням функції SUM.

5. Проаналізуйте розподіл проєктів.

Завдання 4.10: Створення інформаційної панелі

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

   • Загальна кількість проєктів

   • Кількість завершених проєктів

   • Відсоток завершення (завершені / загальні * 100%)

   • Загальний бюджет

   • Середня тривалість проєкту

   • Кількість прострочених проєктів

2. Для кожного показника використовуйте відповідні функції.

3. Відформатуйте інформаційну панель для візуального виділення.

4. Створіть гістограму за даними інформаційної панелі.

Таблиця 5: Аналіз успішності учнів

Завдання 5.1: Створення та форматування таблиці успішності

1. Створіть новий аркуш і назвіть його "Успішність".

2. Створіть таблицю з колонками: "ID учня", "ПІБ", "Клас", "Математика", "Українська мова", "Історія", "Фізика", "Хімія", "Середній бал", "Результат".

3. Заголовки колонок розташуйте в рядку 1, починаючи з комірки A1.

4. Застосуйте форматування до заголовків: жирний шрифт, колір фону лимонно-кремовий, вирівнювання по центру.

5. Заповніть таблицю 10 рядками з різними учнями та оцінками від 1 до 12.

6. Налаштуйте ширину колонок для оптимального відображення.

Завдання 5.2: Розрахунок середнього балу

1. В першій комірці колонки "Середній бал" (J2) введіть формулу: =AVERAGE(D2:H2)

2. Скопіюйте формулу для всіх рядків таблиці.

3. Налаштуйте відображення середнього балу з одним десятковим знаком.

Завдання 5.3: Визначення результату

1. В першій комірці колонки "Результат" (K2) введіть формулу з використанням IF:

   • Якщо середній бал >= 10, то "Відмінно"

   • Якщо середній бал >= 7 та < 10, то "Добре"

   • Якщо середній бал >= 4 та < 7, то "Задовільно"

   • Якщо середній бал < 4, то "Незадовільно"

2. Формула повинна мати вигляд: =IF(J2>=10;"Відмінно";IF(J2>=7;"Добре";IF(J2>=4;"Задовільно";"Незадовільно")))

3. Скопіюйте формулу для всіх рядків таблиці.

4. Перевірте правильність визначення результатів.

Завдання 5.4: Умовне форматування оцінок

1. Виділіть діапазон комірок з оцінками (D2:H11).

2. Натисніть Формат → Умовне форматування.

3. Створіть правила для різних діапазонів оцінок:

   • 10-12 - зелений фон

   • 7-9 - світло-жовтий фон

   • 4-6 - світло-оранжевий фон

   • 1-3 - світло-червоний фон

4. Перевірте, як працює умовне форматування.

Завдання 5.5: Створення функції для аналізу успішності з предмету

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

2. Створіть випадаючий список з назвами предметів.

3. У сусідній комірці введіть формулу =AVERAGE(INDIRECT(CONCATENATE(вибраний_предмет;"2:";вибраний_предмет;"11"))), де вибраний_предмет - комірка з випадаючим списком.

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

5. Використовуйте функції MAX та MIN з аналогічною структурою.

Завдання 5.6: Порівняльний аналіз успішності з предметів

1. Створіть гістограму для порівняння середніх балів з кожного предмету.

2. Виділіть діапазон комірок з назвами предметів та середніми балами.

3. Натисніть "Вставка" → "Діаграма".

4. Виберіть тип діаграми "Гістограма".

5. Налаштуйте заголовок діаграми "Середній бал за предметами".

6. Змініть кольори стовпців та додайте підписи даних.

Завдання 5.7: Розрахунок відсотка успішності

1. Створіть формулу для розрахунку відсотка учнів, які отримали результат "Відмінно" або "Добре".

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

3. Формула повинна мати вигляд: =(COUNTIF(K2:K11,"Відмінно")+COUNTIF(K2:K11,"Добре"))/COUNTA(K2:K11)*100

4. Відформатуйте результат як відсоток з одним десятковим знаком.

5. Створіть кругову діаграму для візуалізації розподілу результатів.

Завдання 5.8: Використання функції RANK для визначення рейтингу учнів

1. Додайте нову колонку "Рейтинг".

2. В першому рядку введіть формулу =RANK(J2,$J$2:$J$11,0), щоб визначити рейтинг учня за середнім балом (0 - за спаданням).

3. Скопіюйте формулу для всіх рядків.

4. Перевірте правильність визначення рейтингів.

5. Відсортуйте таблицю за рейтингом від найкращого до найгіршого.

Завдання 5.9: Визначення проблемних предметів для учнів

1. Додайте нову колонку "Проблемний предмет".

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

3. Використовуйте функції MATCH та MIN:

   • Спочатку знайдіть мінімальну оцінку: =MIN(D2:H2)

   • Потім знайдіть її позицію: =MATCH(MIN(D2:H2),D2:H2,0)

   • Нарешті, перетворіть позицію на назву предмету за допомогою INDIRECT або вручну створеного масиву.

4. Скопіюйте формулу для всіх рядків.

5. Виділіть комірки, де оцінка з проблемного предмету менше 4.

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

1. Виділіть всю таблицю з даними (з заголовками).

2. Натисніть "Дані" → "Зведена таблиця".

3. Налаштуйте зведену таблицю:

   • Рядки: "Клас"

   • Значення: "Математика", "Українська мова", "Історія", "Фізика", "Хімія", "Середній бал"

   • Для значень встановіть агрегацію "AVERAGE" (середнє)

4. Відформатуйте таблицю для кращого відображення.

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

6. Додайте на діаграму лінію тренду.

Заключний етап:

1. Збережіть Google Таблицю під назвою "Прізвище_Ім'я_ІТК_16".

2. Налаштуйте доступ до таблиці, натиснувши кнопку "Поділитися" у верхньому правому куті.

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

4. Виберіть рівень доступу "Редактор" або "Коментатор".

5. Додайте повідомлення з вашим ПІБ та номером практичної роботи.

6. Натисніть "Надіслати".