Zisťovanie chýb vo vzorcoch

V nasledujúcej tabuľke nájdete zhrnutie niektorých najbežnejších chýb, ktorých sa používatelia dopúšťajú pri zadávaní vzorcov, a vysvetlenie, ako ich odstrániť.

Na skontrolovanie chýb vo vzorcoch môžete zadať určité pravidlá. Tieto pravidlá nezaručujú, že daný hárok je bezchybný, ale môžu výrazne pomôcť pri hľadaní bežných chýb. Tieto pravidlá môžu byť individuálne zapnuté alebo vypnuté.

Chyby možno označiť a odstrániť dvoma spôsobmi: buď môžete skontrolovať jednu chybu po druhej (ako pri kontrole pravopisu), alebo môžete hárok kontrolovať priebežne pri zadávaní údajov.

Chybu môžete vyriešiť pomocou možností, ktoré excel zobrazí, alebo ju môžete ignorovať výberom položky Ignorovať chybu. Ak budete chybu v určitej bunke ignorovať, pri ďalších kontrolách sa chyba v danej bunke nezobrazí. Všetky ignorované chyby je však možné obnoviť tak, aby sa znova zobrazovali.

1. V Exceli vo Windowse prejdite na položky Možnosti > súbor > vzorce alebo
   pre Excel v Macu vyberte ponuku Excelu > Predvoľby > kontrola chýb. 

   V Excel 2007 vyberte tlačidlo Microsoft Office > Excel – možnosti > vzorce.

2. V časti Kontrola chýb začiarknite políčko Povoliť kontrolu chýb na pozadí. Každá nájdená chyba sa označí trojuholníkom v ľavom hornom rohu bunky.

   

3. Ak chcete zmeniť farbu trojuholníka, ktorý označuje miesto výskytu chýb, vyberte požadovanú farbu v poli Označiť chyby touto farbou.

4. V časti Pravidlá kontroly v Exceli začiarknite alebo zrušte začiarknutie políčok ľubovoľného z nasledujúcich pravidiel:

   • Bunky obsahujúce vzorce, ktorých výsledkom je chyba: Vzorec nepoužíva očakávanú syntax, argumenty ani typy údajov. Medzi chybové hodnoty patria #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, a #VALUE!. Každá z týchto chybových hodnôt má rôzne príčiny a rieši sa rôznymi spôsobmi.

     Poznámka: Ak zadáte chybovú hodnotu priamo do bunky, uloží sa ako chybová hodnota, ale neoznačí sa ako chyba. Ak však vzorec v inej bunke na túto bunku odkáže, vzorec vráti z tejto bunky chybovú hodnotu.

   • Nekonzistentný vzorec vo vypočítavaných stĺpcoch v tabuľkách: Ak vypočítavaný stĺpec zahŕňa individuálne vzorce, ktoré sú odlišné od hlavného vzorca v stĺpci, vytvorí sa výnimka. Výnimku vo vypočítavanom stĺpci môžete vytvoriť niektorým z nasledujúcich spôsobov:

     • Zadajte iný údaj ako vzorec do bunky vo vypočítavanom stĺpci.

     • Do bunky vypočítaného stĺpca zadajte vzorec a potom použite kombináciu klávesov Ctrl + Z alebo vyberte položku Späť na paneli s nástrojmi Rýchly prístup.

     • Do vypočítavaného stĺpca s jednou alebo viacerými výnimkami zadajte nový vzorec.

     • Do vypočítavaného stĺpca, ktorý sa nezhoduje so vzorcom vo vypočítavanom stĺpci, skopírujte údaje. Ak kopírované údaje obsahujú vzorec, údaje vo vypočítavanom stĺpci sa ním prepíšu.

     • Presunutie alebo odstránenie bunky v inej oblasti hárku, na ktorú odkazuje jeden z riadkov vo vypočítavanom stĺpci.

   • Bunky s rokmi vyjadrenými 2 číslicami: Bunka obsahuje text dátumu, ktorý môže pri použití vo vzorcoch spôsobiť nesprávnu interpretáciu storočia. Dátum vo vzorci =YEAR("1/1/31") by mohol byť napríklad interpretovaný ako 1931 alebo 2031. Toto pravidlo použite na kontrolu problematických textov dátumov.

   • Čísla formátované ako text alebo s apostrofom na začiatku: Bunky obsahujú čísla uložené ako text. Táto chyba sa zvyčajne vyskytuje vtedy, ak ide o údaje importované z iných zdrojov. Čísla uložené ako text môžu spôsobiť neočakávané výsledky zoradenia, preto ich najlepšie skonvertovať na čísla. ‘=SUM(A1:A10) sa zobrazuje ako text.

   • Vzorce nekonzistentné s inými vzorcami v oblasti: Vzorec sa nezhoduje so vzorom iných vzorcov v jeho blízkosti. V mnohých prípadoch sa vzorce susediace s inými vzorcami líšia iba v použitých odkazoch. V nasledujúcom príklade štyroch susediacich vzorcov Excel zobrazí chybu vedľa vzorca =SUM(A10:C10) v bunke D4, pretože susediace vzorce sa zvýšia o jeden riadok a tieto vzorce sa zvýšia o 8 riadkov – Excel očakáva vzorec =SUM(A4:C4).

     

     Ak odkazy použité vo vzorci nie sú konzistentné s odkazmi v susediacich vzorcoch, Excel zobrazí chybu.

   • Vzorce, ktoré vynechávajú bunky v oblasti: Vzorec nemusí automaticky obsahovať odkazy na údaje, ktoré boli vložené medzi pôvodný rozsah údajov a bunku obsahujúcu vzorec. Týmto pravidlom sa porovnáva odkaz vo vzorci s pôvodným rozsahom buniek, ktoré sú priľahlé k bunke obsahujúcej vzorec. Ak priľahlé bunky obsahujú ďalšie hodnoty a nie sú prázdne, Excel zobrazí vedľa vzorca chybu.

     Excel napríklad pri použití tohto pravidla vloží chybu vedľa vzorca =SUM(D2:D4), pretože bunky D5, D6 a D7 susedia s bunkami odkazovanými vo vzorci a bunkou obsahujúcou vzorec (D8) a tieto bunky obsahujú údaje, na ktoré by mal vzorec odkazovať.

     

   • Neuzamknuté bunky obsahujúce vzorce: Vzorec nie je zabezpečený uzamknutím. Všetky bunky hárka sú predvolene zamknuté, aby ich nebolo možné meniť, keď je hárok zabezpečený. Týmto sa možno vyhnúť neúmyselných chybám, napríklad neúmyselným odstránením alebo zmenením vzorcov. Táto chyba signalizuje, že bunka bola nastavená tak, aby nebola zamknutá, no hárok nebol zabezpečený. Skontrolujte, či nechcete, aby bola bunka zamknutá.

   • Vzorce, ktoré odkazujú na prázdne bunky: Vzorec obsahuje odkaz na prázdnu bunku. Môže to spôsobiť nežiaduce výsledky znázornené v nasledujúcom príklade.

     Predpokladajme, že chcete vypočítať priemer čísiel v nasledujúcom stĺpci buniek. Ak je tretia bunka prázdna, nezahrnie sa do výpočtu a výsledok je 22,75. Ak tretia bunka obsahuje nulu, výsledkom je hodnota 18,2.

     

   • Údaje zadané do tabuľky sú neplatné: V tabuľke sa vyskytla chyba overenia. Skontrolujte nastavenie overenia pre bunku prechodom na kartu Údaje > skupine Nástroje pre údaje > Overenie údajov.

1. Vyberte hárok, ktorý chcete skontrolovať.

2. Ak je hárok vypočítaný manuálne, prepočítate ho stlačením klávesu F9.

   Ak sa dialógové okno Kontrola chýb nezobrazuje, vyberte položku Vzorce > kontrola vzorca > kontrolu chýb.

3. Ak ste v minulosti ignorovali všetky chyby, môžete tieto chyby znova skontrolovať takto: prejdite na položku Možnosti > súbor > vzorce. V Exceli v Macu vyberte ponuku Excelu > Predvoľby > kontrola chýb.

   V časti Kontrola chýb vyberte položku Obnoviť ignorované chyby > OK.

   

   Poznámka: Obnovením ignorovaných chýb sa obnovia všetky chyby vo všetkých hárkoch aktívneho zošita.

   Tip: Možno pre vás bude nápomocné, ak dialógové okno Kontrola chýb premiestnite priamo pod riadok vzorcov.

   

4. Vyberte jedno z tlačidiel akcií na pravej strane dialógového okna. Pre každý typ chyby sú k dispozícii odlišné akcie.

5. Vyberte tlačidlo Ďalej.

1. Vedľa bunky vyberte kontrola chýb a potom vyberte požadovanú možnosť. Dostupné príkazy sa líšia pre každý typ chyby a prvá položka popisuje chybu.

   Ak vyberiete možnosť Ignorovať chybu, chyba sa označí ako ignorovaná pri každej po sebe idúcej kontrole.

   

Ak vzorec nedokáže správne vyhodnotiť výsledok, Excel zobrazí chybovú hodnotu, napríklad #####, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, a #VALUE!. Každý typ chyby má rôzne príčiny a rôzne riešenia.

Nasledujúca tabuľka obsahuje odkaz na články, ktoré tieto chyby podrobne opisujú, ako aj stručný opis, ktorý vám pomôže začať s ich riešením.

Téma	Popis
Odstránenie chyby ####	Excel zobrazí túto chybu vtedy, keď stĺpec nie je dostatočne široký na zobrazenie všetkých znakov v bunke, alebo ak bunka obsahuje záporné hodnoty dátumu alebo času. Napríklad vzorec, ktorý odpočíta dátum v budúcnosti od dátumu v minulosti (napríklad = 06/15/2008-07/01/2008, vedie k zápornej hodnote dátumu. Tip: Dvojitým kliknutím medzi hlavičky stĺpcov skúste automaticky prispôsobiť bunku. Ak sa ### zobrazí, pretože Excel nedokáže zobraziť všetky znaky, opraví to.
Odstránenie chyby #DIV/0! (chyba)	Excel zobrazí túto chybu vtedy, keď sa číslo delí nulou (0) alebo bunkou, ktorá neobsahuje žiadnu hodnotu. Tip: Pridajte obslužný program na hromadné odstraňovanie chýb ako v tomto príklade: =IF(C2;B2/C2;0).
Odstránenie chyby #NEDOSTUPNÝ	Excel zobrazí túto chybu vtedy, keď pre funkciu alebo vzorec nie je k dispozícii hodnota. Ak používate funkciu ako VLOOKUP, zodpovedá to, čo sa pokúšate vyhľadať, tomu, čo sa nachádza v rozsahu vyhľadávania? Väčšinou nie. Skúste použitím funkcie IFERROR eliminovať výskyt chyby #NEDOSTUPNÝ. V tomto prípade by ste mohli použiť: =IFERROR(VLOOKUP(D2;$D$6:$E$8;2;TRUE);0)
Odstránenie chyby #NÁZOV? (chyba)	Táto chyba sa zobrazí, keď Excel nerozpozná text vo vzorci. Názov rozsahu alebo názov funkcie môže byť napríklad nesprávne napísaný. Poznámka: Ak používate funkciu, uistite sa, že jej názov je napísaný správne. V tomto prípade je názov funkcie SUM napísaný nesprávne. Odstráňte e a Excel ho opraví.
Vyriešenie chyby #NEPLATNÝ! (chyba)	Excel zobrazí túto chybu vtedy, keď určíte prienik dvoch oblastí, ktoré sa neprekrývajú (nekrižujú). Operátor prieniku je znak medzery, ktorý oddeľuje odkazy vo vzorci. Poznámka: Skontrolujte, či sú rozsahy správne oddelené – oblasti C2:C3 a E4:E6 sa neprekrývajú, takže zadaním vzorca =SUM(C2:C3 E4:E6) sa vráti #NULL! Ak je zadané umiestnenie pred prvou alebo za poslednou položkou v poli, výsledkom vzorca bude chybová hodnota #ODKAZ!. Ak medzi rozsahy C a E vložíte čiarku, opraví sa =SUM(C2:C3;E4:E6)
Odstránenie chyby #ČÍSLO! (chyba)	Excel zobrazí túto chybu vtedy, keď vzorec alebo funkcia obsahuje neplatné číselné hodnoty. Nepoužívate funkciu, ktorá vykonáva iteráciu, napríklad IRR alebo RATE? Ak áno, chyba #ČÍSLO! je pravdepodobne spôsobená tým, že funkcia nedokáže vyhľadať výsledok. Informácie nájdete v príslušnej téme pomocníka uvádzajúcej kroky na riešenie konkrétnej chyby.
Odstránenie chyby #ODKAZ! (chyba)	Excel zobrazí túto chybu vtedy, keď odkaz na bunku nie je platný. Mohli ste napríklad odstrániť bunky, na ktoré odkazovali iné vzorce, alebo ste mohli prilepiť bunky, ktoré ste premiestnili nad bunky, na ktoré odkazovali iné vzorce. Neodstránili ste omylom riadok alebo stĺpec? Vo vzorci =SUM(A2;B2;C2) sme odstránili stĺpec B a pozrite, čo sa stalo. Použite príkaz Späť (Ctrl + Z) a zrušte odstránenie, znova vytvorte vzorec alebo použite odkaz na súvislý rozsah, napríklad =SUM(A2:C2), ktorý by sa po odstránení stĺpca B automaticky aktualizoval.
Odstránenie chyby #HODNOTA! (chyba)	Excel môže zobraziť túto chybu vtedy, ak vzorec zahŕňa bunky, ktoré obsahujú odlišné typy údajov. Nepoužívate matematické operátory (+, -, *, /, ^) s rozličnými typmi údajov? Ak áno, skúste radšej použiť funkciu. V tomto prípade by problém vyriešil vzorec =SUM(F2:F5).

Ak sa bunky v hárku nezobrazujú, môžete ich bunky a ich vzorce sledovať na paneli s nástrojmi Okno sledovania. Pomocou okna sledovania možno pohodlne sledovať, kontrolovať alebo potvrdiť výpočty a výsledky vzorcov vo veľkých hárkoch. Pri použití okna sledovania nie je potrebné neustále posúvanie alebo prechádzanie do rôznych častí hárka.

Tento panel s nástrojmi možno premiestniť alebo ukotviť ako akýkoľvek iný panel s nástrojmi. Môžete ho napríklad ukotviť v dolnej časti okna. Panel s nástrojmi sleduje nasledujúce vlastnosti bunky: 1) Zošit, 2) Hárok, 3) Názov (ak bunka obsahuje zodpovedajúci pomenovaný rozsah), 4) Adresa bunky, 5) Hodnota a 6) Vzorec.

Pridanie buniek do okna sledovania

1. Vyberte bunky, ktoré chcete sledovať.

   Ak chcete vybrať všetky bunky v hárku so vzorcami, prejdite na domovskú stránku > úpravy > vyberte položku Hľadať & Vyberte (alebo môžete použiť kombináciu klávesov Ctrl + G alebo Control + G v Macu)> Prejsť na špeciálne > vzorce.

   

2. Prejdite na položky Vzorce > Kontrola vzorcov > vyberte položku Okno sledovania.

3. Vyberte položku Pridať sledovanie.

   

4. Potvrďte, že ste vybrali všetky bunky, ktoré chcete sledovať, a vyberte položku Pridať.

   

5. Ak chcete zmeniť šírku stĺpca Okno sledovania, posúvajte hranicu na pravej strane záhlavia stĺpca.

6. Bunku, na ktorú sa vzťahuje položka na paneli s nástrojmi Okno sledovania, zobrazíte dvojitým kliknutím na danú položku.

   Poznámka: Bunky, ktoré obsahujú externé odkazy na iné zošity, sa na paneli s nástrojmi Okno sledovania zobrazia len vtedy, ak sú otvorené aj iné zošity.

Odstránenie buniek z okna sledovania

1. Ak sa panel s nástrojmi Okno sledovania nezobrazuje, prejdite na položku Vzorce > kontrola vzorca > vyberte položku Okno sledovania.

2. Vyberte bunky, ktoré chcete odstrániť.

   Ak chcete vybrať viacero buniek, stlačte kláves CTRL a potom vyberte bunky.

3. Vyberte položku Odstrániť sledovanie.

   

Niekedy je ťažké porozumieť, ako vnorený vzorec vypočítal konečný výsledok, pretože mu predchádza viac medzivýpočtov a logických testov. Prostredníctvom dialógového okna Vyhodnotenie vzorca však môžete zobraziť jednotlivé časti vnoreného vzorca vyhodnotené v poradí, v akom sa vzorec vypočíta. Vzorec =IF(AVERAGE(D2:D5)>50;SUM(E2:E5);0) je zrozumiteľnejší, keď sa zobrazia nasledujúce priebežné výsledky:

1. Vyberte bunku, ktorú chcete vyhodnotiť. Naraz môžete vyhodnocovať len jednu bunku.

2. Prejdite na vzorce > auditovanie vzorcov > vyhodnotenie vzorca.

3. Vyberte položku Vyhodnotiť a preskúmajte hodnotu podčiarknutého odkazu. Výsledok hodnotenia sa zobrazí kurzívou.

   Ak je podčiarknutá časť vzorca odkazom na iný vzorec, vyberte položku Krok do, čím zobrazíte druhý vzorec v poli Vyhodnotenie . Výberom položky Vystúpiť prejdete späť na predchádzajúcu bunku a vzorec.

   Tlačidlo Vstúpiť nie je k dispozícii pri druhom zobrazení odkazu vo vzorci, alebo ak vzorec odkazuje na bunku v inom zošite.

4. Pokračujte vo výbere položky Vyhodnotiť , kým sa nevyhodnotí každá časť vzorca.

5. Ak chcete hodnotenie znova zobraziť, vyberte položku Reštartovať.

6. Ak chcete hodnotenie ukončiť, vyberte položku Zavrieť.