Želite dobiti število besed v Excelu? Verjemite ali ne, Excel nima vgrajenega števca besed.
Ampak ne skrbite.
Kul kup excelovih funkcij (ali malo VBA, če se počutite domišljijsko) lahko to enostavno naredi za vas.
V tej vadnici bom prikazal nekaj načinov štetja besed v Excelu s preprostimi formulami. Na koncu bo obravnavana tudi tehnika ustvarjanja formule po meri z uporabo VBA, ki vam bo hitro dala število besed katerega koli besedila v kateri koli celici.
Formula za pridobivanje števila besed v Excelu
Preden vam dam natančno formulo, na hitro poglejmo logiko, da dobimo število besed.
Recimo, da imam stavek, kot je prikazano spodaj, za katerega želim dobiti število besed.
Čeprav Excel ne more šteti števila besed, lahko šteje število presledkov v stavku.
Torej, da dobimo število besed, lahko štejemo te presledke namesto besed in k skupnemu seštevku dodamo 1 (saj bi bilo število presledkov za eno manj število besed).
Zdaj sta lahko dve možnosti:
- Med vsako besedo je en sam presledek
- Med besedami je več presledkov.
Pa poglejmo, kako v vsakem primeru prešteti skupno število besed.
Primer 1 - Ko je med besedami en sam presledek
Recimo, da imam v celici A1 naslednje besedilo: Pustite mačko iz vreče
Če želite prešteti število besed, bi uporabil formulo:
= LEN (A1) -LEN (ZAMENJA (A1, "", ""))+1
To bi posledično vrnilo "7".
Takole deluje ta formula:
- LEN (A1) - Ta del formule vrne 26, kar je skupno število znakov v besedilu v celici A1. Vključuje besedilne znake in presledke.
- ZAMENIK (A1, ”“, ””) - Ta del formule odstrani vse presledke iz besedila. Torej bi bil v tem primeru rezultat Pustite, da vrečko vrečko.
- LEN (ZAMENJIVA (A1, ”“,“”) - Ta del formule šteje skupno število znakov v besedilu, ki nima presledkov. Rezultat tega bi bil torej 20.
- LEN (A1) -LEN (ZAMENJA (A1, ”“,“”)) - To bi od dolžine besedila s presledki odštelo dolžino besedila brez presledkov. V zgornjem primeru bi bilo 26-20, kar je 6.
- = LEN (A1) -LEN (ZAMENJA (A1, ”“,“”))+1 - Skupnemu rezultatu dodamo 1, saj je skupno število presledkov za eno manj od skupnega števila besed. Na primer, en prostor je v dveh besedah in dva presledka v treh besedah.
To dobro deluje, če imate med besedami samo en presledek. Vendar ne bi delovalo, če imate med besedami več presledkov.
V tem primeru uporabite formulo v naslednjem primeru.
Primer 2: Ko je med besedami več presledkov
Recimo, da imate naslednje besedilo: Pustite mačko iz vreče
V tem primeru je med besedami več presledkov.
Če želimo dobiti število besed, moramo najprej odstraniti vse dodatne presledke (tako, da je med dvema besedama samo en presledek), nato pa prešteti skupno število presledkov.
Tukaj je formula, ki nam bo dala pravo število besed:
= LEN (TRIM (A1))-LEN (ZAMENJA (A1, "", ""))+1
To je podobna formula, uporabljena v zgornjem primeru, z rahlo spremembo - tukaj smo uporabili tudi funkcijo TRIM.
Excel TRIM funkcija odstrani vse začetne, zadnje in dodatne presledke (razen posameznih presledkov med besedami).
Preostala formula deluje enako (kot je razloženo v primeru 1).
Opomba: Če med besedami ni presledkov, se šteje za eno besedo.
Uporaba funkcije VBA po meri za štetje besed v Excelu
Čeprav zgornje formule odlično delujejo, če morate pogosto izračunati število besed, lahko z VBA ustvarite funkcijo po meri (imenovano tudi uporabniško definirana funkcija).
Prednost uporabe funkcije po meri je, da jo lahko enkrat ustvarite in nato uporabite kot katero koli drugo običajno Excelovo funkcijo. Torej, namesto da bi ustvarili dolgo zapleteno formulo, kot smo to storili v zgornjih dveh primerih, imate preprosto formulo, ki vzame sklic na celico in vam takoj da število besed.
Tukaj je koda, ki bo ustvarila to funkcijo po meri za pridobivanje števila besed v Excelu.
Funkcija WordCount (CellRef As Range) Dim TextStrng As String Dim Result () As String Result = Split (WorksheetFunction.Trim (CellRef.Text), "") WordCount = UBound (Result ()) + 1 End Function
Ko ustvarite, lahko uporabite funkcijo WordCount tako kot katero koli drugo običajno funkcijo Excel.
V zgornji kodi za funkcijo po meri sem s funkcijo TRIM delovnega lista odstranil vse začetne, zadnje in dvojne presledke med besedami. To zagotavlja, da vse tri celice dajo enak rezultat, saj se štejejo samo besede in ne dvojni presledki.
Kako deluje ta formula:
Zgornja koda VBA najprej uporabi funkcijo TRIM za odstranitev vseh začetnih, zadnjih in dvojnih presledkov iz besedilnega niza v referenčni celici.
Ko ima očiščen niz, uporablja funkcijo SPLIT v VBA za razdelitev besedilnega niza na podlagi razmejevalnika, ki smo ga določili kot presledek. Tako je vsaka beseda ločena kot shranjena kot ločena postavka v spremenljivki Rezultat.
Nato uporabimo funkcijo UBOUND za štetje skupnega števila elementov, shranjenih v spremenljivkah Rezultat. Ker ima VBA osnovo 0, moramo dodati 1, da dobimo skupno število besed.
To pomeni, da rezultat (0) shrani prvo besedo, rezultat (1) drugo besedo itd. Ker se to štetje začne od 0, moramo dodati 1, da dobimo pravo število besed.
Kam postaviti to kodo?
Pri ustvarjanju funkcije po meri morate kodo vnesti v urejevalnik VB delovnega zvezka (ki je zadnji del delovnega zvezka, kamor lahko napišete kodo za avtomatizacijo opravil in ustvarjanje funkcij po meri).
Spodaj so navedeni koraki za vnos kode za funkcijo »GetNumeric« v delovni zvezek.
- Pojdite na zavihek Razvijalec.
- Kliknite možnost Visual Basic. To bo odprlo urejevalnik VB v zaledju.
- V podoknu Raziskovalec projektov v urejevalniku VB z desno tipko miške kliknite kateri koli predmet delovnega zvezka, v katerega želite vstaviti kodo. Če ne vidite Raziskovalca, pojdite na zavihek Pogled in kliknite Raziskovalec projektov.
- Pojdite na Vstavi in kliknite Modul. S tem boste v svoj delovni zvezek vstavili objekt modula.
- Kodo kopirajte in prilepite v okno modula.
Ko ste kodo kopirali v kodno okno, se lahko vrnete na delovni list in uporabite to funkcijo tako kot katero koli drugo običajno funkcijo Excel.
Samo tipkaj = Beseda in prikazala vam bo formulo na seznamu.
Potrebuje en argument, ki je referenca celice, in vam v trenutku prikaže število besed.
