Formula generică
=SUM(--(MMULT(--(criteria),TRANSPOSE(COLUMN(data)))>0))
rezumat
Pentru a număra rânduri care conțin valori specifice, puteți utiliza o formulă matrice bazată pe funcțiile MMULT, TRANSPOSE, COLUMN și SUM. În exemplul prezentat, formula din G5 este:
(=SUM(--(MMULT(--(data=90),TRANSPOSE(COLUMN(data)))>0)))
unde datele sunt intervalul numit B4: B12.
Notă: aceasta este o formulă de matrice și trebuie introdusă cu control shift shift enter.
Explicaţie
Lucrând din interior spre exterior, criteriile logice utilizate în această formulă sunt:
--(data=90)
unde datele sunt intervalul numit B4: D12. Acest lucru generează un rezultat TRUE / FALSE pentru fiecare valoare din date, iar negativul dublu constrânge valorile TRUE FALSE la 1 și 0 pentru a produce o matrice de acest fel:
(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)
La fel ca datele originale, această matrice are 9 rânduri pe 3 coloane (9 x 3) și intră în funcția MMULT ca matrice1 .
Array2 este derivat cu:
TRANSPOSE(COLUMN(data))
Aceasta este partea complicată și distractivă a acestei formule. Funcția COLUMN este utilizată pur și simplu pentru comoditate ca o modalitate de a genera o matrice numerică de dimensiunea potrivită. Pentru a efectua multiplicarea matricei cu MMULT, numărul de coloane din matricea1 (3) trebuie să fie egal cu numărul de rânduri din matricea2 .
COLUMN returnează matricea cu 3 coloane (2,3,4) și TRANSPOSE schimbă această matrice în matricea cu 3 rânduri (2; 3; 4). MMULT apoi rulează și returnează un rezultat de matrice 9 x 1:
=SUM(--((2;0;7;2;0;0;0;0;4)>0))
Verificăm intrări diferite de zero cu> 0 și constrângem din nou TRUE FALSE la 1 și 0 cu un dublu negativ pentru a obține o matrice finală în SUM:
=SUM((1;0;1;1;0;0;0;0;1))
În această matrice finală, un 1 reprezintă un rând în care testul logic (date = 90) a revenit adevărat. Totalul returnat de SUM este un număr al tuturor rândurilor care conțin numărul 90.
Literalul conține
Dacă trebuie să verificați anumite valori de text, cu alte cuvinte, verificați literalmente dacă celulele conțin anumite valori de text, puteți schimba logica din formula de pe această pagină pentru a utiliza funcția ISNUMBER și SEARCH. De exemplu, pentru a număra celulele / rândurile care conțin „măr” puteți utiliza:
=ISNUMBER(SEARCH("apple",data))
Detalii despre modul în care funcționează această formulă aici.
