10 Super pæne måder at rense data i Excel -regneark på

Se video - 10 måder at rense data i Excel

Data danner rygraden i enhver analyse, du foretager i Excel. Og når det kommer til data, er der masser af ting, der kan gå galt - det være sig strukturen, placeringen, formateringen, ekstra mellemrum og så videre.

I dette blogindlæg vil jeg vise dig 10 enkle måder at rense data i Excel.

#1 Slip af med ekstra pladser

Ekstra rum er smertefuldt vanskelige at få øje på. Selvom du på en eller anden måde kan se de ekstra mellemrum mellem ord eller tal, er efterfølgende mellemrum ikke engang synlige. Her er en pæn måde at slippe af med disse ekstra mellemrum - Brug TRIM -funktion.

Syntaks: TRIM (tekst)

Excel TRIM -funktion tager cellereferencen (eller teksten) som input. Det fjerner ledende og bageste mellemrum samt de ekstra mellemrum mellem ord (undtagen enkeltrum).

#2 Vælg og behandl alle tomme celler

Blanke celler kan skabe kaos, hvis de ikke behandles på forhånd. Jeg står ofte over for problemer med tomme celler i et datasæt, der bruges til at oprette rapporter/dashboards.

Du vil måske udfylde alle tomme celler med '0' eller 'Ikke tilgængelig', eller måske bare markere det. Hvis der er et stort datasæt, kan det tage timer at gøre dette manuelt. Heldigvis er der en måde, du kan vælge alle de tomme celler på én gang.

1. Vælg hele datasættet
2. Tryk på F5 (dette åbner dialogboksen Gå til)
3. Klik på knappen Special… (nederst til venstre). Dette åbner dialogboksen Gå til speciel
   
4. Vælg Tom og klik OK
   

Dette vælger alle de tomme celler i dit datasæt. Hvis du vil indtaste 0 eller Ikke tilgængelig i alle disse celler, skal du bare skrive det og trykke på Ctrl + Enter (husk at hvis du kun trykker på enter, indsættes værdien kun i den aktive celle).

#3 Konverter tal gemt som tekst til tal

Nogle gange, når du importerer data fra tekstfiler eller eksterne databaser, gemmes tal som tekst. Nogle mennesker har også for vane at bruge en apostrof (‘) før et tal for at få det til at skrive. Dette kan skabe alvorlige problemer, hvis du bruger disse celler i beregninger. Her er en fjolsikker måde at konvertere disse tal gemt som tekst tilbage til tal.

1. Skriv 1 i en tom celle
2. Vælg cellen, hvor du skrev 1, og tryk på Ctrl + C
3. Vælg den celle/det område, du vil konvertere til tal
4. Vælg Indsæt -> Indsæt special (Tastaturgenvej - Alt + E + S)
5. I feltet Indsæt specialdialog skal du vælge Multiply (i kategorien operationer)
   
6. Klik på OK. Dette konverterer alle numre i tekstformat tilbage til tal.

Der er meget mere, du kan gøre med indsætte specialoperationsmuligheder. Her er forskellige andre måder at multiplicere i Excel ved hjælp af Paste Special.

#4 - Fjern dubletter

Der kan være 2 ting, du kan gøre med dublerede data - Fremhæv det eller Slet det.

• Fremhæv duplikerede data:
  • Vælg dataene og gå til Hjem -> Betinget formatering -> Fremhæv celleregler -> Duplikerede værdier.
  • Angiv formateringen, og alle dublerede værdier fremhæves.
    
• Slet dubletter i data:
  • Vælg dataene, og gå til Data -> Fjern dubletter.
  • Hvis dine data har overskrifter, skal du kontrollere, at afkrydsningsfeltet øverst til højre er markeret.
  • Vælg den eller de kolonner, som du vil fjerne dubletter fra, og klik på OK.
    

Dette fjerner dublerede værdier fra listen. Hvis du vil have den originale liste intakt, skal du kopiere og indsætte dataene på et andet sted og derefter gøre dette.

Relaterede: Den ultimative guide til at finde og fjerne dubletter i Excel.

#5 Fremhæv fejl

Der er 2 måder, du kan fremhæve fejl i data i Excel:

Brug af betinget formatering

1. Vælg hele datasættet
2. Gå til Hjem -> Betinget formatering -> Ny regel
3. I dialogboksen Ny formateringsregel skal du vælge 'Formater kun celler, der indeholder'
4. I regelbeskrivelsen skal du vælge Fejl fra rullemenuen
5. Indstil formatet, og klik på OK. Dette fremhæver enhver fejlværdi i det valgte datasæt
   

Brug Go to Special

1. Vælg hele datasættet
2. Tryk på F5 (dette åbner dialogboksen Gå til dialog)
3. Klik på Specialknap nederst til venstre
4. Vælg formler, og fjern markeringen af ​​alle muligheder undtagen fejl
   

Dette vælger alle de celler, der har en fejl i det. Nu kan du manuelt fremhæve disse, slette det eller skrive noget i det.

#6 Skift tekst til nederste/øvre/korrekte sag

Når du arver en projektmappe eller importerer data fra tekstfiler, er navnene eller titlerne ofte ikke ensartede. Nogle gange kan al teksten være i små/store bogstaver, eller det kan være en blanding af begge. Du kan nemt gøre det hele konsekvent ved at bruge disse tre funktioner:

LOWER () - Konverterer al tekst til små bogstaver.
UPPER () - Konverterer al tekst til store bogstaver.
PROPER () - Konverterer al tekst til korrekt sag.

#7 Analysér data ved hjælp af tekst til kolonne

Når du får data fra en database eller importerer dem fra en tekstfil, kan det ske, at al teksten er trang i en celle. Du kan analysere denne tekst i flere celler ved hjælp af tekst til kolonne -funktionalitet i Excel.

1. Vælg de data/tekst, du vil analysere
2. Gå til data -> Tekst til kolonne (Dette åbner guiden Tekst til kolonner)
   • Trin 1: Vælg datatypen (vælg Afgrænset, hvis dine data ikke har lige store mellemrum og er adskilt af tegn som komma, bindestreg, prik …). Klik på Næste
     
   • Trin 2: Vælg Afgrænsning (tegnet, der adskiller dine data). Du kan vælge foruddefineret afgrænsning eller andet ved hjælp af indstillingen Andet
     
   • Trin 3: Vælg dataformatet. Vælg også destinationscellen. Hvis destinationscellen ikke er markeret, overskrives den aktuelle celle
     

Relaterede: Udtræk brugernavn fra e -mail -id ved hjælp af tekst til kolonne.

#8 Stavekontrol

Intet sænker troværdigheden af ​​dit arbejde end en stavefejl.

Brug tastaturgenvejen F7 til at køre en stavekontrol for dit datasæt.

Her er en detaljeret vejledning i, hvordan du bruger stavekontrol i Excel.

#9 Slet al formatering

I mit job brugte jeg flere databaser til at få dataene i excel. Hver database havde sin egen dataformatering. Når du har alle dataene på plads, kan du her slette al formateringen på én gang:

1. Vælg datasættet
2. Gå til Start -> Ryd -> Ryd formater
   

På samme måde kan du også kun slette kommentarer, hyperlinks eller indhold.

#10 Brug Find og erstat for at rense data i Excel

Find og udskift er uundværlig, når det kommer til datarensning. For eksempel kan du vælge og fjerne alle nuller, ændre referencer i formler, finde og ændre formatering og så videre.

Læs mere om, hvordan Find og erstat kan bruges til at rense data.

Dette er mine top 10 teknikker til at rense data i Excel. Hvis du gerne vil lære nogle flere teknikker, er her en guide fra MS Excel -teamet - Rene data i Excel.

Hvis der er flere teknikker, du bruger, kan du dele det med os i kommentarfeltet!