Splošna formula
=SUM(--(MMULT(--(criteria),TRANSPOSE(COLUMN(data)))>0))
Povzetek
Če želite prešteti vrstice, ki vsebujejo določene vrednosti, lahko uporabite matrično formulo, ki temelji na funkcijah MMULT, TRANSPOSE, COLUMN in SUM. V prikazanem primeru je formula v G5:
(=SUM(--(MMULT(--(data=90),TRANSPOSE(COLUMN(data)))>0)))
kjer so podatki imenovani obseg B4: B12.
Opomba: to je formula matrike in jo je treba vnesti s tipko shift shift enter.
Pojasnilo
Logična merila, uporabljena v tej formuli, delujejo od znotraj navzven:
--(data=90)
kjer so podatki imenovani obseg B4: D12. To ustvari rezultat TRUE / FALSE za vsako vrednost v podatkih, dvojni negativ pa prisili vrednosti TRUE FALSE na 1 in 0, da dobi matriko, kot je ta:
(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)
Tako kot izvirni podatki je tudi ta matrika 9 vrstic s 3 stolpci (9 x 3) in gre v funkcijo MMULT kot matrika1 .
Array2 je izpeljan z:
TRANSPOSE(COLUMN(data))
To je zapleten in zabaven del te formule. Funkcija COLUMN se uporablja zgolj zaradi priročnosti kot način ustvarjanja številskega polja ustrezne velikosti. Če želite izvesti množenje matric z MMULT, mora biti število stolpcev v matriki1 (3) enako številu vrstic v matriki2 .
COLUMN vrne matriko s tremi stolpci (2,3,4) in TRANSPOSE spremeni to matriko v matriko s 3 vrsticami (2; 3; 4). Nato MMULT zažene in vrne rezultat matrike 9 x 1:
=SUM(--((2;0;7;2;0;0;0;0;4)>0))
Preverimo, da vnosi z ničelno vrednostjo> 0 in znova prisilimo TRUE FALSE na 1 in 0 z dvojnim negativom, da dobimo končno matriko znotraj SUM:
=SUM((1;0;1;1;0;0;0;0;1))
V tej zadnji matriki 1 predstavlja vrstico, kjer je logični test (podatki = 90) vrnil true. Vsota, ki jo vrne SUM, je število vseh vrstic, ki vsebujejo številko 90.
Dobesedno vsebuje
Če morate preveriti določene besedilne vrednosti, z drugimi besedami dobesedno preverite, ali celice vsebujejo določene besedilne vrednosti, lahko spremenite logiko v formuli na tej strani, da uporabite funkciji ŠTEVILO in ISKANJE. Na primer za štetje celic / vrstic, ki vsebujejo "jabolko", lahko uporabite:
=ISNUMBER(SEARCH("apple",data))
Podrobnosti o tem, kako deluje ta formula, najdete tukaj.
