10 super fine måter å rense data i Excel -regneark

Se video - 10 måter å rense data i Excel

Data danner ryggraden i enhver analyse du gjør i Excel. Og når det gjelder data, er det tonnevis av ting som kan gå galt - det være seg strukturen, plasseringen, formateringen, ekstra mellomrom og så videre.

I dette blogginnlegget vil jeg vise deg 10 enkle måter å rense data i Excel.

#1 Bli kvitt ekstra plass

Ekstra mellomrom er smertefullt vanskelig å få øye på. Selv om du på en eller annen måte kan oppdage de ekstra mellomrommene mellom ord eller tall, er mellomrom ikke engang synlige. Her er en fin måte å bli kvitt disse ekstra mellomrommene - Bruk TRIM -funksjon.

Syntaks: TRIM (tekst)

Excel TRIM -funksjon tar cellereferansen (eller teksten) som input. Den fjerner ledende og etterfølgende mellomrom, så vel som de ekstra mellomrommene mellom ord (unntatt enkeltrom).

#2 Velg og behandle alle tomme celler

Blanke celler kan skape kaos hvis de ikke behandles på forhånd. Jeg har ofte problemer med tomme celler i et datasett som brukes til å lage rapporter/dashbord.

Det kan være lurt å fylle alle tomme celler med ‘0’ eller ‘Ikke tilgjengelig’, eller du vil ganske enkelt markere det. Hvis det er et stort datasett, kan det ta timer å gjøre dette manuelt. Heldigvis er det en måte du kan velge alle de tomme cellene på en gang.

1. Velg hele datasettet
2. Trykk på F5 (dette åpner dialogboksen Gå til)
3. Klikk på Spesial … -knappen (nederst til venstre). Dette åpner dialogboksen Gå til spesiell
   
4. Velg Tom og klikk OK
   

Dette velger alle de tomme cellene i datasettet ditt. Hvis du vil skrive inn 0 eller Ikke tilgjengelig i alle disse cellene, bare skriv den og trykk Kontroll + Enter (husk at hvis du bare trykker enter, settes verdien bare inn i den aktive cellen).

#3 Konverter tall lagret som tekst til tall

Noen ganger når du importerer data fra tekstfiler eller eksterne databaser, blir tall lagret som tekst. Noen mennesker har også for vane å bruke en apostrof (‘) foran et tall for å gjøre det til tekst. Dette kan skape alvorlige problemer hvis du bruker disse cellene i beregninger. Her er en idiotsikker måte å konvertere disse tallene som er lagret som tekst tilbake til tall.

1. Skriv 1 i en hvilken som helst tom celle
2. Velg cellen der du skrev 1, og trykk Ctrl + C
3. Velg cellen/området du vil konvertere til tall
4. Velg Lim inn -> Lim inn spesial (Tastaturgenvei - Alt + E + S)
5. Velg Multipliser i kategorien Lim inn spesiell dialog (i operasjonskategori)
   
6. Klikk OK. Dette konverterer alle tallene i tekstformat tilbake til tall.

Det er mye mer du kan gjøre med å lime inn spesialoperasjonsalternativer. Her er forskjellige andre måter å multiplisere i Excel ved hjelp av Lim inn spesial.

#4 - Fjern duplikater

Det kan være to ting du kan gjøre med dupliserte data - Marker det eller Slett det.

• Marker dupliserte data:
  • Velg dataene og gå til Hjem -> Betinget formatering -> Marker celleregler -> Dupliser verdier.
  • Spesifiser formateringen og alle dupliserte verdier blir markert.
    
• Slett duplikater i data:
  • Velg dataene og gå til Data -> Fjern duplikater.
  • Hvis dataene dine har overskrifter, må du kontrollere at avmerkingsboksen øverst til høyre er merket.
  • Velg kolonnen (e) du vil fjerne duplikater fra, og klikk OK.
    

Dette fjerner dupliserte verdier fra listen. Hvis du vil at den originale listen skal være intakt, kopierer du inn dataene på et annet sted og gjør dette.

I slekt: Den ultimate guiden for å finne og fjerne dubletter i Excel.

#5 Uthev feil

Det er to måter du kan markere feil i data i Excel:

Bruke betinget formatering

1. Velg hele datasettet
2. Gå til Hjem -> Betinget formatering -> Ny regel
3. I dialogboksen Ny formateringsregel velger du "Formater bare celler som inneholder"
4. Velg Regler i regelbeskrivelsen fra rullegardinmenyen
5. Angi formatet og klikk OK. Dette fremhever enhver feilverdi i det valgte datasettet
   

Bruker Gå til Spesial

1. Velg hele datasettet
2. Trykk på F5 (dette åpner dialogboksen Gå til dialog)
3. Klikk på spesialknappen nederst til venstre
4. Velg Formler og fjern merket for alle alternativer unntatt feil
   

Dette velger alle cellene som har en feil i den. Nå kan du markere disse manuelt, slette det eller skrive inn noe i det.

#6 Endre tekst til nedre/øvre/riktig bokstav

Når du arver en arbeidsbok eller importerer data fra tekstfiler, er ofte navnene eller titlene ikke konsistente. Noen ganger kan all tekst være i små/store bokstaver, eller det kan være en blanding av begge. Du kan enkelt gjøre det hele konsistent ved å bruke disse tre funksjonene:

LOWER () - Konverterer all tekst til små bokstaver.
UPPER () - Konverterer all tekst til store bokstaver.
PROPER () - Konverterer all tekst til riktig sak.

#7 Analyser data ved hjelp av tekst til kolonne

Når du får data fra en database eller importerer dem fra en tekstfil, kan det hende at all teksten er trang i en celle. Du kan analysere denne teksten i flere celler ved å bruke tekst til kolonne -funksjonalitet i Excel.

1. Velg dataene/teksten du vil analysere
2. Gå til data -> Tekst til kolonne (Dette åpner veiviseren for tekst til kolonner)
   • Trinn 1: Velg datatypen (velg Avgrenset hvis dataene ikke er like store, og er atskilt med tegn som komma, bindestrek, prikk …). Klikk Neste
     
   • Trinn 2: Velg skilletegn (tegnet som skiller dataene dine). Du kan velge forhåndsdefinert skilletegn eller noe annet ved å bruke alternativet Annet
     
   • Trinn 3: Velg dataformatet. Velg også målcellen. Hvis målcellen ikke er valgt, blir den nåværende cellen overskrevet
     

I slekt: Trekk ut brukernavn fra e -post -ID ved hjelp av tekst til kolonne.

#8 Stavekontroll

Ingenting reduserer troverdigheten til arbeidet ditt enn en stavefeil.

Bruk hurtigtasten F7 til å kjøre en stavekontroll for datasettet ditt.

Her er en detaljert opplæring om hvordan du bruker stavekontroll i Excel.

#9 Slett all formatering

I jobben min brukte jeg flere databaser for å få dataene i excel. Hver database hadde sin egen dataformatering. Når du har alle dataene på plass, kan du slette all formatering på en gang:

1. Velg datasettet
2. Gå til Hjem -> Slett -> Fjern formater
   

På samme måte kan du også slette bare kommentarene, hyperkoblingene eller innholdet.

#10 Bruk Finn og erstatt for å rense data i Excel

Finn og erstatt er uunnværlig når det gjelder datarensing. For eksempel kan du velge og fjerne alle nuller, endre referanser i formler, finne og endre formatering og så videre.

Les mer om hvordan Finn og erstatt kan brukes til å rense data.

Dette er mine 10 beste teknikker for å rense data i Excel. Hvis du vil lære noen flere teknikker, her er en guide fra MS Excel -teamet - Rene data i Excel.

Hvis det er flere teknikker du bruker, del det med oss ​​i kommentarfeltet!