Formuła ogólna
=SUM(--(MMULT(--(criteria),TRANSPOSE(COLUMN(data)))>0))
Podsumowanie
Aby zliczyć wiersze zawierające określone wartości, możesz użyć formuły tablicowej opartej na funkcjach MMULT, TRANSPOSE, COLUMN i SUMA. W pokazanym przykładzie formuła w G5 to:
(=SUM(--(MMULT(--(data=90),TRANSPOSE(COLUMN(data)))>0)))
gdzie dane to nazwany zakres B4: B12.
Uwaga: jest to formuła tablicowa i należy ją wprowadzić za pomocą klawisza Control Shift Enter.
Wyjaśnienie
Działając od wewnątrz, logiczne kryteria użyte w tej formule to:
--(data=90)
gdzie dane to nazwany zakres B4: D12. To generuje wynik PRAWDA / FAŁSZ dla każdej wartości w danych, a podwójna ujemna wartość przekształca wartości PRAWDA FAŁSZ na 1 i 0, aby uzyskać taką tablicę:
(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)
Podobnie jak oryginalne dane, ta tablica ma 9 wierszy na 3 kolumny (9 x 3) i przechodzi do funkcji MMULT jako tablica1 .
Array2 pochodzi z:
TRANSPOSE(COLUMN(data))
To jest trudna i zabawna część tej formuły. Funkcja KOLUMNA jest używana po prostu dla wygody jako sposób na wygenerowanie tablicy numerycznej o odpowiednim rozmiarze. Aby wykonać mnożenie macierzy za pomocą funkcji MMULT, liczba kolumn w tablicy tablica1 (3) musi być równa liczbie wierszy w tablicy2 .
COLUMN zwraca tablicę 3-kolumnową (2, 3, 4), a TRANSPOSE zmienia tę tablicę na tablicę 3-wierszową (2; 3; 4). Następnie MMULT uruchamia się i zwraca wynik w postaci tablicy 9 x 1:
=SUM(--((2;0;7;2;0;0;0;0;4)>0))
Sprawdzamy niezerowe wpisy z> 0 i ponownie przekształcamy TRUE FALSE na 1 i 0 z podwójnym minusem, aby uzyskać końcową tablicę wewnątrz SUM:
=SUM((1;0;1;1;0;0;0;0;1))
W tej ostatniej tablicy 1 oznacza wiersz, w którym test logiczny (dane = 90) zwrócił wartość true. Suma zwrócona przez SUMA to liczba wszystkich wierszy zawierających liczbę 90.
Dosłowne zawiera
Jeśli chcesz sprawdzić określone wartości tekstowe, innymi słowy, sprawdzić dosłownie, czy komórki zawierają określone wartości tekstowe, możesz zmienić logikę w formule na tej stronie, aby użyć funkcji ISNUMBER i SEARCH. Na przykład, aby policzyć komórki / wiersze zawierające słowo „jabłko”, możesz użyć:
=ISNUMBER(SEARCH("apple",data))
Szczegóły na temat działania tej formuły tutaj.
