Excel ima neke nevjerojatne tekstualne funkcije koje vam mogu pomoći pri radu s tekstualnim podacima.
U nekim slučajevima možda ćete morati izračunati ukupan broj znakova u ćeliji/rasponu ili broj pojavljivanja određenog znaka u ćeliji.
Iako postoji funkcija LEN koja može brojati broj znakova u ćeliji, ostalo možete učiniti i kombinacijom formula (kao što ćemo vidjeti kasnije u primjerima).
U ovom ću vodiču pokriti različite primjere u kojima možete izbrojati ukupne ili određene znakove u ćeliji/rasponu u Excelu.
Prebrojite sve znakove u ćeliji
Ako jednostavno želite dobiti ukupan broj svih znakova u ćeliji, možete upotrijebiti funkciju LEN.
Funkcija LEN uzima jedan argument, koji bi mogao biti tekst u dvostrukim navodnicima ili referenca ćelije na ćeliju koja sadrži tekst.
Na primjer, pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite brojati ukupan broj znakova u svakoj ćeliji:
Ispod je formula koja će to učiniti:
= LEN (A2)
Sama po sebi, funkcija LEN možda ne izgleda puno, ali kada je kombinirate s drugim formulama, može učiniti neke divne stvari (poput dobivanja broja riječi u ćeliji ili podjele imena i prezimena).
Napomena: LEN funkcija će brojati sve znakove u ćeliji, bilo da se radi o posebnom znaku, brojevima, interpunkcijskim znakovima i razmacima (vodeći, zadnji i dvostruki razmaci između riječi).
Budući da funkcija LEN broji svaki znak u ćeliji, ponekad možete dobiti pogrešan rezultat u slučaju da imate više razmaka u ćeliji.
Na primjer, u donjem slučaju funkcija LEN vraća 25 za tekst u ćeliji A1, dok je trebala biti 22. No budući da broji i dodatne razmake, dobivate pogrešan rezultat.
Kako biste izbjegli prebrojavanje dodatnih razmaka, prvo možete upotrijebiti funkciju TRIM za uklanjanje svih početnih, zadnjih i dvostrukih razmaka, a zatim upotrijebiti funkciju LEN na njoj da biste dobili stvarni broj riječi.
Formula ispod će to učiniti:
= LEN (TRIM (A2))
Prebrojite sve znakove u nizu ćelija
Također možete koristiti funkciju LEN za brojanje ukupnog broja znakova u cijelom rasponu.
Na primjer, pretpostavimo da imamo isti skup podataka i ovaj put, umjesto da dobijem broj znakova u svakoj ćeliji, želim znati koliko ih ima u cijelom rasponu.
To možete učiniti pomoću donje formule:
= PODIZVOD (LEN (A2: A7)))
Dopustite mi da objasnim kako ova formula radi.
U gornjoj formuli, LEN dio funkcije zauzima cijeli niz ćelija i broji znakove u svakoj ćeliji.
Rezultat funkcije LEN bio bi:
{22;21;23;23;23;31}
Svaki od ovih brojeva predstavlja broj znakova u ćeliji.
A kad s njom koristite funkciju SUMPRODUCT, ona bi jednostavno dodala sve ove brojeve.
Ako se pitate zašto ne možete koristiti SUM umjesto SUMPRODUCT, razlog je taj što je ovo niz, a SUMPRODUCT može rukovati nizom, ali funkcija SUM ne može.
Međutim, ako i dalje želite koristiti SUM, možete upotrijebiti donju formulu (ali imajte na umu da morate koristiti Control + Shift + Enter da biste dobili rezultat umjesto običnog Enter)
= ZBIR (LEN (A2: A7))
Brojanje posebnih znakova u ćeliji
Kao što sam spomenuo, prava je korisnost funkcije LEN kada se koristi u kombinaciji s drugim formulama.
A ako želite brojati određene znakove u ćeliji (to može biti slovo, broj, poseban znak ili razmak), to možete učiniti kombinacijom formule.
Na primjer, pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite brojati ukupan broj riječi u svakoj ćeliji.
Iako ne postoji ugrađena formula za dobivanje broja riječi, možete izbrojiti razmake i zatim upotrijebiti za saznanje ukupnog broja riječi u ćeliji.
Ispod je formula koja će vam dati ukupan broj razmaka u ćeliji:
= LEN (A2) -LEN (ZAMJENA (A2, "", ""))+1
Gornja formula broji ukupan broj znakova razmaka, a zatim dodaje 1 tom broju kako bi dobila broj riječi.
Evo kako ova formula funkcionira:
- Funkcija ZAMJENA se koristi za zamjenu svih znakova razmaka praznim mjestom. Funkcija LEN tada se koristi za brojanje ukupnog broja znakova kada nema razmaka.
- Rezultat LEN -a (ZAMJENA (A2, ”“, ””)) tada se oduzima od LEN -a (A2). To nam daje ukupan broj razmaka koji se nalaze u ćeliji.
- 1 se dodaje u formulu i ukupan broj riječi bio bi jedan više od ukupnog broja razmaka (jer su dvije riječi odvojene jednim znakom).
Imajte na umu da ćete u slučaju da postoje vodeći, krajnji ili dvostruki razmaci dobiti pogrešan rezultat. U tom je slučaju najbolje koristiti funkciju TRIM zajedno s funkcijom LEN.
Istu logiku možete koristiti i za pronalaženje određenog znaka ili riječi ili izraza u ćeliji.
Na primjer, pretpostavimo da imam skup podataka kao što je prikazano u nastavku gdje imam različite serije, gdje svaka serija ima abecedu i broj koji je predstavlja (poput A1, J2 itd.)
Ispod je formula koja će vam dati ukupan broj izrada serije s abecedom A svakog mjeseca:
= LEN (B2) -LEN (ZAMJENA (B2, "A", ""))
Gornja formula opet koristi istu logiku - pronađite duljinu teksta u ćeliji sa i bez znaka koji želite brojati, a zatim uzmite razliku između ova dva.
U gornjoj formuli sam tvrdo kodirao znak koji želim prebrojati, ali ga možete staviti i u ćeliju, a zatim upotrijebiti referencu ćelije. To ga čini prikladnijim jer bi se formula ažurirala sljedeći put kada promijenite tekst u ćeliji.
Brojanje posebnih znakova pomoću formule koja ne razlikuje velika i mala slova
Postoji jedan problem s formulom koja se koristi za brojanje posebnih znakova u ćeliji.
Funkcija ZAMJENA razlikuje velika i mala slova. To znači da "A" nije jednako "a". Zbog toga u ćeliji C5 dobivate pogrešan rezultat (rezultat je trebao biti 3).
Pa kako možete doći do broja znakova određenog lika kad je to u svakom slučaju moglo biti (donje ili gornje).
To činite tako da formulu formule učinite neosjetljivom. Iako možete ići na složenu formulu, jednostavno sam dodao broj znakova u oba slova (mala i velika A).
= LEN (B2) -LEN (ZAMJENA (B2, "A", ""))+LEN (B2) -LEN (ZAMJENA (B2, "a", ""))
Broji znakove/znamenke prije i poslije decimalnog
Ne znam zašto, ali ovo je uobičajen upit koji dobivam od svojih čitatelja i vidio sam ga na mnogim forumima - poput ovog
Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite brojati znakove prije decimalnog i nakon decimalnog mjesta.
Dolje su formule koje će to učiniti.
Brojanje znakova/brojeva prije decimalnog mjesta:
= LEN (INT (A2))
Brojanje znakova/brojeva nakon decimalnog mjesta:
= LEN (A2) -FIND (".", A2)
Imajte na umu da su ove formule namijenjene samo značajnim znamenkama u ćeliji. U slučaju da imate početne ili krajnje nule ili ste koristili prilagođeno oblikovanje brojeva za prikaz više/manje brojeva, gornje formule će vam i dalje dati značajne znamenke prije i poslije decimalnog mjesta.
Ovo su neki od scenarija u kojima možete koristiti formule za brojanje znakova u ćeliji ili rasponu ćelija u Excelu.
Nadam se da vam je vodič bio koristan!
