Общая формула
=SUM(--(MMULT(--(criteria),TRANSPOSE(COLUMN(data)))>0))
Резюме
Для подсчета строк, содержащих определенные значения, вы можете использовать формулу массива, основанную на функциях ММУЛЬТ, ТРАНСПОРТ, СТОЛБЕЦ и СУММ. В показанном примере формула в G5:
(=SUM(--(MMULT(--(data=90),TRANSPOSE(COLUMN(data)))>0)))
где данные - это именованный диапазон B4: B12.
Примечание. Это формула массива, которую необходимо вводить с помощью клавиши Control Shift.
Объяснение
В этой формуле используются следующие логические критерии, действующие изнутри:
--(data=90)
где data - именованный диапазон B4: D12. Это генерирует результат ИСТИНА / ЛОЖЬ для каждого значения в данных, а двойной отрицательный результат приводит значения ИСТИНА / ЛОЖЬ к 1 и 0, чтобы получить такой массив:
(1,0,0;0,0,0;0,1,1;1,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,1)
Как и исходные данные, этот массив состоит из 9 строк по 3 столбца (9 x 3) и переходит в функцию MMULT как array1 .
Array2 получен с помощью:
TRANSPOSE(COLUMN(data))
Это сложная и забавная часть этой формулы. Функция COLUMN используется просто для удобства как способ сгенерировать числовой массив нужного размера. Чтобы выполнить матричное умножение с MMULT, количество столбцов в array1 (3) должно равняться количеству строк в array2 .
COLUMN возвращает массив из 3 столбцов (2,3,4), а TRANSPOSE изменяет этот массив на массив из 3 строк (2; 3; 4). Затем MMULT запускается и возвращает результат массива 9 x 1:
=SUM(--((2;0;7;2;0;0;0;0;4)>0))
Мы проверяем ненулевые записи с> 0 и снова приводим TRUE FALSE к 1 и 0 с двойным отрицанием, чтобы получить окончательный массив внутри SUM:
=SUM((1;0;1;1;0;0;0;0;1))
В этом последнем массиве 1 представляет строку, в которой логический тест (данные = 90) вернул истину. Сумма, возвращаемая функцией SUM, - это количество всех строк, содержащих число 90.
Литерал содержит
Если вам нужно проверить определенные текстовые значения, другими словами, буквально проверить, содержат ли ячейки определенные текстовые значения, вы можете изменить логику в формуле на этой странице, чтобы использовать функции ISNUMBER и SEARCH. Например, для подсчета ячеек / строк, содержащих слово «яблоко», вы можете использовать:
=ISNUMBER(SEARCH("apple",data))
Подробнее о том, как работает эта формула, можно здесь.
