Generisk formel
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Sammendrag
For å telle hvor mange ganger et bestemt ord (eller et hvilket som helst underlag) vises i et celleområde, kan du bruke en formel basert på funksjonene SUBSTITUTE, LENGE og SUMPRODUCT. I eksemplet vist er formelen i C11:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Merk: Formelen på denne siden teller et ord i et område. For eksempel, hvis en celle inneholder to forekomster av et ord, vil den bidra med 2 til det totale antallet. Hvis du bare vil telle celler som inneholder et bestemt ord, kan du se denne enkle formelen basert på COUNTIF-funksjonen.
Forklaring
I den generiske versjonen av formelen representerer rng området som skal kontrolleres, og txt er ordet eller understrengen som skal telles.
I eksemplet som er vist er B5: B8 området som skal kontrolleres, og C2 inneholder teksten (ord eller understreng) som skal telles.
For hver celle i området fjerner SUBSTITUTE understrengen fra originalteksten, og LEN beregner lengden på teksten uten understrengen. Dette tallet trekkes deretter fra lengden på originalteksten. Resultatet er antall tegn som ble fjernet av SUBSTITUTE.
Deretter blir antall tegn fjernet delt på lengden på undergrunnen. Så hvis et underlag eller ord er fem tegn langt, og det mangler 10 tegn etter at det er fjernet fra originalteksten, vet vi at understrenget / ordet dukket opp to ganger i originalteksten.
Fordi beregningen ovenfor er pakket inn i SUMPRODUCT-funksjonen, er resultatet en matrise som inneholder et tall for hver celle i området. Disse tallene representerer antall forekomster av undergrunnen i hver celle. For dette eksemplet ser matrisen slik ut: (1; 1; 0; 1)
Til slutt summerer SUMPRODUCT alle elementene i matrisen for å få den totale forekomsten av substring i celleområdet.
Ignorer saken
SUBSTITUTE er en saksfølsom funksjon, så den vil matche store og små bokstaver når du kjører en erstatning. Hvis du trenger å telle både store og små bokstaver av et ord eller en delstreng, kan du bruke OPPER-funksjonen i SUBSTITUTE for å konvertere teksten til store bokstaver før du kjører erstatningen:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
