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

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 časti: Chyby vzorca, keď oddeľovač zoznamu nie je správne nastavený 

Excel vyhodnocuje širokú škálu chýb s mriežkou (#), napríklad #HODNOTA!, #ODKAZ!, #ČÍSLO, #NEDOSTUPNÝ, #DIV/0!, #NÁZOV? a #NEPLATNÝ!, čím označuje, ž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? (chyba)

• Vyriešenie chyby #NEPLATNÝ! (chyba)

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

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ž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ť 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.

  

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 skontrolujte pod položkou Výpočet zošita, či ste vybrali možnosť Automatické.

   

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

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.

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.

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

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

Nezadávajte do vzorcov čísla v anglickom formáte so znakom dolára ($) alebo s oddeľovačmi desatinných miest (,), pretože znaky dolára označujú absolútne odkazy a čiarky oddeľujú argumenty. 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čtu, ale môže sa zobraziť aj chyba #ČÍSLO!. 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.

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

Ak ale používate odkazy na bunku, Excel zobrazí chybu #NÁZOV?. Ak je zadané umiestnenie pred prvou alebo za poslednou položkou v poli, výsledkom vzorca bude chybová hodnota #ODKAZ!.

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

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 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. Ak pred a za názvom hárka nebudú úvodzovky, vzorec zobrazí chybu #NÁZOV?.

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.

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 Q2.xlsx]Predaj!A1:A8. Ak sa nezadajú hranaté zátvorky, vzorec zobrazí chybu #ODKAZ!.

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

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

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é sa odstránili alebo nahradili inými údajmi, a ak sa zobrazí chyba #ODKAZ!vyberte bunku s chybou #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?definujte 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.

Niekedy pri kopírovaní obsahu bunky chcete prilepiť len hodnotu, a nie príslušný vzorec, ktorý sa zobrazuje v riadok 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 (#ODKAZ!), ktorá sa zobrazí v cieľovej bunke, pretože na bunky obsahujúce hodnoty, ktoré ste použili vo vzorci, sa už nedá 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 v skupine Schránka vyberte položku Kopírovať .

   

   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 v skupine Schránka vyberte položku Prilepiť a potom vyberte položku Prilepiť hodnoty, alebo stlačte klávesy Alt > E > S > V > Enter (vo Windowse) alebo klávesy Option > Command > 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žky 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á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.