Подсчёт ячеек, содержащих либо x, либо y, в диапазоне Excel

Чтобы подсчитать ячейки, содержащие одно из двух значений, некоторые пользователи Excel складывают результаты двух функций СЧЁТЕСЛИ. Однако такой подход по ошибке применяет логику И (подсчитываются ячейки, содержащие одновременно и x, и y), а не логику ИЛИ (когда учитываются ячейки с x или y). Чтобы избежать двойного учёта, в этом руководстве подробно объясняется, как использовать формулу на основе функции СУММПРОИЗВ для точного подсчёта количества ячеек в заданном диапазоне, содержащих либо x, либо y, в Excel.

Как подсчитать ячейки, содержащие либо x, либо y, в диапазоне Excel

Как показано на рисунке ниже, диапазон данных B3:B9 содержит ячейки, в которых необходимо подсчитать количество значений «KTE» или «KTO». Для этого используйте приведённую ниже формулу.

Универсальная формула

=SUMPRODUCT(--((ISNUMBER(FIND("criteria1",rng)) + ISNUMBER(FIND("criteria2",rng)))>,0))

Аргументы

Как использовать эту формулу?

1. Выберите пустую ячейку, в которую будет выведен результат.

2. Введите в неё приведённую ниже формулу и нажмите клавишу Enter, чтобы получить результат.

=SUMPRODUCT(--((ISNUMBER(FIND(D3,B3:B9)) + ISNUMBER(FIND(D4,B3:B9)))>,0))

Как работают эти формулы?

=SUMPRODUCT(--((ISNUMBER(FIND(D3,B3:B9)) + ISNUMBER(FIND(D4,B3:B9)))>,0))

• 1. FIND(D3,B3:B9): функция НАЙТИ проверяет, содержится ли значение «KTE» из ячейки D3 в ограниченном диапазоне (B3:B9), и возвращает массив: {1,#ЗНАЧ!,#ЗНАЧ!,#ЗНАЧ!,#ЗНАЧ!,1,#ЗНАЧ!}.
  В этом массиве две единицы означают, что первая и предпоследняя ячейки диапазона B3:B9 содержат значение «KTE», а ошибки #ЗНАЧ! указывают, что «KTE» не найдено в остальных ячейках.
• 2. ISNUMBER{1,#VALUE!, #VALUE!, #VALUE!, #VALUE!,1, #VALUE!}Функция ЕЧИСЛО возвращает ИСТИНА, если элемент массива является числом, и ЛОЖЬ — если это ошибка, формируя новый массив: {ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ}.
• 3. FIND(D4,B3:B9)Эта функция НАЙТИ также проверяет, содержится ли значение «KTO» из ячейки D4 в ограниченном диапазоне B3:B9, и возвращает массив: {#ЗНАЧ!; 1; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!}.
• 4. ISNUMBER{#VALUE!,1,#VALUE!,#VALUE!, #VALUE!,#VALUE!,#VALUE!}Функция ЕЧИСЛО возвращает ИСТИНА, если элемент массива является числом, и ЛОЖЬ — если это ошибка, формируя новый массив: {ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}.
• 5. {TURE;FALSE;FALSE;FALSE;FALSE;TURE;FALSE} + {FALSE;TURE;FALSE;FALSE;FALSE;FALSE;FALSE}Здесь два массива дают результат в виде {1;1;0;0;0;1;0}.
• 6. {1;1;0;0;0;1;0}>0Здесь каждое число в массиве сравнивается с 0, и возвращается результат: {ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ}.
• 7. --({TURE;TRUE;FALSE;FALSE;FALSE;TURE;FALSE})Эти два знака минус преобразуют «ИСТИНА» в 1, а «ЛОЖЬ» — в 0, в результате чего вы получите новый массив: {1;1;0;0;0;1;0}.
• 8. =SUMPRODUCT({1,1,0,0,0,1,0})Функция СУММПРОИЗВ суммирует все числа в массиве и возвращает окончательный результат — в данном случае 3.

Связанные функции

Связанные формулы