Excel - proč funkce "když" nefunguje správně?
Excel 2007
Mám jednoduchou tabulku, porovnávám dvě strany rovnice
Levá strana: A+B-C, pravá strana X+Y.
Zadávám hodnoty do sloupců a porovnává se, zda rovnice souhlasí. Pokud ano, výsledkem kontroly (v dalším sloupci) má být "0", pokud ne, výsledkem kontroly je "1"
Přiložil jsem tabulku ve třech formátech, ve všech to ale v některých případech počítá špatně. Strany rovnice souhlasí, ale funkce "když" hlásí, že ne.
Čím to, proboha, může být? 
Používám to jako kontrolu ve svém účetním systému, teď jsem na to náhodou narazil (sloupec "C" bývá v 99,999% případů prázdný, tak jsem na to nenarazil)
Používám čísla s dvěma desetinnými místy.
Všechny řádky tabulky jsou bez chyby, nicméně první, třetí a poslední chybu hlásí. Tohle není normální…
Co s tím? Nějaká vnitřní chyba Excelu? Pokud dám levou stranu vzorečku zaokrouhlit (až na 14 desetinných míst), je výsledek správně, tedy nula. Pokud na 15 nebo bez zaokrouhlení, je tam chybně ta jednička. A přitom u těch chybných výsledků jsou čísla jen na dvě desetinná místa.
Zkuste to někdo v novějším Excelu, já mám jen 2007, díky.
Pikantní je, že když rovnici upravím na A+B=X+Y+C (tedy C přehodím na druhou stranu), je to OK.
Že by ve funkci když "zlobilo" mínus na jedné straně rovnice?
Není to chyba, ale známá vlastnost nejen Excelu. Počítač neumí přesně počítat s desetinnými čísly, vždycky je tam přesnost jenom na určitý počet desetinných míst.
No dobře, ale že (viz ten můj příklad) nepozná, že 20,1-20=0,1 ?? To se mi zdá už trochu moc.
30,5-30=0,5 OK
30,53-30=0,53 chyba
Nebo problémem bude to mínus?
Dočasně jsem tu kontrolu překopal, jak píšu výše, že na obou stranách rovnice je jen součet, to se zdá, že funguje bezchybně.
Zkoušel jsi to v novějším Excelu než mám já?
Excel vypočítá 20,1-20 jako 0,100000000000001. Ale opravdu to není záležitost jenom Excelu. Jakékoliv desetinné číslo se bere jenom s určitou přesností.
Excel mám nejnovější.
Aha.
Rozdíl 20,1-20, výsledek je s chybou na 15. desetinném místě. Součet stejných čísle je i 25 desetinnými místy přesně.
Zdá se, že v mém případě bude problém v "mínus" ve fci "když", tak to upravím.
Díky za nakopnutí.
Když to upravíš, tak budeš mít zase problém s jinými čísly. Jediné řešení je zaokrouhlování.
Jo, podívám se na to globálně a nasadím do těch (vlastně dvou) vzorečků zaokrouhlení na 2 desetinná místa, haléře.
Když to chceš řešit takhle, dal bych to na 3 místa (aby byla rezerva, se 2-mi by to mohlo v nějakém extrémním případě zahaprovat).
Nemusíš to zaokrouhlovat. Někde v nastavení bývalo, že aby to počítalo jen se zobrazenými čísly, ně i z těmi "za rohem". Bohužel, používám už jen LibreOffice tak ti neřeknu, kde to v nastavení najdeš, ale někde to je.
Našel jsem tohle:
Po aktivaci (a i případném uložení) se ale v těch mých tabulkách nic nezměnilo, pořád to ukazuje, že 30,53-30 se nerovná 0,53. Připadá mi to divné, protože podle nápovědy by to fungovat mělo.
Aha, funguje to trochu jinak..
Jako uloženou hodnotu buňky to bere opravdu obsah buňky a ne vzoreček v "když". Pokud si do pomocných sloupečků udělám součty levé a pravé strany rovnice a porovnávám ty, je to už OK. Porovnávají se ty pomocné buňky, kde jsou čísla zaokrouhlena na zobrazovaný počet, tedy v mém případě na haléře/2 desetinná místa.
Je potřeba si ujasnit, co je to "přesná nula" neboli (přesná) rovnost. U celých čísel je to jednoduché, ale u desetinných je to třeba upřesnit (definovat, kdy to už považuju za 0). Desetinná čísla vlastně prakticky nikdy nejsou přesná.
Účetnictví se obvykle vede v celých číslech.
No, jedná se o účetní údaje, takže maximálně halíře. Nic se ale nedělí/nenásobí, pouze sčítá a odčítá, viz ty moje příklady.
Předpokládal jsem, že 10 haléřů je 0,1. Excel to chápe jinak?
Musel bys to vést v těch halířích - pak to bude přesné. Jakmile použiješ desetinný zápis + převod do bináru, je to v háji
Koukni se na vyhodnocení vzorce a vše Ti bude jasné.
PS: Testováno na Excel 2010
Nerozumím.
Tobě (třeba ten první řádek) to počítá/vyhodnocuje správně?
Edit: Mě se to chová naprosto stejně jako Tobě viz *.gif
Díky moc!, tuhle funkci "vyhodnocení" jsem vůbec neznal..
PS: k dalším nepřesnostem dojde při převodu mezi desetinným a binárním zápisem (vždy jen konečný počet cifer).
Klasický problém: 1 - (3 x 1/3) je 0,0...01 podle toho, na kolik míst budu počítat, 0 to bude až po zaokrouhlení výsledku. Jenže do toho promluví převod do bináru a ten to ještě rozhodí. A určitě se to dá nakombinovat na mnoho způsobů, takže to vyjde pokaždé "jinak". Dělat zaokrouhlení v průběhu výpočtů může být ošidné, zase to povede k jiným "paradoxům".
Děkuji všem, už si s tím nějak poradím - když vím, kde a proč se objevuje chyba a jakými cestami jí předejít.