Generička formula
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Sažetak
Da biste izbrojali koliko se puta određena riječ (ili bilo koji podniz) pojavljuje unutar raspona ćelija, možete upotrijebiti formulu koja se temelji na funkcijama ZAMJENA, LEN i SUMPRODUCT. U prikazanom primjeru formula u C11 je:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Napomena: Formula na ovoj stranici broji pojedinosti riječi u rasponu. Na primjer, ako ćelija sadrži dvije instance riječi, to će pridonijeti 2 ukupnom broju. Ako samo želite prebrojati stanice koje sadrže određenu riječ, pogledajte ovu jednostavnu formulu koja se temelji na funkciji COUNTIF.
Obrazloženje
U generičkoj verziji formule rng predstavlja raspon za provjeru, a txt je riječ ili podniz za brojanje.
U prikazanom primjeru B5: B8 je raspon za provjeru, a C2 sadrži tekst (riječ ili podniz) za brojanje.
Za svaku ćeliju u rasponu, SUBSTITUTE uklanja podniz iz izvornog teksta, a LEN izračunava duljinu teksta bez podreda. Taj se broj zatim oduzima od duljine izvornog teksta. Rezultat je broj znakova koje je SUBSTITUTE uklonio.
Zatim se broj uklonjenih znakova podijeli s duljinom podniza. Dakle, ako je podniz ili riječ dugačak 5 znakova, a nedostaje 10 znakova nakon što je uklonjen iz izvornog teksta, znamo da se podniz / riječ dva puta pojavila u izvornom tekstu.
Budući da je gornji izračun umotan u funkciju SUMPRODUCT, rezultat je niz koji sadrži broj za svaku ćeliju u rasponu. Ti brojevi predstavljaju broj pojavljivanja podniza u svakoj ćeliji. U ovom primjeru niz izgleda ovako: (1; 1; 0; 1)
Konačno, SUMPRODUCT zbraja sve stavke u polju da bi dobio ukupne pojavnosti podniza u rasponu ćelija.
Zanemarujući slučaj
ZAMJENA je funkcija koja razlikuje velika i mala slova, pa će se podudarati s malim i velikim slovima prilikom izvođenja zamjene. Ako trebate brojati i velika i mala slova riječi ili podniza, upotrijebite UPPER funkciju unutar SUBSTITUTE za pretvorbu teksta u velika slova prije izvođenja zamjene:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
