10 super úhľadných spôsobov čistenia údajov v tabuľkách programu Excel

Pozrite si video - 10 spôsobov, ako vyčistiť údaje v programe Excel

Dáta tvoria chrbticu akejkoľvek analýzy, ktorú robíte v programe Excel. A pokiaľ ide o údaje, existuje veľa vecí, ktoré sa môžu pokaziť - či už ide o štruktúru, umiestnenie, formátovanie, medzery atď.

V tomto blogovom príspevku vám ukážem 10 jednoduchých spôsobov, ako vyčistiť údaje v programe Excel.

#1 Zbavte sa nadbytočných priestorov

Extra medzery je bolestivo ťažké rozpoznať. Aj keď môžete medzery medzi slovami alebo číslami nejakým spôsobom rozpoznať, medzery za nimi nie sú ani viditeľné. Toto je úhľadný spôsob, ako sa zbaviť týchto medzier - použite funkciu TRIM.

Syntax: TRIM (text)

Funkcia Excel TRIM berie ako vstup odkaz na bunku (alebo text). Odstraňuje úvodné a koncové medzery, ako aj ďalšie medzery medzi slovami (okrem jednoduchých medzier).

#2 Vyberte a ošetrite všetky prázdne bunky

Prázdne bunky môžu spôsobiť chaos, ak nie sú vopred ošetrené. Často sa stretávam s problémami s prázdnymi bunkami v množine údajov, ktorá sa používa na vytváranie zostáv/dashboardov.

Možno budete chcieť vyplniť všetky prázdne bunky „0“ alebo „Nedostupné“, alebo ho jednoducho chcete zvýrazniť. Ak existuje veľký súbor údajov, manuálne to môže trvať hodiny. Našťastie existuje spôsob, ako môžete vybrať všetky prázdne bunky naraz.

1. Vyberte celý súbor údajov
2. Stlačte kláves F5 (otvorí sa dialógové okno Prejsť na)
3. Kliknite na tlačidlo Špeciálne… (vľavo dole). Otvorí sa dialógové okno Prejsť na špeciálne
   
4. Vyberte položku Prázdne a kliknite na tlačidlo OK
   

Tým sa vyberú všetky prázdne bunky vo vašej množine údajov. Ak chcete do všetkých týchto buniek zadať 0 alebo Nie je k dispozícii, zadajte ho a stlačte Ctrl + Enter (pamätajte, že ak stlačíte iba Enter, hodnota sa vloží iba do aktívnej bunky).

#3 Konvertujte čísla uložené ako text na čísla

Niekedy sa pri importovaní údajov z textových súborov alebo externých databáz čísla uložia ako text. Niektorí ľudia majú tiež vo zvyku používať apostrof (‘) pred číslom na to, aby sa stal číslom. Ak tieto bunky používate na výpočty, mohlo by to spôsobiť vážne problémy. Toto je bláznivý spôsob, ako previesť tieto čísla uložené ako text späť na čísla.

1. Do ľubovoľnej prázdnej bunky zadajte 1
2. Vyberte bunku, do ktorej ste zadali 1, a stlačte kombináciu klávesov Control + C.
3. Vyberte bunku/rozsah, ktorý chcete previesť na čísla
4. Vyberte položku Prilepiť -> Prilepiť špeciálne (Klávesová skratka - Alt + E + S)
5. V poli Prilepiť špeciálny dialóg vyberte položku Násobiť (v kategórii operácie)
   
6. Kliknite na tlačidlo OK. Toto prevedie všetky čísla v textovom formáte späť na čísla.

Prilepením možností špeciálnych operácií môžete urobiť oveľa viac. Tu sú rôzne ďalšie spôsoby množenia v programe Excel pomocou Prilepiť špeciálne.

#4 - Odstráňte duplikáty

S duplicitnými údajmi môžete robiť 2 veci - Zvýraznite to alebo Vymaž to.

• Zvýrazniť duplicitné údaje:
  • Vyberte údaje a prejdite na položku Domov -> Podmienené formátovanie -> Zvýrazniť pravidlá buniek -> Duplikovať hodnoty.
  • Zadajte formátovanie a zvýraznia sa všetky duplicitné hodnoty.
    
• Odstrániť duplikáty v dátach:
  • Vyberte údaje a prejdite na položku Údaje -> Odstrániť duplikáty.
  • Ak majú vaše údaje hlavičky, skontrolujte, či je začiarknuté políčko vpravo hore.
  • Vyberte stĺpce, z ktorých chcete odstrániť duplikáty, a kliknite na tlačidlo OK.
    

Týmto sa zo zoznamu odstránia duplicitné hodnoty. Ak chcete, aby bol pôvodný zoznam neporušený, skopírujte a prilepte údaje na iné miesto a potom to urobte.

Súvisiace: Ultimate Guide to Find and remove Duplicates in Excel.

#5 Zvýraznite chyby

Chyby v údajoch v programe Excel môžete zvýrazniť dvoma spôsobmi:

Použitie podmieneného formátovania

1. Vyberte celý súbor údajov
2. Prejdite na domovskú stránku -> Podmienené formátovanie -> Nové pravidlo
3. V dialógovom okne Nové pravidlo formátovania vyberte „Formátovať iba bunky, ktoré obsahujú“
4. V popise pravidla vyberte z rozbaľovacieho zoznamu Chyby
5. Nastavte formát a kliknite na tlačidlo OK. To zvýrazní všetky chybové hodnoty vo vybranej množine údajov
   

Použitie funkcie Prejsť na špeciálne

1. Vyberte celý súbor údajov
2. Stlačte kláves F5 (otvorí sa dialógové okno Prejsť na)
3. Kliknite na špeciálne tlačidlo vľavo dole
4. Vyberte vzorce a zrušte začiarknutie všetkých možností okrem chýb
   

Tým sa vyberú všetky bunky, v ktorých je chyba. Teraz ich môžete manuálne zvýrazniť, odstrániť alebo do neho čokoľvek napísať.

#6 Zmeňte text na malé/veľké/správne

Keď dedíte zošit alebo importujete údaje z textových súborov, názvy alebo názvy nie sú často konzistentné. Niekedy môže byť celý text napísaný malými/veľkými písmenami alebo môže ísť o kombináciu oboch. Všetko môžete ľahko zosúladiť pomocou týchto troch funkcií:

LOWER () - Skonvertuje všetok text na malé písmená.
HORNÁ () - Skonvertuje všetok text na veľké písmená.
PROPER () - Skonvertuje všetok text na správny prípad.

#7 Analyzujte údaje pomocou textu na stĺpec

Keď získate údaje z databázy alebo ich importujete z textového súboru, môže sa stať, že všetok text bude stiesnený v jednej bunke. Tento text môžete analyzovať do viacerých buniek pomocou funkcie prevodu textu na stĺpec v programe Excel.

1. Vyberte údaje/text, ktoré chcete analyzovať
2. Prejdite na údaje -> text do stĺpca (otvára sa sprievodca textom do stĺpcov)
   • Krok 1: Vyberte typ údajov (ak sú údaje v rovnakých medzerách, vyberte možnosť Oddelené a oddeľte ich znakmi, ako sú čiarka, spojovník, bodka …). Kliknite na Ďalej
     
   • Krok 2: Vyberte oddeľovač (znak, ktorý oddeľuje vaše údaje). Môžete vybrať preddefinovaný oddeľovač alebo čokoľvek iné pomocou možnosti Iné
     
   • Krok 3: Vyberte formát údajov. Vyberte aj cieľovú bunku. Ak nie je vybratá cieľová bunka, aktuálna bunka sa prepíše
     

Súvisiace: Extrahujte používateľské meno z ID e -mailu pomocou textu do stĺpca.

#8 Kontrola pravopisu

Nič neznižuje dôveryhodnosť vašej práce ako pravopisná chyba.

Pomocou klávesovej skratky F7 spustite kontrolu pravopisu pre svoju množinu údajov.

Tu je podrobný návod, ako používať kontrolu pravopisu v programe Excel.

#9 Odstráňte všetky formátovania

Pri svojej práci som na získanie údajov v Exceli použil viacero databáz. Každá databáza mala svoje vlastné formátovanie údajov. Keď máte všetky údaje k dispozícii, takto môžete naraz odstrániť všetky formátovania:

1. Vyberte množinu údajov
2. Prejdite na položku Domov -> Vymazať -> Vymazať formáty
   

Podobne môžete vymazať iba komentáre, hypertextové odkazy alebo obsah.

#10 Na vyhľadanie údajov v programe Excel použite funkciu Hľadať a nahradiť

Nájdenie a nahradenie je pri čistení údajov nevyhnutné. Môžete napríklad vybrať a odstrániť všetky nuly, zmeniť odkazy vo vzorcoch, nájsť a zmeniť formátovanie atď.

Prečítajte si viac o tom, ako možno funkciu Nájsť a nahradiť použiť na čistenie údajov.

Toto je mojich 10 najlepších techník na čistenie údajov v programe Excel. Ak by ste sa chceli naučiť ešte niekoľko techník, tu je príručka tímu MS Excel - Čisté údaje v Exceli.

Ak používate ďalšie techniky, podeľte sa o to s nami v sekcii komentárov!