Tekstiandmetega manipuleerimine Excelis on igapäevane vajadus tuhandetele kasutajatele kontorites üle maailma, alates neist, kes impordivad ebajärjekindlaid kliendinimekirju, kuni nendeni, kes puhastavad tühikutest kubisevaid andmebaase. vormingu vead. Tekstifunktsioonide valdamine muudab arvutustabelitega töötamist, säästes aega, vältides inimlikke vigu ja hõlbustades sügavamat analüüsi.
Selles artiklis selgitan üksikasjalikult ja paljude näidetega, kuidas saate Excelis tekstifunktsioone kasutada andmete hõlpsaks manipuleerimiseks ja teisendamiseks. Avastad lisaks kõige tuttavamatele klassikalistele funktsioonidele ka keerukamaid ja vähem ilmseid. Igaüks neist on selgelt lahti seletatud ja sisaldab praktilisi rakendusi, mis on väga kasulikud nii algajatele kui ka neile, kellel on juba arvutustabelite kasutamise kogemus.
Miks kasutada Excelis tekstifunktsioone?
Excel on loodud nii numbrite kui ka teksti töötlemiseks.ja kuigi me seostame seda sageli arvutuste ja matemaatiliste valemitega, on tegelikkuses pidev vajadus andmeid tekstivormingus puhastada, ühendada, eraldada ja analüüsida. Tekstifunktsioonid võimaldavad teil:
- Parandage valed vormingud pärast andmete importimist teistelt platvormidelt.
- Jaga ja grupeeri info ümber detailsemaks analüüsiks.
- Teisenda märgijadasid nende normaliseerimiseks ja võrdlemiseks.
- Looge puhtaid ja professionaalseid aruandeid hästi esitatud tekstiga.
Peamised tekstifunktsioonid Excelis: täielik loend ja milleks igaüks neist kasutatakse
Tekstiandmete tõhusaks käsitlemiseks on oluline teada kõiki Exceli pakutavaid tekstifunktsioone. Ma räägin neist kõige populaarsematest ja kasulikumatest:
- MAATRIKS TEKSTTagastab tekstiväärtuste massiivi mis tahes määratud vahemikust. Väga kasulik suurte tekstimahtude korraga analüüsimiseks.
- ASC/DBCS: Teisendab kahebaidised tähemärgid ühebaidisteks ja vastupidi, mis on eriti oluline keeltes nagu jaapani keel, kus on erinevad tähemärkide laiused.
- TEKSTBAHT/VALUUTA: Teisendab arvud tekstiks, mis on vormindatud kindlas valuutas (näiteks Tai baht või USA dollar).
- TEGELASKOND ja UNICARNad tagastavad numbrilise väärtuse (ASCII või Unicode) märgi, mis sobib ideaalselt spetsiaalsete koodide või sümbolitega töötamiseks.
- Puhasta: Eemaldab mitteprinditavad märgid, mis sobib ideaalselt välistest süsteemidest imporditud andmete puhastamiseks, mis sisaldavad sageli peidetud „prügi”.
- KOOD ja UNIKOOD: Tagastab stringi esimese tähemärgi ASCII- või Unicode-koodi, mis on oluline täpsema tekstianalüüsi jaoks.
- LIIDE/LIIDE/STRING LIIDENeed võimaldavad teil ühendada mitu teksti ja vahemikku ühes lahtris, eraldajaga või ilma.
- SAMA: Kontrollib, kas kaks tekstiväärtust on identsed, võttes arvesse suur- ja väiketähti.
- LEIA ja LEIANad otsivad ühe teksti positsiooni teises tekstis, kuid FIND on tõstutundlik, samas kui FIND mitte.
Rohkem funktsioone, mida peaksite teadma:
- VASAK, PAREM ja VÄLJAVÕTMINE: Need võimaldavad teil teksti alamstringe vastavalt algusest, lõpust või kindlast positsioonist eraldada.
- PIKK: Tagastab tekstistringi tähemärkide arvu, mis on oluline kontrollide ja valideerimiste jaoks.
- Väiketähed, suurtähed ja päristähed: Teisenda tekst väiketäheks, suurtäheks või kirjuta iga sõna esimene täht suureks.
- ASENDA, ASENDANeed asendavad teksti osi teistega, mis on väga kasulik andmebaasides massparanduste tegemiseks.
- KORDA: Kordab teksti või sümbolit teie määratud arv kordi.
- VALUE ja VALUE.NUMBER: Teisendab numbreid esindava teksti Exceli jaoks tõlgendatavateks numbrilisteks väärtusteks.
- SUPRESP: Eemaldab tekstist ebavajalikud tühikud, mis on pärast andmete importimist väga levinud.
- TEKST ja TEKSTIVÄÄRTUS: Vormindab arvu või andme ja teisendab selle tekstiks. Hädavajalik aruannete ja visuaalsete kohanduste jaoks.
- TEKSTJÄREL/TEKST ENNE/TEKSTI JAGAMINE: Ekstraheerib eraldaja järel või ees oleva osa või võimaldab teil stringe kiiresti eraldajate põhjal jagada.
- FONEETILINE: Ekstraheerib foneetilisi märke, väga spetsiifiline kasutus Aasia keeltes.
- T: Teisendab mis tahes argumendi tekstiks.
Praktilised näited: tekstifunktsioonide kasutamine Excelis samm-sammult
Teooria tundmisest ei piisa: Nende funktsioonide valdamine hõlmab nende nägemist tegevuses. Vaatame selgete näidete abil üle, kuidas lahendada levinud probleeme:
1. Muutke tekstivormingut: suurtähed, väiketähed ja suurtähtede kasutamine
Kas teile on antud erinevates vormingutes nimede nimekiri ja peate selle standardiseerima? Funktsioon UPPER teisendab kõik sõnad suurtähtedeks, funktsioon LOWER muudab need väiketähtedeks ja funktsioon PROPER muudab suureks ainult iga sõna algustähe..
Näiteks:
- =UPPER("tere maailm") → TERE MAAILM
- =LOWER("Tere maailm") → Tere maailm
- =PROPER("exCeL funktsioonid") → Exceli funktsioonid
2. Eemaldage ebavajalikud tühikud ja rämpsmärgid
Andmete importimise tulemuseks on sageli tühikute või nähtamatute märkidega täidetud tekst. DEL-funktsioon eemaldab tühikud ja CLEAN kõik mitteprinditavad elemendid..
Näide:
- =DEL(» Tühikutega tekst «) → Tühikutega tekst
- =PUHAS(A2) (kus A2-l on haruldased tähemärgid)
3. Vajadusel tekstide ühendamine ja jagamine
Mitmest lahtrist pärineva teabe kombineerimine on aruannete esitamisel võtmetähtsusega. CONCAT, CONCATENATE ja JOIN võimaldavad teil kogu teabe kokku panna just nii, nagu soovite.. Jagamiseks kasutage funktsioone LEFT, RIGHT, MIX või uusi funktsioone TEXTSPLIT, TEXTAFTER ja TEXTBEFORE.
Näide liitumiseks:
- =CONCATENATE("Eesnimi: "; A2;" Perekonnanimi: "; B2)
- =STRINGJOIN(» «;TÕENE;A2;B2;C2)
Näide jagamiseks:
- =VASAK(A2; LEIA(» «;A2)-1) eesnime eraldamiseks lahtrist, mis sisaldab nii ees- kui ka perekonnanime.
- =PAREM(A2; PIKKUS(A2)-LEIA(» «;A2)) perekonnanimede väljavõtmiseks.
- =TEKSTJÄREL(A2, » «) y =TEKSTENNE(A2, » «) Exceli uuemates versioonides.
Arvude ja kuupäevade vormindamine kohandatud tekstina
Excel kuvab numbreid või kuupäevi sageli ebaselges või ebavajalikus vormingus. Funktsiooni TEXT abil saate mis tahes väärtuse soovitud vormingusse teisendada.:
- =TEKST(12345; "#;##0") → 12.345
- =TEKST(TÄNA(); "pp/kk/aaaa") → 21 (näiteks)
- =TEKST(0,00; "0,00 €") → 0,00 €
Teksti (näiteks ühikute või protsentide) lisamiseks pange tekst jutumärkidesse või kasutage kaldkriipsu:
- =TEKST(100, «0» ja » meetrit») → 100 meetrit
- =TEKST(33; «0 \%») → 33%
Tänu vormingukoodidele saate kuva täielikult kohandada, kasutades räsi (#), nulli (0) ja muid märke.
Tekstiväärtuste ja numbrite teisendamine
Mõnikord saabuvad numbrilised andmed tekstina ja Excel ei tuvasta neid numbritena. Kasutage funktsioone VALUE ja VALUE.NUMBER numbrilise teksti reaalarvuks teisendamiseks.. Samamoodi, kui teil on vaja töödelda tähemärgi ASCII- või Unicode-koodi, annavad CODE ja UNICODE teile võtme.
- =VÄÄRTUS("123,45") → 123,45
- =KOOD("A") → 65
- =UNIKOOD("ñ") → 241
Otsi, analüüsi ja asenda tekstides olevat teavet
Teatud sõna olemasolu, asukoha või osade teistega asendamise kindlakstegemine stringis on väga levinud toimingud. FIND, FIN, SUBSTITUTE ja REPLACE abil saad selle hõlpsalt kätte:
- =FIND("Excel"; "Exceli abi") → 7 (suur- ja väiketähtede tundmine)
- =FIND("excel"; "Exceli abi") → 7 (suur- ja väiketähti ei eristata)
- =RESPLACE("Exceli abi", "cel", "celente") → Suurepärane abi
- =ASENDA("Exceli abi", 9, 3, "suurepärane") → Suurepärane abi
Pikad stringid: teksti jagamine veergudeks ja suurte mahtude käsitlemine
Kui teil on vaja pikki tekstistringe jagada, näiteks eesnimede, perekonnanimede ja muude andmete eraldamiseks erinevatesse veergudesse, Saate kombineerida funktsioone nagu LEFT, MID, RIGHT ja LEN. Täiustatud näide eesnime, keskmise nime ja perekonnanime eraldamiseks oleks:
- =VASAK(A2; LEIA(» «;A2; LEIA(» «;A2,1;1)+XNUMX)) ees- ja kesknimede puhul.
- =EKSTRAKT(A2, LEIA(» «;A2;LEIA(» «;A2,1;1)+1)+2; LEIA(» «;A2;LEIA(» «;A2,1;LEIA(» «;A1;1)+2)+2,1)-(LEIA(» «;A1;LEIA(» «;A1;XNUMX)+XNUMX)+XNUMX)) keskmise nime jaoks.
- =DERECHA(A2,LARGO(A2)-HALLAR(» «,A2,HALLAR(» «,A2,1)+1)+1) perekonnanime pärast.
Kui teil on Exceli uusim versioon, Kasutage otse TEXTSPLIT-i ja salvestage need sammud.
Funktsioonide kombineerimine: tegelikud lahendused levinud probleemidele
Praktikas kasutatakse harva ainult ühte funktsiooni. Peamine on neid kombineerida konkreetsete vajaduste lahendamiseks.. Reaalsed näited:
- Teisenda kuupäevade veerg Ameerika vormingust hispaania vormingusse: =TEKST(A2; "pp-kk-aaaa").
- Ees- ja perekonnanimest initsiaalide eraldamine unikaalsete identifikaatorite loomiseks.
- Erinevate veergude andmete ühendamiseks lisa JOIN-funktsiooniga kohandatud eraldajaid.
- Tuvasta FIND funktsiooni abil, kas lahter sisaldab vigast koodi, ja paranda see seejärel REPLACE funktsiooniga.
- Enne imporditud andmebaasi parsimist eemaldage CLEAN ja DEL abil üleliigsed sümbolid ja jätke alles ainult kehtivad märgid.
Sümbolid ja vormingukoodid: kohandatud kohandamine
Excel pakub andmete esitamisel tohutult kohandamisvõimalusi: Vormindamiskoodides saate literaalteksti kuvamiseks kasutada kohatäiteid (#, 0, ?), erisümboleid ja kaldkriipsu (\).. Kui soovite, et arvu puhul kuvataks fikseeritud tekst või et algusesse lisatakse automaatselt nullid, ajavad funktsiooni TEXT kohandatud vormingud asja ära.
Näide ühikute ja protsentidega:
- =TEKST(100; "0 \m") → 100 m
- =TEKST(0,25; «0 \%») → 25%
- =TEKST(123; "00000") → 00123
Täiustatud funktsioonid ja valemikeel
Lisaks klassikalistele funktsioonidele sisaldab Excel uusi valemeid, mis hõlbustavad ülesandeid, mis traditsiooniliselt nõudsid pikki kombinatsioone. TEXTSPLIT, TEXTAFTER ja TEXTBEFORE võimaldavad teksti eraldajate alusel jagada või palju kiiremini eraldada osasid kindla märgi ees või järel.. Need funktsioonid on saadaval ainult Exceli (Microsoft 365) uusimates versioonides, kuid need parandavad tõhusust.
Näide:
- =TEKSTI JAGAMINE(A2; «;») komaga eraldatud sõnade loendi mitmesse veergu jagamiseks.
- =TEKSTJÄREL(B2; «-«) et lahtris kõik pärast sidekriipsu olev tekst välja võtta.
Näpunäited tekstifunktsioonide maksimaalseks ärakasutamiseks
- Kombineerib mitu funktsiooni üheks valemiks ja automatiseerib korduvaid ülesandeid.
- Massiivivalemite kasutamine mitme väärtuse samaaegseks töötlemiseks kui teil on suuri kuulutusi.
- Proovige uusi funktsioone, kui teil on Microsoft 365, on palju võimsamad ja lihtsamini kasutatavad.
- Salvestage oma lemmikvalemid mallide või korduvkasutatavate koodilõikudena nii et sa ei pea neid iga kord ümber kirjutama.
Exceli tekstifunktsioonide valdamine eristab algtaseme kasutajaid tõeliselt vilunud kasutajatest. Tekstistringide puhastamise, teisendamise ja analüüsimise õppimine võimaldab teil Säästa palju tunde, paranda oma andmete kvaliteeti ja loo palju professionaalsemaid aruandeid.. Kui oled järjepidev ja harjutad siin nähtud näiteid, saad peagi hakkama iga tekstilise väljakutsega, mille Excel sulle ette heidab. Jaga seda teavet ja aita teistel õppida, kuidas Excelis tekstifunktsioone paremini kasutada..