Generisk formel
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Sammanfattning
För att räkna hur många gånger ett specifikt ord (eller vilket underlag som helst) visas i ett cellområde, kan du använda en formel baserad på funktionerna SUBSTITUT, LEN och SUMPRODUKT. I det visade exemplet är formeln i C11:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Obs: Formeln på denna sida räknar i ett ord i ett intervall. Om en cell till exempel innehåller två förekomster av ett ord, kommer den att bidra med 2 till det totala antalet. Om du bara vill räkna celler som innehåller ett visst ord, se den här enkla formeln baserad på COUNTIF-funktionen.
Förklaring
I den generiska versionen av formeln representerar rng det intervall som ska kontrolleras, och txt är ordet eller strängen som ska räknas.
I exemplet som visas är B5: B8 det område som ska kontrolleras, och C2 innehåller texten (ord eller delsträng) som ska räknas.
För varje cell i intervallet tar SUBSTITUTE bort strängen från originaltexten och LEN beräknar längden på texten utan understrängen. Detta nummer subtraheras sedan från längden på originaltexten. Resultatet är antalet tecken som togs bort av SUBSTITUTE.
Antalet borttagna tecken divideras sedan med längden på underlaget. Så om ett underlag eller ord är 5 tecken långt och det tio tecken saknas efter det att det har tagits bort från originaltexten, vet vi att understrängen / ordet föreföll två gånger i originaltexten.
Eftersom ovanstående beräkning är insvept i SUMPRODUCT-funktionen är resultatet en matris som innehåller ett tal för varje cell i intervallet. Dessa siffror representerar antalet förekomster av substratet i varje cell. För det här exemplet ser matrisen ut så här: (1; 1; 0; 1)
Slutligen summerar SUMPRODUCT alla objekt i matrisen för att få de totala förekomsterna av substring i cellområdet.
Ignorerar fallet
SUBSTITUTE är en skiftlägeskänslig funktion, så den kommer att matcha skiftläge när en utbyte körs. Om du behöver räkna både stora och små bokstäver av ett ord eller en delsträng, använd UPPER-funktionen i SUBSTITUT för att konvertera texten till versaler innan du kör substitutionen:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
