Подсчет клеток, которые содержат либо x или Y Excel

Подсчет клеток, которые содержат либо x или Y Excel

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

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

ЕЧИСЛО   Выдает логическое значение ИСТИНА, если аргумент ссылается на число.

НАЙТИ     Ищет вхождение одного текста в другой (с учетом регистра).

Вариант № 1

Ячейка содержит определенный текст
Подсчет ячеек, содержащих определенный текст
Подсчет клеток

Для подсчета ячеек, содержащих либо одно значение, либо другое, можно либо использовать вспомогательном столбце или более сложные формулы ячейки.

При подсчете клеток с помощью «или» критерии, вы должны быть осторожны, чтобы не получить двойной счет. Например, если подсчет ячеек, содержащих «asd» или «edf»  вы не можете просто сложить две функции СЧЕТЕСЛИ потому что это может привести к двойному подсчету клеток, которые содержат как «asd» и «edf».

Решение, вы можете использовать функцию СУММПРОИЗВ с функцией ЕЧИСЛО + НАЙТИ  комбо. Формула в ячейке E12-это:

=СУММПРОИЗВ(—((ЕЧИСЛО(НАЙТИ(«asd»;D5:D11)) + ЕЧИСЛО(НАЙТИ(«edf»;D5:D11)))>0))

Эта формула основана на формуле:

Функции ЕЧИСЛО(НАЙТИ(«edf»;D5:D11)

При заданном диапазоне ячеек, этот фрагмент возвратит массив значений Истина или Ложь, одно значение для каждой ячейки диапазона. Так как мы создаем два массива (один раз для «asd» и еще для «edf»).

Далее, мы добавляем эти массивы вместе (с +), которая создает новый один массив чисел. Каждое число в этом массиве является результатом сложения значений Истины и Ложь в двух исходных массивов.

Нам нужно добавить эти цифры, но мы не хотим двойной счет. Поэтому мы должны убедиться, что любое значение больше нуля, это всего лишь раз пересчитать. Чтобы сделать это, мы заставляем все значения Истина или Ложь с «>0», потом заставляют 1/0 с двойной отрицательной (—).

Наконец, функция СУММПРОИЗВ добавляет эти цифры вверх.

Вариант № 2

Вспомогательный столбец решение

С вспомогательным столбцом для проверки каждой ячейки в отдельности, проблема менее сложна. Мы можем использовать СЧЕТЕСЛИ с двумя значениями (при условии, как «массив»). Формула в H5 :

=—(СУММ(СЧЁТЕСЛИ(G5;{«*asd*»;»*edf*»}))>0)

СЧЕТЕСЛИ возвращает массив, который содержит два пункта: счетчик для «asd» и рассчитывает на «edf». Во избежание двойного счета, мы добавляем элементы и затем принудительно результат «истина/ложь» с «>0». Наконец, преобразуем значения Истина или Ложь на 1 и 0 с двойной отрицательный (—).

Конечный результат 1 или 0 для каждой ячейки. Чтобы получить общую для всех ячеек в диапазоне, вы будете просто просуммировать столбец.

 

Файл Подсчет клеток, которые содержат либо x или Y xls

 

Комментарии запрещены.

Навигация по записям