Deset nejlepších způsobů čištění dat

Chybně napsaná slova, zapomenuté koncové mezery, nežádoucí předpony, nesprávně napsaná malá a velká písmena a netisknutelné znaky nepůsobí na první dojem dobře. A to je jen několik příkladů špatné práce s daty. Vyhrňte si rukávy. Je čas provést pomocí Microsoft Excelu důkladný jarní úklid ve vašich listech.

Ne vždy můžete ovlivnit formát a typ dat, která importujete z externího zdroje, jako může být databáze, textový soubor nebo webová stránka. Než provedete analýzu dat, často je musíte nejdřív vyčistit. Naštěstí má Excel řadu funkcí, které vám pomůžou data převést na přesně požadovaný formát. Někdy je to jednoduchý úkol, který za vás udělá určitá funkce. Například nástroj pro kontrolu pravopisu snadno vyčistí chybně napsaná slova ve sloupcích s popisy nebo komentáři. Nebo když chcete odstranit duplicitní řádky, zvládnete to rychle pomocí dialogového okna Odebrat duplicity.

V jiných případech budete muset pracovat s jedním nebo několika sloupci pomocí vzorce, který převede importované hodnoty na nové hodnoty. Když chcete odebrat například koncové mezery, můžete vytvořit nový sloupec, ve kterém data vyčistíte pomocí vzorce, potom tento sloupec vyplníte požadovanými vzorci, vzorce nového sloupce převedete na hodnoty a původní sloupec pak odeberete.

Základní kroky pro čištění dat jsou následující:

  1. Importujte data z externího zdroje.

  2. V samostatném sešitě vytvořte záložní kopii původních dat.

  3. Data musí tvořit tabulku se sloupci a daty, přičemž v každém sloupci musí být podobná data, všechny sloupce a řádky musí být viditelné a v oblasti nesmí být žádné prázdné řádky. Nejlepších výsledků dosáhnete pomocí excelové tabulky.

  4. Nejdřív se pusťte do úkolů, které nevyžadují manipulaci se sloupci, jako je například kontrola pravopisu nebo práce s dialogovým oknem Najít a nahradit.

  5. Pak udělejte úkoly, které vyžadují manipulaci se sloupci. Obecný postup pro práci se sloupci je následující:

    1. Vedle původního sloupce (A), který je potřeba vyčistit, vložte nový sloupec (B).

    2. Do horní části nového sloupce (B) přidejte vzorec, který transformuje požadovaná data.

    3. Vyplňte nový sloupec (B) tímto vzorcem. V excelové tabulce se automaticky vytvoří počítaný sloupec s vyplněnými hodnotami.

    4. Vyberte nový sloupec (B), zkopírujte ho a vložte jako hodnoty do nového sloupce (B).

    5. Odeberte původní sloupec (A), čímž převedete nový sloupec ze sloupce B do sloupce A.

Pokud chcete určitý zdroj dat čistit pravidelně, doporučujeme nahrát makro nebo vytvořit kód, který celý proces zautomatizuje. Existuje také řada doplňků od externích dodavatelů. Jejich seznam najdete v části Poskytovatelé třetích stran. Přinášejí řešení v situaci, kdy nemáte čas nebo zdroje potřebné pro automatizaci tohoto procesu.

Další informace

Popis

Přehled připojování k datům (jejich importu)

Popisuje všechny způsoby importu externích dat do Excelu v Office.

Automatické vyplnění dat v buňkách listu

Ukazuje, jak používat příkaz Vyplnit.

Vytvoření nebo odstranění excelové tabulky

Přidání nebo odebrání řádků a sloupců excelové tabulky

Použití počítaných sloupců v tabulce aplikace Excel

Ukazuje, jak vytvořit tabulku v Excelu a přidat nebo odebrat sloupce nebo počítané sloupce.

Vytvoření makra

Ukazuje několik způsobů, jak pomocí makra automatizovat opakující se úlohy.

Pomocí nástroje pro kontrolu pravopisu můžete najít nejen chybně napsaná slova, ale také nejednotně používané hodnoty, jako jsou názvy firem nebo produktů. Stačí tyto hodnoty zadat do vlastního slovníku.

Další informace

Popis

Kontrola pravopisu a gramatiky

Ukazuje, jak v sešitě opravit chybně napsaná slova.

Přidávání slov do vlastních slovníků kontroly pravopisu

Vysvětluje, jak používat vlastní slovníky.

Duplicitní řádky představují při importu dat běžný problém. Než odeberete duplicitní hodnoty, je dobré nejdřív vyfiltrovat jedinečné hodnoty, abyste si ověřili, že jste získali požadované výsledky.

Další informace

Popis

Filtrování jedinečných hodnot nebo odebrání duplicitních hodnot

Ukazuje dva úzce související postupy: filtrování jedinečných řádků a odstraňování duplicitních řádků.

Možná budete chtít odebrat obyčejný koncový řetězec, jako je popisek, po kterém následuje dvojtečka nebo mezera, nebo příponu, jako je fráze v závorkách na konci řetězce, která už je neaktuální nebo nepotřebná. Můžete to udělat tak, že najdete příklady daného textu, který pak nahradíte jiným nebo žádným textem.

Další informace

Popis

Zjištění, zda buňka obsahuje text (bez rozlišení malých a velkých písmen)

Zjištění, zda buňka obsahuje text (s rozlišením malých a velkých písmen)

Ukazuje, jak pracovat s příkazem Najít a několika funkcemi pro hledání textu.

Odebrání znaků z textu

Ukazuje, jak pracovat s příkazem Nahradit a několika funkcemi pro odebrání textu.

Vyhledání nebo nahrazení textu a čísel v listu

Najít a nahradit

Ukazuje, jak pracovat s dialogovými okny Najít a Nahradit.

NAJÍT, FINDB

HLEDAT, SEARCHB

NAHRADIT, REPLACEB

DOSADIT

ZLEVA, LEFTB

ZPRAVA, RIGHTB

DÉLKA, LENB

ČÁST, MIDB

Jedná se o funkce, pomocí kterých můžete různě pracovat s řetězci, například najít a hradit v řetězci podřetězec, extrahovat části řetězce nebo určit délku řetězce.

Někdy se v textu míchají velká a malá písmena. Pomocí tří funkcí pro nastavení malých a velkých písmen můžete převést texty na malá písmena (například e-mailové adresy), velká písmena (například kódy produktů) nebo můžete u všech slov nastavit velké první písmeno (například u jmen).

Další informace

Popis

Změna malých a velkých písmen v textu

Ukazuje, jak používat tři funkce pro práci s velkými a malými písmeny.

MALÁ

Převede všechna velká písmena v textu na malá.

VELKÁ2

Převede první písmeno textu a všechna další písmena, která následují bezprostředně za neabecedními znaky, na velká písmena. Všechna ostatní písmena se převedou na malá.

VELKÁ

Převede text na velká písmena.

Textové hodnoty někdy obsahují na začátku nebo na konci mezery, několik vložených mezer (hodnoty znakové sady Unicode 32 a 160) nebo netisknutelné znaky (hodnoty znakové sady Unicode 0 až 31, 127, 129, 141, 143, 144 a 157). Při řazení, filtrování nebo vyhledávání můžou tyto znaky způsobit, že se zobrazí neočekávané výsledky. V externím zdroji dat mohli například uživatelé způsobit překlepy, když omylem přidali nadbytečné mezery, nebo v textových datech importovaných z externích zdrojů můžou být vložené netisknutelné znaky. Tyto znaky se dají snadno přehlédnout, takže může být těžké porozumět neočekávaným výsledkům. Pokud chcete tyto nežádoucí znaky odebrat, můžete použít kombinaci funkcí PROČISTIT, VYČISTIT a DOSADIT.

Další informace

Popis

Ukazuje, jak odebrat všechny mezery a netisknutelné znaky ze znakové sady Unicode.

KÓD

Vrátí číselný kód prvního znaku zadaného textového řetězce.

VYČISTIT

Odebere z textu prvních 32 netisknutelných znaků v 7bitovém kódu ASCII (hodnoty 0 až 31).

PROČISTIT

Odebere z textu znak mezery v 7bitovém kódu ASCII (hodnota 32).

DOSADIT

Pomocí funkce DOSADIT můžete znaky Unicode s vyšší hodnotou (hodnoty 127, 129, 141, 143, 144, 157 a 160) nahradit znaky 7bitové znakové sady ASCII, pro které byly vytvořeny funkce PROČISTIT a VYČISTIT.

Dva hlavní problémy s čísly, které si můžou vynutit vyčištění dat, jsou následující: číslo se omylem importovalo jako text a symbol minus je nutné změnit na standardní formát vaší organizace.

Další informace

Popis

Převod čísel uložených jako text na čísla

Ukazuje, jak čísla formátovaná a uložená v buňkách jako text, která můžou způsobovat potíže s výpočty nebo vést k matoucímu řazení, převést do číselného formátu.

Převede číslo do textové podoby a přidá k němu symbol měny.

TEXT

Převede hodnotu na text v konkrétním číselném formátu.

ZAOKROUHLIT.NA­.TEXT

Zaokrouhlí číslo na určený počet desetinných míst, zformátuje toto číslo v desetinném formátu s tečkou a čárkami a výsledek vrátí v podobě textu.

HODNOTA

Převede textový řetězec představující číslo na číslo.

Protože existuje řada různých formátů dat a tyto formáty si můžete plést s číselnými kódy nebo jinými řetězci, které obsahují lomítka nebo pomlčky, často je nutné datum a čas převést a přeformátovat.

Další informace

Popis

Změna kalendářního systému, formátu data a způsobu vyhodnocení roků vyjádřených pomocí dvou číslic

Popisuje, jak v aplikaci Office Excel funguje kalendářní systém.

Převod časů

Ukazuje, jak převádět různé časové jednotky.

Převedení dat uložených jako text na kalendářní data

Ukazuje, jak data formátovaná a uložená v buňkách jako text, která můžou způsobovat potíže s výpočty nebo vést k matoucímu řazení, převést do kalendářního formátu.

DATUM

Vrátí sekvenční pořadové číslo, které představuje určité datum. Pokud měla buňka před zadáním vzorce obecný formát, výsledek se naformátuje jako datum.

DATUMHODN

Převede datum v podobě textu na pořadové číslo.

ČAS

Vrátí desetinné číslo aktuálního času. Jestliže je formát buňky před zadáním funkce Obecný, je výsledek formátován jako datum.

ČASHODN

Vrátí desetinné číslo času zadaného jako textový řetězec. Desetinné číslo je hodnota v rozmezí od 0 (nula) do 0,99999999, která představuje čas od 0:00:00 (12:00:00 dop.) do 23:59:59 (11:59:59 odp.).

Po importování dat z externího zdroje je často další úlohou buď sloučení několika sloupců do jednoho, nebo rozdělení jednoho sloupce do většího počtu. Například můžete chtít rozdělit sloupec, který obsahuje celé jméno, na sloupec se jménem a sloupec s příjmením. Nebo můžete rozdělit sloupec, který obsahuje pole adresy, do samostatných sloupců s ulicí, městem, krajem a PSČ. Možná je i opačná situace. Můžete sloučit sloupce se jménem a příjmením do jednoho sloupce s celým jménem nebo sloučit sloupce s jednotlivými prvky adresy do jednoho. Mezi další hodnoty, u kterých se často provádí sloučení nebo rozdělení sloupců, patří kódy produktů, cesty k souborům a IP adresy.

Další informace

Popis

Kombinace jména a příjmení

Sloučení textu a čísel

Kombinace textu s datem nebo časem

Sloučení dvou nebo více sloupců pomocí funkce

Ukazuje typické příklady sloučení hodnot ze dvou nebo více sloupců.

Rozdělení textu do různých sloupců pomocí Průvodce převodem textu do sloupců

Ukazuje, jak pomocí tohoto průvodce rozdělit sloupce na základě různých běžných oddělovačů.

Rozdělení textu do samostatných sloupců pomocí funkcí

Ukazuje, jak pomocí funkcí VLEVO, ČÁST, VPRAVO, HLEDAT a DÉLKA rozdělit jeden sloupec se jménem do dvou nebo víc sloupců.

Sloučení nebo rozdělení obsahu buněk

Ukazuje, jak pracovat s funkcí CONCATENATE, operátorem & (ampersand) a Průvodcem převodem textu do sloupců.

Sloučení buněk nebo rozdělení sloučených buněk

Ukazuje, jak používat příkazy Sloučit buňky, Sloučit vodorovně a Sloučit a zarovnat na střed.

CONCATENATE

Spojí dva nebo víc textových řetězců do jednoho.

Většina analytických a formátovacích funkcí předpokládá, že data se nachází v jedné ploché dvojrozměrné tabulce. Někdy třeba chcete, aby se z řádků staly sloupce a ze sloupců řádky. Jindy data nejsou strukturovaná do tabulkového formátu, ale potřebujete je do něho převést.

Další informace

Popis

TRANSPOZICE

Z vodorovné oblasti buněk vytvoří horizontální a naopak.

Správci databází někdy používají Office Excel, aby našli a opravili chybná spárování vzniklá při sloučením dvou nebo více tabulek. Součástí tohoto procesu může být sjednocení dvou tabulek z různých listů, aby správci viděli například všechny záznamy v obou tabulkách nebo aby porovnali tabulky a našli v nich špatně spárované řádky.

Další informace

Popis

Vyhledání hodnot v seznamu dat

Ukazuje běžné možnosti, jak vyhledávat data pomocí funkcí vyhledávání.

VYHLEDAT

Vrátí hodnotu z oblasti zahrnující jeden řádek nebo sloupec nebo z matice. Funkce VYHLEDAT má dvě syntaktické formy: vektorovou a maticovou.

VVYHLEDAT

Vyhledá hodnotu v horním řádku tabulky nebo v matici hodnot a vrátí hodnotu buňky z určeného řádku stejného sloupce, který jste zadali v dané tabulce nebo matici.

SVYHLEDAT

Vyhledá hodnotu v prvním sloupci tabulky a vrátí hodnotu buňky ze stejného řádku z jiného sloupce v dané tabulce.

INDEX

Vrátí hodnotu nebo odkaz na hodnotu z tabulky nebo oblasti. Existují dvě různé formy funkce INDEX: maticová forma a forma odkazu.

POZVYHLEDAT

Vrátí relativní pozici položky v matici, která odpovídá určité hodnotě v určeném pořadí. Když potřebujete zjistit pozici položky v oblasti, ne její obsah, použijte funkci POZVYHLEDAT.

POSUN

Vrátí odkaz na oblast, která obsahuje určený počet řádků a sloupců, od určité buňky nebo oblasti buněk. Vrácený odkaz může být jedna buňka nebo oblast buněk. Počet řádků a sloupců, které se mají vrátit, můžete určit.

Tady najdete několik poskytovatelů třetích stran, kteří nabízejí produkty, které umí různými způsoby vyčistit data.

Poznámka: Microsoft neposkytuje podporu produktům třetích stran.

Poskytovatel

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

Začátek stránky

Rozšiřte své znalosti a dovednosti
Projít školení
Získejte nové funkce jako první
Připojte se k účastníkům programu Office Insiders

Byly tyto informace užitečné?

Děkujeme vám za zpětnou vazbu.

Děkujeme vám za váš názor. Vypadá to, že bude užitečné, když vás spojíme s některým z našich agentů z podpory Office.

×