Hledání chyb ve vzorcích - MS Excel

Úvodem k chybám ve vzorcích

Při tvorbě vzorců v MS Excelu dojdeme do stadia. Kdy některý vzorec nefunguje nebo jeho výsledek neodpovídá předpokladům. V tomto článku se pokusím popsat jak tyto chyby nalézt pomocí nástrojů MS Excelu a následně opravit tak ať fungují jak mají.

Upozornění: Použivání Excelu svádí k tomu, že uživatel začne plně důvěřovat provedeným výpočtům a neuvědomí si zjevnou chybu. Například při měsíční úložce 1.000 Kč po dobu 10 let (úrok 2,5%) nemůže mít na účtu 3.000.000 Kč atd.

U jednoduchým výpočtu není problém chybu odhalit. Pokud ale začnete vzorce rozšiřovat a případně využívat VBA, pravděpodobnost vzniku chyby vzrůstá.

Nejčastější problémy a chyby

V MS Excelu se můžeme setkat s následujicími problémy ve vzorcích:

• Syntaktická - zapomenutá závorka (Excel někdy může sám doplnit závorku, sice na to upozorní, ale uživatel může zprávu přehlednout)
• Počet zobrazených míst - rovnice kterou excel automaticky vygeneruje u grafu má omezen počet míst u jednotlivých členů rovnice. Pokud se použijou tyto zkracené členy (a ne plný rozsah 30 desetiných míst)) pro další výpočty dochází k chybnému výpočtu.
• Sémantické - chybně napsaný vzorec. V případě používání názvu oblastí může dojít k záměně názvu funkce s názvem oblasti.
• Cyklické odkazy - kdy se funkce odakzuje sama na sebe A1=A1+1
• Chybný odkaz - odkazujete se na nevhodnou buňku, u některých funkcí (např. SUMA), se težko dohledává, jelikož funkce neoznačí chybu jen sečte požadovanou chybnou oblast. Prázdná buňka nemusí být prázdná může obsahovat například mezeru.
• Logické chyby - nejsložitěji odhalitelné. Použijeme chybný úsudek.
• Nespraně zaznamenán maticový vzorec - člověk opomene u matic Ctrl+Shift+Enter
• absolutní a relativní odkazy - problém vzniká při kopírování, následující buňka se bude odkazovat na jinou buňku než očekáváme.
• přednost operátoru - opomenutí že násobení má přednost před sčítaním, chybně uzavřená závorka
• nastavený (zobrazený) formát - máme nastavený formát datum, ale v buňce je číslo 234, nebo formát zaokrouhluje.
• přesnost excelu - Excel počíta a ukládá čísla jako 8 bajtová. Tj. někdy 1 není 1 ale 0,99999999999999999999. Podrobnější popis níže.
• chybná propojení - pokud se odkazujeme na list, který pak smažeme. Nebo využíváme pojmenovaných oblastí a část listu zkopírujeme do jiného projektu. Ve vzorcích vidíme #REF!, (tj. chybný odkaz)
• náhodné přepsání - místo vzorce již je v buňce číselná hodnota.
• chyba v časových údajích - hlavně u finančních funkcí místo zadání úrokové sazby roční zadáme měsíční. Místo měsíčních splátek roční, atd.

Poznámka:

Někdy Excel nestihne vypočíst celý list. Setkal jsem se u složitějších operatí, kdy při automatickém vložení dat musím dát přepočíst list. Jinak osy v grafu se nepřepočítají.

Seznam chyb

Když není vzorec napsán správně můžeme obdržet některou z následujících chyb:

• #DIV/O!
• #N/A
• #NÁZEV?
• #NULL!
• #NUM!
• #REF!
• #HODNOTA!

Popis chyb jsem sepsal v článku: Chyby ve vzorcích - teorie

Podrobný popis jednolivých chyb a jejich řešení

Syntaktická

Chybná závorka. Při psaní vzorce umí Excel automaticky doplnit zavorku např pro =SUMA(A1:A5 doplní na =SUMA(A1:A5), při složitějších výpočtech může dojít k tomu, že závorku dá na jiné místo než chceme. Proto v případě chybných výpočtu doporučuji kontrolovat závorky.

Takže pozor syntaktický může být navržený vzorec správně, ale je v něm logická chyba.

Příklad

*****

Přednost operátoru

Excel má následující přednosti automaticky začleněny:

• 1. - negace
• 2. % procento
• 3. ^ mocnina
• 4. * / násobení a dělení
• 5. + - sčítaní a odečítaní
• 6. & spojování textů
• 7. < > = porovnávání

Rešení: Pokud si nejsme jisti raději použijte o pár závorek navíc. Budete mít jistuto že zvolená operace bude mít 100% přednost.

Příklad

=A1+A2*A3 =A1+(A2*A3)

Oba vzorečky jsou správné. ten druhý je jasnější.

Příklad 2

-2^2=4 0-2^2=-4

Nejprve mocnina a pak odečítání

Formátování

Například datum a čas je reprezentovaný číslem. Pokud se změní buňce formátování je místo čísla zobrazen datum. Pokud si zobrazím jen rok tak při přičtení k dané buňce mohu dostat jiné číslo.

=A1 (datum kdy je zobrazen jen rok 2001) =A1+1 nedostanu hodnotu 2002 ale stále 2001

To stejné zaokrouhlování 0,3+0,3+0,3 = 1 je logicky divné, ale v buňce je vidět 0,3. Ve skutčnosti je v ní hodnota 1/3 tj. 0,333333 periodicky.

Přesnost excelu

Excel ukládá mezivýpočty do 8 bajtové proměné. Takže pokud se číslo přesně nevejde, tak jej aproximuje a ve výsledku je to poznat. Sice až na druhý pohled, ale jde to.

=(1,222222-1,122222)-(1,2222222-1,1222222)=2,22045E-16 tj. téměř nula

Nástoje pro hledání chyb

Jaké chyb jsou v Excelu jsem probral. Na hledání chyb a jejich následné řešení existují v MS Excel nástroje. Jak na ně proberu v dalším článku.

Článek byl aktualizován: 19.09.2020 10:57

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od dubna roku 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím 8x za sebou). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 20 let (o Excel píší přes 25). Zdarma je zde přes 1.500 návodu, tipů a triků, včetně přes 350 různých šablon, sešitů a přes 70 taháků v pdf.