Obecný vzorec
=SUM(--(MMULT(--(criteria),TRANSPOSE(COLUMN(data)))>0))
souhrn
Chcete-li spočítat řádky, které obsahují konkrétní hodnoty, můžete použít maticový vzorec založený na funkcích MMULT, TRANSPOSE, COLUMN a SUM. V zobrazeném příkladu je vzorec v G5:
(=SUM(--(MMULT(--(data=90),TRANSPOSE(COLUMN(data)))>0)))
kde data jsou pojmenovaný rozsah B4: B12.
Poznámka: Toto je maticový vzorec a musí být zadán pomocí Enter Control Shift.
Vysvětlení
Při práci zevnitř ven je logickým kritériem použitým v tomto vzorci:
--(data=90)
kde data jsou pojmenovaný rozsah B4: D12. To generuje výsledek TRUE / FALSE pro každou hodnotu v datech a dvojitý zápor vynutí hodnoty TRUE FALSE na 1 a 0 a získá tak pole:
(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)
Stejně jako původní data má toto pole 9 řádků o 3 sloupcích (9 x 3) a jde do funkce MMULT jako array1 .
Pole 2 je odvozeno od:
TRANSPOSE(COLUMN(data))
Toto je složitá a zábavná část tohoto vzorce. Funkce COLUMN se používá jednoduše pro pohodlí jako způsob generování numerického pole správné velikosti. Chcete-li provést násobení matic pomocí MMULT, musí se počet sloupců v poli1 (3) rovnat počtu řádků v poli2 .
COLUMN vrací pole se 3 sloupci (2,3,4) a funkce TRANSPOSE toto pole změní na pole se 3 řádky (2; 3; 4). MMULT poté spustí a vrátí výsledek pole 9 x 1:
=SUM(--((2;0;7;2;0;0;0;0;4)>0))
Zkontrolujeme nenulové položky s> 0 a znovu vynucujeme TRUE FALSE na 1 a 0 s dvojitým záporem, abychom získali konečné pole uvnitř SUM:
=SUM((1;0;1;1;0;0;0;0;1))
V tomto konečném poli 1 představuje řádek, kde logický test (data = 90) vrátil hodnotu true. Celkový vrácený součet je počet všech řádků, které obsahují číslo 90.
Doslovný obsahuje
Pokud potřebujete zkontrolovat konkrétní textové hodnoty, jinými slovy, doslova zkontrolovat, zda buňky obsahují určité textové hodnoty, můžete změnit logiku ve vzorci na této stránce a použít funkci ISNUMBER a SEARCH. Například k počítání buněk / řádků, které obsahují „jablko“, můžete použít:
=ISNUMBER(SEARCH("apple",data))
Podrobnosti o tom, jak tento vzorec funguje zde.
