Üldine valem
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Kokkuvõte
Kui soovite arvutada, mitu korda konkreetne sõna (või mõni alamstring) lahtrivahemikus ilmub, võite kasutada valemit, mis põhineb funktsioonidel SUBSTITUTE, LEN ja SUMPRODUCT. Näidatud näites on valem C11-s järgmine:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Märkus. Sellel lehel olev valem loendab vahemikus oleva sõna i esinemisi. Näiteks kui lahter sisaldab kahte sõna eksemplari, panustab see koguarvu 2-ni. Kui soovite lihtsalt loendada lahtrid, mis sisaldavad konkreetset sõna, vaadake seda funktsiooni COUNTIF põhistavat lihtsat valemit.
Selgitus
Valemi üldises versioonis tähistab rng kontrollitavat vahemikku ja txt on loendatav sõna või alamstring.
Näidatud näites on kontrollitav vahemik B5: B8 ja C2 sisaldab loendatavat teksti (sõna või alamstringi).
Vahemiku iga lahtri jaoks eemaldab SUBSTITUT alamstringi algtekstist ja LEN arvutab teksti pikkuse ilma alamstringita. Seejärel lahutatakse see number algteksti pikkusest. Tulemuseks on märkide arv, mille SUBSTITUTE eemaldas.
Seejärel jagatakse eemaldatud märkide arv alamstringi pikkusega. Seega, kui alamstring või sõna on 5 tähemärki ja pärast selle eemaldamist algtekstist on puudu 10 tähemärki, teame, et alamstring / sõna ilmus originaaltekstis kaks korda.
Kuna ülaltoodud arvutus on pakitud funktsiooni SUMPRODUCT, on tulemuseks massiiv, mis sisaldab arvu vahemiku iga lahtri jaoks. Need numbrid tähistavad igas lahtris alamstringi esinemiste arvu. Selle näite korral näeb massiiv välja selline: (1; 1; 0; 1)
Lõpuks võtab SUMPRODUCT kokku kõik massiivi üksused, et saada alamstringide koguarv rakkude vahemikus.
Juhtumi ignoreerimine
SUBSTITUTE on tõstutundlik funktsioon, nii et see sobib asenduse käivitamisel väiketähtedega. Kui peate loendama nii sõna kui ka alamstringi esinemise nii suurtes kui ka väikestes tähtedes, kasutage enne asenduse käivitamist teksti suurte tähtede teisendamiseks funktsiooni SUBSTITUTE funktsiooni UPPER:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
