Počítačové školicí středisko
Školení
Konzultace
Když Excel nechce počítat - aneb formáty v Excelu
Přibývá tabulek, které nevznikly v Excelu. Vznikly různými exporty z jiných programů, z runtime měřících aplikací, z účetních systémů, z podnikových informačních systémů jako je např. Helios, SAP, Navision apod. Tyto tabulky se na první pohled tváří jako excelové tabulky, ale občas se chovají divně. Např. nedokážou počítat s některými čísly. Sečtete sloupec a výsledkem je nula. nebo vzorec pracuje s více čísly a jedno z nich nezapočítá. Také jsou problémy s grafickým formátem tabulky a mnoho dalších.
Ach ty formáty
Tyto potíže jsou často dány tím, že formát buňky není vytvářen Excelem, ale uměle zdrojovou aplikací. Ta sice nastaví nějaký číselný formát buňky, ale skutečný formát buňky je odlišný.
Podívejme se právě na ty čísla, která se nechovají jako čísla. Na to, jak odhalit problém v konkrétní tabulce, nelze dát jednoduchou odpověď, vždy záleží na té tabulce, jak vznikla a najít metodu, jak číslo napravit, je často magie.
Tečka jako oddělovač tisíců
Častý problém bývá, že tisíce jsou u velkých čísel odděleny tečkou, čísla se pak chovají jako texty.
Pak nezbývá, než pomocí hromadného vyhledávání a nahrazování nahradit tečku „ničím“, tedy tečku odstranit. Jen pozor, že pokud jsou v listu i datumové položky, nahrazení se provede i v nich a tím je spolehlivě zničíte. V takové případě musíte nahrazování provést jen v označené oblasti nebo jednotlivě. Podobně se řeší tabulky vzniklé v ne-českých windows, které používají místo desetinné čárky desetinnou tečku.
Číslo jako text
Další záludností je, když je číslo uložené jako text.
Nemám teď na mysli číslo uvozené apostrofem, což je korektní metoda. Uvažujme spíše o číslech, která se chovají jako text, jsou zarovnaná vlevo, v seznamu formátů je nastavený číselný formát a někdy také mají známý zelený trojúhelník v levém horním rohu buňky. Pak lze rozkliknout inteligentní značku a z nabídky vybrat Převést na číslo. Totéž lze hromadně provést pro celou označenou oblast.
Text x 1 = číslo
Převod textových čísel na skutečná čísla lze také provést pomocí násobení jedničkou. Využívá se přitom principu, že text*číslo=číslo. Tento postup můžete zkusit, když inteligentní značky a zelené šipky u čísel nemáte. Jak na to? Máte sloupec, který se jeví v nepořádku, máte pocit, že obsahuje nějaká „textová“ čísla. Do volné buňky vedle si napište jedničku. Zkopírujte ji do schránky a označte zmíněný sloupec dat. Pak použijte příkaz Vložit jinak, v něm pak variantu Násobení. Označená oblast se vynásobí jedničkou ve schránce a tím se spolehlivě převede na čísla.
Odstranit mezery v číslech
Ani předchozí postup však někdy nepomůže. Např. číslo v označené buňce vypadá jako číslo, má nastaven formát čísla, ale do případného vzorce vstupuje jako text. (A přitom neobsahuje vstupní apostrof).
Na obrázku je mezi tisícem a stovkou vložena málo patrná mezera. Při podobném ručním vkládání Excel běžně mezeru odstraní a zapne oddělovač tisíců. Tato tabulka však byla generována nějakým programem, za tisícem zůstala mezera a formát je přitom číselný. Řešením tedy je buď ručně nebo hromadně mezery odstranit.Podobné potíže jsou s formátem datumových a časových položek.
Ověřujte platnost dat a formátů předem
Možná se zeptáte, jak poznat, že v určité oblasti vaší tabulky takové problémy jsou. To je právě to. To se pozná, až vám výpočty začnou špatně sčítat... A bohužel ani špatný výsledek se někdy rozpoznat, některé výpočty se ověřují velmi těžko.
Zkušený uživatel Excel si je proto na pozoru, a jestliže dostane rozsáhlejší tabulku neznámého původu, nejprve důkladně ověřuje platnost dat a jejich formátů
Chcete o této problematice vědět více? Kontaktujte nás..
2016