Formuła ogólna
(=SUM(--(MMULT(--(criteria),TRANSPOSE(COLUMN(data)^0))>=N)))
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 K6 to:
(=SUM(--(MMULT(--((data)=2)))
gdzie dane to nazwany zakres C5: I14.
Zauważ, że 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)<70
gdzie dane to nazwany zakres C5: I14. 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ę:
(0,0,0,1,0,1,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0;0,1,1,0,0,1,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0;0,1,0,0,0,0,0;0,0,0,0,0,0,0;0,0,0,0,0,0,0)
Podobnie jak oryginalne dane, ta tablica ma 10 wierszy na 7 kolumn (10 x 7) i przechodzi do funkcji MMULT jako tablica1 . Następny argument, tablica2 jest tworzony za pomocą:
TRANSPOSE(COLUMN(data)^0))
W tym przypadku funkcja KOLUMNA jest używana jako sposób na wygenerowanie tablicy numerycznej o odpowiednim rozmiarze, ponieważ mnożenie macierzy wymaga, aby liczba kolumn w tablica1 (7) była równa liczbie wierszy w tablicy2 .
Funkcja COLUMN zwraca tablicę 7-kolumnową (3,4,5,6,7,8,9). Podnosząc tę tablicę do potęgi zerowej, otrzymujemy tablicę 7 x 1, taką jak (1,1,1,1,1,1,1), której TRANSPOSE zmienia się na tablicę 1 x 7, taką jak (1; 1 ; 1; 1; 1; 1; 1).
Następnie MMULT uruchamia i zwraca wynik tablicy 10 x 1 (2; 0; 0; 3; 0; 0; 0; 1; 0; 0), który jest przetwarzany za pomocą wyrażenia logicznego> = 2, co daje tablicę PRAWDA FALSE wartości:
(TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE).
Ponownie wymuszamy TRUE FALSE na 1 i 0 z podwójnym minusem, aby uzyskać ostateczną tablicę wewnątrz SUM:
=SUM((1;0;0;1;0;0;0;0;0;0))
Co poprawnie zwraca 2, czyli liczbę nazwisk z co najmniej 2 punktami poniżej 70.
