Desať najlepších spôsobov čistenia údajov

Nesprávne napísané slová, ťažko odstrániteľné medzery na konci, nechcené predpony, nesprávne použité veľké a malé písmená a netlačiteľné znaky môžu zanechať zlý prvý dojem. A to dokonca nie je ani úplný zoznam všetkého, čo môže kaziť úhľadnosť vašich údajov. Vyhrňte si rukávy. Je čas na veľké jarné upratovanie v hárkoch pomocou Microsoft Excelu.

Nie vždy máte kontrolu nad formátom a typom údajov, ktoré importujete z externého zdroja údajov, ako je napríklad databáza, textový súbor alebo webová stránka. Pred analýzou údajov je často potrebné ich vyčistiť. Našťastie sa v Exceli nachádza množstvo funkcií, pomocou ktorých môžete údaje získať v presne vami požadovanom formáte. Niekedy, keď je úloha jasná, môžete na jej vykonanie použiť konkrétnu funkciu, ktorá urobí všetko za vás. Kontrolu pravopisu môžete napríklad jednoducho použiť na vyčistenie nesprávne napísaných slov v stĺpcoch, v ktorých sa nachádzajú komentáre a popisy. Alebo, ak chcete odstrániť duplicitné riadky, môžete to urobiť rýchlo pomocou dialógového okna Odstránenie duplicít.

V iných prípadoch zas možno budete chcieť spracovávať jeden alebo viacero stĺpcov pomocou vzorca, ktorý slúži na konverziu importovaných hodnôt na nové hodnoty. Ak chcete napríklad odstrániť koncové medzery, môžete vytvoriť nový stĺpec na vyčistenie údajov pomocou vzorca, a to vyplnením nového stĺpca, konverziou vzorcov v novom stĺpci na hodnoty a potom odstránením pôvodného stĺpca.

Základné kroky na čistenie údajov:

  1. Importujte údaje z externých zdrojov údajov.

  2. Vytvorte záložnú kópiu pôvodných údajov v samostatnom zošite.

  3. Zabezpečte, aby boli údaje v riadkoch a stĺpcoch v tabuľkovom formáte: podobné údaje v jednotlivých stĺpcoch, všetky stĺpce a riadky, ktoré sú viditeľné, a žiadne prázdne riadky v rozsahu. Najlepšie výsledky dosiahnete použitím tabuľky Excelu.

  4. Najprv vykonajte úlohy, ktoré nevyžadujú manipuláciu so stĺpcami, ako je napríklad kontrola pravopisu alebo použitie dialógového okna Hľadať a nahradiť.

  5. Potom vykonajte úlohy, ktoré manipuláciu so stĺpcami vyžadujú. Všeobecné kroky na manipuláciu so stĺpcom sú:

    1. Vloženie nového stĺpca (B) vedľa pôvodného stĺpca (A), ktorý je potrebné vyčistiť.

    2. Pridanie vzorca, ktorý v hornej časti nového stĺpca (B) zmení údaje.

    3. Vyplnenie vzorca v novom stĺpci (B). V excelovej tabuľke sa automaticky vytvorí vypočítaný stĺpec s vyplnenými hodnotami.

    4. Výber nového stĺpca (B), jeho skopírovanie a prilepenie v podobe hodnôt do nového stĺpca (B).

    5. Odstránenie pôvodného stĺpca (A), čím sa skonvertuje nový stĺpec z B na A.

Ak chcete rovnaký zdroj údajov čistiť pravidelne, zvážte zaznamenanie makra alebo vytvorenie kódu, pomocou ktorého budete môcť celý proces zautomatizovať. K dispozícii je aj niekoľko externých doplnkov od nezávislých dodávateľov (zoznam nájdete v sekcii Nezávislí dodávatelia), ktoré môžete použiť, ak nemáte na zautomatizovanie procesu čas ani prostriedky.

Ďalšie informácie

Popis

Prehľad pripojenia (importovania) údajov

Popisuje rôzne spôsoby importovania externých údajov do programu Office Excel.

Automatické zadávanie údajov do buniek hárka

Zobrazuje postup použitia príkazu Vyplniť.

Vytvorenie alebo odstránenie excelovej tabuľky

Pridanie alebo odstránenie riadkov a stĺpcov v excelovej tabuľke

Použitie vypočítaných stĺpcov v tabuľke programu Excel

Zobrazuje vytvorenie excelovej tabuľky a pridanie alebo odstránenie stĺpcov alebo vypočítaných stĺpcov.

Vytvorenie makra

Zobrazuje rôzne spôsoby automatizácie opakujúcich sa úloh pomocou makra.

Kontrolu pravopisu môžete okrem vyhľadania nesprávne napísaných slov použiť aj na vyhľadanie hodnôt, ktoré sa nepoužívajú konzistentne (môžu to byť názvy produktov alebo spoločností), a to pridaním týchto hodnôt do vlastného slovníka.

Ďalšie informácie

Popis

Kontrola pravopisu a gramatiky

Zobrazuje postup opravy nesprávne napísaných slov v hárku.

Pridávanie slov do kontroly pravopisu pomocou vlastných slovníkov

Vysvetľuje používanie vlastných slovníkov.

Výskyt duplicitných riadkov je bežným problémom, ktorý sa pri importe údajov vyskytuje. Je výhodnejšie najskôr filtrovať jedinečné hodnoty, aby ste sa ešte pred odstránením duplicitných hodnôt uistili, že dosiahnete požadované výsledky.

Ďalšie informácie

Popis

Filtrovanie jedinečných hodnôt alebo odstránenie duplicitných hodnôt

Zobrazuje dva úzko súvisiace postupy: spôsob filtrovania jedinečných riadkov a spôsob odstránenia duplicitných riadkov.

Možno budete chcieť odstrániť spoločný úvodný reťazec, ako je napríklad menovka, za ktorou nasleduje dvojbodka a medzera, alebo prípona, ako je napríklad výraz v zátvorkách na konci reťazca, ktorý je zastaraný alebo nepotrebný. Môžete to urobiť vyhľadaním výskytu tohto textu, ktorý potom nahradíte iným alebo žiadnym textom.

Ďalšie informácie

Popis

Kontrola, či sa v bunke nachádza text (nerozlišuje veľké a malé písmená)

Kontrola, či sa v bunke nachádza text (rozlišuje veľké a malé písmená)

Zobrazuje postup používania príkazu Hľadať a niekoľkých ďalších funkcií na vyhľadanie textu.

Odstránenie znakov z textu

Zobrazuje postup použitia príkazu Nahradiť a niekoľkých ďalších funkcií na odstránenie textu.

Vyhľadávanie alebo nahrádzanie textu a čísel v hárku

Hľadať a nahradiť

Zobrazuje používanie dialógových okien Hľadať a Nahradiť.

FIND, FINDB

SEARCH, SEARCHB

REPLACE, REPLACEB

SUBSTITUTE

LEFT, LEFTB

RIGHT, RIGHTB

LEN, LENB

MID, MIDB

Toto sú funkcie, ktoré môžete použiť na rôzne úlohy súvisiace s manipuláciou reťazca, ako je hľadanie a nahradenie podreťazca v rámci reťazca, extrahovanie častí reťazca alebo určovanie dĺžky reťazca.

Niekedy sa text zobrazuje odlišne, najmä ak ide o veľkosť písmen v texte. Pomocou jednej alebo viacerých z troch funkcií CASE môžete skonvertovať text na malé písmená, ako sú napríklad e-mailové adresy, na veľké písmená, ako sú napríklad kódy produktov, alebo do formátu vlastných podstatných mien, ako sú mená a názvy kníh.

Ďalšie informácie

Popis

Zmena veľkosti písmen v texte

Zobrazuje postup použitia troch funkcií CASE.

LOWER

Skonvertuje všetky veľké písmená v textovom reťazci na malé písmená.

PROPER

Zmení prvé písmeno v textovom reťazci a iné písmená v texte, ktoré nasledujú po nejakom znaku inom ako je písmeno. Všetky ostatné písmená zmení na malé.

UPPER

Skonvertuje text na veľké písmená.

Niekedy sa v textových hodnotách nachádzajú znaky úvodných medzier, medzier na konci alebo viacerých vložených medzier (tabuľka znakov Unicode hodnôt 32 a 160), prípadne netlačiteľné znaky (tabuľka znakov Unicode hodnôt od 0 po 31, 127, 129, 141, 143, 144 a 157). Tieto znaky môžu niekedy pri zoraďovaní, filtrovaní a vyhľadávaní spôsobiť neočakávané výsledky. V externom zdroji údajov môžu napríklad používatelia spôsobiť typografické chyby neúmyselným pridaním nadbytočné znakov medzier, prípadne môžu údaje z importovaného textu z externých zdrojov obsahovať netlačiteľné znaky, ktoré sú v texte vnorené. Keďže sú tieto znaky ťažko rozpoznateľné, neočakávané chyby je veľmi zložité odhaliť. Ak chcete tieto nežiaduce znaky odstrániť, môžete použiť kombináciu funkcií TRIM, CLEAN a SUBSTITUTE.

Ďalšie informácie

Popis

Zobrazuje postup odstránenia všetkých medzier a netlačiteľných znakov z tabuľky znakov Unicode.

CODE

Vráti číselný kód prvého znaku textového reťazca.

CLEAN

Odstráni z textu prvých 32 netlačiteľných znakov nachádzajúcich sa v 7-bitovom kóde ASCII (hodnoty 0 až 31).

TRIM

Odstráni z textu znak medzery 7-bitového kódu ASCII (hodnota 32).

SUBSTITUTE

Funkciu SUBSTITUTE môžete použiť na nahradenie znaku Unicode s vyššou hodnotou (hodnoty 127, 129, 141, 143, 144, 157 a 160) znakmi 7-bitovej tabuľky znakov ASCII, ktoré je možné použiť s funkciami TRIM a CLEAN.

Existujú dva hlavné problémy s číslami, ktoré môžu vyžadovať vyčistenie údajov: číslo sa neúmyselne importovalo ako text a znamienko mínus je potrebné vo vašej organizácii zmeniť na štandardný znak.

Ďalšie informácie

Popis

Konverzia čísiel uložených ako text na čísla

Zobrazuje postup konverzie čísel, ktoré sú formátované a uložené v bunkách ako text, čo môže spôsobiť problémy pri výpočtoch alebo spôsobe zoradenia, na číselný formát.

DOLLAR

Skonvertuje číslo do textového formátu a priradí mu symbol meny.

TEXT

Skonvertuje hodnotu na text v špecifickom číselnom formáte.

FIXED

Zaokrúhli číslo na určený počet desatinných miest, naformátuje číslo v desiatkovom formáte pomocou desatinnej čiarky a oddeľovačov tisícok a výsledok vráti ako text.

VALUE

Skonvertuje textový reťazec predstavujúci číslo na číslo.

Keďže existuje veľa rôznych formátov dátumu, ktoré je možné si pomýliť s číselnými časťami kódov alebo s inými reťazcami, ktoré obsahujú lomky alebo spojovníky, musia byť dátumy a časy často skonvertované a preformátované.

Ďalšie informácie

Popis

Zmena kalendárneho systému, formátu alebo dvoj-číselného vyjadrenia roka

Popisuje fungovanie kalendárneho systému v Office Exceli.

Konverzia času

Zobrazuje postup konverzie medzi rôznymi časovými jednotkami.

Konverzia dátumov uložených ako text na dátumy

Zobrazuje postup konverzie dátumov, ktoré sú formátované a uložené v bunkách ako text, čo môže spôsobiť problémy pri výpočtoch alebo spôsobe zoradenia, na dátumový formát.

DATE

Vráti poradové číslo predstavujúce konkrétny dátum. Ak mala bunka pred zadaním funkcie formát Všeobecné, výsledok sa naformátuje ako dátum.

DATEVALUE

Skonvertuje dátum zadaný ako text na poradové číslo.

TIME

Vráti desatinné číslo pre určitý čas. Ak mala bunka pred zadaním funkcie formát Všeobecné, výsledok sa naformátuje ako dátum.

TIMEVALUE

Vráti desatinné číslo času vyjadreného textovým reťazcom. Desatinné číslo je hodnota z rozsahu od 0 (nula) po 0,99999999, vyjadrujúca časy od 0:00:00 (12:00:00 dop.) po 23:59:59 (11:59:59 odp.).

Bežnou úlohou, ktorá nasleduje po importovaní údajov z externého zdroja údajov, je zlúčenie dvoch alebo viacerých stĺpcov do jedného, prípadne rozdelenie jedného stĺpca do dvoch alebo viacero stĺpcov. Môžete napríklad rozdeliť stĺpec obsahujúci celé meno na meno a priezvisko. Alebo môžete rozdeliť stĺpec, ktorý obsahuje pole s adresou na samostatné stĺpce obsahujúce ulicu, mesto, oblasť a PSČ. Môžete to urobiť aj naopak. Môžete zlúčiť stĺpec s názvom Meno a Priezvisko do stĺpca s názvom Celé meno alebo spojiť samostatné stĺpce s adresou do jedného stĺpca. Ďalšie bežné hodnoty, ktoré môžu vyžadovať zlúčenie do jedného stĺpca alebo rozdelenie do viacerých stĺpcov, môžu obsahovať kódy produktov, cesty k súborom a IP adresy.

Ďalšie informácie

Popis

Spojenie mena a priezviska

Spojenie textu a čísel

Spojenie textu s dátumom alebo časom

Spojenie dvoch alebo viacerých stĺpcov pomocou funkcie

Zobrazuje typické príklady spájania hodnôt z dvoch alebo viacerých stĺpcov.

Rozdelenie textu do rôznych stĺpcov pomocou Sprievodcu konvertovaním textu na stĺpce

Zobrazuje postup použitia sprievodcu na rozdelenie stĺpcov na základe rôznych bežných oddeľovačov.

Rozdelenie textu do rozdielnych stĺpcov pomocou funkcií

Zobrazuje postup použitia funkcií LEFT, MID, RIGHT, SEARCH a LEN, pomocou ktorých môžete rozdeliť názov stĺpca na dva alebo viacero stĺpcov.

Spojenie alebo rozdelenie obsahu buniek

Zobrazuje postup použitia funkcie CONCATENATE, operátora & (ampersand) a Sprievodcu konvertovaním textu na stĺpce.

Zlúčenie buniek alebo rozdelenie zlúčených buniek

Zobrazuje postup použitia príkazov Zlúčiť bunky, Zlúčiť cez a Zlúčiť a centrovať.

CONCATENATE

Spojí dva alebo viaceré textové reťazce do jedného textového reťazca.

Väčšina funkcií analýz a formátovania v programe Office Excel predpokladá, že sú údaje usporiadané v jednoduchej dvojrozmernej tabuľke. Niekedy možno chcete riadky zmeniť na stĺpce a stĺpce na riadky. Inokedy zas údaje nie sú štruktúrované v tabuľkovom formáte a potrebujete pretransformovať údaje z formátu, ktorý nie je tabuľkový, do tabuľkového.

Ďalšie informácie

Popis

TRANSPOSE

Vráti zvislý rozsah buniek ako vodorovný rozsah a naopak.

Niekedy správcovia databázy používajú Office Excel na vyhľadávanie a opravu zhodných chýb pri dvoch alebo viacerých spojených tabuľkách. Tento proces môže zahŕňať zosúladenie dvoch tabuliek pochádzajúcich z rôznych hárkov, aby ste napríklad mohli zobrazovať všetky záznamy z oboch tabuliek alebo porovnávať tabuľky a vyhľadávať riadky, ktoré sa nezhodujú.

Ďalšie informácie

Popis

Vyhľadávanie hodnôt v zozname údajov

Zobrazuje bežné spôsoby vyhľadávania údajov pomocou vyhľadávacích funkcií.

LOOKUP

Vráti hodnotu v jednoriadkovom alebo jednostĺpcovom rozsahu alebo v poli. Funkcia LOOKUP používa dvojakú syntax: vektorovú formu a formu poľa.

HLOOKUP

Vyhľadá hodnotu v hornom riadku tabuľky alebo poľa hodnoty a potom vráti hodnotu v tom istom stĺpci počnúc riadkom, ktorý ste v tabuľke alebo poli zadali.

VLOOKUP

Vyhľadá hodnotu v prvom stĺpci poľa tabuľky a vráti hodnotu v tom istom riadku z iného stĺpca v poli tabuľky.

INDEX

Vráti hodnotu alebo odkaz na hodnotu z tabuľky alebo rozsahu. Existujú dve formy funkcie INDEX: forma poľa a forma odkazu.

MATCH

Vráti relatívnu pozíciu položky v poli, ktorá zodpovedá danej hodnote v určenom poradí. Ak namiesto samotnej položky potrebujete zistiť polohu tejto položky, použite namiesto jednej z funkcií LOOKUP funkciu MATCH.

OFFSET

Vráti odkaz na rozsah, ktorý je posunutý o daný počet riadkov a stĺpcov od bunky alebo rozsahu buniek. Vrátený odkaz môže byť jedna bunka alebo rozsah buniek. Môžete určiť aj počet riadkov a stĺpcov, ktoré sa majú vrátiť.

Nižšie nájdete čiastočný zoznam nezávislých dodávateľov poskytujúcich produkty, ktoré sa používajú na čistenie údajov rôznymi spôsobmi.

Poznámka : Spoločnosť Microsoft neposkytuje podporu pre produkty od nezávislých strán.

Poskytovateľ

Produkt

Add-in Express Ltd.

Ultimate Suite for Excel, Merge Tables Wizard, Duplicate Remover, Consolidate Worksheets Wizard, Combine Rows Wizard, Cell Cleaner, Random Generator, Merge Cells, Quick Tools for Excel, Random Sorter, Advanced Find & Replace, Fuzzy Duplicate Finder, Split Names, Split Table Wizard, Workbook Manager

Add-Ins.com

Duplicate Finder

AddinTools

AddinTools Assist

J-Walk &Associates, Inc.

Power Utility Pak Version 7

WinPure

ListCleaner Lite
ListCleaner Pro
Clean and Match 2007

Na začiatok stránky

Rozšírte svoje zručnosti
Preskúmať školenie
Buďte medzi prvými, ktorí získajú nové funkcie
Pridajte sa k insiderom pre Office

Boli tieto informácie užitočné?

Ďakujeme za vaše pripomienky!

Ďakujeme vám za pripomienky. Pravdepodobne vám pomôže, ak vás spojíme s pracovníkom podpory pre Office.

×