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ť.

Postupujte podľa týchto krokov:	Ďalšie informácie
Začatie každej funkcie znamienkom rovnosti (=)	Ak znamienko rovnosti vynecháte, zadané znamienko sa môže zobraziť ako text alebo ako dátum. Ak zadáte napríklad SUM(A1:A10), Excel zobrazí textový reťazec SUM(A1:A10), ale nevykoná výpočet. Ak zadáte 11/2, Excel namiesto vydelenia čísla 11 číslom 2 zobrazí dátum 2-Nov (za predpokladu, že formát bunky je Všeobecné).
Spárovanie všetkých ľavých a pravých zátvoriek	Skontrolujte, či sú všetky správne uzavreté všetky zátvorky (zodpovedajúce ľavé a pravé). Pri použití funkcie vo vzorci je dôležité, aby sa každá zátvorka nachádzala v správnej pozícii, aby funkcia fungovala správne. Napríklad vzorec =IF(B5<0);"Neplatné";B5*1,05) nebude fungovať, pretože obsahuje dve pravé zátvorky a iba jednu ľavú (mala by tam byť len jedna z každej). Správny vzorec vyzerá takto: =IF(B5<0;"Neplatné";B5*1,05).
Označenie rozsahu pomocou dvojbodky	Keď odkazujete na rozsah buniek, použite dvojbodku (:) na oddelenie odkazu na prvú bunku v rozsahu a odkaz na poslednú bunku v rozsahu. Napríklad =SUM(A1:A5), nie =SUM(A1 A5), ktorý by vrátil #NULL! Chyba.
Zadanie všetkých požadovaných argumentov	Niektoré funkcie obsahujú požadované argumenty. Skontrolujte tiež, či ste nezadali príliš veľa argumentov.
Zadanie správneho typu argumentov	Niektoré funkcie, napríklad SUM, vyžadujú číselné argumenty. Iné funkcie, ako napríklad REPLACE, vyžadujú textovú hodnotu pre aspoň jeden z ich argumentov. Ak ako argument použijete nesprávny typ údajov, Excel môže vrátiť neočakávané výsledky alebo zobraziť chybu.
Vnorenie maximálne 64 funkcií	Do funkcie môžete vložiť alebo vnoriť maximálne 64 úrovní funkcií.
Uzavretie názvov ďalších hárkov do jednoduchých úvodzoviek	Ak vzorec odkazuje na hodnoty alebo bunky v iných hárkoch alebo zošitoch a názov iného zošita alebo hárka obsahuje medzery alebo neabecedné znaky, je potrebné uviesť jeho názov v jednoduchých úvodzovkách ( ' ), napríklad ='Kvartálne údaje'!D3 alebo =‘123’!A1.
Umiestnenie výkričníka (!) za názov hárka, na ktorý sa odkazuje vo vzorci	Ak chcete, aby sa napríklad vrátila hodnota z bunky D3 v hárku s názvom Kvartálne údaje v tom istom zošite, použite tento vzorec: ='Kvartálne údaje'!D3.
Zahrnutie cesty k externým zošitom	Skontrolujte, či každý externý odkaz obsahuje názov zošita a cestu k zošitu. Odkaz na zošit obsahuje názov zošita a musí byť v hranatých zátvorkách ([Nazovzosita.xlsx]). Odkaz musí tiež obsahovať názov hárka v zošite. Vo vzorci môžete odkazovať aj na zošit, ktorý nie je otvorený v programe Excel. Potrebné je zadať úplnú cestu k súboru, ako je to v nasledujúcom príklade: =ROWS('C:\Moje dokumenty\[Prevádzková činnosť za Q2.xlsx]Predaj'!A1:A8). Tento vzorec vráti počet riadkov v rozsahu buniek A1 až A8 v zadanom druhom zošite (8). Poznámka: Ak sú v úplnej ceste znaky medzery ako v predchádzajúcom príklade, cestu je nutné vložiť do jednoduchých úvodzoviek (jednoduchá úvodzovka musí byť na začiatku cesty a za názvom hárka ešte pred výkričníkom).
Zadanie čísel bez formátovania	Neformátujte čísla, keď ich zadávate do vzorcov. Ak napríklad chcete zadať do vzorca hodnotu 1 000 EUR, zadajte iba hodnotu 1000. Ak zadáte ako súčasť čísla čiarku, Excel ju bude považovať za znak oddeľovača. Ak chcete zobraziť čísla tak, aby obsahovali oddeľovače tisícov alebo miliónov alebo menové symboly, naformátujte bunky až po zadaní čísel. Ak napríklad chcete k hodnote v bunke A3 pridať číslo 3100 a zadáte vzorec =SUM(3;100;A3),Excel sčíta čísla 3 a 100 a potom tento súčet pripočíta k hodnote z bunky A3 namiesto pripočítania čísla 3100 do bunky A3, čo by bol =SUM(3100;A3). Ak zadáte vzorec =ABS(-2;134), Excel zobrazí chybu, pretože funkcia ABS akceptuje iba jeden argument: =ABS(-2134).

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.

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:

V dialógovom okne Vyhodnotenie vzorca	Popis
=IF(AVERAGE(D2:D5)>50;SUM(E2:E5);0)	Na začiatku sa zobrazí vnorený vzorec. Funkcie AVERAGE a SUM sú vnorené v rámci funkcie IF. Rozsah buniek D2:D5 obsahuje hodnoty 55, 35, 45 a 25 a výsledkom funkcie AVERAGE(D2:D5) je teda 40.
=IF(40>50;SUM(E2:E5);0)	Rozsah buniek D2:D5 obsahuje hodnoty 55, 35, 45 a 25 a výsledkom funkcie AVERAGE(D2:D5) je teda 40.
=IF(False;SUM(E2:E5);0)	Číslo 40 nie je väčšie ako číslo 50, preto výraz v prvom argumente funkcie IF (argument logický_test) má hodnotu nepravda. Funkcia IF vráti hodnotu tretieho argumentu (argument hodnota_ak_nepravda). Funkcia SUM sa nevyhodnotí, pretože je druhým argumentom funkcie IF (argument hodnota_ak_pravda) a vracia sa iba vtedy, ak má výraz hodnotu pravda.

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ť.