Подсчёт и сумма по условию

В таблице сотни покупок. Сколько из них — книги и на какую сумму? Столбец признаков из урока 3.1 ответит, но короче так: СЧЁТЕСЛИ и СУММЕСЛИ проверяют условие по каждой ячейке диапазона сами — без столбца признаков.

Разберём подсчёт и сумму по одному и нескольким условиям, а затем частоты чисел в строке — основу задания 9 ЕГЭ.

СЧЁТЕСЛИ(диапазон;критерий) считает ячейки диапазона, подходящие под критерий. Критерием бывает число, текст или сравнение:

Первая формула считает пятёрки, вторая — числа больше пяти, третья — текст «Москва». Сравнение в критерии пишут в кавычках, как текст: ">5" — особый вид аргумента этой функции. В обычной проверке из урока 3.1 кавычек нет: =A2>5.

СЧЁТЕСЛИ не различает регистр: «москва» в B6 засчитана как «Москва». Если искомое значение лежит в ячейке, критерий — её адрес без кавычек: =СЧЁТЕСЛИ(A2:A7;E9) (строка 6 листа). Запись "E9" искала бы буквально текст «E9».

Критерий «больше значения из ячейки» собирают знаком &: ">"&E9 превращается в ">7". На листе выше порог лежит в E9, и числа больше порога считает

Знак & склеивает текст «>» и значение E9 в один критерий. В ">E9" адрес спрятан внутри кавычек и не заменяется значением. Вот как собирается критерий:

Так же пишут ">="&E9 и "<>"&E9; для равенства склейка не нужна — достаточно E9. Поменяй порог в E9 на листе выше: изменятся строки 5 и 6.

СЧЁТЕСЛИМН проверяет несколько условий для одной и той же строки — это И между критериями. Аргументы идут парами «диапазон; критерий»:

Один диапазон указан дважды — к нему два условия. Для разных столбцов: =СЧЁТЕСЛИМН(A2:A7;"Москва";B2:B7;">=60") считает строки, где и город Москва, и балл не меньше 60.

Диапазоны должны описывать одни и те же строки: с A2:A7 и B3:B8 функция сравнивала бы город одной записи с баллом соседней.

СУММЕСЛИ складывает значения тех строк, где выполнено условие; у СУММЕСЛИМН диапазон суммирования стоит первым. Один диапазон проверяется, из другого берутся слагаемые:

A — категория, D — выручка. В каждой строке проверяется категория; если это «Книги», к итогу прибавляется выручка той же строки. Порядок аргументов: диапазон проверки, критерий, диапазон суммирования:

У СУММЕСЛИМН другой порядок: сначала диапазон суммирования, затем пары условий:

Функция складывает выручку строк, удовлетворяющих обоим условиям. Названия похожи, но порядок аргументов нельзя переносить по памяти с одной функции на другую. Читай подсказку при вводе.

СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН вычисляют среднее подходящих числовых значений. Если подходящих чисел нет, среднее не определено. Нулевая условная сумма сама по себе не доказывает, что записи найдены: совпадений могло не быть вообще — как у игрушек в Москве.

«Или» по двум условиям считают одним признаком либо вычитают общую часть. Если категории не пересекаются, результаты можно сложить. Количество строк города Москва или города Казань равно сумме двух отдельных подсчётов, когда в строке ровно один город.

Если условия пересекаются, сложение удвоит общую часть. Например, число делится и на 2, и на 3. Для подсчёта «кратно 2 или 3» удобно сделать один признак через ИЛИ. Другой путь: сложить два количества и вычесть количество строк, удовлетворяющих обоим условиям.

Кратных 2 здесь два (2 и 6), кратных 3 — тоже два (3 и 6), но 6 попало в обе группы: 2 + 2 − 1 = 3.

Одна формула со смешанной ссылкой считает, сколько раз каждое число встречается в своей строке. В строке A2:F2 — 4, 4, 1, 2, 3, 5. В G2 пишем

и копируем вправо до L2. Диапазон $A2:$F2 закреплён по столбцам и остаётся всей строкой, а критерий A2 сдвигается: B2, C2… Пройди по шагам:

Число показывает частоту значения — сколько раз оно встречается в строке. Повторяющееся дважды значение даёт две ячейки с частотой 2, поэтому число двоек в частотах — не число разных повторяющихся значений.

При копировании всей полосы вниз диапазон переходит на следующую строку. Выбери H2 и H3: рамки показывают свою строку и своё число.

По полосе частот СУММЕСЛИ складывает отдельно повторяющиеся и одиночные числа строки. Если G2:L2 — частоты соответствующих значений A2:F2, сумма одиночных элементов вычисляется так:

Сумма всех вхождений повторяющихся элементов:

Для 4, 4, 1, 2, 3, 5 первая сумма равна 11, вторая — 8. Повторяющееся различное значение здесь одно — 4, но его вхождений два. «Сумма повторяющихся элементов» и «сумма разных повторяющихся значений» могут означать разные величины. Читай, считает ли условие каждое вхождение. Поменяй числа строки: например, сделай третье число тоже четвёркой — обе суммы и частоты пересчитаются.