Формулы Excel - подсчет количества строк для определенного совпадающего значения

Подсчет количества строк для определенного значения соответствия

Синтаксис используемых функций

 СУММ (диапазон)
MMULT (массив1, массив2)
КОЛОННЫ ([ссылка])
ТРАНСП (массив)

Функция SUM, одна из функций math и trig, добавляет значения.
Функция MMULT возвращает матричное произведение двух массивов. В результате получается массив с тем же количеством строк, что и array1, и таким же количеством столбцов, что и array2.
Функция COLUMN возвращает номер столбца данной ссылки на ячейку.
Функция TRANSPOSE возвращает вертикальный диапазон ячеек в виде горизонтального диапазона или наоборот.

Что делать?

Для подсчета строк, которые содержат как минимум определенное количество совпадающих значений, можно использовать формулу массива на основе функций MMULT, TRANSPOSE, COLUMN и SUM. В показанном примере формула в G7:

{=SUM(--(MMULT(--(C5:F10>H7),TRANSPOSE(COLUMN(C5:F10)^0))>=2))}

Приведенная выше формула является формулой массива, она должна быть использована ctrl + shift + enter key.

формула

=SUM(--(MMULT(--(C5:F10>H7),TRANSPOSE(COLUMN(C5:F10)^0))>=2))

Как работает эта формула

Внутри приведенной выше формулы используются логические критерии:

--(C5:F10>H7)

Эта формула генерирует TRUE и FALSE результат для каждого значения в диапазоне C5: F10, а двойное отрицание (-) преобразует его в 1 и 0, и массив выглядит так:

{1;0;1;1; 0;0;1;0; 0;1;0;1; 0;0;1;0; 1;1;0;0; 0;1;0;1;}

Этот массив состоит из 6 строк и 4 столбцов (6x4) и предназначен для следующего выполнения функции MMULT как массив1, а массив2 создается из

TRANSPOSE(COLUMN(C5:F10)))

Функция COLUMN используется для генерации числового массива правильного размера. Поэтому умножение матриц с функцией MMULT выполнено.
Количество столбцов в массиве 1 (4) должно быть равно количеству строк в массиве 2.

COLUMN возвращает массив из 4 столбцов {3,4,5,6}, а TRANSPOSE заменяет этот массив на массив из 4 строк {3; 4; 5; 6;}. Затем MMULT запускается и возвращает результат массива 6 x 1, который выглядит как -

{14;0;10;0;7;10}

Поэтому проверьте ненулевые записи с> 2 и снова преобразуйте TRUE FALSE в 1 и 0 с двойным отрицанием, и массив будет выглядеть как -

{1;0;1;0;1;1;}

И, наконец, функция SUM добавляет массив и возвращает результат.

Предыдущая: Excel Formulas - Подсчитать количество ячеек, содержащих любой текст
Далее: формулы Excel - подсчет строк, соответствующих двум критериям, в двух столбцах в строке