СЧЁТЕСЛИ и СУММЕСЛИ
Через десять минут созвон с отделом продаж. От вас ждут две цифры: сколько было заказов на наушники и на какую сумму. Под рукой выгрузка из CRM, где заказы разных товаров идут вперемешку:
СЧЁТ и СУММ тут не помогут: они берут все строки подряд. Нужны функции, которые сначала проверяют условие и берут только подходящие строки.
Как они работают, удобнее разобрать на первых восьми заказах выгрузки: это строки со второй по девятую.
Сколько строк подходит: СЧЁТЕСЛИ
У СЧЁТЕСЛИ два аргумента: диапазон, где проверять, и условие. Заказы наушников считает такая формула:
=СЧЁТЕСЛИ(A2:A9;"Наушники")
Условие пишут в кавычках, потому что для функции это текст. Она сама прикладывает его к каждой ячейке диапазона по очереди: в A2 «Наушники»? А в A3? Поэтому адрес ячейки в условие не пишут.
Ячейку функция сравнивает целиком: «Наушники беспроводные» под условие «Наушники» не подойдут. Большие и маленькие буквы она не различает.
Условие может быть и сравнением. Знак и число пишут вместе, тоже в кавычках: ">5000" значит «больше 5 000». Подходят те же знаки, что и в обычных сравнениях, например "<>Наушники" значит «любой товар, кроме наушников». Условие про сумму проверяют на столбце B, а про товар на столбце A:
На какую сумму: СУММЕСЛИ
СУММЕСЛИ берёт те же два аргумента и добавляет третий: что складывать.
=СУММЕСЛИ(A2:A9;"Наушники";B2:B9)
Функция идёт по строкам. Если в A стоят «Наушники», она берёт сумму из той же строки столбца B. Поэтому оба диапазона должны начинаться и заканчиваться на одних и тех же строках.
На схеме обе функции проходят заказы строка за строкой. Переключите условие на «Дороже 5 000»: проверять и складывать придётся один и тот же столбец сумм.
Среднее по подходящим строкам считает СРЗНАЧЕСЛИ. Аргументы у неё те же, что у СУММЕСЛИ. Средний заказ часов, например, считается так, и получается 3 850 ₽:
=СРЗНАЧЕСЛИ(A2:A9;"Часы";B2:B9)
Самый крупный и самый мелкий заказ по условию находят МАКСЕСЛИ и МИНЕСЛИ, только столбец сумм у них стоит первым, а диапазон с условием после него. Например, =МАКСЕСЛИ(B2:B9;A2:A9;"Часы") вернёт самый крупный заказ часов, 4 300 ₽.
Отчёт к созвону
Коллега уже начал отчёт под заказами, но обе ячейки показывают 0. Почините формулы:
На восьми строках формулы работают. Осталось то, ради чего всё затевалось: вся выгрузка. Формулы те же, меняется только последняя строка диапазонов:
Сводка по всем товарам
На созвоне могут спросить и про остальные товары. Писать для каждого свою формулу с названием в кавычках не нужно: условием может быть ячейка, где это название уже записано. Формулу пишут один раз и протягивают:
=СУММЕСЛИ($A$2:$A$9;D2;$B$2:$B$9)
Диапазоны закреплены знаками доллара, чтобы не съехали при протягивании. А D2 без долларов: строкой ниже условие станет D3, то есть следующим товаром.
Перед созвоном сводку стоит сверить: СУММ по столбцу E должна совпасть с СУММ по всем заказам. Если сводка меньше, в выгрузке есть товар, которого в ней не хватает.