Zabránenie vzniku nefunkčných vzorcov

Zabránenie vzniku nefunkčných vzorcov

Poznámka: Radi by sme vám čo najrýchlejšie poskytovali najaktuálnejší obsah Pomocníka vo vašom vlastnom jazyku. Táto stránka bola preložená automaticky a môže obsahovať gramatické chyby alebo nepresnosti. Naším cieľom je, aby bol tento obsah pre vás užitočný. Dali by ste nám v dolnej časti tejto stránky vedieť, či boli pre vás tieto informácie užitočné? Tu nájdete anglický článok pre jednoduchú referenciu.

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 .

Vrátite na bunku s nefunkčný vzorec, ktorý bude v režime úprav, a Excel zvýrazní miesto, kde má problém. Ak ešte nemáte vysvetliť, ako to tam a chcete začať odznova, môžete znova stlačte kláves ESC alebo kliknite na tlačidlo Zrušiť v riadku vzorcov, ktoré bude ukončenie režimu ú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 hodí širokú škálu hash (#) chyby, ako napríklad #VALUE!, #REF!, #NUM, hodnoty #nedostupný, #DIV/0!, #NAME?, a #NULL!, označovať niečo vo vašom vzorci nepracuje správne. Napríklad #VALUE! chyba je spôsobená nesprávne formátovanie alebo nepodporovaných typov údajov v argumentoch. Alebo sa zobrazí #REF! Ak vzorec odkazuje na bunky, ktoré boli odstránené alebo nahradené aj iné údaje sa vyskytla chyba. Pokyny na riešenie problémov sa budú líšiť v prípade každej chyby.

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 ďalšom otvorení hárok, ktorý obsahuje vzorce, ktoré odkazujú na hodnoty v iných tabuliek, zobrazí sa výzva na aktualizáciu odkazy alebo nechať ako-je.

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 kombináciu klávesov Ctrl + " (kláves nad kláves Tab). Po vykonaní tohto kroku stĺpcov automaticky rozšíri Zobraziť vzorce, ale nebojte sa, ak prepnete späť k normálnemu zobrazeniu zmení veľkosť stĺpcov.

  • Ak vyššie uvedený krok stále problém nevyrieši, je možné v bunke je formátované ako text. Môžete kliknite pravým tlačidlom myši na bunku a vyberte Formát buniek > Všeobecné (alebo Ctrl + 1), stlačte kláves F2 > Enter pri zmene formátu.

  • Ak máte veľký rozsah buniek v stĺpci, ktoré sú formátované ako text, potom môžete vybrať rozsah, použitie číselného formátu podľa vášho výberu a prejdite na údaje > Text do stĺpca > dokončiť. Tento formát sa použiť na všetky vybratých buniek.

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

Ak nie je výpočet vzorca, musíte začiarknite políčko, ak je povolený automatický výpočet v programe Excel. Vzorce nebude vypočítavať manuálneho výpočtu, ak je povolená. Ak chcete vyhľadať Automatický výpočettieto kroky:

  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

Tiež, niektoré funkcie, napríklad SUM, vyžadujú iba číselné argumenty, zatiaľ čo iné funkcie, napríklad nahradiťa vyžaduje textovej hodnoty pre aspoň jeden argument. Ak používate nesprávny typ údajov, funkcie môže vrátiť neočakávané výsledky alebo zobraziť #VALUE! chyby.

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 používate formátovaný čísla v argumentoch, výpočet neočakávané výsledky dosiahnete, ale tiež môže zobrazovať #NUM! chyby. Ak zadáte vzorec na vyhľadanie absolútna hodnota argumentu-2134 =ABS(-2,134) , napríklad Excel zobrazí #NUM! chybu, pretože funkcia ABS prijme len jeden argument.

Poznámka: Môžete formátovať výsledok vzorca pomocou oddeľovačov desatinných miest a meny symboly po zadaní vzorca pomocou nenaformátovaných čísel (konštanty). Vo všeobecnosti nie je vhodné umiestniť konštánt vo vzorcoch, pretože môžu byť ťažké nájsť, ak je potrebné aktualizovať neskôr, a ich väčšou nesprávne napísaný. Je to oveľa lepšie dať konštanty v bunkách, kde sú v otvorenej a jednoducho neexistujú.

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: Najjednoduchším spôsobom, ako získať cesty do iného zošita, je otvoriť zošit, potom z pôvodného dokumentu, zadajte = a potom presunúť do druhého zošita a vyberte ľubovoľnú bunku v hárku, ktorý sa má použiť Alt + Tab . Zatvorte zdrojový zošit. Vzorec sa automaticky aktualizuje zobrazíte celý súbor cestu a hárka názov spolu s požadovanú syntax. Môžete dokonca kopírovať a prilepiť cestu a použiť kdekoľvek 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 na vyhodnotenie vzorca nebude potrebné povedať, prečo vzorec nefunguje, ale vám môže pomôcť zdôrazniť, kde. Môže to byť veľmi užitočný nástroj vo vzorcoch väčších, kde v opačnom prípade je ťažké nájsť problém.

    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 programe Excel

Zisťovanie chýb vo vzorcoch

Zoznam funkcií Excelu (podľa abecedy)

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

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.

×