- Считать подходящие ячейки с помощью COUNTIF
- Суммировать по условию с помощью SUMIF
- Составлять условия со сравнениями, ссылками на ячейки и подстановочными знаками
- Знать COUNTIFS и SUMIFS для нескольких условий
Магазин Эльвина за неделю продал десятки товаров. Теперь он хочет знать: сколько было продаж электроники и сколько денег они принесли? Вместо того чтобы просматривать список и складывать на калькуляторе, хватит двух функций: COUNTIF и SUMIF.
COUNTIF: подсчёт по условию
- rangeпроверяемые ячейки
- criteriaусловие: число, текст, сравнение или ячейка
| Условие | Что считает |
|---|---|
"Electronics" | ячейки, где записано ровно «Electronics» (регистр не важен) |
">=100" | числа от 100 и больше |
"<>Home" | всё, кроме «Home» |
E2 | значения, равные значению в E2 |
">"&E1 | значения больше числа в E1 |
"*pen*" | текст, содержащий «pen» (* — любые символы) |
"B?" | текст из двух символов, начинающийся с B (? — один символ) |
SUMIF: суммирование по условию
- rangeячейки, по которым проверяется условие
- criteriaусловие (как в COUNTIF)
- sum_rangeячейки для суммирования; если не указаны, суммируется сам
range
В B2:B9 — категории, в C2:C9 — суммы продаж в манатах. Найди количество продаж электроники и их общую сумму. Продажи электроники: 250, 310 и 180 ₼.
Показать решениеСкрыть решение
=COUNTIF(B2:B9,"Electronics") → 3.Сумма:
=SUMIF(B2:B9,"Electronics",C2:C9) → 250 + 310 + 180 = 740 ₼.Условие проверяется в столбце B, а складываются ячейки C из тех же строк.
Несколько условий: COUNTIFS и SUMIFS
Функции с «S» на конце проверяют сразу несколько условий и учитывают только строки, где выполнены все условия. Например, сумма продаж электроники от 200 ₼: =SUMIFS(C2:C9,B2:B9,"Electronics",C2:C9,">=200") → 250 + 310 = 560 ₼. Подсчёт: =COUNTIFS(B2:B9,"Electronics",C2:C9,">=200") → 2. Среднее по условию находит AVERAGEIF.
- Сколько учеников набрали 90 баллов и больше:
=COUNTIF(B2:B30,">=90"). - Все расходы семьи на продукты:
=SUMIF(B2:B60,"Food",C2:C60). - Поиск повторов:
=COUNTIF($A$2:$A$100,A2)>1возвращает TRUE, если имя встречается в списке больше одного раза. - Средний балл только класса 7A:
=AVERAGEIF(C2:C90,"7A",D2:D90).
Главное
=COUNTIF(range, criteria)считает подходящие ячейки.=SUMIF(range, criteria, sum_range)складывает значения в строках, где выполнено условие.- Условие со сравнением пишется в кавычках:
">=100"; с ячейкой:">="&E1. *заменяет любое число символов,?— ровно один.- COUNTIFS/SUMIFS работают с несколькими условиями; в SUMIFS суммируемый диапазон идёт первым.
Проверь себя
Вопросов: 10. Каждый правильный ответ приносит XP.
=COUNTIF(C2:C9,">=100")?