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.
Základy čistenia údajov
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:
Importujte údaje z externých zdrojov údajov.
Vytvorte záložnú kópiu pôvodných údajov v samostatnom zošite.
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.
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ť.
Potom vykonajte úlohy, ktoré manipuláciu so stĺpcami vyžadujú. Všeobecné kroky na manipuláciu so stĺpcom sú:
- Vloženie nového stĺpca (B) vedľa pôvodného stĺpca (A), ktorý je potrebné vyčistiť.
- Pridanie vzorca, ktorý v hornej časti nového stĺpca (B) zmení údaje.
- Vyplnenie vzorca v novom stĺpci (B). V excelovej tabuľke sa automaticky vytvorí vypočítaný stĺpec s vyplnenými hodnotami.
- Výber nového stĺpca (B), jeho skopírovanie a prilepenie v podobe hodnôt do nového stĺpca (B).
- 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 |
|---|---|
| Automatické zadávanie údajov do buniek hárka | Zobrazuje postup použitia príkazu Vyplniť. |
|
Vytváranie a formátovanie tabuliek Zmena veľkosti tabuľky pridaním alebo odstránením riadkov a stĺpcov Použitie vypočítaných stĺpcov v excelovej tabuľke |
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. |
Kontrola pravopisu
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. |
Odstránenie duplicitných riadkov
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. |
Hľadanie a nahradenie textu
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 bunka obsahuje text (v ktorom sa nerozlišujú malé a veľké písmená) Kontrola, či bunka obsahuje text (s rozlíšením malých a veľkých 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 | 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. |
Zmena veľkosti písmen v texte
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á. |
Odstraňovanie medzier a netlačiteľných znakov z textu
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) alebo 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 |
|---|---|
| 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. |
Oprava čísel a znakov čísel
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. |
Oprava dátumov a časov
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 časového údaju, ktorý je vyjadrený textovým reťazcom. Desatinné číslo je hodnota v rozsahu od 0 (nula) do 0,99999999, čo predstavuje časový údaj od 0:00:00 do 23:59:59. |
Zlúčenie a rozdelenie stĺpcov
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 Kombinovanie 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. |
Transformácia a zmena usporiadania stĺpcov a riadkov
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. |
Zosúladenie údajov v tabuľke spájaním alebo priradením
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ť. |
Nezávislí dodávatelia
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.