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 nerozumie tomu, čo sa snažíte urobiť, preto by ste v tvorbe vzorca nemali pokračovať a mali by ste začať odznova.

Najskôr zatvorte chybové hlásenie kliknutím na tlačidlo OK alebo stlačením klávesu ESC .

Excel vás vráti na bunku s nefunkčným vzorcom, ktorý bude v režime úprav, a zvýrazní problematické miesto. Ak netušíte, ako chybu opraviť, a chcete začať odznova, môžete znova stlačiť kláves ESC alebo môžete kliknúť na tlačidlo Zrušiť v riadku vzorcov, ktorým ukončíte režim úprav.

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

Ak si nie ste si istí, ako pokračovať ďalej alebo aký druh Pomocníka potrebujete, skúste nájsť podobné otázky vo fóre komunity používateľov Excelu alebo uverejnite vlastnú otázku.

Prepojenie na fórum komunity používateľov Excelu

Ak chcete pokračovať v riešení problému, nasledujúci kontrolný zoznam obsahuje kroky na riešenie problémov, ktoré vám pomôžu zistiť, čo vo vzorcoch pravdepodobne nie je správne.

Excel vyhodnocuje širokú škálu chýb pomocou hashtagu (#), napríklad #HODNOTA!, #ODKAZ!, #ČÍSLO, #NEDOSTUPNÝ, #DELENIENULOU!, #NÁZOV? a #NEPLATNÝ!, čím označuje, že niečo vo vzorci nefunguje správne. Chybu #HODNOTA! napríklad spôsobuje nesprávne formátovanie alebo nepodporované typy údajov v argumentoch. Prípadne sa môže chyba #ODKAZ! zobraziť, ak vzorec odkazuje na bunky, ktoré boli odstránené alebo nahradené inými údajmi. Pokyny na riešenie problémov sú pre každú chybu iné.

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

Prečítajte si niektorú z týchto tém podľa zodpovedajúcej chyby s hashtagom, ktorá sa zobrazuje:

Pri každom otvorení 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 v pôvodnom stave.

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

V prípade zmeny referenčnej hodnoty Excel zobrazí vyššie uvedené dialógové okno, aby sa zaistilo, že vzorce v aktuálnom tabuľkovom hárku vždy odkazujú na najaktuálnejšiu hodnotu, ak sa hodnota, na ktorú sa odkazuje, zmenila. 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 vykonať, prejdite na položky Súbor > Možnosti > Rozšírené > Všeobecné a zrušte začiarknutie políčka Potvrdiť aktualizáciu automatických prepojení. V Exceli 2007 je potrebné kliknúť na položku tlačidlo Office > Možnosti programu Excel. Tlačidlo Office 2007

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

Dôležité : Ak s nefunkčnými prepojeniami vo vzorcoch pracujete prvýkrát, potrebujete si pripomenúť riešenie nefunkčných prepojení, alebo neviete, či sa majú aktualizovať všetky odkazy, 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. Na karte Vzorce v skupine Kontrola vzorca kliknite na tlačidlo Zobraziť vzorce a skontrolujte nastavenia.

    Tip : Môžete tiež použiť klávesovú skratku Ctrl+` (druhý kláves sa nachádza nad klávesom Tab). Po vykonaní tohto kroku sa stĺpce automaticky rozšíria a zobrazia vzorce. Nemajte ale žiadne obavy, ak sa prepnete späť na normálne zobrazenie, veľkosť stĺpcov sa zmení.

  • Ak vám uvedený krok nepomohol vyriešiť váš problém, 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 v stĺpci veľký rozsah buniek, ktoré sú formátované ako text, potom môžete vybrať celý rozsah, použiť požadovaný číselný formát a prejsť na položky Údaje > Text na stĺpce > 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, musíte skontrolovať, či je v Exceli povolený automatický výpočet. Vzorce sa nevypočítajú, ak je zapnutý manuálny výpočet. Ak chcete zistiť, či je zapnutá funkcia Automatický výpočet, postupujte podľa týchto krokov:

  1. Kliknite na kartu Súbor, Možnosti a potom kliknite na 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 Zmeniť opätovný výpočet, iteráciu alebo presnosť vzorca.

Zacyklený odkaz sa zobrazí v prípade, že vzorec odkazuje na bunku, v ktorej sa nachádza. Môžete ho opraviť tak, že premiestnite vzorec do inej bunky alebo zmeníte syntax vzorca, ktorá zabráni vzniku zacyklených odkazov. V niektorých prípadoch však môžu byť zacyklené odkazy užitočné. Spôsobujú totiž, že vo funkcii dochádza k iterácii, čiže opakovaniu dovtedy, kým sa splní zadaná číselná podmienka. V takom prípade budete musieť povoliť Iteračný výpočet.

Ďalšie informácie o zacyklených odkazoch nájdete v téme Vyhľadanie a vyriešenie 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.

Ak zadáte niečo ako SUM(A1:A10), namiesto výsledku vzorca zobrazí Excel textový reťazec SUM(A1:A10). Ak zadáte 11/2, Excel namiesto vydelenia čísla 11 číslom 2 zobrazí dátum, napríklad 2.11. alebo 02.11.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)=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 teda, č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 obsahuje dve pravé zátvorky a iba jednu ľavú. 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 sú počas písania uvedené v plávajúcom paneli s nástrojmi s odkazmi na funkciu, ktorý sa zobrazuje pod vzorcom .

Snímka obrazovky s panelom s nástrojmi Odkaz na funkciu
Panel s nástrojmi odkazu 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 vrátia neočakávané výsledky alebo zobrazia chybu #HODNOTA!.

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 naformátované ako značky dolára ($) alebo bodkočiarky (;), pretože značky dolára predstavujú absolútne odkazy a bodkočiarky oddeľovače argumentov. Namiesto $1000 zadajte do vzorca iba 1000.

Ak ste použili naformátované čísla v argumentoch, výsledkom budú neočakávané výpočty, ale môže sa zobraziť aj chyba #ČÍSLO!. Ak napríklad zadáte vzorec =ABS(-2;134) na vyhľadanie absolútnej hodnoty -2134, Excel zobrazí chybu #ČÍSLO!, pretože funkcia ABS prijíma iba jeden argument.

Poznámka : Výsledok vzorca môžete naformátovať pomocou oddeľovačov desatinných miest a symbolov mien po zadaní vzorca s nenaformátovanými číslami (konštantami). Vo všeobecnosti sa neodporúča do vzorcov zadávať konštanty, pretože sa nedajú ľahko vyhľadať v prípade potreby neskoršej aktualizácie. Navyše môžete pri ich zadávaní ľahko urobiť preklep. Omnoho lepšie je vložiť konštanty do samostatných buniek, kde na ne môžete ľahko odkazovať.

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é, a to nasledujúcim spôsobom:

  1. Vyberte bunku.

  2. Kliknite na položku Domov > na šípku vedľa položky Formát čísla (alebo stlačte kombináciu klávesov Ctrl+1) a kliknite na 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 *
Chybové hlásenie pri použití znaku x s konštantami namiesto symbolu * pre násobenie

Ak ale používate odkazy na bunku, Excel zobrazí chybu #NÁZOV?.

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

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". Bez úvodzoviek v texte môže vzorec zobraziť chybu #NÁZOV?.

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

Vzorec =IF(SQRT(PI())<2;"Menej než dva!";"Viac než dva!" má napríklad 3 úrovne funkcií: Funkcia PI je vnorená vo funkcii SQRT, ktorá je zasa vnorená vo funkcii 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, aby sa napríklad vrátila hodnota z bunky D3 v hárku s názvom Kvartálne údaje vo vašom zošite, zadajte: ='Kvartálne údaje'!D3. Bez úvodzoviek pred a za názvom hárka zobrazí vzorec chybu #NÁZOV?.

Môžete kliknúť aj na hodnoty alebo bunky v inom hárku a odkazovať tak 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 zahrnúť odkaz na bunky A1 až A8 v hárku s názvom Predaj v zošite Prevádzková činnosť za Q2, ktorý je otvorený v Exceli, zadajte: =[Prevádzková činnosť za Q2.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 : Najľahšie sa do druhého zošita dostanete tak, že tento druhý zošit otvoríte a v pôvodnom zošite zadáte = a potom sa stlačením klávesov Alt+Tab prepnite na druhý zošit a vyberte požadovanú bunku v hárku. 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 #DELENIENULOU!.

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

=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 odstraňovaním vždy skontrolujte, či vzorce neodkazujú na údaje v bunkách, rozsah, definované názvy, hárky alebo zošity. 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 vráti chybu #REF!, vyberte bunku obsahujúcu chybu #REF!. V riadku vzorcov vyberte hodnotu #REF! a odstráňte ju. Potom opätovne zadajte rozsah vzorca.

  • Ak definovaný názov chýba a vzorec, ktorý na tento názov odkazuje, vráti chybu #NÁZOV?, zadefinujte 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. Kliknite na položku Zrušiť a potom sa uistite, že sa tieto údaje nestratia po nahradení vzorcov, ktoré odkazujú na chýbajúci zošit, výsledkami vzorca.

Niekedy pri kopírovaní obsahu bunky chcete prilepiť len hodnotu, a nie príslušný vzorec, ktorý sa zobrazuje v riadku vzorcov.

Môžete napríklad chcieť skopírovať výslednú hodnotu vzorca do bunky v inom hárku. Prípadne po skopírovaní výslednej hodnoty do ďalšej bunky v hárku možno budete chcieť odstrániť hodnoty, ktoré ste použili vo vzorci. Obe tieto akcie spôsobia chybu neplatného odkazu na bunku (#ODKAZ!), ktorá sa zobrazí v cieľovej bunke, pretože na bunky obsahujúce hodnoty, ktoré ste použili vo vzorci, už nie je možné odkazovať.

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

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

  2. Na karte Domov v skupine Schránka kliknite na tlačidlo Kopírovať Tlačidlo Zoznam priečinkov na navigačnej table .

    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 výber premiestniť alebo kopírovať do iného hárka alebo iného zošita, kliknite na druhý hárok alebo prejdite do druhého zošita a potom vyberte bunku v ľavom hornom rohu oblasti prilepenia.

  4. Na karte Domov v skupine Schránka kliknite na položku Prilepiť Obrázok tlačidla a potom kliknite na položku Prilepiť hodnoty, alebo stlačte klávesy Alt > E > S > V > Enter (vo Windowse) alebo klávesy Option > Command > V > V > Enter (na 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. Kliknite na položky Vzorce > Vyhodnotiť vzorec.

    Skupina Kontrola vzorca na karte Vzorec

  3. Kliknite na položku Vyhodnotiť a skontrolujte 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, kliknite na položku Vstúpiť, čím zobrazíte druhý vzorec v poli Vyhodnotenie. Kliknutím na položku Vystúpiť prejdete späť na predchádzajúcu bunku a vzorec.

    Tlačidlo Vstúpiť nie je druhýkrát k dispozícii v prípade, ak sa odkaz zobrazí 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 Vyhodnotenie vzorca vám nemusí vždy prezradiť, prečo je vzorec nefunkčný, ale môže vám pomôcť nájsť miesto, kde je chyba. 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).

    • Existujú funkcie, ktoré sa prepočítajú pri každej zmene hárku. 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 technikov pre Excel, získať podporu v rámci komunity lokality Answers alebo navrhnúť novú funkciu či vylepšenie na lokalite Excel User Voice.

Pozrite tiež

Prehľad vzorcov v Exceli

Zisťovanie chýb vo vzorcoch

Funkcie programu Excel (abecedné zoradenie)

Funkcie programu Excel (podľa kategórie)

Rozšírte svoje zručnosti
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.

×