Общая формула
(=SUM(--(MMULT(--(criteria),TRANSPOSE(COLUMN(data)^0))>=N)))
Резюме
Для подсчета строк, содержащих определенные значения, вы можете использовать формулу массива, основанную на функциях ММУЛЬТ, ТРАНСПОРТ, СТОЛБЕЦ и СУММ. В показанном примере формула в K6:
(=SUM(--(MMULT(--((data)=2)))
где data - именованный диапазон C5: I14.
Обратите внимание, что это формула массива, и ее нужно вводить с помощью клавиши Control Shift.
Объяснение
В этой формуле используются следующие логические критерии, действующие изнутри:
(data)<70
где data - именованный диапазон C5: I14. Это генерирует результат ИСТИНА / ЛОЖЬ для каждого значения в данных, а двойной отрицательный результат приводит значения ИСТИНА / ЛОЖЬ к 1 и 0, чтобы получить такой массив:
(0,0,0,1,0,1,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0;0,1,1,0,0,1,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0;0,1,0,0,0,0,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0)
Как и исходные данные, этот массив состоит из 10 строк на 7 столбцов (10 x 7) и входит в функцию MMULT как array1 . Следующий аргумент, array2 , создается с помощью:
TRANSPOSE(COLUMN(data)^0))
Здесь функция COLUMN используется как способ создания числового массива правильного размера, поскольку для умножения матриц требуется, чтобы количество столбцов в array1 (7) было равным количеству строк в array2 .
Функция COLUMN возвращает массив из 7 столбцов (3,4,5,6,7,8,9). Возводя этот массив в степень нуля, мы получаем массив 7 x 1, например (1,1,1,1,1,1,1), который TRANSPOSE заменяет массив 1 x 7, например (1; 1 ; 1; 1; 1; 1; 1).
Затем MMULT запускается и возвращает результат массива 10 x 1 (2; 0; 0; 3; 0; 0; 0; 1; 0; 0), который обрабатывается с логическим выражением> = 2, в результате получается массив ИСТИНА ЛОЖНЫЕ значения:
(ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ).
Мы снова приводим TRUE FALSE к 1 и 0 с двойным отрицанием, чтобы получить окончательный массив внутри SUM:
=SUM((1;0;0;1;0;0;0;0;0;0))
Что правильно возвращает 2, количество имен с минимум 2 баллами ниже 70.
