Andmete väljavõtmine ja puhastamine on iga andmeanalüüsi projekti esimene ja kõige olulisem ülesanne. Oma viieaastase kogemusega olen saanud andmeid, mis nägid välja nagu prügi, kuid kasutades Exceli PAREMA- ja VASAKU -funktsioone suutsin need sisukateks aruanneteks muuta.
Andmete puhastamine Excelis on lihtne. Siin ma selgitan, kuidas saate Exceli 2016 lahtrist konkreetse teksti välja võtta ja prügi andmed maha jätta.
Ülaltoodud pildil on mul Excelis andmed. Sellel on Nimi, Vanus, ja kui inimene kas tal on kindlustus või mitte (jah/ei). Nende andmete kasutamiseks pean andmed välja võtma erinevatest veergudest. Iga veerg peaks sisaldama ainult ühte tüüpi väärtusi. Kui see on tehtud, saame Excelis koostada pöördaruandeid, mis räägivad meile andmetest paremini. Nii et alustame.
Exceli parempoolne funktsioon andmete väljavõtmiseks tekstist paremalt
Alustame kõige lihtsamast. Selles näites on meil "Kas teil on kindlustus?" andmed stringi lõpus. Ja see on ainult üks märk (Y või N). Nende andmete ekstraheerimiseks kasutame Exceli poolt antud ÕIGET valemit. Funktsioon PAREM on saadaval Excelis 2016, Excel 2013, Excel 2010 ja vanemates Exceli versioonides.
Funktsioon ÕIGE Excelis võtab vaid kaks argumenti. Esiteks TEKST ja teiseks tähemärkide arv, mida soovite Excelis paremalt eraldada.
Üldine valem:
= PAREM (tekst, tähemärkide arv)
Siin on meil oma teksti lahtris B3 ja märkide arv, mida me soovime välja võtta, on 1. Lihtsalt kirjutage see valem lahtrisse Excel D3 või kus iganes soovite neid andmeid saada.
= ÕIGE (B3,1)
Tulemus kuvatakse lahtris D3. lihtsalt kopeerige või lohistage see valem allpool olevatesse lahtritesse.
Funktsioon Excel LEFT andmete ekstraheerimiseks tekstist vasakult
Nüüd saame oma vanuse. Enne selgitamist näitan teile valemit. Kopeerige see valem soovitud lahtritesse.
= VASAK (PAREM (B3,4), 2)
Ülaltoodud valemis oleme kombineerinud LEFT ja RIGHT funktsioone, et saada stringist meie konkreetne tekst. See näeb välja selline.
Mõistame nüüd seda ebameeldivat valemit.
Iga funktsioon töötab seest väljapoole. Nii et alustame:
ÕIGE (B3,4): See lihtsalt ekstraheerib antud tekstist 4 tähemärki. See annab meile 30_Y. Miks 4? Kuna me teame, et vanus on ainult üks kahest tähemärgist ja meil on 1 “_” ja 1 Y või N.
VASAK (PAREM (B3,4), 2): Nüüd on meil tekst, mis algab kahe tähemärgi vanusega. Siin hakkab tööle LEFT funktsioon. See võtab PAREM (B3,4) selle tekstiargumendina ja ekstraheerib sellest 2 märki. See annab meile esimese lahtri jaoks 30. Kopeerige valem allpool olevatesse lahtritesse ja saate ülejäänud andmed, nagu ülaltoodud pildil näidatud.
Üldine valem vasakule:
= LEFT (tekst, tähemärkide arv)
Nii nagu Exceli funktsiooni LEFT funktsioon PAREM, võtab ka kaks sama argumenti. See lihtsalt ekstraheerib andmed esitatud stringi vasakult poolt.
Funktsioon Excel VASAK ja paremal, et eraldada stringist tundmatu pikkusega tekst
Lihtsalt kleepige see valem soovitud lahtritesse, et nimi Excelist tekstist välja võtta.
= VASAK (B3, LEN (B3) -5)
Ülaltoodud näidetes teadsime täpselt antud tekstist väljavõetavate tekstide arvu. Kuid nüüd ei tea me, kui pikk nimi on. Mida saaks teha?
Me teame, et nimele järgneb täpselt 5 tähemärki. 1 tühiku, 2 vanuse, 1 „_” alljoone ja üks Y jaoks.
Kui saame kogu arvu teksti ja lahutame sellest 5, saame nime jaoks täpse arvu teksti.
Teksti pikkuse leidmiseks Excelis saame kasutada funktsiooni LEN. Exceli funktsioon LEN tagastab kaasatud märkide arvu.
Süntaks:
= LEN (tekst)
Niisiis LEN (B3) tagastab 20.
LEN (B3) -5 tagastab 15. Ja see on nime pikkus lahtris B3
Lõpuks VASAK (B3, LEN (B3) -5) annab meile Georgina Schalk tulemusena. Kui kopeerite valemi allolevatesse lahtritesse, saate kõik nimed.
Nüüd saate liigendtabelite abil väljavõetud andmete põhjal selliseid aruandeid ette valmistada. See ütleb meile, et pooltel meie subjektidest on kindlustus ja teisel poolel mitte.
Exceli VASAK ja PAREM FUNKTSIOON on väga võimas ja kasulik andmete puhastamiseks. Kombineerituna LEN -funktsiooniga, FIND -funktsiooniga ja Exceli funktsiooniga SUBSTITUTE, saavad nad Exceli stringist eemaldada mis tahes alamstringi.
Tulevikus näeme Excelis keerukamat andmete ekstraheerimist. Praeguseks hüvasti. Ja jah, jätke oma mõtted allpool olevasse kommentaaride sektsiooni. Mul on hea meel teid aidata.