Při analýze obchodních dat je často nutné pracovat s datovými hodnotami v Excelu. Potřebujeme například zjistit „kolik jsme dnes vydělali“ nebo „jak se daří ve srovnání se stejným dnem minulého týdne“. To však může být obtížné, pokud Excel nerozpozná určité údaje jako data.
Bohužel, je to běžný problém, zvláště pokud data zadává více uživatelů, jsou kopírována z jiných systémů nebo importována z databází.
V tomto článku se podíváme na čtyři různé situace a způsoby, jak převést textové hodnoty na data.
Data s tečkami jako oddělovači
Jednou z nejčastějších chyb při zadávání dat do Excelu je použití tečky jako oddělovače dne, měsíce a roku.
Excel takový formát nerozpozná jako datum a uloží ho jako text. Tento problém lze však vyřešit pomocí nástroje „Najít a nahradit“. Nahrazením teček lomítky (/) Excel automaticky rozpozná hodnoty jako data.
Postupujte následovně:
- Vyberte sloupce, ve kterých chcete provést hledání a nahrazení.
- Klikněte na „Domů“ > „Najít a vybrat“ > „Nahradit“, nebo stiskněte Ctrl+H.
V okně „Najít a nahradit“ zadejte tečku (.) do pole „Najít“ a lomítko (/) do pole „Nahradit čím“. Poté klikněte na „Nahradit vše“.
Všechny tečky se převedou na lomítka a Excel rozpozná nový formát jako datum.
Pokud se vaše data v tabulce pravidelně mění a potřebujete automatické řešení, můžete použít funkci SUBSTITUTE.
Příklad vzorce:
=VALUE(SUBSTITUTE(A2,“.“,“/“))
Funkce SUBSTITUTE je textová funkce, takže sama o sobě nepřevádí na datum. Funkce VALUE pak převede textovou hodnotu na číselnou hodnotu.
Výsledky je nutné naformátovat jako datum.
To provedete v seznamu „Formát čísel“ na kartě „Domů“.
Stejnou techniku můžete použít pro nahrazení jakéhokoli jiného oddělovače.
Převod formátu RRRRMMDD
Pokud máte data v tomto formátu, budete potřebovat jiný přístup.
Tento formát je v technologii standardní, protože eliminuje nejasnosti při ukládání dat v různých zemích. Excel jej však napoprvé nerozpozná.
Pro rychlé manuální řešení můžete použít funkci „Text do sloupců“.
Vyberte rozsah hodnot, které chcete převést, a poté klikněte na „Data“ > „Text do sloupců“.
Spustí se průvodce „Text do sloupců“. V prvních dvou krocích klikněte na „Další“. Ve třetím kroku vyberte „Datum“ a poté vyberte formát data, který se v buňkách používá. V tomto příkladě se jedná o formát YMD.
Alternativně můžete použít funkci „DATUM“ spolu s textovými funkcemi „ZLEVA“, „ČÁST“ a „ZPRAVA“ k extrahování dne, měsíce a roku z obsahu buňky.
Vzorec níže ukazuje tento postup:
=DATE(ZLEVA(A2;4);ČÁST(A2;5;2);ZPRAVA(A2;2))
Pomocí těchto technik můžete převést jakoukoli osmicifernou číselnou hodnotu, například datum ve formátu DDMMRRRR nebo MMDDRRRR.
Funkce DATEVALUE a VALUE
Někdy problém není v oddělovači, ale v nevhodné struktuře data, která je jednoduše uložená jako text.
Následuje seznam dat v různých strukturách, která jsou rozpoznatelná jako data, ale uložená jako text, takže je potřeba je převést.
Pro tyto případy je snadné převést data pomocí funkcí DATEVALUE a VALUE.
Funkce DATEVALUE převede text na hodnotu data, zatímco funkce VALUE převede text na obecnou číselnou hodnotu. Rozdíly mezi nimi jsou minimální.
Na obrázku výše obsahuje jedna z hodnot i časovou informaci, což ukáže rozdíly mezi funkcemi.
Následující vzorec s funkcí DATEVALUE převede každou hodnotu na datum:
=DATEVALUE(A2)
Všimněte si, že z výsledku na řádku 4 byl odstraněn čas. Tento vzorec vrátí pouze hodnotu data, kterou je ještě třeba naformátovat.
Následující vzorec používá funkci VALUE:
=VALUE(A2)
Tento vzorec poskytne stejné výsledky, s výjimkou řádku 4, kde je zachována i hodnota času.
Výsledky pak můžete naformátovat jako datum a čas nebo jen jako datum, čímž skryjete časovou hodnotu (která se však neodstraní).