Tæl kolonner, der indeholder specifikke værdier i Excel

Generisk formel

=SUM(--(MPRODUKT(TRANSPONER(RÆKKE(data)^0);--(kriterie))>0))

Resumé

For at tælle rækker, der indeholder specifikke værdier, kan du bruge en formel baseret på funktionerne MPRODUKT, TRANSPONER, RÆKKE og SUM. I det viste eksempel er formlen i I4:

=SUM(--(MPRODUKT(TRANSPONER(RÆKKE(Data)^0);--(Data=I2))>0))

hvor Data er det navngivne område B2:F16. Resultatet er 3, antallet af kolonner, der indeholder tallet 20.

Forklaring

I dette eksempel er målet at tælle antallet af kolonner i dataene, der indeholder 20 (værdien i celle I2). Den største udfordring i dette problem er, at værdien kan forekomme i en hvilken som helst række og mere end én gang i samme kolonne. Hvis vi blot ville tælle det samlede antal gange, en værdi optrådte i et interval, kunne vi bruge funktionen TÆL.HVIS. Men vi har brug for en mere avanceret formel til at tælle kolonner, der kan indeholde flere forekomster af en bestemt værdi. Forklaringen nedenfor gennemgår to muligheder: en baseret på funktionen MPRODUKT og en baseret på den nyere funktion BYCOL.

MPRODUKT

En mulighed for at løse dette problem er funktionen MPRODUKT. Funktionen MPRODUKT returnerer matrixproduktet af to arrays, nogle gange kaldet “Skalarproduktet”. Resultatet fra MPRODUKT er et array, der indeholder det samme antal rækker som array1 og det samme antal kolonner som array2. Funktionen MPRODUKT tager to argumenter, matrix1 og matrix2, som begge er påkrævet. Kolonneantallet i matrix1 skal svare til rækkeantallet i matrix2. I det viste eksempel er formlen i I4:

=SUM(--(MPRODUKT(TRANSPONER(RÆKKE(Data)^0);--(Data=I2))>0))

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

--(Data=I2)

hvor data er det navngivne område B2:F16. Dette udtryk genererer et SAND eller FALSK resultat for hver værdi i data, og den dobbelte negative (–) tvinger SAND og FALSK værdierne til henholdsvis 1’er og 0’er. Resultatet er en matrix af 1’ere og 0’ere som dette:

{1,0,0,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,1,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,0,0,0,1,0,0,0,0,0,0,0,0}

Ligesom de originale data er dette array 15 rækker gange 5 kolonner (15 x 5) og leveres direkte til funktionen MPRODUKT som array2. Array1 er afledt med dette uddrag:

TRANSPONER(RÆKKE(Data)^0)

Dette er den vanskelige del af formlen. Funktionen RÆKKE bruges til at generere et numerisk array af den rigtige størrelse. For at udføre matrixmultiplikation med MPRODUKT skal kolonnetællingen i matrix1 (15) svare til rækkeantallet i matrix2 (15). RÆKKE returnerer en matrix med 15 rækker, som hæves til nul’te potens og konverteres med funktionen TRANSPONER til en matrix med 15 kolonner:

=TRANSPOSER(RÆKKE(data)^0)
=TRANSPOSER({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}^0)
=TRANSPOSER({1;1;1;1;1;1;1;1;1;1;1;1;1;1;1})
={1;1;1;1;1;1;1;1;1;1;1;1;1;1;1}

Med begge arrays på plads, kører funktionen MPRODUKT og returnerer et array med 1 række og 5 kolonner, {2;2;0;1;0}. Det er de data, vi kan bruge til at løse problemet. Hvert tal, der ikke er nul, repræsenterer en kolonne, der indeholder tallet 20. Vi kan nu forenkle formlen til:

=SUM(--({2;2;0;1;0}>0))

Vi tjekker for poster, der ikke er nul med >0, og tvinger igen TRUE FALSE til 1 og 0 med en dobbelt negativ (–) for at få en endelig matrix inde i SUM:

=SUM({1;1;0;1;0})

I dette array repræsenterer 1 en kolonne, der indeholder 20, og 0 repræsenterer en kolonne, der ikke indeholder 20. SUM-funktionen returnerer et slutresultat på 3, antallet af alle kolonner, der indeholder tallet 20.

BYCOL

Funktionen BYCOL anvender en LAMBDA-funktion til hver kolonne i en given matrix og returnerer et resultat pr. kolonne i en enkelt matrix. Formålet med BYCOL er at behandle data i et array eller område på en “efter kolonne” måde. For eksempel, hvis BYCOL får en matrix med 5 kolonner, vil BYCOL returnere en matrix med 5 resultater. I dette eksempel kan vi bruge BYCOL sådan her:

=SUM(BYCOL(Data;LAMBDA(kol;--(SUM(--(kol=I2))>0))))

Funktionen BYCOL gentager de navngivne områdedata (B2:F16) én kolonne ad gangen. Ved hver kolonne evaluerer og gemmer BYCOL resultatet af den medfølgende LAMBDA-funktion:

LAMBDA(kol;--(SUM(--(kol=I2))>0))

Ved at arbejde indefra og ud, kontrollerer logikken her for værdier i kol, der er lig med I2, hvilket resulterer i en matrix af SAND og FALSK værdier. SAND- og FALSK-værdierne tvinges til 1’er og 0’er med den dobbelte negativ (–), og SUM-funktionen summerer resultatet. Dernæst kontrollerer vi, om resultatet fra SUM er >0 og tvinger det resultat til 1 eller 0 med en anden dobbelt negativ. Efter at BYCOL er kørt, har vi et array med ét resultat pr. kolonne, enten 1 eller 0:

{1,1,0,1,0} // resultat fra BYCOL

Formlen kan nu forenkles som følger:

=SUM({1;1;0;1;0}) // returnerer 3

I det sidste trin summerer funktionen SUM elementerne i arrayet og returnerer et slutresultat på 3.

Tekst indhold

Hvis du har brug for at tjekke for 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 ER.TAL og SØG funktionen. For at tælle celler/rækker, der indeholder “banan”, kan du f.eks. bruge:

=ER.TAL(SØG("banan";Data))

Skriv et svar

Din e-mailadresse vil ikke blive publiceret.

4 × 5 =