Функция СЧЁТЕСЛИ в Excel с разными условиями: мы объясняем это на примерах.
В этом руководстве объясняется, как использовать функцию СЧЁТЕСЛИ с несколькими критериями в Excel на основе логики И и ИЛИ. Вы найдете примеры для разных типов данных: чисел, дат, текста, подстановочных знаков. Цель этого поста — продемонстрировать разные подходы и помочь выбрать наиболее эффективное решение для каждого конкретного бизнеса.
Начиная с выпуска Excel 2007 года, Microsoft добавила «старших сестер» Excel к функциям выборочного подсчета СУММЕСЛИ, СЧЁТЕСЛИ и СРЕДНЕЛИ — функциям СУММЕСЛИ, СЧЁТЕСЛИ и СРЕДНЕЛИ. На английском языке эти функции выглядят как СУММЕСЛИМН, СЧЁТЕСЛИМН и СРЕДНИМЕСЛИМН, то есть в конце у них есть буква -S, которая в английском означает множественное число. В русской версии эту роль играет -MN.
Их часто путают, потому что они очень похожи друг на друга и рассчитаны на основе определенных критериев.
Разница в том, что функция СЧЁТЕСЛИ предназначена для подсчета ячеек с одним и тем же условием в одном диапазоне, а СЧЁТЕСЛИ может оценивать разные критерии в том же или разных диапазонах.
Вычисляет количество совпадений в нескольких диапазонах на основе одного или нескольких критериев.
Синтаксис функции следующий:
СЧЁТЕСЛИ (интервал1; условие1; [интервал2; условие2]…)
На самом деле нет необходимости запоминать этот синтаксис. Microsoft Excel отобразит аргументы функции, как только вы начнете вводить текст; тема, которую вы вводите, будет выделена жирным шрифтом.
Этот вариант является самым простым, поскольку функция СЧЁТЕСЛИ предназначена для подсчета только ячеек, для которых все указанные параметры имеют значение ИСТИНА. Мы называем это логикой И, потому что функция И работает точно так же.
Предположим, у нас есть список продуктов, как показано на скриншоте ниже. Вы хотите знать количество продуктов, которые есть в наличии (у них значение в столбце B больше 0), но еще не проданных (значение в столбце D равно 0).
Задачу можно сделать так:
= СЧЁТЕСЛИ (B2: B11; G1; D2: D11; G2)
или
= СЧЁТЕСЛИ (B2: B11; «> 0»; D2: D11,0)
Видим, что 2 товара (крыжовник и ежевика) есть в наличии, но не в продаже.
Если вы хотите подсчитать элементы по одним и тем же критериям, вам все равно нужно указать каждую пару диапазон / условие отдельно.
Например, вот правильный подход к подсчету элементов, у которых 0 как в столбце B, так и в столбце D:
= СЧЁТЕСЛИ (B2: B11,0; D2: D11,0)
Мы получаем 1, потому что только Plum имеет значение «0» в обоих столбцах.
Использование упрощенной версии с ограничением выбора, например = COUNTIFS (B2: D11; 0), даст другой результат: общее количество ячеек в B2: D11, содержащих ноль (в этом примере это 5).
Как вы видели в приведенных выше примерах, подсчет ячеек, отвечающих всем вышеперечисленным критериям, прост, потому что функция СЧЁТЕСЛИМН предназначена для выполнения этой работы.
Но что, если вы хотите подсчитать значения, для которых хотя бы одно из указанных условий истинно, то есть использовать логику ИЛИ? В принципе, это можно сделать двумя способами: 1) путем добавления нескольких формул СЧЁТЕСЛИ или 2) с помощью комбинации СУММ + СЧЁТЕСЛИ с константой массива.
Считаем заказы со статусом «Отменено» и «Ожидание». Для этого вы можете просто написать 2 обычные формулы СЧЁТЕСЛИ и затем сложить результаты:
= СЧЁТЕСЛИ (E2: E11, «Отменено») + СЧЁТЕСЛИ (E2: E11, «Ожидание»)
Если вам нужно оценить более одного параметра выбора, используйте COUNTPIIF.
Чтобы узнать количество «отмененных» и «ожидающих» заказов на клубнику, используйте эту опцию:
= СЧЁТЕСЛИ (A2: A11, «клубника»; E2: E11, «Отменено») + СЧЁТЕСЛИ (LA2: A11, «клубника»; E2: E11, «Ожидание»)
В ситуациях, когда вам нужно оценить множество критериев, описанный выше подход — не лучший вариант, потому что ваша формула станет слишком громоздкой. Чтобы выполнить те же вычисления в более компактной форме, перечислите все критерии в константе массива и предоставьте этот массив в качестве аргумента функции COUNTPIIF.
Введите СЧЁТЕСЛИМН в функцию СУММ, например:
СУММ (СЧЁТЕСЛИМН (диапазон; {«условие1»; «условие2»; «условие3»;…}))
В нашей таблице с примерами подсчета заказов со статусом «Отменено» или «В ожидании» расчет будет выглядеть так:
= СУММ (СЧЁТЕСЛИМН (E2: E11; {«Отменено», «В ожидании»}))
Массив означает, что мы сначала ищем все отмененные ордера, затем отложенные ордера. В результате получается двузначная матрица итогов. А затем функция СУММ складывает их.
Точно так же вы можете использовать две или более пары диапазон / условие. Чтобы рассчитать количество отмененных или отложенных заказов на клубнику, используйте это выражение:
= СУММ (СЧЁТЕСЛИМН (A2: A11; «Клубника»; E2: E11; {«Отменено», «Ожидает рассмотрения»}))
СЧЁТЕСЛИМН вычисляет 2 типа итогов: 1) на основе множества ограничений (объясненных в примерах выше) и 2) когда числа находятся между двумя указанными значениями. Последнее можно сделать двумя способами: с помощью функции СЧЁТЕСЛИ или вычитанием одного СЧЁТЕСЛИ из другого.
Чтобы узнать, сколько поступило заказов с количеством товаров от 10 до 20, сделаем так:
= СЧЁТЕСЛИ (D2: D11; «> 10»; D2: D11; «
Тот же результат можно получить, вычитая одну формулу СЧЁТЕСЛИ из другой. Сначала мы вычисляем, сколько чисел больше значения нижней границы диапазона (10 в этом примере). Второй возвращает количество заказов, превышающих верхний предел (в данном случае 20). Разница между ними — это результат, который вы ищете.
= СЧЁТЕСЛИ (G2: D11; «> 10») — СЧЁТЕСЛИ (G2: D11; «> 20»)
Это выражение вернет ту же сумму, что и на изображении выше.
При использовании логических операторов, таких как «>», « =» вместе со ссылками на ячейки, обязательно заключите оператор в «двойные кавычки» и добавьте амперсанд (&) перед ссылкой. Другими словами, требование выбора должно быть представлено в виде текста, заключенного в кавычки.
рис6
В этом примере мы будем считать заказы с количеством более 30 единиц, несмотря на то, что на складе было менее 50 единиц товара.
= СЧЁТЕСЛИ (B2: B11; « 30″)
или
= СЧЁТЕСЛИ (B2: B11; «» & G2)
если вы отметили значения ограничений в определенных ячейках, например в G1 и G2, и обратитесь к ним.
Традиционно можно использовать следующие подстановочные знаки:
Примечание. Если вы хотите подсчитывать ячейки, в которых есть вопросительный знак или звездочка, как буквы, поставьте тильду (~) перед звездочкой или вопросительным знаком в записи параметра поиска.
Теперь посмотрим, как можно использовать подстановочный знак.
Допустим, у нас есть список заказов, к которым менеджеры прикреплены лично. Вы хотите знать, сколько заказов уже было кому-то назначено и при этом для них установлен крайний срок. Другими словами, в столбцах B и E таблицы есть значения.
Нам нужно узнать количество заказов, по которым заполнены столбцы B и E:
= СЧЁТЕСЛИ (B2: B21; «*»; E2: E21;»»&»»)
Обратите внимание, что в первом критерии мы используем подстановочный знак *, поскольку мы рассматриваем текстовые значения (фамилии). По второму критерию мы разбираем даты, потом пишем по-другому: «» & «» (значит — не равно пустому значению).
Правила работы с датами очень похожи на рассмотренные выше вычисления с числами.
Вы также можете использовать СЧЁТЕСЛИ с двумя критериями или комбинацию двух функций СЧЁТЕСЛИ для подсчета дат, попадающих в определенный временной диапазон.
Следующие выражения подсчитывают в областях от D2 до D21 количество дат, которые попадают в период с 1 по 7 февраля 2020 года включительно:
= СЧЁТЕСЛИ (G2: G21; «> = 01.02.2020»; G2: G21; «
или
= СЧЁТЕСЛИ (G2: D21; «> =» & H3; D2: D21; «
Точно так же вы можете использовать СЧЁТЕСЛИМН, чтобы подсчитать количество дат в разных столбцах, которые соответствуют двум или более требованиям. Например, посчитаем, сколько заказов было принято до 1 февраля и доставлено после 5 февраля:
Как обычно, будем писать двумя способами: со ссылками и без них:
= СЧЁТЕСЛИ (D2: D21; «> =» & H3; E2: E21; «> =» & H4)
а также
= СЧЁТЕСЛИ (G2: D21; «> = 01.02.2020»; E2: E21; «> = 05.02.2020»)
вы можете использовать функцию СЕГОДНЯ () для подсчета дат относительно сегодняшнего дня.
Эта формула с двумя областями и двумя критериями покажет вам, сколько товаров уже было куплено, но еще не доставлено.
= СЧЁТЕСЛИ (G2: D21; «» & СЕГОДНЯ())
Он допускает множество возможных вариаций. В частности, вы можете настроить его так, чтобы он подсчитывал, сколько заказов было размещено более недели назад и еще не доставлено:
= СЧЁТЕСЛИ (G2: D21; «» & СЕГОДНЯ())
Таким образом, вы можете подсчитать клетки, соответствующие различным условиям.
