Zabránenie vzniku nefunkčných vzorcov v Exceli

Ak Excel nedokáže vyriešiť vzorec, ktorý sa pokúšate vytvoriť, zobrazí sa chybové hlásenie podobné tomuto:

Bohužiaľ to znamená, že Excel nerozumie tomu, čo sa pokúšate urobiť, takže budete musieť aktualizovať vzorec alebo skontrolovať, či funkciu používate správne.

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

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 pravdepodobne nie je správne. Ďalšie informácie získate výberom nadpisov.

Používate vo vzorci správne oddeľovače zoznamov?

Vzorce s viacerými argumentmi používajú oddeľovače zoznamov na oddelenie argumentov. Oddeľovač, ktorý sa používa, sa môže líšiť v závislosti od miestneho nastavenia operačného systému a nastavení Excelu. Najbežnejšie oddeľovače zoznamov sú čiarka „,“ a bodkočiarka „;“.

Vzorec sa preruší, ak niektorá z jeho funkcií používa nesprávne oddeľovače.

Ďalšie informácie nájdete v téme: Chyby vzorca, keď oddeľovač zoznamu nie je správne nastavený

Zobrazuje sa vo vzorcoch Excelu chyba s mriežkou (#)?

Excel generuje rôzne chyby mriežky (#), napríklad #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? a #NULL! môžete označiť, že niečo vo vzorci nefunguje správne. Napríklad #HODNOTA! je chyba, ktorú spôsobujú nesprávne formátovania alebo nepodporované typy údajov v argumentoch. Prípadne sa môže zobraziť chyba #ODKAZ!, 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é.

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

• Odstránenie chyby #ČÍSLO! (chyba)
• Odstránenie chyby #HODNOTA! (chyba)
• Odstránenie chyby #NEDOSTUPNÝ
• Odstránenie chyby #DIV/0! (chyba)
• Odstránenie chyby #ODKAZ! (chyba)
• Odstránenie chyby #NÁZOV?
• Vyriešenie chyby #NEPLATNÝ! (chyba)

Oprava nefunkčných prepojení vo vzorcoch Excelu

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.

Excel zobrazí vyššie uvedené dialógové okno, aby zabezpečil, že vzorce v aktuálnom tabuľkovom hárku vždy odkazujú na najaktuálnejšiu hodnotu v prípade, že sa referenčná hodnota 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 urobiť, prejdite na položku Možnosti > súboru > Rozšírené > Všeobecné a zrušte začiarknutie políčka Potvrdiť aktualizáciu automatických prepojení.

Vzorec zobrazuje v Exceli syntax namiesto hodnoty

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 vyberte tlačidlo Zobraziť vzorce a skontrolujte nastavenia.

  Tip

  Môžete tiež použiť klávesovú skratku Ctrl + ` (kláves 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 vyššie uvedený krok nepomohol problém vyriešiť, je možné, že bunka je naformátovaná ako text. Kliknite pravým tlačidlom myši na bunku a vyberte položku Formátovať bunky > všeobecné (alebo Ctrl + 1) a potom stlačte kláves 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ť celý rozsah, použiť požadovaný číselný formát a prejsť na položku Text údajov > po dokončenie stĺpca>. Takto sa použije požadovaný formát použije na všetky vybrané bunky.

  

Povoliť automatický výpočet v zošite, ak sa vzorec nevypočítava v Exceli

Ak sa vzorec nevypočíta, budete musieť skontrolovať, či je v Exceli povolený automatický výpočet. Vzorce sa nevypočítajú, ak je zapnutý manuálny výpočet. Ak chcete skontrolovať funkciu Automatický výpočet, 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 v časti Výpočet zošita skontrolujte, či je vybratá možnosť Automaticky .

   

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

Vo vzorci je jeden alebo viacero zacyklených odkazov

Zacyklený odkaz sa vyskytne vtedy, keď 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 na takú, ktorá zabráni vzniku zacyklených odkazov. V niektorých prípadoch však môžete zacyklené odkazy potrebovať. pretože spôsobujú iteráciu funkcií, teda ich opakovanie dovtedy, kým sa splní konkrétna číselná podmienka. V takýchto prípadoch budete musieť zapnúť 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.

Začína funkcia znakom rovnosti (=)?

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

Zhodujú sa všetky ľavé a pravé zátvorky?

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 obsahuje dve pravé zátvorky, ale iba jednu ľavú. Správny vzorec vyzerá takto: =IF(B5<0;"Neplatné";B5*1,05).

Nachádzajú sa v syntaxi všetky požadované argumenty?

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)

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

Práca s neformátovanými číslami vo vzorcoch Excelu

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 $1,000 zadajte do vzorca iba 1000.

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

Majú odkazované bunky správny typ údajov?

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. Vyberte položku Domov a výberom šípky rozbaľte skupinu Číslo alebo Formát čí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 hodnota číselného dátumu, nie ako dátum. Ak chcete číslo zobraziť ako dátum, vyberte formát Dátum v galérii Formát čísla.

Pokúšate sa násobiť bez použitia symbolu *?

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 (*).

Ak ale používate odkazy na bunku, Excel vráti #NAME? chyby.

Chýbajú okolo textu vo vzorcoch úvodzovky?

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

Je vo vzorci vnorených viac než 64 funkcií?

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á 3 úrovne funkcií; Funkcia PI je vnorená vo funkcii SQRT, ktorá je zasa vnorená vo funkcii IF.

Sú názvy hárkov uzavreté do jednoduchých úvodzoviek?

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, aby sa 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 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 svojom vzorci. Excel takto automaticky pridá úvodzovky pred a za názvy hárkov.

Oprava externých ciest k zošitom vo vzorcoch Excelu

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 Prevádzková činnosť za Q2, ktorý je otvorený v Exceli, zadajte: =[Prevádzková činnosť za Operations.xlsx]Predaj! A1:A8. Bez hranatých zátvoriek vzorec zobrazí 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).

Delíte číselné hodnoty nulou?

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. Mohli by ste 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).

Odkazuje vzorec na odstránené údaje?

Pred odstránením vždy skontrolujte, či nemáte nejaké vzorce, ktoré odkazujú na údaje v bunkách, rozsahy, 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 ak vráti chybu #REF!, vyberte bunku s označením #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 hodnotu #REF! a odstráňte ju. Potom znova 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. Vyberte 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.

Skopírovali a prilepili ste bunky, ktoré sú priradené k vzorcu, do tabuľkového hárka?

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ť kopírovať výslednú hodnotu vzorca do bunky v inom hárku. Prípadne po skopírovaní výslednej hodnoty do inej 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 (#REF!), 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 vzorca do cieľových buniek.

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

2. On the Home tab, in the Clipboard group, select Copy
   
   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 odlišné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 vyberte v skupine Schránka položku Prilepiťprilepenia a potom vyberte položku Prilepiť hodnoty, alebo stlačte kláves Alt>, E>, S, V >>, vo Windowse, alebo Option > Command > V >> Enter v Macu.

Ak máte vnorený vzorec, vyhodnocujte tento vzorec po krokoch

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 možnosť Vzorce>Vyhodnotiť vzorec.
   

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

4. Ak je podčiarknutá časť vzorca odkazom na iný vzorec, vyberte položku Vstúpiť, aby sa druhý vzorec zobrazil 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, keď sa druhýkrát 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 Vyhodnotiť vzorec 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ámka

   • Niektoré časti funkcií IF a CHOOSE sa nevyhodnotia a v poli Vyhodnotenie sa môže zobraziť chyba #N/A.
   • 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 Community alebo získať podporu v komunitách.

Pozrite tiež

Prehľad vzorcov v Exceli

Pomocník pre Excel & vzdelávanie