Excel ima nekaj neverjetnih besedilnih funkcij, ki vam lahko pomagajo pri delu z besedilnimi podatki.
V nekaterih primerih boste morda morali izračunati skupno število znakov v celici/obsegu ali kolikokrat se v celici pojavi določen znak.
Čeprav obstaja funkcija LEN, ki lahko šteje število znakov v celici, lahko ostalo naredite tudi s kombinacijo formul (kot bomo videli kasneje v primerih).
V tej vadnici bom obravnaval različne primere, kjer lahko v Excelu preštejete skupne ali posebne znake v celici/obsegu.
Šteje vse znake v celici
Če preprosto želite pridobiti skupno število vseh znakov v celici, lahko uporabite funkcijo LEN.
Funkcija LEN sprejme en argument, ki je lahko besedilo v dvojnih narekovajih ali sklic celice na celico z besedilom.
Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite šteti skupno število znakov v vsaki celici:
Spodaj je formula, ki bo to naredila:
= LEN (A2)
Funkcija LEN sama po sebi morda ne izgleda veliko, vendar če jo združite z drugimi formulami, lahko naredi nekaj čudovitih stvari (na primer, da v celico vnesete število besed ali razdelite ime in priimek).
Opomba: Funkcija LEN bo štela vse znake v celici, pa naj bo to poseben znak, številke, ločila in presledke (začetni, zadnji in dvojni presledki med besedami).
Ker funkcija LEN šteje vse znake v celici, lahko včasih dobite napačen rezultat, če imate v celici dodatne presledke.
Na primer, v spodnjem primeru funkcija LEN vrne 25 za besedilo v celici A1, medtem ko bi moralo biti 22. Toda ker šteje tudi dodatne presledke, dobite napačen rezultat.
Da se izognete štetju dodatnih presledkov, lahko najprej s funkcijo TRIM odstranite vse začetne, zadnje in dvojne presledke, nato pa uporabite funkcijo LEN, da dobite pravo število besed.
Spodnja formula bo to naredila:
= LEN (TRIM (A2))
Preštejte vse znake v nizu celic
Funkcijo LEN lahko uporabite tudi za štetje skupnega števila znakov v celotnem obsegu.
Recimo, da imamo isti nabor podatkov in tokrat namesto da dobim število znakov v vsaki celici, želim vedeti, koliko jih je v celotnem obsegu.
To lahko storite po spodnji formuli:
= PODROČJE (LEN (A2: A7)))
Naj razložim, kako deluje ta formula.
V zgornji formuli del funkcije LEN zajema celoten obseg celic in šteje znake v vsaki celici.
Rezultat funkcije LEN bi bil:
{22;21;23;23;23;31}
Vsaka od teh številk predstavlja število znakov v celici.
In ko z njo uporabite funkcijo SUMPRODUCT, bi preprosto dodala vse te številke.
Če se sprašujete, zakaj ne morete uporabiti SUM namesto SUMPRODUCT, je razlog v tem, da je to matrika in SUMPRODUCT lahko obravnava matriko, funkcija SUM pa ne.
Če pa še vedno želite uporabiti SUM, lahko uporabite spodnjo formulo (vendar ne pozabite, da morate uporabiti Control + Shift + Enter, da dobite rezultat namesto običajnega Enter)
= SUM (LEN (A2: A7))
Štejte posebne znake v celici
Kot sem že omenil, je dejanska uporabnost funkcije LEN takrat, ko se uporablja v kombinaciji z drugimi formulami.
Če želite v celici šteti določene znake (lahko je to črka, številka, poseben znak ali presledek), lahko to storite s kombinacijo formule.
Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite šteti skupno število besed v vsaki celici.
Čeprav ni vgrajene formule za štetje besed, lahko preštejete znake presledka in jih nato uporabite za poznavanje skupnega števila besed v celici.
Spodaj je formula, ki vam bo dala skupno število presledkov v celici:
= LEN (A2) -LEN (ZAMENJA (A2, "", ""))+1
Zgornja formula šteje skupno število presledkov in nato k temu številu doda 1, da dobi število besed.
Takole deluje ta formula:
- Funkcija SUBSTITUTE se uporablja za zamenjavo vseh presledkov s prazno. Funkcija LEN se nato uporabi za štetje skupnega števila znakov, ko ni presledkov.
- Rezultat LEN (ZAMENIK (A2, ”“, ””)) se nato odšteje od LEN (A2). To nam daje skupno število presledkov, ki so v celici.
- V formulo se doda 1 in skupno število besed bi bilo za eno več kot skupno število presledkov (saj sta dve besedi ločeni z enim znakom).
Upoštevajte, da boste v primeru vodilnih, končnih ali dvojnih presledkov dobili napačen rezultat. V tem primeru je najbolje uporabiti funkcijo TRIM skupaj s funkcijo LEN.
Z isto logiko lahko v celici poiščete določen znak ali besedo ali besedno zvezo.
Recimo, da imam nabor podatkov, kot je prikazano spodaj, kjer imam različne serije, kjer ima vsaka serija abecedo in številko, ki jo predstavlja (na primer A1, J2 itd.)
Spodaj je formula, ki vam bo povedala, kolikokrat je vsak mesec nastala serija z abecedo A:
= LEN (B2) -LEN (ZAMENJA (B2, "A", ""))
Zgornja formula spet uporablja isto logiko - poiščite dolžino besedila v celici z in brez znaka, ki ga želite šteti, in nato vzemite razliko teh dveh.
V zgornji formuli sem trdo kodiral znak, ki ga želim prešteti, lahko pa ga vnesete tudi v celico in nato uporabite sklic na celico. Zaradi tega je bolj priročno, saj se bo formula ob naslednji spremembi besedila v celici posodobila.
Štejte posebne znake s formulo, ki ne razlikuje med velikimi in malimi črkami
Obstaja ena težava s formulo, ki se uporablja za štetje določenih znakov v celici.
Funkcija SUBSTITUTE razlikuje velike in male črke. To pomeni, da "A" ni enako "a". Zato dobite napačen rezultat v celici C5 (rezultat bi moral biti 3).
Torej, kako lahko dobite število znakov določenega znaka, če bi bilo v vsakem primeru (spodnje ali zgornje).
To naredite tako, da naredite formulo neobčutljivo. Čeprav se lahko odločite za zapleteno formulo, sem preprosto dodal število znakov obeh velikih črk (male in velike A).
= LEN (B2) -LEN (SUBSTITUTE (B2, "A", ""))+LEN (B2) -LEN (SUBSTITUTE (B2, "a", ""))
Štejte znake/števke pred in po decimalni številki
Ne vem zakaj, vendar je to pogosto vprašanje, ki ga dobim od svojih bralcev in sem jih videl na številnih forumih - kot je ta
Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite šteti znake pred decimalko in za decimalko.
Spodaj so formule, ki bodo to naredile.
Štetje znakov/številk pred decimalko:
= LEN (INT (A2))
Štetje znakov/številk za decimalko:
= LEN (A2) -FIND (".", A2)
Upoštevajte, da so te formule namenjene le pomembnim številkam v celici. Če imate začetne ali zadnje ničelne vrednosti ali ste oblikovanje številk po meri uporabili za prikaz več/manj števil, bi vam zgornje formule vseeno dale pomembne števke pred decimalko in po njej.
To je torej nekaj scenarijev, kjer lahko uporabite formule za štetje znakov v celici ali obsegu celic v Excelu.
Upam, da vam je bila vadnica koristna!
