Практична робота "Використання умовних агрегатних функцій в електронних таблицях "

Завдання для роботи

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

Блок А: Освоєння Простих Умов (COUNTIF, SUMIF)

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

Завдання 1. Базовий Підрахунок за Текстовою Умовою

Інструкція: Підрахуйте загальну кількість замовлень, які мають Статус_Оплати "Сплачено" (стовпець E).
Підказка: Для підрахунку елементів використовуйте функцію COUNTIF. Пам'ятайте, що текстовий критерій завжди має бути в лапках, наприклад, "Сплачено".
Очікувана Формула: =COUNTIF(E:E; "Сплачено")

Завдання 2. Підрахунок за Числовим Критерієм (Проста Умова)

Інструкція: Підрахуйте, скільки замовлень мають Вартість (стовпець D) понад 10,000 грн.
Підказка: Числовий критерій з логічним оператором також має бути укладений у лапки. Критерій для цієї умови: ">10000".
Очікувана Формула: =COUNTIF(D:D; ">10000")

Завдання 3. Сумування за Текстовою Категорією

Інструкція: Обчисліть загальну Вартість (стовпець D) усіх замовлень, які належать до Категорії "Електроніка" (стовпець B).
Підказка: Тут необхідно застосувати функцію SUMIF. Ви повинні визначити діапазон умови (B), сам критерій, та діапазон сумування (D).
Очікувана Формула: =SUMIF(B:B; "Електроніка"; D:D)

Завдання 4. Пошук Мінімуму за Множинними Умовами (MINIFS)

Інструкція: Знайдіть мінімальну Вартість (стовпець D) замовлення, яке належить до Категорії "Офісне обладнання" (B) та має Відсоток_Знижки (F) менше 0.1 (10%).
Підказка: Використовуйте функцію MINIFS. Синтаксис вимагає вказати діапазон мінімуму (D) першим, а потім пари "діапазон-критерій".
Очікувана Формула: =MINIFS(D:D; B:B; "Офісне обладнання"; F:F; "<0.1")

Блок Б: Використання Складних Умов (Множинні Критерії, Логічне "І")

Цей блок знайомить учнів із функціями *IFS, які дозволяють застосовувати логіку "І" (AND) для аналізу, а також вимагає роботи з числовими діапазонами та датами.

Завдання 5. Підрахунок із Двома Текстовими Умовами (COUNTIFS)

Інструкція: Підрахуйте кількість замовлень, які одночасно належать до Категорії "Офісне обладнання" (B) та мають Статус_Оплати "Сплачено" (E).
Підказка: Використовуйте COUNTIFS. Не забудьте, що ця функція очікує пари: Діапазон1, Критерій1, Діапазон2, Критерій2.
Очікувана Формула: =COUNTIFS(B:B; "Офісне обладнання"; E:E; "Сплачено")

Завдання 6. Сумування в Числовому Діапазоні (SUMIFS)

Інструкція: Обчисліть загальну Вартість замовлень (D), де Відсоток_Знижки (F) перебуває в діапазоні: більше 2% (0.02) і менше 10% (0.1).
Підказка: Це приклад логіки "І". Вам потрібно вказати два окремих критерії (">0.02" та "<0.1") для одного й того ж діапазону (F) у функції SUMIFS. Пам'ятайте, що діапазон суми (D) йде першим.
Очікувана Формула: =SUMIFS(D:D; F:F; ">0.02"; F:F; "<0.1")

Завдання 7. Аналіз Дат та Категорій (AVERAGEIFS)

Інструкція: Знайдіть середню Вартість замовлень (D) Категорії "Електроніка" (B), які були відправлені після 01.03.2024 (G).
Підказка: Використовуйте AVERAGEIFS. Формат критерію дати має включати логічний оператор: ">01.03.2024".
Очікувана Формула: =AVERAGEIFS(D:D; B:B; "Електроніка"; G:G; ">01.03.2024")

Завдання 8. Використання Спеціальних Символів (COUNTIF Wildcards)

Інструкція: Підрахуйте кількість замовлень (використовуючи COUNTIF), які були відправлені до будь-якого Регіону (C), назва якого починається на літеру "П".
Підказка: Для позначення будь-якої послідовності символів після літери "П" використовуйте спеціальний символ-замінник (зірочку *). Критерій: "П*".
Очікувана Формула: =COUNTIF(C:C; "П*")

Блок В: Аналіз, Узагальнення та Спеціальні Прийоми

Фінальний блок вимагає від учнів інтеграції знань, моделювання логіки "АБО", роботи з динамічними критеріями та проведення порівняльного аналізу синтаксису функцій.

Завдання 9. Комплексний Фінансовий Аналіз (SUMIFS, Три Умови)

Інструкція: Розрахуйте загальну Вартість (D) усіх замовлень, які відповідають трьом умовам: належать до Категорії "Офісне обладнання" (B), мають Статус_Оплати "Сплачено" (E) і їхня Вартість менше 15,000 грн (D).
Підказка: Вам потрібна функція SUMIFS з трьома парами "Діапазон-Критерій". Важливо, що діапазон сумування (D) має бути вказаний першим, а потім він знову використовується як діапазон умови.
Очікувана Формула: =SUMIFS(D:D; B:B; "Офісне обладнання"; E:E; "Сплачено"; D:D; "<15000")

Завдання 10. Створення Динамічного Звіту (COUNTIF та Конкатенація)

Інструкція: Введіть у комірку K1 числове значення (наприклад, 7500). Обчисліть, скільки замовлень мають Вартість (D) більшу за значення, що міститься в K1, використовуючи COUNTIF.
Підказка: Щоб формула автоматично оновлювалася при зміні K1, ви повинні поєднати логічний оператор (>) з посиланням на комірку (K1) за допомогою символу конкатенації (&).
Очікувана Формула: =COUNTIF(D:D; ">"&K1)

Завдання 11. Моделювання Логічного "АБО" (SUMIF Advanced OR Logic)

Інструкція: Обчисліть загальну Вартість (D) усіх замовлень, які були відправлені або до Регіону "Захід" (C), або до Регіону "Північ" (C). (Логічне "АБО").
Підказка: Оскільки функції *IFS підтримують лише логіку "І", для реалізації логічного "АБО" необхідно обчислити суму результатів двох окремих функцій SUMIF (по одному для кожного регіону).
Очікувана Формула: =SUMIF(C:C; "Захід"; D:D) + SUMIF(C:C; "Північ"; D:D)

Завдання 12. Порівняльний Аналіз Синтаксису (SUMIF vs SUMIFS)

Інструкція: Розрахуйте загальну Вартість замовлень, де Регіон="Захід", використовуючи спочатку функцію SUMIF, а потім — функцію SUMIFS. Порівняйте їхній синтаксис.
Підказка 1 (SUMIF): Діапазон умови йде першим, діапазон суми — останнім.
Підказка 2 (SUMIFS): Діапазон суми йде першим.
Очікувана Формула (SUMIF): =SUMIF(C:C; "Захід"; D:D)
Очікувана Формула (SUMIFS): =SUMIFS(D:D; C:C; "Захід")

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

ІНФОРМАТИКА НУШ