Splošna formula
=(LEN(text)-LEN(SUBSTITUTE(text,word,"")))/LEN(word)
Povzetek
Če morate prešteti, kolikokrat se določena beseda (ali kateri koli podniz) pojavi znotraj celice, lahko uporabite formulo, ki uporablja SUBSTITUTE in LEN. V splošni obliki zgornje formule "besedilo" predstavlja celico, ki vsebuje besedilo, "beseda" pa besedo ali podniz, ki se šteje. V primeru uporabljamo to formulo:
=(LEN(B4)-LEN(SUBSTITUTE(B4,C4,"")))/LEN(C4)
Pojasnilo
B4 je celica, v kateri štejemo besede, in C4 vsebuje podniz (besedo ali kateri koli podniz), ki ga štejete.
SUBSTITUTE odstrani podniz iz izvirnega besedila in LEN 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.
Na koncu se število odstranjenih znakov deli z dolžino podniza. Če je torej podniz dolg 5 znakov in po odstranitvi iz prvotnega besedila manjka 10 znakov, vemo, da se je podniz v izvirnem besedilu pojavil dvakrat.
Primer za ravnanje
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:
=(LEN(B4)-LEN(SUBSTITUTE(UPPER(B4),UPPER(C4),"")))/LEN(C4)
Ker ta formula pred nadomestitvijo pretvori podniz in besedilo v velike črke, bo v vsakem primeru enako dobro delovala z besedilom.
Reševanje težav
Štetje besed v Excelu je zapleteno, ker Excel ne podpira regularnih izrazov. Posledično je težko natančno ciljati na besede, ki jih želite natančno prešteti, pri čemer ignorirate podnize in druga delna ujemanja (tj. Poiščite "lisica", ne pa "lisice"). Ločila in razlike med velikimi in malimi črkami povzročajo težavo.
Ena od rešitev je uporaba prve formule v pomožnem stolpcu za "normalizacijo besedila" kot prvi korak. Nato uporabite formulo na tej strani za štetje besed, zavitih v presledke, da dobite natančno štetje (tj. V normaliziranem besedilu lahko poiščete "lisica".
Opomba: ta pristop je tako dober kot normalizirano besedilo, ki ga lahko ustvarite, in morda boste morali večkrat prilagoditi normalizacijsko formulo, da dobite želeni rezultat.
