Tietojen poisto ja puhdistus on kaikkien tietojen analysointiprojektien ensimmäinen ja tärkein tehtävä. Viiden vuoden kokemukseni aikana olen saanut tietoja, jotka näyttivät roskilta, mutta Excelin OIKEA- ja VASEN -toimintojen avulla pystyin muuttamaan ne mielekkäiksi raportteiksi.
Tietojen puhdistus Excelissä on helppoa. Tässä aion selittää, kuinka voit poimia tietyn tekstin Excel 2016: ssa solusta ja jättää roskatiedot.
Yllä olevassa kuvassa minulla on tietoja Excelissä. Sillä on Nimi, Ikä, ja jos henkilö on vakuutus tai ei (K/E). Jotta voisin käyttää näitä tietoja, minun on poimittava tiedot eri sarakkeista. Jokaisen sarakkeen tulee sisältää vain yhdenlaisia arvoja. Kun se on tehty, voimme tehdä Excelissä pivot -raportteja, jotka kertovat meille paremmin tiedoista. Aloitetaan siis.
Excel OIKEA -toiminto tietojen poimimiseksi tekstin oikealta puolelta
Aloitetaan helpoimmasta. Tässä esimerkissä meillä on "Onko vakuutus?" tiedot merkkijonon lopussa. Ja se on vain yksi merkki (Y tai N). Poimia nämä tiedot käytämme OIKEA kaava Excel tarjoaa. OIKEA -toiminto on käytettävissä Excel 2016: ssa, Excel 2013: ssa, Excel 2010: ssä ja vanhemmissa Excel -versioissa.
Excelin OIKEA -funktio sisältää vain kaksi argumenttia. Ensin TEKSTI ja toiseksi niiden merkkien lukumäärä, jotka haluat poimia Excelistä oikealta.
Yleinen kaava:
= OIKEA (teksti, merkkien määrä)
Tässä meillä on omamme teksti solussa B3 ja kuinka monta merkkiä haluamme poimia 1. Kirjoita tämä kaava Exceliin soluun D3 tai missä haluat saada nämä tiedot.
= OIKEA (B3,1)
Tuloksesi näkyy solussa D3. kopioi tai vedä tämä kaava alla oleviin soluihin.
Excel LEFT -toiminto tietojen poimimiseksi tekstin vasemmalta puolelta
Otetaan nyt ikämme. Ennen kuin selität, näytän sinulle kaavan. Kopioi tämä kaava haluttuihin soluihin.
= VASEN (OIKEA (B3,4), 2)
Yllä olevassa kaavassa olemme yhdistäneet VASEN ja OIKEA -funktiot saadaksemme tekstimme merkkijonosta. Tältä se näyttää.
Yritetään nyt ymmärtää tämä kapea kaava.
Jokainen toiminto toimii sisäänpäin ja ulospäin. Aloitetaan siis:
OIKEA (B3,4): Se vain poimii 4 merkkiä annetusta tekstistä. Se antaa meille 30_Y. Miksi 4? Koska tiedämme, että ikä on vain yksi kahdesta merkistä, ja meillä on 1 “_” ja 1 Y tai N.
VASEN (OIKEA (B3,4), 2): Nyt meillä on tekstimme, joka alkaa 2 merkin iästä. Tässä tulee LEFT -toiminto käyttöön. Se ottaa OIKEA (B3,4) sen tekstiargumenttina ja poimii siitä 2 merkkiä. Tämä antaa meille 30 ensimmäiselle solulle. Kopioi kaava alla oleviin soluihin ja saat loput tiedot yllä olevan kuvan mukaisesti.
Yleinen kaava VASEMMALLE:
= VASEN (teksti, merkkien määrä)
Aivan kuten Excelin OIKEA -funktio Excelin VASEN -funktiossa, sisältää myös kaksi samaa argumenttia. Se vain poimii tiedot annetun merkkijonon vasemmalta puolelta.
Excel VASEN ja oikea toiminto poimia tuntemattoman pituisen tekstin merkkijonosta
Liitä tämä kaava haluttuihin soluihin poimiaksesi nimen Excel -tekstistä.
= VASEN (B3, LEN (B3) -5)
Yllä olevissa esimerkeissä tiesimme tarkan määrän tekstejä, jotka otettiin annetusta tekstistä. Mutta nyt emme tiedä kuinka pitkä nimi on. Mitä voidaan tehdä?
Tiedämme, että nimen perässä on tarkat 5 merkkiä. 1 tilaa, 2 ikä, 1 "_" alaviiva ja yksi Y.
Jos saamme kaiken tekstimäärän ja vähennämme sitten siitä 5, saamme nimen nimen tarkan määrän.
Tekstin pituuden löytämiseksi Excelistä voimme käyttää LEN -funktiota. Excelin LEN -funktio palauttaa mukana olevien merkkien määrän.
Syntaksi:
= LEN (teksti)
Niin LEN (B3) palauttaa 20.
LEN (B3) -5 palauttaa 15. Ja tämä on nimen pituus solussa B3
Viimeinkin VASEN (B3, LEN (B3) -5) antaa meille Georgina Schalk tuloksena. Kun kopioit kaavan alla oleviin soluihin, saat kaikki nimet.
Nyt voit laatia tällaisia raportteja poimittujen tietojen perusteella pivot -taulukoiden avulla. Se kertoo meille, että puolet kohteistamme on vakuutettuja ja toisella puolet ei.
Excelin VASEN ja OIKEA TOIMINTA on erittäin tehokas ja hyödyllinen tietojen puhdistamiseen. Yhdistettynä Excelin LEN-, FIND- ja SUBSTITUTE -toimintoihin ne voivat poimia minkä tahansa Excel -merkkijonon alimerkkijonon.
Tulevaisuudessa näemme monimutkaisempia tietojen poimintoja Excelissä. Toistaiseksi heippa. Ja kyllä, jätä ajatuksesi alla olevaan kommenttiosaan. Autan sinua mielelläni.