Generisk formel
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Resumé
For at tælle, hvor mange gange et bestemt ord (eller et hvilket som helst underlag) vises inden for et celleområde, kan du bruge en formel baseret på SUBSTITUT, LEN og SUMPRODUKT-funktionerne. I det viste eksempel er formlen i C11:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Bemærk: Formlen på denne side tæller et ords indhold i et interval. For eksempel, hvis en celle indeholder to forekomster af et ord, bidrager den 2 til det samlede antal. Hvis du bare vil tælle celler, der indeholder et bestemt ord, skal du se denne enkle formel baseret på COUNTIF-funktionen.
Forklaring
I den generiske version af formlen repræsenterer rng det område, der skal kontrolleres, og txt er det ord eller den understreng, der skal tælles.
I det viste eksempel er B5: B8 det område, der skal kontrolleres, og C2 indeholder teksten (ord eller understreng), der skal tælles.
For hver celle i området fjerner SUBSTITUTE understrengen fra den originale tekst, og LEN beregner længden af teksten uden understrengen. Dette nummer trækkes derefter fra længden på den originale tekst. Resultatet er antallet af tegn, der blev fjernet af SUBSTITUTE.
Derefter divideres antallet af tegn fjernet med længden af understrengen. Så hvis et understreng eller ord er 5 tegn langt, og der mangler 10 tegn, efter at det er blevet fjernet fra den originale tekst, ved vi, at understrenget / ordet blev vist to gange i den originale tekst.
Da ovenstående beregning er pakket ind i funktionen SUMPRODUCT, er resultatet en matrix, der indeholder et tal for hver celle i området. Disse tal repræsenterer antallet af forekomster af substratet i hver celle. I dette eksempel ser matrixen sådan ud: (1; 1; 0; 1)
Endelig summerer SUMPRODUCT alle elementer i arrayet for at få de samlede forekomster af substring i celleområdet.
Ignorerer sag
SUBSTITUTE er en sagsfølsom funktion, så den svarer til store og små bogstaver, når en udskiftning køres. Hvis du har brug for at tælle både store og små bogstaver af et ord eller en understreng, skal du bruge OPPER-funktionen i SUBSTITUTE til at konvertere teksten til store bogstaver, før du kører erstatningen:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
