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