Převod částky na číslo vzorcem? Proč? Případná měna je přeci realizována formátem buňky a v nich je pak reálné číslo. Navíc čísla a vzorce na listu si Excel převádí sám do jazykové mutace toho či onoho Excelu. Ne vždy. Částky do Excelu mohou být nakopírovány ze schránky, z databáze a jiných zdrojů, a není neobvyklé, aby se v tabulce objevily jako text s uvedením měny, a co víc, v české i anglické podobě. Rázem tak máme ve sloupci namísto čísel textový binec. Toto je i případ klientky, která v různých mezinárodních projektech zpracovává data z účetních programů v Excelu na MacOs. Pracuje přitom se vzdálenou plochou a řeší tedy přístupy, nejrůznější výstupy, jazyky, operační systémy. Ruční vyčištění zdrojových dat a převod na skutečné částky má své otravnosti, tak jsem si položil otázku, je-li možné úlohu řešit vzorcem (i když jsem tušil, že to bude supervzorec).
Pozn. Nemůžete se ani spoléhat na automatický překlad funkcí na listu, resp. jejich parametrů. Funkce POLÍČKO bohužel nabízí i lokalizované parametry, stejně tak ve funkci HODNOTA.NA.TEXT se může v rámci textového vyjádření formátu objevit lokalizovaný zápis (viz třeba český zástupný znak pro rok v datumu, kdy my používáme „r“, zatímco v angličtině je jím „y“). Ve vlastním formátu buňky je to jedno, v uvozovkách funkce HODNOTA.NA.TEXT už nikoliv…
Normalizace částky
Ať už se jedná o jakoukoliv verzi Excelu, předfinální převod by měl začínat ze stejného základu – binec zbavený o mezery a fyzicky se objevující texty měn v buňkách. Měn je samozřejmě nekonečné množství a já se omezil pouze na tři (Kč, $, €). Neuvažoval jsem přitom kurzovní formáty (CZK, USD, EUR). Jak na to vzorcem? Inu nejčastější způsob řešení je vnořováním funkce DOSADIT.
B1: =DOSADIT(DOSADIT(DOSADIT(DOSADIT(A1;“ „;““);“$“;““);“€“;““);“Kč“;““)
Tato část se objeví žel bohu ve finálním supervzorci několikrát. Jedná se přitom o textový řetězec.
Úvaha pokračuje tím, jaký formát vyžaduje aktuální Excel uživatele (oddělovač tisíců a desetinný oddělovač). Ty si Excel bere standardně ze systému, ale je možné je upravit v dialogu Možnosti. Je nějaká funkce, který by oddělovače identifikovala? Není. Microsoft sice přichází v nových verzích s funkcemi jako NUMBERVALUE, ale ve výsledku k ničemu. Nepomůže nám ani funkce O.PROSTŘEDÍ. Ano, ta je potomkem jedné z Excel4 funkcí, ve kterých bychom se dobrali informací o oddělovačích. Naneštěstí jsou na listu zakázány a přístup k nim je možný pouze přes definované názvy a sešit s nimi se bude chovat stejně, jako sešit s makry, tedy podléhat zabezpečení (to už si rovnou můžeme zjišťovat oddělovače makrem, případně UDF).
Do jisté míry nám pomůže funkce HODNOTA. Pokud její argument není forma čísla platná pro daný Excel, vrátí chybu. Problém je, že jednou budeme chtít nahrazovat čárku za tečku (CZ do EN, DE do EN, případné mezery jsme se již zbavili dříve), jednou se zbavovat čárky a tečku nahrazovat za čárku (EN do CZ, EN do DE). Vznikají tak kombinace a pravděpodobně jediným identifikátorem, o který převod půjde, je pokus o nalezení tečky v řetězci.
Supervzorec pro převod částky na číslo
Níže uvedený vzorec byl sestavován postupně a je určen k přímému použití. Jeho luštění za pár dní už nebudu přát ani sám sobě :-)
C1: =HODNOTA(KDYŽ(A(JE.CHYBA(HODNOTA(DOSADIT(DOSADIT(DOSADIT(DOSADIT(A1;“ „;““);“$“;““);“€“;““);“Kč“;““)));NE(JE.CHYBA(HLEDAT(„.“;DOSADIT(DOSADIT(DOSADIT(DOSADIT(A1;“ „;““);“$“;““);“€“;““);“Kč“;““)))));DOSADIT(DOSADIT(DOSADIT(DOSADIT(DOSADIT(DOSADIT(A1;“ „;““);“$“;““);“€“;““);“Kč“;““);“,“;““);“.“;“,“);KDYŽ(A(JE.CHYBA(HODNOTA(DOSADIT(DOSADIT(DOSADIT(DOSADIT(A1;“ „;““);“$“;““);“€“;““);“Kč“;““)));NE(JE.CHYBA(HLEDAT(„,“;DOSADIT(DOSADIT(DOSADIT(DOSADIT(A1;“ „;““);“$“;““);“€“;““);“Kč“;““)))));DOSADIT(DOSADIT(DOSADIT(DOSADIT(DOSADIT(A1;“ „;““);“$“;““);“€“;““);“Kč“;““);“,“;“.“);DOSADIT(DOSADIT(DOSADIT(DOSADIT(A1;“ „;““);“$“;““);“€“;““);“Kč“;““))))
Příloha
prevod_castky_na_cislo.zip