Zabránenie vzniku nefunkčných vzorcov

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ť, aby ste sa mohli len chcieť začať odznova.

Začnite výberom tlačidla OK alebo stlačením klávesu ESC zatvoríte 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 urobiť tam a chcete začať znova, môžete znova stlačiť kláves ESC alebo vybrať tlačidlo Zrušiť v riadku vzorcov, ktorý vás prevedie 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 stať nesprávne.

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

Excel hádže rôzne chyby v librách (#), ako je napríklad #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? a #NULL!, ak chcete uviesť niečo vo vašom vzorci nefunguje správne. Napríklad #VALUE! chyba je spôsobená nesprávnym formátovaním alebo nepodporovanými typmi údajov v argumentoch. Alebo sa zobrazí #REF. chyba, ak vzorec odkazuje na bunky, ktoré boli odstránené alebo nahradené inými údajmi. Pokyny na riešenie problémov sa budú pre každú chybu 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 libra, ktorú vidíte:

Pri každom otvorení tabuľkového hárka so vzorcami, ktoré odkazujú na hodnoty v iných tabuľkových hárkoch, sa zobrazí výzva na aktualizáciu odkazov alebo na ich opustenie.

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

Excel zobrazí vyššie uvedené dialógové okno s cieľom uistiť sa, že vzorce v aktuálnom tabuľkovom hárku vždy odkazujú na najaktuálnejšie hodnoty 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 na položku Možnosti súboru > > 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 pri práci s nefunkčnými prepojeniami vo vzorcoch používate prvý raz, ak potrebujete obnoviť riešenie nefunkčných prepojení alebo ak neviete, či sa odkazy aktualizujú, prečítajte si tému kontrola dostupnosti externých odkazov (prepojení).

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

  • Uistite sa, že Excel je 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 tiež použiť klávesovú skratku Ctrl + ' (kláves nad klávesom Tab). Keď to urobíte, stĺpce sa automaticky rozšíria na zobrazenie vzorcov, ale nemusíte sa obávať, keď prepnete späť na normálne zobrazenie, stĺpce budú meniť veľkosť.

  • Ak vyššie uvedený krok problém nevyrieši, 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ť požadovaný formát čísla a prejsť na údaje > text do stĺpca > 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 sa vzorec nevypočítava, bude potrebné skontrolovať, či je v Exceli zapnutý automatický výpočet. Vzorce sa nevypočítavajú, ak je zapnutý manuálny výpočet. Ak chcete skontrolovať Automatický výpočet, postupujte podľa týchto krokov.

  1. Vyberte kartu súbor , vyberte položku Možnostia 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, iterácie alebo presnosti vzorca.

Zacyklený odkaz sa vyskytuje vtedy, keď vzorec odkazuje na bunku, v ktorej sa nachádza. Oprava je buď premiestniť vzorec do inej bunky alebo zmeniť syntax vzorca na takú, ktorá zabraňuje zacykleným odkazom. V niektorých prípadoch však možno budete potrebovať zacyklené odkazy, pretože spôsobia opakovanie funkcií, kým sa nedosiahne určitá číselná podmienka. V takýchto prípadoch budete musieť povoliť Odstránenie alebo povolenie zacykleného odkazu.

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

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), v Exceli sa namiesto výsledku vzorca zobrazí textový reťazec SUM (a1: A10) . Prípadne, ak zadáte 11/2, Excel zobrazí dátum, napríklad 2-Nov alebo 11/02/2009, namiesto delenia 11 o 2.

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 sú tam dve zatváracie zátvorky, ale len jednu pravú zátvorku. 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, ktoré sú uvedené na paneli s nástrojmi plávajúceho odkazu na funkciu, sa zobrazia pod vzorcom pri písaní.

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žívate 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 čísla formátované znakmi dolára ($) alebo oddeľovačmi desatinných miest (,) vo vzorcoch, pretože znaky v dolároch označujú absolútnu referenciu a čiarkové oddeľovače argumentov. Namiesto $1,000 zadajte do vzorca iba 1000.

Ak v argumentoch používate formátované čísla, získate 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) , aby ste našli absolútnu hodnotu-2134, Excel zobrazí #NUM. chyba, pretože funkcia ABS akceptuje iba jeden argument a zobrazuje čísla-2 a 134 ako samostatné argumenty.

Poznámka: Výsledok vzorca môžete naformátovať pomocou oddeľovačov desatinných miest a symbolov meny po zadaní vzorca pomocou nenaformátovaných čísel (konštánt). Vo všeobecnosti nie je vhodné vložiť konštanty vo vzorcoch, pretože je ťažké zistiť, či je potrebné aktualizovať neskôr a že sú náchylnejší na nesprávne písanie. Je oveľa lepšie umiestniť konštanty do buniek, kde sa nachádzajú v otvorených a jednoducho sa odkazuje.

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 číselné alebo číselné formáty (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žívate odkazy na bunky, Excel vráti #NAME? .

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; "menej než dva!", "viac ako dve!") má 3 úrovne funkcií; funkcia Pi je vnorená vo vnútri funkcie SQRT, ktorá je zasa vnorená v rámci 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 sa vo vzorci zobrazí chybové hlásenie #NAME?.

Môžete tiež vybrať hodnoty alebo bunky v inom hárku tak, aby sa na ne vzťahovali 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 predaja v zošite operácie Q2, ktorý je otvorený v Exceli, zadajte: = [Q2 Operations.xlsx] predaj! A1: A8. Bez hranatých zátvoriek sa vo vzorci zobrazuje chyba #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ť iný zošit, potom z pôvodného zošita, zadať hodnotu =, a použiť kombináciu klávesov ALT + TAB na presun do druhého zošita. Vyberte ľubovoľnú bunku v požadovanom hárku a potom zatvorte 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. Dalo by sa 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).

Vždy skontrolujte, či nemáte žiadne vzorce, ktoré odkazujú na údaje v bunkách, rozsahoch, definovaných názvoch, hárkoch alebo zošitoch pred odstránením čohokoľvek. 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 #REFom. . V riadku vzorcov vyberte položku #REF. a odstráňte ju. Potom znova zadajte rozsah vzorca.

  • Ak chýba definovaný názov a vzorec, ktorý odkazuje na tento názov, vráti #NAME? chybaDefinujte nový názov, ktorý odkazuje na požadovaný rozsah, alebo zmeňte vzorec tak, aby odkazoval 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 uistite sa, že tieto údaje sa nestratia nahradením vzorcov, ktoré odkazujú na chýbajúci zošit s výsledkami vzorca.

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

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

Túto chybu môžete predísť tak, že prilepíte výsledné hodnoty 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 kliknite v skupine Schránka na položku Kopírovať Obrázok 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 Kopírovať výber do iného hárka alebo zošita, vyberte inú kartu hárka alebo prejdite do iného zošita a potom vyberte bunku v ľavom hornom rohu oblasti prilepenia.

  4. Na karte domov kliknite v skupine Schránka na položku Prilepiť Vzhľad tlačidla a potom vyberte položku Prilepiť hodnotyalebo stlačte klávesovú skratku Alt > E > S > V > enter pre Windows alebo možnosť > 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. Ak chcete skontrolovať hodnotu podčiarknutého odkazu, vyberte položku vyhodnotiť . Výsledok hodnotenia sa zobrazí kurzívou.

    Dialógové okno Vyhodnotenie vzorca

  4. Ak je podčiarknutá časť vzorca odkazom na iný vzorec, výberom položky krok v zobrazíte ďalší vzorec v poli vyhodnotenie . Výberom položky vystúpiť prejdite späť na predchádzajúcu bunku a vzorec.

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

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

    Nástroj vyhodnotiť vzorec nemusí nevyhnutne informovať o tom, prečo je vzorec poškodený, ale môže vám pomôcť upozorniť na miesto. 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čítajú vždy, keď sa hárok zmení. 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 práce s balíkom Office
Preskúmať školenie
Buďte medzi prvými, ktorí získajú nové funkcie
Pridajte sa k insiderom pre Office

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

Ďakujeme za vaše pripomienky!

Ďakujeme vám za pripomienky. Pravdepodobne vám pomôže, ak vás spojíme s pracovníkom podpory pre Office.

×