Расчёты по нескольким условиям: COUNTIFS, SUMIFS, IFS, SWITCH

В этом уроке ты узнаешь

Правильно записывать пары условий в COUNTIFS, SUMIFS и AVERAGEIFS
Использовать в условиях операторы сравнения, подстановочные знаки и ссылки на ячейки
Заменять вложенные IF на IFS, SWITCH или таблицу поиска
Знать разницу между IFERROR и IFNA

Менеджер интернет-магазина спрашивает: «Сколько заказов электроники в Баку и на какую сумму? Какова сумма оплаченных заказов дороже 500 ₼?» COUNTIF и SUMIF проверяют только одно условие. В этом уроке поработаем с их «старшими братьями» — функциями с буквой S на конце.

Учебная таблица A1:D11: City, Category, Amount, Status и 10 заказов. Баку: 850 (Electronics, Paid), 540 (Home, Pending), 460 (Electronics, Pending), 275 (Home, Paid), 1320 (Electronics, Paid). Гянджа: 320 (Home, Paid), 980 (Electronics, Paid), 150 (Home, Paid). Сумгайыт: 1200 (Electronics, Paid), 610 (Home, Pending).

Подсчёт и суммирование по нескольким условиям: COUNTIFS, SUMIFS, AVERAGEIFS

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

где:

sum_rangeскладываемые числа — в SUMIFS это первый аргумент
criteria_range, criteriaпара «где проверяем — что проверяем»; до 127 пар

COUNTIFS состоит только из пар; AVERAGEIFS, как и SUMIFS, сначала принимает диапазон для усреднения. Все условия должны выполняться одновременно (логика И).

Вопрос	Формула	Результат
Сколько заказов электроники в Баку?	`=COUNTIFS(A2:A11,"Baku",B2:B11,"Electronics")`	3
Их сумма	`=SUMIFS(C2:C11,A2:A11,"Baku",B2:B11,"Electronics")`	2630
Оплачено и ≥ 500 ₼	`=SUMIFS(C2:C11,D2:D11,"Paid",C2:C11,">=500")`	4350
Сколько заказов от 500 до 1000?	`=COUNTIFS(C2:C11,">=500",C2:C11,"<=1000")`	4
Средний заказ Гянджи	`=AVERAGEIFS(C2:C11,A2:A11,"Ganja")`	483,33
Всё, кроме Баку	`=SUMIFS(C2:C11,A2:A11,"<>Baku")`	3260
Категории на «E»	`=COUNTIFS(B2:B11,"E*")`	5

Интерактив

Загрузка симуляции…

Заказы (в манатах): логика SUMIFS через вспомогательный столбец и ступенчатая формула бонуса.

IFS и SWITCH: решения без вложенных IF

Бонус и стоимость доставки

Правила: за неоплаченный заказ бонуса нет; если оплаченный заказ от 500 ₼, менеджер получает 5%, иначе 2%. Доставка: от 1000 ₼ — бесплатно, от 500 ₼ — 5 ₼, остальные — 10 ₼. Посчитай для первых трёх заказов (850 Paid, 320 Paid, 540 Pending).

Показать решение

Бонус через IFS: =IFS(D2<>"Paid",0,C2>=500,C2*5%,TRUE,C2*2%)
850 Paid → 850 · 0,05 = 42,50 ₼; 320 Paid → 320 · 0,02 = 6,40 ₼; 540 Pending → 0.
Доставка: =IFS(C2>=1000,0,C2>=500,5,TRUE,10)
850 → 5 ₼; 320 → 10 ₼; 540 → 5 ₼.
Последнее TRUE означает «все остальные случаи»; без него, если ни одно условие не выполнено, IFS вернёт #N/A.
Сумма бонусов по всем 10 заказам — 232,40 ₼.

SWITCH сравнивает одно выражение со списком значений и возвращает соответствующий результат: =SWITCH(A2,"Baku","Aysel","Ganja","Rauf","Sumgait","Elvin","—") показывает ответственного менеджера по городу, а для города не из списка — последнее значение («—»). SWITCH проверяет только равенство; если нужны > и <, выбирай IFS.

Ситуация	Лучший выбор
Два исхода	IF
3–5 порогов, сравнения `>=`	IFS
Список точных значений (код → название)	SWITCH
Много ступеней или часто меняющиеся правила	отдельная таблица + XLOOKUP

Четыре правила вложенной логики

Сначала защитное условие. Первым делом проверь пустые или неверные данные: =IF(C2="","",IFS(…)) — чтобы пустые строки не показывали ложных результатов.
Самое узкое условие — первым. Excel останавливается на первом истинном условии, поэтому >=1000 должно стоять раньше >=500.
AND — для «оба», OR — для исключений. «Оплачен и больше 500» — AND(D2="Paid",C2>=500); «VIP или больше 1000» — OR(…).
Выноси числа из формул. Ставки и пороги держи в отдельных ячейках — при смене правила исправишь одну ячейку, а не сотню формул.

Обработка ошибок: IFERROR и IFNA

=IFERROR(значение, если_ошибка) перехватывает любую ошибку: #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?. А =IFNA(значение, если_НД) — только #N/A. Для поиска IFNA безопаснее: она скрывает случай «не найдено», но продолжает показывать опечатку в формуле (#NAME?) или удалённый столбец (#REF!).

После имени функции вставить названия её аргументовCtrl+Shift+A

Включить/выключить фильтр — чтобы проверить результат COUNTIFS глазамиCtrl+Shift+L

Показать на листе формулы вместо результатовCtrl+`

Автосумма: формула SUM под выделенным столбцомAlt+=

Главное

COUNTIFS, SUMIFS и AVERAGEIFS проверяют все условия одновременно (И); в SUMIFS диапазон суммирования идёт первым.
Условия: ">=500", "<>Baku", "E*"; чтобы взять из ячейки — ">="&G2.
IFS проверяет условия по порядку; последнее TRUE означает «все остальные случаи».
SWITCH сравнивает точные значения; для многих ступеней лучше отдельная таблица.
IFERROR ловит любую ошибку, IFNA — только #N/A; для поиска IFNA безопаснее.

Проверь себя

Вопросов: 10. Каждый правильный ответ приносит XP.

1 / 10

Какая формула правильно считает сумму оплаченных заказов в Баку?