Zabránenie vzniku nefunkčných vzorcov

Ak Excel nedokáže vami vytváraný vzorec spracovať, zobrazí sa chybové hlásenie podobné tomuto:

Obrázok dialógového okna v Exceli S týmto vzorcom sa vyskytol problém

Bohužiaľ to znamená, že Excel nedokáže pochopiť, čo sa snažíte urobiť, takže možno budete chcieť začať odznova.

Začnite výberom tlačidla OK alebo stlačením klávesu ESC zavrite chybové hlásenie.

Excel vás vráti na bunku s nefunkčným vzorcom, ktorý bude v režime úprav, a zvýrazní problematické miesto. Ak stále neviete, čo robiť odtiaľ a chcete začať odznova, môžete znova stlačiť kláves ESC alebo vybrať tlačidlo Zrušiť v riadku vzorcov, čím prejdete z režimu úprav.

Obrázok tlačidla Zrušiť v riadku vzorcov

Ak sa chcete posunúť dopredu, nasledujúci kontrolný zoznam obsahuje kroky na riešenie problémov, ktoré vám pomôžu zistiť, čo sa mohlo pokaziť.

Poznámka: Ak používateOffice pre web, nemusia sa zobraziť rovnaké chyby alebo riešenia nemusia platiť.

Získanie dynamických a bezplatných odpovedí v Exceli

Excel vyhodí rôzne chyby v librách (#), napríklad #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME?, a #NULL!, aby uviedol, že niečo vo vzorci nefunguje správne. Napríklad #VALUE! je spôsobená nesprávnym formátovaním alebo nepodporovanými typmi údajov v argumentoch. Alebo sa zobrazí #REF! ak vzorec odkazuje na bunky, ktoré boli odstránené alebo nahradené inými údajmi. Pokyny na riešenie problémov sa pri každej chybe budú líšiť.

Poznámka: #### nie je chyba, ktorá by súvisela so vzorcami. Znamená to iba, že stĺpec nie je dostatočne široký na zobrazenie obsahu bunky. Jednoducho roztiahnite stĺpec myšou, alebo kliknite na položky Domov > Formát > Prispôsobiť šírku stĺpca.

Obrázok položiek Domov > Formát > Prispôsobiť šírku stĺpca

Pozrite si niektorú z nasledujúcich tém, ktoré zodpovedajú chybe libry, ktorú vidíte:

Pri každom otvorení tabuľkového hárka, ktorý obsahuje vzorce odkazujúce na hodnoty v iných tabuľkových hárkoch, sa zobrazí výzva na aktualizáciu odkazov alebo ich ponechanie tak, ako je.

Dialógové okno nefunkčných odkazov v Exceli

Excel zobrazí vyššie uvedené dialógové okno, aby sa zabezpečilo, že vzorce v aktuálnom tabuľkovom hárku vždy odkazujú na najaktuálnejšiu hodnotu v prípade zmeny referenčnej hodnoty. Odkazy môžete aktualizovať, no ak to nechcete, môžete aktualizáciu vynechať. Dokonca aj po výbere možnosti neaktualizovať odkazy môžete prepojenia v tabuľkovom hárku manuálne aktualizovať vždy, keď chcete.

Zobrazovanie dialógového okna pri spustení môžete zakázať. Ak to chcete urobiť, prejdite do časti Možnosti > súbor > Rozšírené > Všeobecné a zrušte začiarknutie políčka Požiadať o aktualizáciu automatických prepojení.

Obrázok možnosti Potvrdiť aktualizáciu automatického prepojenia

Dôležité: Ak vo vzorcoch pracujete s nefunkčnými prepojeniami prvýkrát, ak potrebujete obnoviť riešenie nefunkčných prepojení alebo ak neviete, či sa majú odkazy aktualizovať, pozrite si tému Ovládanie aktualizácie externých odkazov (prepojení).

Ak vzorec nezobrazuje hodnotu, postupujte podľa týchto krokov:

  • Skontrolujte, či je Excel nastavený na zobrazenie vzorcov v tabuľkovom hárku. Ak to chcete urobiť, vyberte kartu Vzorce a v skupine Kontrola vzorca vyberte položku Zobraziť vzorce.

    Tip: Môžete použiť aj klávesovú skratku Ctrl + ' (kláves nad klávesom Tab). Keď to urobíte, stĺpce sa automaticky rozšíria, aby sa zobrazili vzorce, ale nemusíte sa obávať, keď prepnete späť do normálneho zobrazenia, veľkosť stĺpcov sa zmení.

  • Ak sa problém nevyrieši vyššie uvedeným krokom, je možné, že bunka je naformátovaná ako text. Kliknite pravým tlačidlom myši na bunku a vyberte položky Formátovať bunky > Všeobecné (alebo kombináciu klávesov Ctrl+1) a potom stlačte klávesy F2 > Enter, čím zmeníte formát.

  • Ak máte stĺpec s veľkým rozsahom buniek, ktoré sú formátované ako text, môžete vybrať rozsah, použiť formát čísla podľa vlastného výberu a prejsť na položku Údaje > Text na stĺpec > Dokončiť. Takto sa použije požadovaný formát použije na všetky vybrané bunky.

    Obrázok dialógového okna Údaje > Text na stĺpce

Ak vzorec nevypočítava, budete musieť skontrolovať, či je automatický výpočet v Exceli povolený. Vzorce sa nevypočítajú, ak je zapnutý manuálny výpočet. Ak chcete skontrolovať automatické výpočty, postupujte podľa týchto krokov.

  1. Vyberte kartu Súbor , vyberte položku Možnosti a potom vyberte kategóriu Vzorce .

  2. V časti Možnosti výpočtu skontrolujte pod položkou Výpočet zošita, či ste vybrali možnosť Automatické.

    Obrázok možností Automatický a Manuálny výpočet.

Ďalšie informácie o výpočtoch nájdete v téme Zmena prepočítania vzorca, iterácie alebo presnosti.

Zacyklený odkaz sa vyskytne, keď vzorec odkazuje na bunku, v ktorú sa nachádza. Oprava spočíva v premiestnení vzorca do inej bunky alebo zmene syntaxe vzorca na bunku, ktorá sa vyhne zacykleným odkazom. V niektorých prípadoch však možno budete potrebovať zacyklené odkazy, pretože spôsobujú iterovanie funkcií – opakujú sa, kým nie je splnená konkrétna číselná podmienka. V takýchto prípadoch je potrebné zapnúť funkciu Odstrániť alebo povoliť zacyklený odkaz.

Ďalšie informácie o zacyklených odkazoch nájdete v téme Odstránenie alebo povolenie zacyklených odkazov.

Ak sa zadávaný reťazec nezačína znakom rovnosti, nie je to vzorec a nevypočíta sa – toto je bežná chyba.

Keď zadáte niečo ako SUM(A1:A10), Excel namiesto výsledku vzorca zobrazí textový reťazec SUM(A1:A10 ). Prípadne, ak zadáte 11/2, Excel namiesto delenia čísla 11 číslom 2 zobrazí dátum, napríklad 2. nov alebo 11.02.2009.

Ak chcete zabrániť týmto neočakávaným výsledkom, vždy začnite funkciu znakom rovnosti. Zadajte napríklad: =SUM(A1:A10) a =11/2.

Pri používaní funkcie vo vzorci je dôležité, aby boli všetky zátvorky na správnom mieste, inak nebude funkcia fungovať. Skontrolujte, či sú všetky zátvorky súčasťou zhodujúceho sa páru. Napríklad vzorec =IF(B5<0);"Neplatné";B5*1,05) nebude fungovať, pretože existujú dve pravé zátvorky, ale iba jedna ľavá zátvorka. Správny vzorec vyzerá takto: =IF(B5<0;"Neplatné";B5*1,05).

Funkcie Excelu obsahujú argumenty, čiže hodnoty, ktoré sa musia poskytnúť, aby funkcia fungovala. Len niekoľko funkcií (napríklad PI alebo TODAY) neobsahuje žiadne argumenty. Skontrolujte syntax vzorca, ktorá sa zobrazí po začatí písania funkcie, aby ste mali istotu, že funkcia obsahuje požadované argumenty.

Funkcia UPPER napríklad prijme ako svoj argument iba jeden textový reťazec alebo odkaz na bunku: =UPPER("ahoj") alebo =UPPER(C2)

Poznámka: Argumenty funkcie sa zobrazia na plávajúcom paneli s nástrojmi odkazu na funkciu pod vzorcom počas písania.

Snímka obrazovky s panelom s nástrojmi Odkaz na funkciu

Niektoré funkcie, ako napríklad SUM, vyžadujú iba číselné argumenty, zatiaľ čo iné funkcie, ako napríklad REPLACE, vyžadujú textovú hodnotu minimálne pre jeden argument. Ak použijete nesprávny typ údajov, funkcie môžu vrátiť neočakávané výsledky alebo zobraziť chybu #VALUE! .

Ak potrebujete rýchlo vyhľadať syntax konkrétnej funkcie, pozrite si tému Zoznam funkcií Excelu (podľa kategórie).

Nezadávajte do vzorcov čísla formátované znakmi dolára ($) alebo oddeľovače desatinných miest (,), pretože značky dolára označujú absolútne odkazy a čiarky oddeľovače argumentov. Namiesto $1,000 zadajte do vzorca iba 1000.

Ak použijete formátované čísla v argumentoch, zobrazia sa neočakávané výsledky výpočtu, ale môže sa zobraziť aj chyba #NUM! . Ak napríklad zadáte vzorec =ABS(-2;134) na vyhľadanie absolútnej hodnoty -2134, Excel zobrazí #NUM! pretože funkcia ABS akceptuje iba jeden argument a vidí argumenty -2 a 134 ako samostatné argumenty.

Poznámka: Výsledok vzorca môžete po zadaní vzorca pomocou neformátovaných čísel (konštánt) formátovať pomocou oddeľovačov desatinných miest a symbolov meny. Vo všeobecnosti nie je vhodné vkladať konštanty do vzorcov, pretože ak ich budete neskôr potrebovať aktualizovať, môže byť ťažké ich nájsť a sú náchylnejšie na nesprávne zadávanie. Oveľa lepšie je umiestniť konštanty do buniek, kde sú otvorené a jednoducho na ne odkazujú.

Váš vzorec nemusí vrátiť očakávané výsledky, ak sa pri výpočtoch nemôže použiť typ údajov bunky. Ak napríklad zadáte jednoduchý vzorec =2+3 do bunky, ktorá je naformátovaná ako text, Excel nedokáže vypočítať zadané údaje. V bunke uvidíte iba text =2+3. Túto chybu opravíte tak, že typ údajov bunky zmeníte z možnosti Text na Všeobecné takto:

  1. Vyberte bunku.

  2. Vyberte položku Domov a výberom šípky rozbaľte skupinu Formát čísla alebo Čísla (alebo stlačte kombináciu klávesov Ctrl + 1). Potom vyberte položku Všeobecné.

  3. Stlačením klávesu F2 prepnite bunku do režimu úprav a potom stlačením klávesu Enter potvrďte vzorec.

Dátum zadaný do bunky s typom údajov Číslo sa môže zobraziť ako číselná hodnota dátumu, nie ako dátum. Ak chcete číslo zobraziť ako dátum, vyberte formát Dátum v galérii Formát čísla.

Je pomerne bežné používať ako znak násobenia vo vzorci x, ale Excel namiesto toho používa hviezdičku (*). Ak vo vzorci použijete konštantu, Excel zobrazí chybové hlásenie a ponúkne možnosť opravy nahradením znaku x hviezdičkou (*).

Okno s výzvou na nahradenie znaku x symbolom násobenia *

Ak však použijete odkazy na bunky, Excel vráti #NAME? Ak je zadané umiestnenie pred prvou alebo za poslednou položkou v poli, výsledkom vzorca bude chybová hodnota #ODKAZ!.

Chyba #NÁZOV? pri použití znaku x spolu s odkazmi na bunky namiesto symbolu násobenia *

Ak vytvárate vzorec, ktorý obsahuje text, uzavrite text do úvodzoviek.

Vo vzorci ="Dnes je " & TEXT(TODAY();"dddd. mm. rrrr") sa napríklad kombinuje text "Dnes je " s výsledkami funkcií TEXT a TODAY a v bunke sa vráti hodnota napríklad Dnes je pondelok 30. máj.

Vo vzorci je za textom "Dnes je " pred koncovými úvodzovkami medzera, ktorá vytvára požadovanú medzeru medzi slovami "Dnes je" a "pondelok 30. máj"..

V rámci funkcie je možné kombinovať alebo vnoriť maximálne 64 úrovní funkcií.

Napríklad vzorec =IF(SQRT(PI())<2;"Menšie ako dva!";"Viac ako dva!") má 3 úrovne funkcií; pi je vnorená do funkcie SQRT, ktorá je zasa vnorená do funkcie IF.

Keď zadávate odkaz na hodnoty alebo bunky v inom hárku a názov daného hárka obsahuje neabecedný znak (napríklad medzeru), uzavrite daný názov do jednoduchých úvodzoviek (').

Ak chcete napríklad vrátiť hodnotu z bunky D3 v hárku s názvom Štvrťročné údaje v zošite, zadajte: ='Štvrťročné údaje'! D3. Bez úvodzoviek okolo názvu hárka vzorec zobrazí chybu #NAME?.

Môžete tiež vybrať hodnoty alebo bunky v inom hárku a odkazovať na ne vo vzorci. Excel takto automaticky pridá úvodzovky pred a za názvy hárkov.

Keď zadávate odkaz na hodnoty alebo bunky v inom zošite, zahrňte názov zošita v hranatých zátvorkách ([]), za ktorými nasleduje názov hárka s požadovanými hodnotami alebo bunkami.

Ak chcete napríklad odkazovať na bunky A1 až A8 v hárku Predaj v zošite operácií 2. štvrťroka, ktorý je otvorený v Exceli, zadajte: =[Q2 Operations.xlsx]Predaj! A1:A8. Bez hranatých zátvoriek zobrazí vzorec chybu #REF!.

Ak zošit nie je otvorený v Exceli, zadajte úplnú cestu k súboru.

Napríklad =ROWS('C:\Moje dokumenty\[Prevádzková činnosť za Q2.xlsx]Predaj'!A1:A8).

Poznámka:  Ak sú v úplnej ceste znaky medzery, cestu je nutné vložiť do jednoduchých úvodzoviek (jednoduchá úvodzovka musí byť na začiatku cesty a za názvom hárka a pred výkričníkom).

Tip: Najjednoduchším spôsobom, ako získať cestu k inému zošitu, je otvoriť druhý zošit, potom z pôvodného zošita zadať =, a pomocou kombinácie klávesov Alt + Tab prejsť na iný zošit. Vyberte ľubovoľnú bunku na požadovanom hárku a potom zavrite zdrojový zošit. Vzorec sa automaticky aktualizuje a zobrazí celú cestu k súboru spolu s názvom hárku a požadovanou syntaxou. Cestu môžete dokonca kopírovať a prilepiť a použiť ju všade, kde potrebujete.

Delenie bunky inou bunkou, ktorá obsahuje nulu (0) alebo žiadnu hodnotu, má za následok chybu #DIV/0!.

Ak chcete tejto chybe predísť, môžete ju vyriešiť priamo a môžete otestovať existenciu menovateľa. Môžete použiť: 

=IF(B1;A1/B1;0)

Význam vzorca: AK(B1 existuje, potom sa bunka A1 vydelí bunkou B1, inak sa vráti hodnota 0).

Pred odstránením vždy skontrolujte, či máte vzorce, ktoré odkazujú na údaje v bunkách, rozsahoch, definovaných názvoch, hárkoch alebo zošitoch. Pred odstránením údajov, na ktoré sa odkazuje, budete potom môcť tieto vzorce nahradiť ich výsledkami.

Ak sa vzorce výsledkami nahradiť nedajú, pozrite si tieto informácie o chybách a možných riešeniach:

  • Ak vzorec odkazuje na bunky, ktoré boli odstránené alebo nahradené inými údajmi a ak vráti chybu #REF!vyberte bunku s #REF! Ak je zadané umiestnenie pred prvou alebo za poslednou položkou v poli, výsledkom vzorca bude chybová hodnota #ODKAZ!. V riadku vzorcov vyberte položku #REF! a odstráňte ho. Potom znova zadajte rozsah vzorca.

  • Ak definovaný názov chýba a vzorec odkazujúci na tento názov vráti chybu #NAME?, definujte nový názov, ktorý odkazuje na požadovaný rozsah, alebo zmeňte vzorec tak, aby odkazovať priamo na rozsah buniek (napríklad A2:D8).

  • Ak hárok chýba a vzorec, ktorý naň odkazuje, vráti chybu #REF!, neexistuje žiadny spôsob, ako tento problém odstrániť. Žiaľ, hárok, ktorý bol odstránený, nie je možné obnoviť.

  • Ak chýba zošit, vzorec, ktorý naň odkazuje, ostáva nezmenený, až kým vzorec neaktualizujete.

    Ak je vzorec napríklad = [Zošit1.xlsx]Hárok1'!A1, ale dokument s názvom Zošit1.xlsx už nemáte, hodnoty, na ktoré sa v tomto zošite odkazuje, budú aj naďalej k dispozícii. Ak však upravíte a uložíte vzorec, ktorý odkazuje na daný zošit, Excel zobrazí dialógové okno Aktualizovať hodnoty a vyzve vás na zadanie názvu súboru. Vyberte položku Zrušiť a potom sa uistite, že tieto údaje sa nestratia nahradením vzorcov odkazujúcich na chýbajúci zošit výsledkami vzorca.

Niekedy pri kopírovaní obsahu bunky chcete prilepiť len hodnotu, nie základný vzorec, ktorý sa zobrazuje v riadok vzorcov.

Môžete napríklad skopírovať výslednú hodnotu vzorca do bunky v inom hárku. Alebo môžete chcieť odstrániť hodnoty, ktoré ste použili vo vzorci po skopírovaní výslednej hodnoty do inej bunky v hárku. Obe tieto akcie spôsobujú chybu neplatného odkazu na bunku (#REF!) sa zobrazí v cieľovej bunke, pretože na bunky obsahujúce hodnoty použité vo vzorci už nemožno odkazovať.

Tejto chybe sa môžete vyhnúť prilepením výsledných hodnôt vzorcov bez vzorca do cieľových buniek.

  1. V hárku vyberte bunky obsahujúce výsledné hodnoty vzorca, ktoré chcete kopírovať.

  2. Na karte Domov vyberte v skupine Schránka položku Kopírovať Vzhľad tlačidla.

    Pás s nástrojmi v programe Excel

    Klávesová skratka: Stlačte kombináciu klávesov CTRL + C.

  3. Vyberte bunku v ľavom hornom rohu oblasti prilepenia.

    Tip: Ak chcete premiestniť alebo skopírovať výber do iného hárka alebo zošita, vyberte inú kartu hárka alebo prepnite na iný zošit a potom vyberte bunku v ľavom hornom rohu oblasti prilepenia.

  4. Na karte Domov vyberte v skupine Schránka položku Prilepiť Vzhľad tlačidlaa potom vyberte položku Prilepiť hodnoty alebo stlačte kláves Alt > E > S > V > Enter pre Windows alebo príkaz > > V > V > Enter v Macu.

Ak chcete porozumieť tomu, ako zložitý alebo vnorený vzorec vypočíta konečný výsledok, môžete tento vzorec vyhodnotiť.

  1. Vyberte vzorec, ktorý chcete vyhodnotiť.

  2. Vyberte položku Vzorce > vyhodnotiť vzorec.

    Skupina Kontrola vzorca na karte Vzorec

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

    Dialógové okno Vyhodnotenie vzorca

  4. Ak je podčiarknutá časť vzorca odkazom na iný vzorec, vyberte položku Krok do a v poli Vyhodnotenie zobrazte druhý vzorec. Ak chcete prejsť späť na predchádzajúcu bunku a vzorec, vyberte položku Vystúpiť .

    Tlačidlo Krok v nie je k dispozícii pri druhom zobrazení odkazu vo vzorci alebo v prípade, že vzorec odkazuje na bunku v inom zošite.

  5. Pokračujte, kým nebude vyhodnotená každá časť vzorca.

    Nástroj Vyhodnotenie vzorca nemusí nevyhnutne povedať, prečo je vzorec nefunkčný, ale môže pomôcť poukázať na to, kde. Tento nástroj môže byť obzvlášť užitočný vo väčších vzorcoch, kde by bolo hľadanie problému bez neho ťažké.

    Poznámky: 

    • Niektoré časti funkcií IF a CHOOSE sa nevyhodnotia a v poli Vyhodnotenie sa môže vyskytnúť chyba #NIE JE K DISPOZÍCII.

    • Prázdne odkazy sa v poli Vyhodnotenie zobrazujú ako nulové hodnoty (0).

    • Niektoré funkcie sa prepočítavajú pri každej zmene hárka. Tieto funkcie vrátane funkcií RAND, AREAS, INDEX, OFFSET, CELL, INDIRECT, ROWS, COLUMNS, NOW, TODAY a RANDBETWEEN môžu zapríčiniť, že sa v dialógovom okne Vyhodnotiť vzorec zobrazia výsledky, ktoré sa od aktuálnych výsledkov v bunke v hárku líšia.

Potrebujete ďalšiu pomoc?

Vždy sa môžete opýtať odborníka v komunite Excel Tech alebo získať podporu v rámci komunity lokality Answers.

Pozrite tiež

Prehľad vzorcov v Exceli

Zisťovanie chýb vo vzorcoch

Zoznam funkcií Excelu (podľa abecedy)

Zoznam funkcií Excelu (podľa kategórie)

Potrebujete ďalšiu pomoc?

Rozšírte svoje zručnosti

PRESKÚMAŤ ŠKOLENIE >

Buďte medzi prvými, ktorí získajú nové funkcie

Pripojiť k Microsoft insiderov chcú >

Boli tieto informácie užitočné?

Aká je podľa vás jazyková kvalita textu?
Čo sa vám páčilo, prípadne čo nie?

Ďakujeme za vaše pripomienky!

×