Формулы Excel — подсчет количества строк, содержащих определенное значение

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

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

 SUM (диапазон) MMULT (array1, array2) COLUMN ([ссылка]) TRANSPOSE (массив) 

Функция СУММ, одна из математических и триггерных функций, складывает значения.
Функция ММУЛЬТ возвращает матричное произведение двух массивов. Результатом является массив с тем же количеством строк, что и array1, и тем же количеством столбцов, что и array2.
Функция COLUMN возвращает номер столбца данной ссылки на ячейку.
Функция TRANSPOSE возвращает вертикальный диапазон ячеек как горизонтальный или наоборот.

Что делать?

Чтобы подсчитать строки, содержащие определенные значений, можно использовать формулу массива, основанную на функциях ММУЛЬТ, ТРАНСПОРТ, СТОЛБЕЦ и СУММ. В показанном примере формула в G7:

  {= СУММ (- (ММУЛЬТИМЕНТ (- (B5: D12 = F7), ТРАНСПОРТИРОВКА (СТОЛБЕЦ (B5:  D12)))> 0))}  

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

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

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

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

  - (B5: D12 = F7)  

Эта формула генерирует результат ИСТИНА и ЛОЖЬ для каждого значения в диапазон 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 столбцов (8×3) и для следующего выполнения переходит к функции MMULT как array1, а массив array2 создается из

  TRANSPOSE (COLUMN (B5: D12)  ))  

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

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

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

Поэтому проверьте наличие ненулевых записей с помощью> 0 и снова преобразуйте ИСТИННУЮ ЛОЖЬ в 1 и 0 с двойным отрицанием, и массив будет выглядеть так —

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

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

Оцените статью
nanomode.ru
Добавить комментарий