10 erittäin siistiä tapaa puhdistaa tietoja Excel -laskentataulukoissa

Katso video - 10 tapaa puhdistaa tiedot Excelissä

Tiedot muodostavat kaiken Excelissä tekemän analyysin selkärangan. Ja mitä tulee tietoihin, on monia asioita, jotka voivat mennä pieleen - olipa kyseessä rakenne, sijoittelu, muotoilu, ylimääräiset tilat ja niin edelleen.

Tässä blogikirjoituksessa näytän sinulle 10 yksinkertaista tapaa puhdistaa tiedot Excelissä.

#1 Päästä eroon ylimääräisistä tiloista

Ylimääräiset tilat ovat tuskallisen vaikeita havaita. Vaikka saatat jotenkin huomata sanojen tai numeroiden väliset ylimääräiset välilyönnit, perätilat eivät ole edes näkyvissä. Tässä on siisti tapa päästä eroon näistä ylimääräisistä tiloista - Käytä TRIM -toimintoa.

Syntaksi: TRIM (teksti)

Excel TRIM -toiminto ottaa syötteeksi soluviittauksen (tai tekstin). Se poistaa edessä ja takana olevat välilyönnit sekä sanojen väliset lisätilat (paitsi yksittäiset välilyönnit).

#2 Valitse ja käsittele kaikki tyhjät solut

Tyhjät solut voivat aiheuttaa tuhoa, jos niitä ei käsitellä etukäteen. Minulla on usein ongelmia tyhjien solujen kanssa tietojoukossa, jota käytetään raporttien/koontinäyttöjen luomiseen.

Voit halutessasi täyttää kaikki tyhjät solut numerolla 0 tai Ei saatavilla tai korostaa sen. Jos tietojoukko on valtava, tämän tekeminen manuaalisesti voi kestää tunteja. Onneksi voit valita kaikki tyhjät solut kerralla.

1. Valitse koko tietojoukko
2. Paina F5 (tämä avaa Siirry -valintaikkunan)
3. Napsauta Special… -painiketta (vasemmassa alakulmassa). Tämä avaa Siirry erikoisvalintaikkunaan
   
4. Valitse Tyhjä ja napsauta OK
   

Tämä valitsee kaikki tietojoukon tyhjät solut. Jos haluat kirjoittaa 0 tai Ei saatavilla kaikkiin näihin soluihin, kirjoita se ja paina Control + Enter (muista, jos painat vain Enter -näppäintä, arvo lisätään vain aktiiviseen soluun).

#3 Muunna tekstiksi tallennetut numerot numeroiksi

Joskus, kun tuot tietoja tekstitiedostoista tai ulkoisista tietokannoista, numerot tallennetaan tekstinä. Lisäksi joillakin ihmisillä on tapana käyttää heittomerkkiä (’) ennen numeroa tekstin tekemiseksi. Tämä voi aiheuttaa vakavia ongelmia, jos käytät näitä soluja laskelmissa. Tässä on tyhmä todiste tapa muuntaa nämä tekstiksi tallennetut numerot takaisin numeroiksi.

1. Kirjoita mihin tahansa tyhjään soluun 1
2. Valitse solu, johon kirjoitit 1, ja paina Control + C
3. Valitse solu/alue, jonka haluat muuntaa numeroiksi
4. Valitse Liitä -> Liitä erityinen (Näppäimistön pikavalinta - Alt + E + S)
5. Valitse Liitä erityinen dialogi -ruudusta Kerro (toimintoluokassa)
   
6. Napsauta OK. Tämä muuntaa kaikki tekstimuodossa olevat numerot takaisin numeroiksi.

Liittämällä erityistoimintovaihtoehtoja voit tehdä paljon enemmän. Tässä on useita muita tapoja kertoa Excelissä Paste Specialin avulla.

#4 - Poista kaksoiskappaleet

Päällekkäisillä tiedoilla voi tehdä kaksi asiaa - Korosta se tai Poista se.

• Korosta päällekkäiset tiedot:
  • Valitse tiedot ja siirry etusivulle -> Ehdollinen muotoilu -> Korosta solusäännöt -> Monista arvot.
  • Määritä muotoilu ja kaikki päällekkäiset arvot korostuvat.
    
• Poista kopiot tiedoista:
  • Valitse tiedot ja siirry kohtaan Tiedot -> Poista kaksoiskappaleet.
  • Jos tiedoissasi on otsikoita, varmista, että oikeassa yläkulmassa oleva valintaruutu on valittu.
  • Valitse sarakkeet, joista haluat poistaa kaksoiskappaleet, ja napsauta OK.
    

Tämä poistaa päällekkäiset arvot luettelosta. Jos haluat alkuperäisen luettelon ehjänä, kopioi ja liitä tiedot johonkin muuhun paikkaan ja tee sitten tämä.

Aiheeseen liittyviä: Lopullinen opas kopioiden löytämiseen ja poistamiseen Excelissä.

#5 Korosta virheet

Voit korostaa Excelin tietojen virheet kahdella tavalla:

Ehdollisen muotoilun käyttäminen

1. Valitse koko tietojoukko
2. Siirry etusivulle -> Ehdollinen muotoilu -> Uusi sääntö
3. Valitse Uusi muotoilusääntö -valintaikkunassa "Alusta vain soluja, jotka sisältävät"
4. Valitse säännön kuvauksessa avattavasta valikosta Virheet
5. Aseta muoto ja napsauta OK. Tämä korostaa valitun tietojoukon mahdolliset virhearvot
   

Go to Special -toiminnon käyttäminen

1. Valitse koko tietojoukko
2. Paina F5 (tämä avaa Siirry dialogiin -ruudun)
3. Napsauta vasemmassa alakulmassa olevaa erikoispainiketta
4. Valitse Kaavat ja poista kaikki valinnat paitsi Virheet
   

Tämä valitsee kaikki solut, joissa on virhe. Nyt voit korostaa nämä manuaalisesti, poistaa sen tai kirjoittaa siihen mitä tahansa.

#6 Muuta teksti pieneksi/suureksi/oikeaksi

Kun perit työkirjan tai tuot tietoja tekstitiedostoista, nimet tai otsikot eivät usein ole johdonmukaisia. Joskus kaikki teksti voi olla pieniä/isoja kirjaimia tai se voi olla sekoitus molemmista. Voit helposti tehdä kaiken johdonmukaiseksi käyttämällä näitä kolmea toimintoa:

LOWER () - Muuntaa kaiken tekstin pieniksi kirjaimiksi.
YLÖS () - Muuntaa kaiken tekstin isoiksi kirjaimiksi.
OIKEA () - muuntaa kaiken tekstin oikeaksi asiakirjaksi.

#7 Parsaa tiedot käyttämällä saraketta tekstiä

Kun haet tietoja tietokannasta tai tuot niitä tekstitiedostosta, voi tapahtua, että kaikki teksti on ahtaassa yhdessä solussa. Voit jäsentää tämän tekstin useisiin soluihin käyttämällä Teksti sarakkeeseen -toimintoa Excelissä.

1. Valitse data/teksti, jonka haluat jäsentää
2. Siirry tietoihin -> Teksti sarakkeeseen (tämä avaa ohjatun tekstin sarakkeisiin)
   • Vaihe 1: Valitse tietotyyppi (valitse Erotettu, jos tietosi eivät ole tasavälein ja erotettu merkeillä, kuten pilkulla, yhdysmerkillä, pisteellä …). Napsauta Seuraava
     
   • Vaihe 2: Valitse Erotin (merkki, joka erottaa tietosi). Voit valita esimääritetyn erottimen tai mitä tahansa muuta käyttämällä Muu-vaihtoehtoa
     
   • Vaihe 3: Valitse datamuoto. Valitse myös kohdesolu. Jos kohdesolua ei ole valittu, nykyinen solu korvataan
     

Liittyy: Pura käyttäjänimi sähköpostitunnuksesta tekstin avulla sarakkeeseen.

#8 Oikeinkirjoituksen tarkistus

Mikään ei heikennä työn uskottavuutta kuin kirjoitusvirhe.

Käytä tietojoukon oikeinkirjoituksen tarkistusta pikanäppäimellä F7.

Tässä on yksityiskohtainen opetusohjelma oikeinkirjoituksen käyttämisestä Excelissä.

#9 Poista kaikki muotoilut

Työssäni käytin useita tietokantoja tietojen saamiseksi Exceliin. Jokaisella tietokannalla oli oma tietomuoto. Kun sinulla on kaikki tiedot, voit poistaa kaikki muotoilut kerralla seuraavasti:

1. Valitse tietojoukko
2. Siirry etusivulle -> Tyhjennä -> Tyhjennä muodot
   

Voit myös tyhjentää vain kommentit, hyperlinkit tai sisällön.

#10 Käytä Etsi ja korvaa tietojen puhdistamiseen Excelissä

Etsi ja korvaa on välttämätöntä tietojen puhdistamisessa. Voit esimerkiksi valita ja poistaa kaikki nollat, muuttaa kaavojen viittauksia, etsiä ja muuttaa muotoilua jne.

Lue lisää siitä, miten Etsi ja korvaa -toimintoa voidaan käyttää tietojen puhdistamiseen.

Nämä ovat 10 parasta tekniikkaani tietojen puhdistamiseen Excelissä. Jos haluat oppia lisää tekniikoita, tässä on MS Excel -tiimin opas - Clean Data in Excel.

Jos käytät muita tekniikoita, jaa se kanssamme kommenttiosassa!