Практическое задание: работа с функциями СУММЕСЛИ и СУММЕСЛИМН

Практическое задание:

работа с функциями СУММЕСЛИ и СУММЕСЛИМН

Цель: отработать навык подсчёта сумм по условиям в таблицах.
Время: 60–90 мин.
Программа: Microsoft Excel или Google Таблицы.

Шаблон таблицы для заданий

Заполните 10 строк по образцу, затем выполняйте задания.

Порядок выполнения

1.     Создайте таблицу (см. шаблон выше).

2.     Для каждого задания:

o    прочитайте условие;

o    вызовите функцию, заполнив аргументы функции;

o    проверьте результат (сравнивайте с ручной суммой или фильтрацией).

3.     Если формула выдаёт ошибку:

o    проверьте кавычки и точки с запятой;

o    убедитесь, что диапазоны совпадают по размеру;

o    перечитайте условие задания.

Задания

Задание 1. Простая сумма по условию (СУММЕСЛИ)

Условие: посчитайте общую сумму продаж для товара «Яблоко».

Данные: таблица «Продажи фруктов» (столбцы: A — Товар, B — Количество, C — Цена, D — Сумма).

Формула: =СУММЕСЛИ(A2:A10; "Яблоко"; D2:D10)

Проверка: должно получиться число (например, 2 500 руб.).

Задание 2. Сумма с числовым условием (СУММЕСЛИ)

Условие: посчитайте сумму всех продаж, где количество > 5 кг.

Формула: =СУММЕСЛИ(B2:B10; ">5"; D2:D10)

Подсказка: не забудьте кавычки для условия ">5".

Задание 3. Сумма по тексту (СУММЕСЛИ)

Условие: найдите общую выручку от продажи «Бананов».

Формула: =СУММЕСЛИ(A2:A10; "Банан"; D2:D10)

Проверка: сравните с ручной суммой строк, где товар — «Банан».

Задание 4. Два условия: товар и цена (СУММЕСЛИМН)

Условие: посчитайте сумму продаж для «Груши» с ценой ≥ 100 руб./кг.

Данные: столбцы: A — Товар, C — Цена, D — Сумма.

Формула: =СУММЕСЛИМН(D2:D10; A2:A10; "Груша"; C2:C10; ">=100")

Проверка: выделите строки с «Грушей» и ценой ≥ 100, сложите их суммы вручную.

Задание 5. Три условия: товар, количество, дата (СУММЕСЛИМН)

Условие: найдите сумму продаж «Апельсинов», где количество > 3 кг и дата — «01.04.2025».

Данные: столбцы: A — Товар, B — Количество, E — Дата, D — Сумма.

Формула: =СУММЕСЛИМН(D2:D10; A2:A10; "Апельсин"; B2:B10; ">3"; E2:E10; "01.04.2025")

Подсказка: дата в условии пишется в кавычках как текст.

Задание 6. Исключение товара (СУММЕСЛИМН)

Условие: посчитайте сумму всех продаж, кроме «Яблока».

Формула: =СУММЕСЛИМН(D2:D10; A2:A10; "<>Яблоко")

Объяснение: <> означает «не равно».

Задание 7. Диапазон значений (СУММЕСЛИМН)

Условие: найдите сумму продаж, где цена от 50 до 150 руб./кг.

Формула: =СУММЕСЛИМН(D2:D10; C2:C10; ">=50"; C2:C10; "<=150")

Проверка: выделите строки с ценой 50–150, сложите суммы.

Задание 8. Два текстовых условия (СУММЕСЛИМН)

Условие: посчитайте выручку от «Бананов» в регионе «Юг».

Данные: столбцы: A — Товар, F — Регион, D — Сумма.

Формула: =СУММЕСЛИМН(D2:D10; A2:A10; "Банан"; F2:F10; "Юг")

Подсказка: оба условия должны выполняться одновременно.

Задание 9. Сочетание текста и числа (СУММЕСЛИМН)

Условие: найдите сумму продаж «Груш» с количеством ≥ 4 кг.

Формула: =СУММЕСЛИМН(D2:D10; A2:A10; "Груша"; B2:B10; ">=4")

Проверка: отфильтруйте таблицу по товару и количеству, посчитайте сумму.

Задание 10. Сложное условие с датами (СУММЕСЛИМН)

Условие: посчитайте сумму продаж «Апельсинов» за период с 01.04.2025 по 05.04.2025.

Формула: =СУММЕСЛИМН(D2:D10; A2:A10; "Апельсин"; E2:E10; ">=01.04.2025"; E2:E10; "<=05.04.2025")

Подсказка: даты в условии — в кавычках, формат как в таблице.

Дополнительные практические задания по функциям СУММЕСЛИ и СУММЕСЛИМН

Цель: закрепить навыки работы с условными суммами на разноплановых примерах.
Формат: работа с готовыми таблицами, пошаговое выполнение.
Уровень сложности: от базового к продвинутому.

Задание 11. Сумма по категории (базовый уровень)

Условие: посчитайте общую выручку от продажи овощей в магазине.

Таблица «Товары»:

Формула: =СУММЕСЛИ(B2:B6; "Овощи"; E2:E6)

Ответ: 3 900 руб.

Проверка: сложите вручную строки с категорией «Овощи».

Задание 12. Сумма с отрицательным условием (средний уровень)

Условие: найдите сумму продаж всех товаров, кроме фруктов.
Используйте ту же таблицу «Товары».

Формула: =СУММЕСЛИ(B2:B6; "<>Фрукты"; E2:E6)

Ответ: 3 900 руб. (только овощи).

Подсказка: <> означает «не равно».

Задание 13. Два условия: категория и цена (средний уровень)

Условие: посчитайте выручку от овощей, где цена выше 100 руб.
Таблица: та же («Товары»).

Формула: =СУММЕСЛИМН(E2:E6; B2:B6; "Овощи"; C2:C6; ">100")

Ответ: 3 000 руб. (помидор + перец).

Проверка: отфильтруйте строки: категория = «Овощи» и цена > 100.

Задание 14. Сумма по диапазону значений (средний уровень)

Условие: найдите сумму продаж товаров, где количество проданного от 10 до 15 шт.

Таблица «Продажи»:

Формула:

=СУММЕСЛИМН(D2:D6; C2:C6; ">=10"; C2:C6; "<=15")

Ответ: 4 600 руб. (чай + сахар + соль).

Подсказка: используйте два условия для диапазона.

Задание 15. Три условия: товар, регион, дата (продвинутый уровень)

Условие: посчитайте сумму продаж чая в регионе «Центр» за апрель 2025 г.

Таблица «Региональные продажи»:

Формула:

=СУММЕСЛИМН(D2:D6; A2:A6; "Чай"; B2:B6; "Центр"; C2:C6; ">=01.04.2025"; C2:C6; "<=30.04.2025")

Ответ: 3 300 руб. (две строки с чаем в Центре за апрель).

Важно: даты в кавычках, формат как в таблице.

Задание 16. Сумма по части текста (средний уровень)

Условие: найдите выручку от товаров, в названии которых есть слово «молоко» (например, «молоко цельное», «молоко обезжир.»).

Таблица «Молочная продукция»:

Формула:

=СУММЕСЛИ(A2:A6; "*молоко*"; D2:D6)

Ответ: 6 800 руб. (* — любой текст до/после «молоко»).

Подсказка: символ * заменяет любые символы.

Задание 17. Сумма с логическим «ИЛИ» (продвинутый уровень)

Условие: посчитайте сумму продаж для товаров «Чай» или «Кофе».

Используйте таблицу из задания 14 («Продажи»).

Решение:
Так как СУММЕСЛИ не поддерживает «ИЛИ» напрямую, используем две формулы: =СУММЕСЛИ(A2:A6; "Чай"; D2:D6) + СУММЕСЛИ(A2:A6; "Кофе"; D2:D6)

Ответ: 5 400 руб. (3 000 + 2 400).

Объяснение: складываем результаты двух отдельных условий.

Задание 18. Сумма по цвету ячейки (визуальная задача)

Условие: посчитайте сумму только для зелёных ячеек в столбце «Сумма».

Таблица «Акции» (ячейки выделены цветом):

Примечание:

• В Excel/Google Таблицах нет встроенной функции для суммы по цвету.
• Решение: вручную выделите зелёные ячейки и используйте =СУММ(...).
• Цель задания: показать ограничение стандартных функций и необходимость визуального контроля.

Задание 19. Сумма с округлением (продвинутый уровень)

Условие: найдите сумму продаж, где цена округлена до целого (например, 100, 150, но не 99,5).

Таблица «Ценник»:

Формула:

=СУММЕСЛИМН(D2:D5; C2:C5; "=ЦЕЛОЕ(C2:C5)")