Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » funkce » hledani-chyb-ve-vzorcich

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

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

... kdo hledá chybu najde ...

Ú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í

...vidím něco jiného než je skutečně v buňce...

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

...téměř nula...

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: 01.11.2014 09:36

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, PowerPoint) se věnuji od roku 2006. Své vědomosti a zkušenosti dávám k dispozici na různých školeních a konzultacích, ale také na tomto webu. K dispozici na tomto webu je mnoho návodu, tipů a triků včetně desítek různých šablon.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil

Doporučte tento článek přátelům

Pokud vám článek pomohl, případně si myslíte, že může pomoci i někomu dalšímu, budu rád když jej sdílením doporučíte přátelům - děkuji:



Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.






Excel


Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články

25.03.2017:
ROUNDUP, ROUNDDOWN - zaokrouhlování

07.04.2017:
Řazení Excel - bug - problém

09.04.2017:
Podmínkové funkce Excel - bug - problém


Newsletter

Přihlaste se odběru novinek a získejte:
Ebook zdarma -
10 kroků ke zvládnutí (Word, Excel, PowerPoint)
Šipka Kniha 3D Více o ebooku ...

TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2017 | 9731

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.