СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН в Excel (эксель)

=СРЗНАЧЕСЛИ(A2:A7;"North";C2:C7) считает среднее продаж из C2:C7 в строках, где в столбце A стоит North. Это функция СРЗНАЧЕСЛИ (по-английски AVERAGEIF). Она работает как СУММЕСЛИ, только делит итог на количество подходящих строк. В таблице ниже формулы записаны по-английски, но их можно вводить и по-русски, с точкой с запятой.

ФормулыСбросить

F2fx

=AVERAGEIF(A2:A7,"North",C2:C7)

	A	B	C	D	E	F
1	Region	Product	Sales		Condition	Average
2	North	Apple	120		North	90
3	South	Pear	45		North, Apple	80
4	North	Pear	110		Over 50	120
5	East	Apple	55
6	South	Apple	195
7	North	Apple	40

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СРЗНАЧЕСЛИ(A2:A7;"North";C2:C7)

F2 усредняет три строки North, 120, 110 и 40, и показывает 90. F3 нужны два условия, North и Apple, поэтому в ней СРЗНАЧЕСЛИМН (AVERAGEIFS): (120 + 40) / 2 = 80. У F4 нет отдельного диапазона усреднения, поэтому она усредняет сами подходящие продажи.

Синтаксис СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН

=AVERAGEIF(range, criteria, [average_range])
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Порядок аргументов здесь такая же ловушка, как у СУММЕСЛИ и СУММЕСЛИМН: СРЗНАЧЕСЛИ ставит диапазон усреднения последним (и его можно пропустить), а СРЗНАЧЕСЛИМН ставит его первым. Критерии в обеих пишутся одинаково: "North", ">50", "<>0", "*apple*" или оператор, присоединённый к ячейке, ">"&F5. Пустые ячейки и текст в диапазоне усреднения пропускаются.

Среднее без учёта нулей

СРЗНАЧ считает 0 значением, поэтому два отсутствовавших ученика с оценкой 0 тянут среднее по классу вниз. С пустыми ячейками иначе: СРЗНАЧ их пропускает. =СРЗНАЧЕСЛИ(B2:B7;"<>0") пропускает и нули.

ФормулыСбросить

E3fx

=AVERAGEIF(B2:B7,"<>0")

	A	B	C	D	E
1	Student	Score		Method	Result
2	Ana	80		AVERAGE	48
3	Ben	0		Ignore zeros	80
4	Cara	90		Count of zeros	2
5	Dan			Count of numbers	5
6	Eva	70
7	Finn	0

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СРЗНАЧЕСЛИ(B2:B7;"<>0")

СРЗНАЧ делит 240 на 5, потому что пустая ячейка Dan не учитывается, а два нуля учитываются, и показывает 48. СРЗНАЧЕСЛИ с "<>0" делит 240 на 3 и показывает 80. Введите 60 в B5, и изменятся обе; введите 0 в B5, и изменится только СРЗНАЧ. Чтобы не учитывать и нули, и отрицательные числа, используйте ">0".

Почему СРЗНАЧЕСЛИ возвращает #ДЕЛ/0!

Когда ничего не подходит, СРЗНАЧЕСЛИ не на что делить, и она возвращает #ДЕЛ/0! (по-английски #DIV/0!, так ошибки показывают таблицы на этой странице). Оберните её в ЕСЛИОШИБКА, чтобы вместо ошибки показать прочерк, сообщение или пустую ячейку.

ФормулыСбросить

F2fx

=AVERAGEIF(A2:A7,"West",C2:C7)

	A	B	C	D	E	F
1	Region	Product	Sales		Formula	Result
2	North	Apple	120		West average	#DIV/0!
3	South	Pear	45		With IFERROR	No sales
4	North	Pear	110		North max	120
5	East	Apple	55		North min	40
6	South	Apple	195		Apple max	195
7	North	Apple	40

#DIV/0! Формула делит на ноль или на пустую ячейку.В русском Excel: =СРЗНАЧЕСЛИ(A2:A7;"West";C2:C7)

Строки West нет, поэтому F2 показывает #DIV/0!, а F3 показывает сообщение. Замените A3 на West, и обе покажут 45.

МАКСЕСЛИ и МИНЕСЛИ

F4, F5 и F6 в таблице выше находят наибольшее и наименьшее значение по условию. Это МАКСЕСЛИ (MAXIFS) и МИНЕСЛИ (MINIFS), у них порядок как у СРЗНАЧЕСЛИМН, первым идёт диапазон для поиска: =МАКСЕСЛИ(C2:C7;A2:A7;"North") возвращает 120, а =МИНЕСЛИ(C2:C7;A2:A7;"North") возвращает 40. В отличие от СРЗНАЧЕСЛИ, когда ничего не подходит, они возвращают 0, а не ошибку.

МАКСЕСЛИ и МИНЕСЛИ требуют Excel 2019 или новее либо Microsoft 365. В Excel 2016 и более ранних то же самое делает =МАКС(ЕСЛИ(A2:A7="North";C2:C7)); в этих версиях вводите её сочетанием Ctrl+Shift+Enter (Cmd+Shift+Enter на Mac).

Практика: среднее по двум условиям

ФормулыСбросить

F2fx

	A	B	C	D	E	F
1	Student	Class	Score		Condition	Average
2	Ana	A	80		Class A, no zeros
3	Ben	B	75
4	Cara	A	0
5	Dan	B	60
6	Eva	A	90
7	Finn	B	0
8	Gus	A	70

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.

Ваша очередь: Посчитайте среднюю оценку класса A без нулей (отсутствовавших учеников). Напишите формулу в F2.

ПроверитьПоказать ответ

Среднее средних: частая ошибка

Среднее из средних по группам разного размера даёт неверное общее среднее. В North три строки, а в South две, поэтому в среднем из двух средних каждая строка South весит больше, чем должна.

ФормулыСбросить

E4fx

=AVERAGE(E2:E3)

	A	B	C	D	E
1	Region	Sales		Formula	Result
2	North	120		North	90
3	South	45		South	120
4	North	110		Average of the two	105
5	South	195		All rows	102
6	North	40

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СРЗНАЧ(E2:E3)

E4 показывает 105, а E5 настоящее среднее пяти строк, 102. Когда группы разного размера, усредняйте сами строки одной СРЗНАЧЕСЛИМН или делите СУММЕСЛИМН на СЧЁТЕСЛИМН с теми же условиями:

=SUMIFS(B2:B6,A2:A6,"North")/COUNTIFS(A2:A6,"North")

В русском Excel: =СУММЕСЛИМН(B2:B6;A2:A6;"North")/СЧЁТЕСЛИМН(A2:A6;"North"). Оценка, взвешенная по кредитам или количеству, это уже другое вычисление: взвешенное среднее.