Exceli valem: loendage lahtris olevad sõnad kokku

Lang L: none (table-of-contents)

Üldine valem

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1

Kokkuvõte

Lahtris olevate sõnade koguarvu lugemiseks võite funktsiooni TRIM abiga kasutada funktsioone LEN ja SUBSTITUTE põhinevat valemit. Näidatud näites on lahtris C5 valem kopeeritud allpool:

=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+1

Valem tagastab lahtris B5 sõnade arvu.

Selgitus

Excelis pole lahtris sõnade lugemiseks spetsiaalset funktsiooni. Kuid väikese leidlikkusega saate sellise valemi luua funktsioonide ASENDA ja LEN abil, kasutades TRIM-i, nagu näites näidatud. Kõrgel tasemel kasutab see valem funktsiooni LEN, et loendada lahtris olevate märkide arv tühikutega ja ilma, seejärel kasutab erinevus sõnade arvu selgitamiseks. See töötab, sest sõnade arv on võrdne tühikute arvuga + 1, kui iga sõna vahel on üks tühik.

Valemi esimene osa loeb lahtris B5 olevad märgid pärast lisaruumi eemaldamist:

=LEN(TRIM(B5)) // normalize space, count characters

LEN-i sees eemaldab funktsioon TRIM kõigepealt kõik lisaväljad sõnade vahel või teksti alguses või lõpus. See on oluline, sest kõik lisaruumid heidavad sõnade arvu maha. Sel juhul ei ole lisatühikuid, nii et TRIM tagastab algteksti otse funktsioonile LEN, mis tagastab 30:

LEN("All Quiet on the Western Front") // returns 30

Sel hetkel on meil:

=30-LEN(SUBSTITUTE(B5," ",""))+1

Järgmisena kasutame funktsiooni SUBSTITUTE kõigi tühimärkide eemaldamiseks tekstist:

SUBSTITUTE(B5," ","") // strip all space

Märkus. SUBSTITUTE on konfigureeritud otsima tühimärki ("") ja asendama tühja stringiga (""). Vaikimisi asendab kõik tühikud SUBSTITUTE. Tulemus edastatakse otse funktsiooni LEN, mis tagastab loenduse:

LEN("AllQuietontheWesternFront") // returns 25

LEN tagastab 25, mis tähendab järelejäänud märkide arvu pärast kogu ruumi eemaldamist. Nüüd saame valemit lihtsustada:

=30-25+1 // returns 6

mis tagastab lõpptulemuseks 6, sõnade arvu lahtris B5.

Tühjade lahtritega tegelemine

Näites toodud valem tagastab 1 isegi siis, kui lahter on tühi või sisaldab ainult tühikut. See juhtub seetõttu, et pärast sõnade vahele tühikute lisamist lisame tingimusteta ühe. Selle probleemi vältimiseks saate kohandada valemit, nagu allpool näidatud:

=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+(LEN(TRIM(B5))>0)

Pange tähele, et oleme asendanud 1 selle väljendiga:

LEN(TRIM(B5))>0

See kood kärbib kõigepealt B5, seejärel kontrollib pikkust. Kui B5 sisaldab teksti, tagastab LEN positiivse arvu ja avaldis TÕENE. Kui B5 on tühi või sisaldab ainult tühikut, tagastab TRIM LEN-ile tühja stringi (""). Sel juhul tagastab LEN nulli (0) ja avaldise FALSE. Trikk seisneb selles, et mis tahes matemaatikaoperatsioonis osaledes hindavad TRUE ja FALSE vastavalt 1 ja null. Selle tulemusel lisab avaldis 1 ainult siis, kui B5-s on teksti. Vastasel juhul lisab see nulli (0). Selle loogika võiks kirjutada ka funktsiooni IF funktsiooniga järgmiselt: