Splošna formula
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Povzetek
Če želite prešteti, kolikokrat se določena beseda (ali kateri koli podniz) prikaže znotraj obsega celic, lahko uporabite formulo, ki temelji na funkcijah SUBSTITUTE, LEN in SUMPRODUCT. V prikazanem primeru je formula v C11:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Opomba: Formula na tej strani šteje vse besede v obsegu. Če celica na primer vsebuje dva primera besede, bo prispevala 2 k skupnemu štetju. Če želite samo šteti celice, ki vsebujejo določeno besedo, si oglejte to preprosto formulo, ki temelji na funkciji COUNTIF.
Pojasnilo
V splošni različici formule rng predstavlja obseg, ki ga je treba preveriti, txt pa je beseda ali podniz, ki ga je treba šteti.
V prikazanem primeru je B5: B8 obseg za preverjanje, C2 pa vsebuje besedilo (besedo ali podniz) za štetje.
Za vsako celico v obsegu SUBSTITUTE odstrani podniz iz izvirnega besedila, LEN pa izračuna dolžino besedila brez podnizov. Nato se to število odšteje od dolžine izvirnega besedila. Rezultat je število znakov, ki jih je SUBSTITUTE odstranil.
Nato se število odstranjenih znakov deli z dolžino podniza. Če je torej podniz ali beseda dolg 5 znakov in po odstranitvi iz prvotnega besedila manjka 10 znakov, vemo, da se je podniz / beseda dvakrat pojavil v izvirnem besedilu.
Ker je zgornji izračun ovit v funkcijo SUMPRODUCT, je rezultat matrika, ki vsebuje število za vsako celico v obsegu. Te številke predstavljajo število pojavitev podniza v vsaki celici. V tem primeru je matrika videti takole: (1; 1; 0; 1)
Končno, SUMPRODUCT sešteje vse elemente v matriki, da dobi celotne pojavitve podniza v obsegu celic.
Ignoriranje primera
NAMESTA je funkcija, ki razlikuje med velikimi in malimi črkami, zato se bo med izvajanjem zamenjave ujemala z velikimi in malimi črkami. Če morate prešteti pojavitve velike ali male črke besede ali podniza, uporabite funkcijo UPPER znotraj SUBSTITUTE, da pred pretvorbo besedila pretvorite besedilo v velike črke:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
