Формула на Excel: Бройте редове, които съдържат конкретни стойности -

Обща формула

=SUM(--(MMULT(--(criteria),TRANSPOSE(COLUMN(data)))>0))

Обобщение

За да преброите редове, които съдържат конкретни стойности, можете да използвате формула на масив, базирана на функциите MMULT, TRANSPOSE, COLUMN и SUM. В показания пример формулата в G5 е:

(=SUM(--(MMULT(--(data=90),TRANSPOSE(COLUMN(data)))>0)))

където данните са наименованите диапазони B4: B12.

Забележка: това е формула на масив и трябва да се въведе с контрола shift enter.

Обяснение

Работейки отвътре навън, логическите критерии, използвани в тази формула, са:

--(data=90)

където данните са наименуваният диапазон B4: D12. Това генерира TRUE / FALSE резултат за всяка стойност в данните и двойното отрицателно принуждава TRUE FALSE стойности на 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))

Подробности за това как работи тази формула тук.

Добри връзки

Отговор на Stackoverflow от XOR LX

Интересни статии...