Вычисления с проверкой условий. Функции СУММЕСЛИМН / SUMIFS и другие
Файл с примерами: Расчеты с условиями.xlsx Есть четыре базовые функции для подсчетов без условий: СУММ / SUM (сумма аргументов), СЧЁТ / COUNT (количество чисел) и СЧЁТЗ / COUNTA (количество любых значений), СРЗНАЧ / AVERAGE (среднее арифметическое аргументов).
А есть функции с теми же вычислениями, позволяющие суммировать/подсчитывать/усреднять, но не все ячейки, а только удовлетворяющие определенным условиям.
У этих функций несколько аргументов: диапазон суммирования или усреднения (что суммируем или усредняем; такие аргументы есть у СУММЕСЛИ(МН) и СРЗНАЧЕСЛИ(МН)), диапазоны условий (где ищем) и условия (что ищем).
Важно, чтобы все диапазоны условий и диапазоны суммирования/усреднения были одинаковой размерности. Это могут быть и столбцы целиком (E: E), и диапазоны (E2:E40), и столбцы таблиц (Название_таблицы[Столбец]). Например, если один аргумент — это столбец целиком (D: D), то и другой должен быть в таком же формате (такого же размера — E: E, а не E2:E120).
Функции с окончанием ЕСЛИ / IF позволяют проводить вычисления с одним условием, а функции ЕСЛИМН / IFS — как с одним, так и с несколькими. Все функции — в таблице ниже.
Например, нам нужна сумма продаж по одному каналу продаж — рассылке.
Нужна сумма — значит, мы можем использовать СУММЕСЛИ или СУММЕСЛИМН (условие одно, поэтому подойдут обе функции). Ссылаться можно на столбцы с суммами и с каналами продаж целиком:
=СУММЕСЛИМН(E: E; D: D; I2)
Либо на диапазоны. Условие может задаваться как в виде ссылки на ячейку, где оно указано, так и в кавычках (регистр значения не имеет).
=СУММЕСЛИМН(E2:E50; D2:D50; "рассылка")
Либо на столбцы таблицы:
=СУММЕСЛИМН(Сделки[Сумма];Сделки[Откуда];I2)
Главное, чтобы размеры диапазонов были одинаковыми и чтобы они были параллельны (то есть чтобы диапазон суммирования не начинался, например, с первой строки, а диапазон условия — со второй). В случае со столбцами целиком и с таблицей мы предполагаем, что в диапазоне могут появиться новые данные — все строки, добавленные внизу, в таком случае попадут в расчет. Но формулы с таблицами работают быстрее, так что это предпочтительный вариант.
Если условий несколько, мы перечисляем их попарно с их диапазонами — порядок условий значения не имеет:
=СУММЕСЛИМН(E2:E49;D2:D49;"сайт";B2:B49;"курс")
У функций СРЗНАЧЕСЛИ / AVERAGEIF и СРЗНАЧЕСЛИМН / AVERAGEIFS синтаксис идентичный — только вместо диапазона суммирования тут диапазон усреднения. В следующем примере считаем среднюю продажу через выставку (соответствующее значение в столбце D):
=СРЗНАЧЕСЛИМН(E: E;D: D;"выставка")
Если мы хотим получить количество строк, удовлетворяющих одному или нескольким условиям, то используем функцию СЧЁТЕСЛИ / COUNTIF или СЧЁТЕСЛИМН / COUNTIFS. Работает она аналогично, только диапазона суммирования/усреднения у нее нет — мы ничего не суммируем, а только подсчитываем, сколько строк удовлетворяют заданным условиям:
=СЧЁТЕСЛИМН(B2:B49;"электронная библиотека")
Общие правила записи условий в этих функциях:
• в них используются знаки сравнения: «больше» (>), «меньше» (<), «больше либо равно» (>=), «меньше либо равно» (<=), «не равно» (<>);
• если вы ищете точное совпадение с текстовым значением, а не сравниваете числа и даты, то эти знаки не нужны; просто укажите текст в кавычках или дайте ссылку на ячейку с текстом;
• условие берется в кавычки;
• можно ссылаться на ячейки с условиями (в таком случае нужно объединять знаки в условии со ссылкой на ячейку через амперсанд &) или указывать условия прямо в формуле (обратите внимание, что условия, как любые текстовые значения в формулах, указываются в кавычках).
Вот как записываются условия на разные типы данных.
Обратите внимание, что в условиях всех функций …ЕСЛИМН / …IFS регистр не учитывается, то есть вы можете ввести условие и как "МОСКВА", и как "Москва", и как "москва" — в любом случае все ячейки, в которых это слово встречается (и тоже в любом регистре), попадут в расчет.
СИМВОЛЫ ПОДСТАНОВКИ (WILDCARD CHARACTERS) В ФУНКЦИЯХ …ЕСЛИМН / …IFS
В условиях функций можно использовать два символа подстановки — * (звездочка) и ? (знак вопроса):
* — текстовая строка любой длины, включая нулевую (то есть на месте звездочки может быть любой текст или не быть вообще ничего);
? — один любой символ (на месте знака вопроса не может быть ничего, это строго одна позиция в тексте, занятая пробелом, буквой, цифрой, символом).
Так, например, если мы хотим просуммировать продажи книг одного автора в следующей таблице, нам не подойдет стандартное условие, потому что у авторов есть разные книги, а в столбце с названием есть и название произведения, и имя автора.
И если нам нужно подсчитать продажи книг Роулинг, например, нам необходимо добавить по звездочке слева и справа от фамилии автора в условии функции:
=СУММЕСЛИМН(C: C;A: A;"*Роулинг*")
Это условие — любой текст (в том числе ничего, текст нулевой длины) + Роулинг (в любом регистре, напомним) + любой текст.
То есть слово между звездочек может встречаться в любом месте в ячейке. Если бы было нужно, например, искать ячейки с «Роулинг» только в конце, то условие выглядело бы так:
"*Роулинг"
Здесь все заканчивается на фамилии автора — после нее уже не предполагается никаких символов.
Что, если мы хотим выяснить сумму продаж или среднее значение по книгам с названиями из определенного количества символов? Например, только из четырех, как «Дюна» или «1984».
Названия в нашей таблице в кавычках-«елочках» — этим можно воспользоваться. Но звездочку внутрь них в условии помещать бесполезно, ведь это текст любой длины. То есть следующее условие:
*«*»*
это любой текст, в котором встречаются кавычки-«елочки» с любым же текстом внутри.
Поэтому тут нам понадобится знак вопроса — это один любой символ. А значит, нам подойдет следующий шаблон:
*«????»*
Это любой текст + четыре любых символа внутри кавычек-«елочек» + любой текст.
Если вам нужно найти именно звездочки или знаки вопроса (например, чтобы удалить все звездочки в какой-то таблице), поставьте перед символом тильду (~):
~* — поиск звездочки;
— ? — поиск знака вопроса;
~~ — поиск самой тильды.
В следующем примере суммируем продажи всех книг, в названии которых есть звездочка:
=СУММЕСЛИМН(C: C;A: A;"*~**")
ПОДСЧЕТ УНИКАЛЬНЫХ ЗНАЧЕНИЙ ПО УСЛОВИЯМ: ФУНКЦИЯ COUNTUNIQUEIFS В GOOGLE ТАБЛИЦАХ
Google Таблица с примером: COUNTUNIQUEIFS
В Google Таблицах есть функции для подсчета уникальных значений: COUNTUNIQUE вычисляет количество уникальных значений в диапазоне. Например, мы можем вычислить, сколько городов представлено в следующей таблице.
COUNTUNIQUEFS позволяет делать это с условиями — например, посчитать, сколько клиентов приобретали у нас консультации.