Dataekstraksjon og rensing er den første og mest avgjørende oppgaven for ethvert dataanalyseprosjekt. I mine fem års erfaring har jeg mottatt data som så ut som søppel, men ved å bruke Excel HØYRE og VENSTRE funksjoner klarte jeg å gjøre dem til meningsfulle rapporter.
Datarensing i excel er enkelt. Her skal jeg forklare hvordan du kan trekke ut spesifikk tekst i excel 2016 fra en celle og etterlate søppeldata.
I bildet ovenfor får jeg data i Excel. Det har Navn, Alder, og hvis personen har noen forsikring eller ingen (J/N). For å bruke disse dataene må jeg trekke ut data i forskjellige kolonner. Hver kolonne bør bare inneholde én type verdi. Når det er gjort kan vi lage pivotrapporter i excel som vil fortelle oss bedre om dataene. Så la oss komme i gang.
Excel RIGHT -funksjon for å trekke ut data fra høyre i teksten
La oss starte med den enkleste. I dette eksemplet har vi "Har forsikring?" data på slutten av strengen. Og det er bare ett tegn (Y eller N). For å trekke ut disse dataene bruker vi RIGHT Formula Excel. RIGHT -funksjonen er tilgjengelig i Excel 2016, Excel 2013, Excel 2010 og eldre versjoner av Excel.
RIGHT -funksjonen i excel tar bare to argumenter. Først TEKSTEN, og deretter ANTALL KARAKTERER du vil trekke ut fra høyre i excel.
Generisk formel:
= HØYRE (tekst, antall_tegn)
Her har vi vår tekst i cellen B3 og antallet tegn vi ønsker å trekke ut er 1. Bare skriv denne formelen i excel i celle D3 eller hvor du vil ha disse dataene.
= HØYRE (B3,1)
Du får resultatet i celle D3. bare kopier eller dra ned denne formelen i cellene nedenfor.
Excel LEFT -funksjon for å trekke ut data fra venstre i teksten
La oss få vår alder. Før jeg forklarer, la meg vise deg formelen. Kopier denne formelen i ønskede celler.
= VENSTRE (HØYRE (B3,4), 2)
I formelen ovenfor har vi kombinert VENSTRE og HØYRE funksjoner for å få vår spesifikke tekst fra strengen. Slik ser det ut.
La oss nå forstå denne fine formelen.
Hver funksjon fungerer fra innover til utover. Så la oss starte med:
HØYRE (B3,4): Det trekker ganske enkelt ut 4 tegn fra en gitt tekst. Det gir oss 30_Y. Hvorfor 4? Siden vi vet at alder bare er en av to tegn, og vi har 1 “_” og 1 Y eller N.
VENSTRE (HØYRE (B3,4), 2): Nå har vi teksten vår som begynner med en alder på 2 tegn. Her kommer LEFT -funksjonen til handling. Det tar HØYRE (B3,4) som sitt tekstargument og trekker ut 2 tegn fra det. Det gir oss 30 for den første cellen. Kopier formelen i cellene nedenfor, og du får resten av dataene som vist på bildet ovenfor.
Generisk formel for VENSTRE:
= VENSTRE (tekst, Antall tegn)
Akkurat som RIGHT -funksjonen i excel LEFT -funksjonen i Excel også tar to samme argumenter. Det trekker bare ut data fra venstre for strengen som er gitt.
Excel VENSTRE og høyre funksjon for å trekke ut ukjent lengde på tekst fra en streng
Bare lim inn denne formelen i ønskede celler for å trekke ut navnet fra teksten i excel.
= VENSTRE (B3, LEN (B3) -5)
I eksemplene ovenfor visste vi det eksakte antallet tekster vi skulle trekke ut fra den gitte teksten. Men nå vet vi ikke hvor langt et navn er. Hva kan bli gjort?
Vi vet at navnet etterfølges av nøyaktig 5 tegn. 1 for mellomrom, 2 for alderen, 1 for “_” understreking, og en for Y.
Hvis vi kan få alt antall tekst og deretter trekke 5 fra det, vil vi ha det eksakte antallet tekst for navnet.
For å finne lengden på en tekst i Excel kan vi bruke LEN -funksjonen. LEN -funksjonen til excel returnerer antall involverte tegn.
Syntaks:
= LEN (tekst)
Så LENG (B3) returnerer 20.
LENG (B3) -5 returnerer 15. Og dette lengden på navnet i cellen B3
Endelig VENSTRE (B3, LEN (B3) -5) gir oss Georgina Schalk som et resultat. Når du kopierer formelen i cellene nedenfor, får du alle navnene.
Nå kan du utarbeide rapporter som dette basert på ekstraherte data ved hjelp av pivottabeller. Det forteller oss at halvparten av våre fag har forsikring og den andre halvparten ikke.
VENSTRE og HØYRE FUNKSJON for excel er veldig kraftig og nyttig for datarensing. Når de kombineres med LEN -funksjon, FIND -funksjon og SUBSTITUTE -funksjon i excel, kan de trekke ut hvilken som helst delstreng i en streng i excel.
I fremtiden vil vi se mer komplekst datautvinning i excel. For nå farvel. Og ja, la tankene stå i kommentarfeltet nedenfor. Jeg hjelper deg mer enn gjerne.