Excel-formel: Tæl rækker, der indeholder specifikke værdier -

Generisk formel

=SUM(--(MMULT(--(criteria),TRANSPOSE(COLUMN(data)))>0))

Resumé

For at tælle rækker, der indeholder specifikke værdier, kan du bruge en matrixformel baseret på funktionerne MMULT, TRANSPOSE, COLUMN og SUM. I det viste eksempel er formlen i G5:

(=SUM(--(MMULT(--(data=90),TRANSPOSE(COLUMN(data)))>0)))

hvor data er det navngivne område B4: B12.

Bemærk: dette er en matrixformel og skal indtastes med kontrolskiftindtastning.

Forklaring

Når man arbejder indefra og ud, er de logiske kriterier, der anvendes i denne formel:

--(data=90)

hvor data er det navngivne område B4: D12. Dette genererer et TRUE / FALSE-resultat for hver værdi i data, og det dobbelte negative tvinger de TRUE FALSE-værdier til 1 og 0 for at give en matrix som denne:

(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)

Ligesom de originale data er denne matrix 9 rækker med 3 kolonner (9 x 3) og går ind i MMULT-funktionen som array1 .

Array2 er afledt med:

TRANSPOSE(COLUMN(data))

Dette er den vanskelige og sjove del af denne formel. COLUMN-funktionen bruges simpelthen for nemheds skyld som en måde at generere et numerisk array med den rigtige størrelse. For at udføre matrixmultiplikation med MMULT skal søjletællingen i array1 (3) svare til rækkeantalet i array2 .

KOLONN returnerer 3-søjles array (2,3,4), og TRANSPOSE ændrer dette array til 3-row array (2; 3; 4). MMULT kører derefter og returnerer et 9 x 1 array-resultat:

=SUM(--((2;0;7;2;0;0;0;0;4)>0))

Vi tjekker for ikke-nul poster med> 0 og tvinger igen SAND FALSK til 1 og 0 med en dobbelt negativ for at få en endelig matrix inde i SUM:

=SUM((1;0;1;1;0;0;0;0;1))

I dette sidste array repræsenterer en 1 en række, hvor den logiske test (data = 90) returnerede sand. Det samlede returnerede af SUM er et antal af alle rækker, der indeholder tallet 90.

Bogstaveligt indeholder

Hvis du har brug for at kontrollere specifikke tekstværdier, med andre ord, bogstaveligt talt kontrollere, om celler indeholder bestemte tekstværdier, kan du ændre logikken i formlen på denne side for at bruge ISNUMBER og SEARCH-funktionen. For eksempel kan du tælle celler / rækker, der indeholder "æble":