Excel COUNTIF и COUNTIFS с логикой ИЛИ
В учебнике объясняется, как использовать функции Excel COUNTIF и COUNTIFS для подсчета ячеек с несколькими условиями ИЛИ, например, если ячейка содержит X, Y или Z.
Как всем известно, функция СЧЁТЕСЛИ в Excel предназначена для подсчёта ячеек только на основе одного критерия, в то время как СЧЁТЕСЛИМН оценивает несколько критериев с помощью логического И. Но что, если для вашей задачи требуется логика ИЛИ — при наличии нескольких условий любое из них может совпасть, чтобы быть включенным в подсчет?
Есть несколько возможных решений этой задачи, и в этом руководстве мы подробно рассмотрим их все. Примеры подразумевают, что вы хорошо знаете синтаксис и общие принципы использования обеих функций. Если нет, вы можете начать с пересмотра основ:
Функция Excel СЧЁТЕСЛИ – подсчитывает ячейки с одним критерием.
Функция Excel СЧЁТЕСЛИМН – подсчитывает ячейки с несколькими критериями И.
Теперь, когда все на одной странице, давайте углубимся:
Подсчет ячеек с условиями ИЛИ в Excel
В этом разделе рассматривается простейший сценарий — подсчет ячеек, удовлетворяющих любому (хотя бы одному) из указанных условий.
Формула 1. СЧЁТЕСЛИ + СЧЁТЕСЛИ
Самый простой способ подсчета ячеек, имеющих то или иное значение (Countif а или же б) состоит в том, чтобы написать обычную формулу СЧЕТЕСЛИ для подсчета каждого элемента по отдельности, а затем сложить результаты:
СЧЁТЕСЛИ(диапазон, критерий 1) + СЧЁТЕСЛИ(диапазон, критерий2)
В качестве примера выясним, сколько ячеек в столбце А содержат либо «яблоки», либо «бананы»:
=СЧЁТЕСЛИ(A:A, “яблоки”) + COUNTIF(A:A, “бананы”)
В реальных рабочих листах рекомендуется оперировать диапазонами, а не целыми столбцами, чтобы формула работала быстрее. Чтобы избавить вас от необходимости обновлять формулу каждый раз при изменении условий, введите интересующие вас элементы в предопределенные ячейки, скажем, F1 и G1, и укажите ссылки на эти ячейки. Например:
=СЧЁТЕСЛИ(A2:A10, F1) + СЧЁТЕСЛИ(A2:A10, G1)
Этот метод прекрасно работает для нескольких критериев, но добавление трех или более функций СЧЁТЕСЛИ вместе сделало бы формулу слишком громоздкой. В этом случае вам лучше придерживаться одного из следующих вариантов.
Формула 2. СЧЁТЕСЛИ с константой массива
Вот более компактная версия формулы СУММЕСЛИ с условиями ИЛИ в Excel:
СУММ(СЧЁТЕСЛИ(диапазон{критерий 1, критерий2, критерий3…}))
Формула строится таким образом:
Во-первых, вы упаковываете все условия в константу массива — отдельные элементы, разделенные запятыми, и массив, заключенный в фигурные скобки, например {“яблоки”, “бананы”, “лимоны”}.
Затем вы включаете константу массива в критерии аргумент обычной формулы СЧЁТЕСЛИ: СЧЁТЕСЛИ(A2:A10, {“яблоки”,”бананы”,”лимоны”})
Наконец, деформируйте формулу СЧЁТЕСЛИ в функции СУММ. Это необходимо, потому что СЧЁТЕСЛИ вернёт 3 отдельных числа для «яблок», «бананов» и «лимонов», и вам нужно сложить эти числа вместе.
Наша полная формула выглядит следующим образом:
=СУММ(СЧЁТЕСЛИ(A2:A10,{“яблоки”,”бананы”,”лимоны”}))
Если вы предпочитаете указывать свои критерии в виде ссылок на диапазоны, вам нужно будет ввести формулу с помощью Ctrl + Shift + Enter, чтобы сделать ее формулой массива. Например:
=СУММ(СЧЁТЕСЛИ(A2:A10;F1:H1))
Обратите внимание на фигурные скобки на скриншоте ниже — это наиболее очевидный признак формулы массива в Excel:
Формула 3. СУММПРОИЗВ
Другой способ подсчета ячеек с логикой ИЛИ в Excel — использовать функцию СУММПРОИЗВ следующим образом:
СУММПРОИЗВ(1*(диапазон знак равнокритерий 1, критерий2, критерий3…}))
Чтобы лучше визуализировать логику, это также можно записать так:
СУММПРОИЗВ((диапазонзнак равнокритерий 1) + (диапазонзнак равнокритерий2) + …)
Формула проверяет каждую ячейку в диапазоне по каждому критерию и возвращает значение ИСТИНА, если критерий выполняется, и ЛОЖЬ в противном случае. В качестве промежуточного результата вы получаете несколько массивов значений ИСТИНА и ЛОЖЬ (количество массивов равно количеству ваших критериев). Затем элементы массива в одной и той же позиции складываются вместе, т. е. первые элементы во всех массивах, вторые элементы и так далее. Операция сложения преобразует логические значения в числа, так что вы получите один массив единиц (один из критериев соответствует) и нулей (ни один из критериев не соответствует). Поскольку все критерии проверяются на одних и тех же ячейках, никакое другое число не может появиться в результирующем массиве — только один исходный массив может иметь TRUE в определенной позиции, другие — FALSE. Наконец, СУММПРОИЗВ складывает элементы результирующего массива, и вы получаете желаемое количество.
Первая формула работает аналогичным образом, с тем отличием, что она возвращает один двумерный массив значений ИСТИНА и ЛОЖЬ, которые вы умножаете на 1, чтобы преобразовать логические значения в 1 и 0 соответственно.
Применительно к нашему выборочному набору данных формулы принимают следующий вид:
=СУММПРОИЗВ(1*(A2:A10={“яблоки”,”бананы”,”лимоны”}))
Или же
=СУММПРОИЗВ((A2:A10=”яблоки”) + (A2:A10=”бананы”) + (A2:A10=”лимоны”))
Замените жестко заданную константу массива ссылкой на диапазон, и вы получите еще более элегантное решение:
=СУММПРОИЗВ(1*(A2:A10=F1:H1))
Примечание. Функция СУММПРОИЗВ работает медленнее, чем СЧЁТЕСЛИ, поэтому эту формулу лучше всего использовать для относительно небольших наборов данных.
Подсчет ячеек с помощью ИЛИ, а также логики И
При работе с большими наборами данных, которые имеют многоуровневые и межуровневые отношения между элементами, есть вероятность, что вам потребуется одновременно подсчитывать ячейки с условиями ИЛИ и И.
В качестве примера получим количество «яблок», «бананов» и «лимонов», которые «доставлены». Как мы это делаем? Для начала переведем наши условия на язык Excel:
- Столбец A: «яблоки», «бананы» или «лимоны».
- Колонка C: «доставлено»
Глядя под другим углом, нам нужно посчитать строки с «яблоками и доставленными» ИЛИ «бананами и доставленными» ИЛИ «лимонами и доставленными». Таким образом, задача сводится к подсчету ячеек с тремя условиями ИЛИ — именно то, что мы делали в предыдущем разделе! Единственное отличие состоит в том, что вы будете использовать СЧЁТЕСЛИМН вместо СЧЁТЕСЛИ для оценки критерия И в каждом условии ИЛИ.
Формула 1. СЧЁТЕСЛИМН + СЧЁТЕСЧЕСЛИМН
Это самая длинная формула, которую проще всего написать 🙂
=СЧЁТЕСЧЕСЛИМН(A2:A10, “яблоки”, C2:C10, “доставлено”) + СЧЁТЕСЧЕСЛИМН(A2:A10, “бананы”, C2:C10, “доставлено”)) + СЧЁТЕСЛИМН(A2:A10, “лимоны”, С2:С10, “доставлено”))
На снимке экрана ниже показана та же формула со ссылками на ячейки:
=СЧЁТЕСЧЕСЛИМН(A2:A10, K1, C2:C10, K2) + СЧЁТЕСЛИМН(A2:A10, L1, C2:C10, K2) + СЧЁТЕСЛИМН(A2:A10, M1,C2:C10, K2)
Формула 2. СЧЁТЕСЛИМН с константой массива
Более компактную формулу СЧЁТЕСЛИМН с логикой И/ИЛИ можно создать, упаковав критерий ИЛИ в константу массива:
=СУММ(СЧЁТЕСЛИМН(A2:A10, {“яблоки”,”бананы”,”лимоны”}, C2:C10, “доставлено”))
При использовании ссылки на диапазон для критериев вам потребуется формула массива, заполненная нажатием Ctrl + Shift + Enter:
=СУММ(СЧЁТЕСЛИМН(A2:A10,F1:H1,C2:C10,F2))
Кончик. При необходимости вы можете использовать подстановочные знаки в критериях любых формул, описанных выше. Например, для подсчета всех видов бананов, таких как «зеленые бананы» или «бананы с золотым пальцем», вы можете использовать эту формулу:
=СУММ(СЧЁТЕСЛИМН(A2:A10, {“яблоки”,”*бананы*”,”лимоны”}, C2:C10, “доставлено”))
Аналогичным образом вы можете создать формулу для подсчета ячеек на основе других типов критериев. Например, чтобы получить количество «яблок», «бананов» или «лимонов», которые «доставлены», и количество которых превышает 200, добавьте еще одну пару критериев диапазон/критерий в СЧЁТЕСЛИМН:
=СУММ(СЧЁТЕСЛИМН(A2:A10, {“яблоки”,”*бананы*”,”лимоны”}, C2:C10, “доставлено”, B2:B10, “>200”))
Или используйте эту формулу массива (вводится через Ctrl + Shift + Enter):
=СУММ(СЧЁТЕСЛИМН(A2:A10,F1:H1,C2:C10,F2, B2:B10, “>”&F3))
Подсчет ячеек с несколькими условиями ИЛИ
В предыдущем примере вы узнали, как проверить один набор условий ИЛИ. Но что, если у вас есть два или более набора, и вы хотите получить в сумме все возможные отношения ИЛИ?
В зависимости от того, сколько условий вам нужно обработать, вы можете использовать либо СЧЁТЕСЛИМН с константой массива, либо СУММПРОИЗВ с ISNUMBER MATCH. Первый относительно легко построить, но он ограничен только двумя наборами условий ИЛИ. Последний может оценивать любое количество условий (конечно, разумное количество, учитывая ограничение Excel в 255 аргументов и 8192 символа на общую длину формулы), но может потребоваться некоторое усилие, чтобы понять логику формулы.
Подсчет ячеек с 2 наборами условий ИЛИ
При работе только с двумя наборами критериев ИЛИ просто добавьте еще одну константу массива в формулу СЧЁТЕСЛИМН, рассмотренную выше.
Чтобы формула заработала, требуется одно минутное, но критическое изменение: используйте горизонтальный массив (элементы, разделенные запятыми) для одного набора критериев и вертикальный массив (элементы, разделенные точкой с запятой) для другого. Это говорит Excel «сопоставить» или «перекрестно вычислить» элементы в двух массивах и вернуть двумерный массив результатов.
В качестве примера посчитаем «яблоки», «бананы» или «лимоны», которые либо «доставлены», либо «в пути»:
=СУММ(СЧЁТЕСЛИМН(A2:A10, {“яблоки”, “бананы”, “лимоны”}, B2:B10, {“доставлено”; “в пути”}))
Обратите внимание на точку с запятой во второй константе массива:
Поскольку Excel является двумерной программой, невозможно построить трехмерный или четырехмерный массив, поэтому эта формула работает только для двух наборов критериев ИЛИ. Чтобы подсчитать по большему количеству критериев, вам придется переключиться на более сложную формулу СУММПРОИЗВ, описанную в следующем примере.
Подсчет ячеек с несколькими наборами условий ИЛИ
Для подсчета ячеек с более чем двумя наборами критериев ИЛИ используйте функцию СУММПРОИЗВ вместе с ПОИСКПОЗ ЕЧИСЛО.
Например, давайте подсчитаем «яблоки», «бананы» или «лимоны», которые «доставлены» или «в пути» и упакованы либо в «пакет», либо в «лоток»:
=СУММПРОИЗВ(ЧИСЛО(СОПОСТАВЛЕНИЕ(A2:A10,{“яблоки”,”бананы”,”лимоны”},0))*
ISNUMBER(MATCH(B2:B10,{“сумка”,”лоток”},0))*
ISNUMBER(MATCH(C2:C10,{“доставлено”,”в пути”},0)))
В основе формулы функция ПОИСКПОЗ проверяет критерии, сравнивая каждую ячейку в указанном диапазоне с соответствующей константой массива. Если совпадение найдено, он возвращает относительную позицию значения, если массив, в противном случае N/A. ISNUMBER преобразует эти значения в TRUE и FALSE, которые равны 1 и 0 соответственно. СУММПРОИЗВ берет его оттуда и умножает элементы массивов. Поскольку умножение на ноль дает ноль, выживают и суммируются только те ячейки, которые имеют 1 во всех массивах.
На скриншоте ниже показан результат:
Вот как вы используете функции COUNTIF и COUNTIFS в Excel для подсчета ячеек с несколькими условиями AND, а также OR. Чтобы поближе познакомиться с формулами, обсуждаемыми в этом руководстве, вы можете загрузить наш образец книги ниже. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь
СЧЁТЕСЛИ в Excel с условиями ИЛИ — примеры (файл .xlsx)
