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

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

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

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

Общая формула

=СУММПРОИЗВ(--((ЕЧИСЛО(НАЙТИ("критерий1";диапазон)) + ЕЧИСЛО(НАЙТИ("критерий2";диапазон)))>0))

Аргументы

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

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

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

=СУММПРОИЗВ(--((ЕЧИСЛО(НАЙТИ(D3;B3:B9)) + ЕЧИСЛО(НАЙТИ(D4;B3:B9)))>0))

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

=СУММПРОИЗВ(--((ЕЧИСЛО(НАЙТИ(D3;B3:B9)) + ЕЧИСЛО(НАЙТИ(D4;B3:B9)))>0))

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

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

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