10 snygga sätt att rengöra data i Excel -kalkylblad

Titta på video - 10 sätt att rengöra data i Excel

Data utgör ryggraden i alla analyser du gör i Excel. Och när det gäller data finns det massor av saker som kan gå fel - det är strukturen, placeringen, formateringen, extra mellanslag och så vidare.

I det här blogginlägget kommer jag att visa dig 10 enkla sätt att rengöra data i Excel.

#1 Bli av med extra utrymmen

Extra utrymmen är smärtsamt svåra att upptäcka. Även om du på något sätt kan upptäcka de extra mellanslagen mellan ord eller siffror, är bakre mellanslag inte ens synliga. Här är ett snyggt sätt att bli av med dessa extra utrymmen - Använd TRIM -funktion.

Syntax: TRIM (text)

Excel TRIM -funktion tar cellreferensen (eller texten) som inmatning. Det tar bort ledande och bakre mellanslag samt de extra mellanslagen mellan ord (utom enkla mellanslag).

#2 Välj och behandla alla tomma celler

Tomma celler kan skapa förödelse om de inte behandlas i förväg. Jag har ofta problem med tomma celler i en datamängd som används för att skapa rapporter/instrumentpaneler.

Du kanske vill fylla alla tomma celler med "0" eller "Inte tillgängligt", eller kanske bara vill markera det. Om det finns en enorm datamängd kan det ta timmar att göra detta manuellt. Tack och lov finns det ett sätt att välja alla tomma celler samtidigt.

1. Välj hela datamängden
2. Tryck på F5 (detta öppnar dialogrutan Gå till)
3. Klicka på knappen Special … (längst ned till vänster). Detta öppnar dialogrutan Gå till special
   
4. Välj Tom och klicka på OK
   

Detta markerar alla tomma celler i din datamängd. Om du vill ange 0 eller Ej tillgängligt i alla dessa celler skriver du bara in det och trycker på Ctrl + Enter (kom ihåg att om du bara trycker på enter, sätts värdet bara in i den aktiva cellen).

#3 Konvertera nummer lagrade som text till nummer

Ibland när du importerar data från textfiler eller externa databaser, lagras siffror som text. Vissa människor har också för vana att använda en apostrof (‘) före ett tal för att göra det till en text. Detta kan skapa allvarliga problem om du använder dessa celler i beräkningar. Här är ett idiotsäkert sätt att konvertera dessa nummer lagrade som text tillbaka till siffror.

1. Skriv 1 i en tom cell
2. Markera cellen där du skrev 1 och tryck på Ctrl + C
3. Markera cellen/intervallet som du vill konvertera till tal
4. Välj Klistra in -> Klistra in special (Kortkommando - Alt + E + S)
5. I rutan Klistra in specialdialog väljer du Multiplicera (i operationskategori)
   
6. Klicka på OK. Detta konverterar alla siffror i textformat tillbaka till siffror.

Det finns mycket mer du kan göra med att klistra in specialoperationsalternativ. Här finns olika andra sätt att multiplicera i Excel med Paste Special.

#4 - Ta bort dubbletter

Det kan finnas två saker du kan göra med dubblettdata - Markera det eller Radera det.

• Markera dubblettdata:
  • Välj data och gå till Hem -> Villkorlig formatering -> Markera cellregler -> Dubbla värden.
  • Ange formateringen och alla dubblettvärden markeras.
    
• Ta bort dubbletter i data:
  • Välj data och gå till Data -> Ta bort dubbletter.
  • Om dina data har rubriker, se till att kryssrutan längst upp till höger är markerad.
  • Välj den eller de kolumner du vill ta bort dubbletter från och klicka på OK.
    

Detta tar bort dubblettvärden från listan. Om du vill att den ursprungliga listan är intakt, kopiera och klistra in data på någon annan plats och gör sedan detta.

Relaterad: Den ultimata guiden för att hitta och ta bort dubbletter i Excel.

#5 Markera fel

Det finns två sätt att markera fel i data i Excel:

Använda villkorlig formatering

1. Välj hela datamängden
2. Gå till Hem -> Villkorlig formatering -> Ny regel
3. I dialogrutan Ny formateringsregel väljer du "Formatera endast celler som innehåller"
4. I regelbeskrivningen väljer du Fel från rullgardinsmenyn
5. Ställ in formatet och klicka på OK. Detta markerar eventuella felvärden i den valda datamängden
   

Använda Gå till Special

1. Välj hela datamängden
2. Tryck på F5 (detta öppnar dialogrutan Gå till dialog)
3. Klicka på Specialknappen längst ned till vänster
4. Välj formler och avmarkera alla alternativ utom Fel
   

Detta väljer alla celler som har ett fel i det. Nu kan du markera dessa manuellt, ta bort det eller skriva in något i det.

#6 Ändra text till undre/övre/rätt fall

När du ärver en arbetsbok eller importerar data från textfiler är namnen eller titlarna ofta inte konsekventa. Ibland kan all text vara liten/stor eller det kan vara en blandning av båda. Du kan enkelt göra det hela konsekvent med hjälp av dessa tre funktioner:

LOWER () - Konverterar all text till gemener.
UPPER () - Konverterar all text till versaler.
PROPER () - Konverterar all text till rätt fall.

#7 Analysera data med text till kolumn

När du får data från en databas eller importerar den från en textfil kan det hända att all text är trång i en cell. Du kan analysera denna text i flera celler med hjälp av funktionen Text till kolumn i Excel.

1. Välj den data/text du vill analysera
2. Gå till data -> Text till kolumn (Detta öppnar guiden Text till kolumner)
   • Steg 1: Välj datatyp (välj Avgränsad om dina data inte är lika fördelade och separeras med tecken som kommatecken, bindestreck, punkt …). Klicka på Nästa
     
   • Steg 2: Välj Avgränsare (tecknet som separerar dina data). Du kan välja fördefinierad avgränsare eller något annat med alternativet Annat
     
   • Steg 3: Välj dataformat. Välj också målcellen. Om målcellen inte är markerad skrivs den aktuella cellen över
     

Relaterad: Extrahera användarnamn från e -post -id med text till kolumn.

#8 Stavningskontroll

Ingenting sänker trovärdigheten i ditt arbete än ett stavfel.

Använd kortkommandot F7 för att köra en stavningskontroll för din datamängd.

Här är en detaljerad handledning om hur du använder stavningskontroll i Excel.

#9 Radera all formatering

I mitt jobb använde jag flera databaser för att få data i excel. Varje databas hade sin egen dataformatering. När du har all data på plats kan du radera all formatering på en gång:

1. Välj datauppsättning
2. Gå till Hem -> Rensa -> Rensa format
   

På samma sätt kan du bara rensa bara kommentarer, hyperlänkar eller innehåll.

#10 Använd Sök och ersätt för att rensa data i Excel

Hitta och ersätt är oumbärligt när det gäller datarensning. Till exempel kan du välja och ta bort alla nollor, ändra referenser i formler, hitta och ändra formatering och så vidare.

Läs mer om hur Sök och ersätt kan användas för att rensa data.

Det här är mina 10 bästa tekniker för att rensa data i Excel. Om du vill lära dig några fler tekniker, här är en guide från MS Excel -teamet - Ren data i Excel.

Om det finns fler tekniker du använder, dela det med oss ​​i kommentarsfältet!