Excel Formulas - Подсчитать количество строк, содержащих конкретное значение

Подсчитать количество строк, содержащих определенное значение

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

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

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

Что делать?

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

{=SUM(--(MMULT(--(B5:D12=F7),TRANSPOSE(COLUMN(B5:D12)))>0))}

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

формула

=SUM(--(MMULT(--(B5:D12=F7),TRANSPOSE(COLUMN(B5:D12)))>0))

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

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

--(B5:D12=F7)

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

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

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

TRANSPOSE(COLUMN(B5:D12)))

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

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

{3;0;2;2;0;0;3;4;}

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

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

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

Предыдущий: Excel Formulas - Подсчитать количество вхождений в нескольких листах в рабочей книге.
Далее: Excel 2013 - Basic