10 super úhledných způsobů čištění dat v tabulkách aplikace Excel

Podívejte se na video - 10 způsobů, jak vyčistit data v Excelu

Data tvoří páteř jakékoli analýzy, kterou provádíte v aplikaci Excel. A pokud jde o data, existuje spousta věcí, které se mohou pokazit - ať už jde o strukturu, umístění, formátování, mezery atd.

V tomto blogovém příspěvku vám ukážu 10 jednoduchých způsobů, jak vyčistit data v Excelu.

#1 Zbavte se dalších prostorů

Extra mezery jsou bolestivě obtížně rozpoznatelné. Mezery mezi slovy nebo čísly můžete nějakým způsobem rozpoznat, mezery za nimi však nejsou ani viditelné. Zde je úhledný způsob, jak se těchto nadbytečných mezer zbavit - použijte funkci TRIM.

Syntaxe: TRIM (text)

Funkce Excel TRIM bere jako vstup odkaz na buňku (nebo text). Odstraňuje úvodní a koncové mezery a také další mezery mezi slovy (kromě jednotlivých mezer).

#2 Vyberte a ošetřete všechny prázdné buňky

Prázdné buňky mohou způsobit zmatek, pokud nejsou předem ošetřeny. Často se potýkám s problémy s prázdnými buňkami v datové sadě, která se používá k vytváření sestav/dashboardů.

Možná budete chtít vyplnit všechny prázdné buňky „0“ nebo „Nedostupné“, nebo to jednoduše chcete zvýraznit. Pokud existuje velký soubor dat, může to ruční provedení trvat hodiny. Naštěstí existuje způsob, jak můžete vybrat všechny prázdné buňky najednou.

1. Vyberte celou sadu dat
2. Stiskněte klávesu F5 (otevře se dialogové okno Přejít na)
3. Klikněte na tlačítko Special… (vlevo dole). Otevře se dialogové okno Přejít na speciální
   
4. Vyberte prázdné a klikněte na OK
   

Tím se vyberou všechny prázdné buňky ve vaší sadě dat. Chcete -li do všech těchto buněk zadat 0 nebo Není k dispozici, zadejte jej a stiskněte Ctrl + Enter (pamatujte, že pokud stisknete pouze Enter, hodnota se vloží pouze do aktivní buňky).

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

Někdy při importu dat z textových souborů nebo externích databází se čísla uloží jako text. Někteří lidé mají také ve zvyku používat apostrof (‘) před číslem, aby z něj bylo text. Pokud tyto buňky používáte ve výpočtech, mohlo by to způsobit vážné problémy. Zde je hloupý způsob, jak převést tato čísla uložená jako text zpět na čísla.

1. Do libovolné prázdné buňky zadejte 1
2. Vyberte buňku, do které jste zadali 1, a stiskněte Ctrl + C
3. Vyberte buňku/rozsah, který chcete převést na čísla
4. Vyberte Vložit -> Vložit jinak (Klávesová zkratka - Alt + E + S)
5. V poli Vložit speciální dialog vyberte Násobit (v kategorii operací)
   
6. Klikněte na OK. To převede všechna čísla v textovém formátu zpět na čísla.

S možností vložení speciálních operací můžete udělat mnohem více. Zde jsou různé další způsoby, jak se v aplikaci Excel množit pomocí Vložit jinak.

#4 - Odeberte duplikáty

S duplicitními daty můžete dělat 2 věci - Zvýrazněte to nebo Smazat to.

• Zvýrazněte duplicitní data:
  • Vyberte data a přejděte na Domů -> Podmíněné formátování -> Pravidla zvýraznění buněk -> Duplikovat hodnoty.
  • Zadejte formátování a zvýrazní se všechny duplicitní hodnoty.
    
• Odstranit duplikáty v datech:
  • Vyberte data a přejděte na Data -> Odebrat duplikáty.
  • Pokud vaše data obsahují záhlaví, zkontrolujte, zda je zaškrtnuto políčko vpravo nahoře.
  • Vyberte sloupce, ze kterých chcete odebrat duplikáty, a klikněte na OK.
    

Tím se ze seznamu odstraní duplicitní hodnoty. Pokud chcete, aby byl původní seznam neporušený, zkopírujte data na jiné místo a proveďte to.

Příbuzný: The Ultimate Guide to Find and remove Duplicates in Excel.

#5 Zvýrazněte chyby

Chyby v datech v aplikaci Excel můžete zvýraznit dvěma způsoby:

Použití podmíněného formátování

1. Vyberte celou sadu dat
2. Přejděte na Domů -> Podmíněné formátování -> Nové pravidlo
3. V dialogovém okně Nové pravidlo formátování vyberte „Formátovat pouze buňky, které obsahují“
4. V popisu pravidla vyberte z rozevíracího seznamu Chyby
5. Nastavte formát a klikněte na OK. To zvýrazní jakoukoli chybovou hodnotu ve vybrané datové sadě
   

Pomocí Přejít na speciální

1. Vyberte celou sadu dat
2. Stiskněte F5 (otevře se dialogové okno Přejít na)
3. Vlevo dole klikněte na Speciální tlačítko
4. Vyberte vzorce a zrušte zaškrtnutí všech možností kromě chyb
   

Tím se vyberou všechny buňky, ve kterých je chyba. Nyní je můžete ručně zvýraznit, odstranit nebo do něj cokoli napsat.

#6 Změňte text na malá/velká/správná písmena

Když zdědíte sešit nebo importujete data z textových souborů, často názvy nebo názvy nejsou konzistentní. Někdy může být celý text psán malými/velkými písmeny nebo může jít o kombinaci obojího. Můžete to snadno udělat konzistentním pomocí těchto tří funkcí:

LOWER () - Převede veškerý text na malá písmena.
UPPER () - Převede veškerý text na velká písmena.
PROPER () - Převede veškerý text na správný případ.

#7 Analyzujte data pomocí textu na sloupec

Když získáte data z databáze nebo je importujete z textového souboru, může se stát, že veškerý text bude stísněný v jedné buňce. Tento text můžete analyzovat do více buněk pomocí funkce převodu textu na sloupec v aplikaci Excel.

1. Vyberte data/text, který chcete analyzovat
2. Přejít na data -> Text do sloupce (otevře se Průvodce textem do sloupců)
   • Krok 1: Vyberte datový typ (vyberte Oddělovač, pokud vaše data nejsou rovnoměrně rozmístěna, a jsou oddělena znaky, jako je čárka, spojovník, tečka…). Klikněte na Další
     
   • Krok 2: Vyberte Oddělovač (znak, který odděluje vaše data). Můžete vybrat předdefinovaný oddělovač nebo cokoli jiného pomocí možnosti Jiné
     
   • Krok 3: Vyberte formát dat. Vyberte také cílovou buňku. Pokud není vybrána cílová buňka, aktuální buňka se přepíše
     

Příbuzný: Extrahujte uživatelské jméno z e -mailu pomocí textu do sloupce.

#8 Kontrola pravopisu

Nic nesnižuje důvěryhodnost vaší práce než pravopisná chyba.

Pomocí klávesové zkratky F7 spusťte kontrolu pravopisu pro svou sadu dat.

Zde je podrobný návod, jak používat kontrolu pravopisu v aplikaci Excel.

#9 Odstranit veškeré formátování

Ve své práci jsem použil více databází k získání dat v aplikaci Excel. Každá databáze měla své vlastní formátování dat. Když máte všechna data na svém místě, můžete najednou odstranit veškeré formátování:

1. Vyberte datovou sadu
2. Přejděte na Domů -> Vymazat -> Vymazat formáty
   

Podobně můžete také vymazat pouze komentáře, hypertextové odkazy nebo obsah.

#10 K vyhledání dat v Excelu použijte funkci Najít a nahradit

Najít a nahradit je nepostradatelné, pokud jde o čištění dat. Můžete například vybrat a odebrat všechny nuly, změnit odkazy ve vzorcích, najít a změnit formátování atd.

Přečtěte si více o tom, jak lze k čištění dat použít funkci Najít a nahradit.

Toto je mých 10 nejlepších technik čištění dat v Excelu. Pokud byste se chtěli naučit další techniky, zde je průvodce týmem MS Excel - Čištění dat v Excelu.

Pokud ještě nějaké techniky používáte, podělte se o ně s námi v sekci komentáře!