Jak se vyhnout nefunkčním vzorcům

Jak se vyhnout nefunkčním vzorcům

Poznámka: Snažíme se pro vás co nejrychleji zajistit aktuální obsah nápovědy ve vašem jazyce. Tato stránka byla přeložena automaticky a může obsahovat gramatické chyby nebo nepřesnosti. Naším cílem je to, aby pro vás byl její obsah užitečný. Mohli byste nám prosím dát ve spodní části této stránky vědět, jestli vám informace v článku pomohly? Pokud byste se rádi podívali na jeho anglickou verzi, najdete ji tady.

Pokud Excel nedokáže vyhodnotit vzorec, který se pokoušíte vytvořit, může se zobrazit takováto chybová zpráva:

Obrázek excelového dialogu Problém s tímto vzorcem

Bohužel to znamená, že Excel nedokáže pochopit, co chcete udělat, takže může být lepší začít celý vzorec vytvářet znovu od začátku.

Začněte tím, že kliknutím na OK nebo stisknutím ESC zavřete chybovou zprávu.

Vrátíte se do buňky s nefunkčním vzorcem, která bude v režimu úprav a Excel zvýrazní na místo, kde ho potíže. Pokud ještě není vědět, co dělat odtud a chcete začít, můžete znovu stiskněte klávesu ESC nebo klikněte na tlačítko Zrušit v řádku vzorců, které jste ukončíte režim úprav.

Obrázek tlačítka Zrušit na řádku vzorců

Pokud si v tomto bodě nejste jistí, co udělat nebo jakou pomoc byste přesně potřebovali, můžete zkusit najít podobný dotaz na fóru komunity Excelu nebo tam publikovat svůj vlastní.

Odkaz na fórum komunity Excelu

Pokud se budete chtít posunout dál, použijte tento kontrolní seznam, který obsahuje postupy pro řešení potíží, pomocí kterých můžete zjistit, co by ve vzorcích mohlo být špatně.

Excel vyvolá řadu hash (#) chyby, jako jsou #VALUE!, #REF!, #NUM, #není_k_dispozici, #DIV/0!, #NAME? a #NULL!, označíte něco ve vzorci nefunguje doprava. Například #VALUE! Chyba způsobená nesprávné formátování nebo nepodporovaných datových typů v argumentech. Můžete také, zobrazí se #REF! Chyba, pokud vzorec odkazuje na buňky, které se odstraní nebo nahrazen příkazem další data. Poradce při potížích s pokyny se budou lišit pro každou chybu.

Poznámka: #### není chyba související se vzorcem. Znamená jenom, že sloupec není dost široký, aby se do něj vešel obsah buňky. Jednoduše přetažením sloupec rozšiřte nebo přejděte na Domů > Formát > Přizpůsobit šířku sloupců.

Obrázek možnosti Domů > Formát > Automaticky upravit šířky sloupců

Informace o chybě se značkou #, která se vám zobrazuje, najdete v jednom z odpovídajících témat:

Pokaždé, když otevřete tabulku obsahující vzorce odkazující na hodnoty v jiných tabulkách, zobrazí se výzva k aktualizaci odkazy nebo necháte jako-je.

Dialogové okno s přerušenými odkazy v Excelu

Excel zobrazí výše uvedené dialogové okno, aby zajistil, že vzorce v aktuální tabulce budou vždy ukazovat na nejaktuálnější hodnotu – pro případ, že se hodnota odkazu změnila. Můžete zvolit možnost, že se odkazy mají aktualizovat, nebo když je nechcete aktualizovat, můžete tuto akci přeskočit. I když se rozhodnete odkazy v tabulce neaktualizovat, můžete je aktualizovat ručně, kdykoli budete chtít.

Zobrazování tohoto dialogového okna při spuštění můžete kdykoli vypnout. Uděláte to tak, že přejdete na Soubor > Možnosti > Upřesnit > Obecné a zrušíte zaškrtnutí políčka Potvrdit aktualizaci automatických propojení. V Excelu 2007 byste klikli na Tlačítko Office > Možnosti aplikace Excel. Tlačítko Office 2007

Obrázek možnosti Potvrzovat aktualizaci automatických odkazů

Důležité informace: Pokud nefunkční odkazy řešíte poprvé, potřebujete si připomenout, jak se nefunkční odkazy řeší, nebo nevíte, jestli máte odkazy aktualizovat, přečtěte si článek Řízení, kdy mají být aktualizovány externí odkazy (propojení).

Pokud se nezobrazuje hodnota vzorce, postupujte takto:

  • Zkontrolujte, jestli není Excel nastavený tak, aby v tabulce zobrazoval vzorce. Uděláte to tak, že kliknete na kartu Vzorce a ve skupině Závislosti vzorců kliknete na tlačítko Zobrazit vzorce.

    Tip: Můžete taky použít klávesovou zkratku kombinaci kláves Ctrl + ' (klíč nad klávesy Tab). Po výběru sloupce se automaticky rozšířit zobrazíte vzorce, ale Nedělejte si starosti, když přepnout zpátky do normálního zobrazení, které bude změna velikosti sloupců.

  • Pokud z předchozího kroku ještě nemá řešení tohoto problému je možné že buňky formátované jako text. Klikněte pravým tlačítkem myši na buňku a vyberte Formát buněk > Obecné (nebo Ctrl + 1), stiskněte klávesu F2 > Enter chcete změnit formát.

  • Pokud máte rozsáhlá oblast buněk ve sloupci, které jsou formátované jako text a pak vyberte oblast, použití číselného formátu se výběr a přejděte na Data > Text ve sloupci > dokončit. Tato změna se projeví formátu u všech vybraných buněk.

    Obrázek dialogu Data > Text do sloupců

Pokud vzorec není vypočítat, budete muset zkontrolujte, zda je povolena automatický výpočet v aplikaci Excel. Vzorce nebude počítat, pokud je povoleno ručního výpočtu. Tímto postupem vyhledat Automatický výpočet:

  1. Klikněte na kartu Soubor, na položku Možnosti a poté na kategorii Vzorce.

  2. V oddílu Možnosti výpočtů v části Přepočet sešitu musí být vybrána možnost Automaticky.

    Obrázek možností Automatický výpočet a Ruční výpočet

Další informace o výpočtech najdete v článku Změna přepočtu, iterace nebo přesnosti vzorce.

Cyklický odkaz vznikne, když vzorec odkazuje na buňku, ve které se nachází. Opravit ho můžete tak, že vzorec přesunete do jiné buňky nebo změníte syntaxi vzorce na takovou, která nezpůsobuje cyklické odkazy. V určitých situacích ale můžete cyklické odkazy potřebovat, protože díky nim můžou funkce iterovat – opakovaně počítat, dokud nebude splněna určitá číselná podmínka. V takovém případě musíte povolit iterativní přepočet.

Další informace o cyklických odkazech najdete v článku Nalezení a oprava cyklických odkazů

Pokud zadání v buňce nebude začínat symbolem rovnítka, nebude se brát jako vzorec a nebude se ani počítat. Je to běžná chyba.

Pokud zadáte SUMA(A1:A10), místo výsledku vzorce se zobrazí textový řetězec SUMA(A1:A10). Pokud zadáte 11/2, místo podílu čísel 11 a 2 se zobrazí datum, třeba 11. únor nebo 11/02/2009.

Aby se zabránilo těmto neočekávaným výsledkům, vždy začínejte funkci symbolem rovnítka. Zadejte třeba: =SUMA(A1:A10) a =11/2.

Když ve vzorci použijete funkci, musí každá levá okrouhlá závorka mít odpovídající pravou okrouhlou závorku, aby funkce správně fungovala. Zkontrolujte proto, jestli máte všechny závorky v páru. Například vzorec =KDYŽ(B5<0);"Není platné";B5*1,05) nebude fungovat, protože obsahuje dvě pravé závorky, ale jenom jednu levou závorku. Správný vzorec by vypadal takto: =KDYŽ(B5<0;"Není platné";B5*1,05).

Excelové funkce mají argumenty (hodnoty, které musíte zadat, aby vzorec fungoval). Jenom pár funkcí (třeba funkce PI nebo DNES) nemá argumenty žádné. Zkontrolujte syntaxi vzorce, která se zobrazí, když začnete zadávat funkci, abyste měli jistotu, že daná funkce obsahuje povinné argumenty.

Například argumentem funkce VELKÁ může být jenom jeden textový řetězec nebo odkaz na buňku: =VELKÁ("ahoj") nebo =VELKÁ(C2).

Poznámka: Při psaní vzorce se na plovoucím panelu nástrojů s informacemi o funkci zobrazí argumenty funkce.

Snímek obrazovky s panelem nástrojů s informacemi o funkci
Panel nástrojů s informacemi o funkci

Některé funkce, jako je SUMA, vyžadují navíc, číselné argumenty pouze, zatímco jiné funkce, například Nahradit, vyžadují textové hodnoty pro alespoň jedno z jejich argumenty. Pokud používáte nesprávný typ dat, může funkce vrátí neočekávané výsledky nebo zobrazit #VALUE! chyby.

Pokud potřebujete rychle vyhledat syntaxi určité funkce, podívejte se na seznam excelových funkcí (podle kategorie).

Nezadávejte ve vzorcích čísla formátovaná znaky dolaru ($) nebo oddělovači tisíců (,), protože znaky dolaru označují absolutní odkazy a čárky se můžou používat jako oddělovače argumentů. Místo zadání $1,000 zadejte ve vzorci 1000.

Pokud používáte formátovaný čísel v argumentech, dostanete výpočtu neočekávané výsledky, ale se může zobrazit #NUM! chyby. Pokud zadáte vzorec =ABS(-2,134) najdete absolutní hodnotu čísla-2134, třeba Excel zobrazí #NUM! Chyba, protože Funkce ABS přijímá jenom jeden argument.

Poznámka: Můžete naformátovat výsledek vzorce s oddělovačem desetinných míst a měny symboly poté, co zadáte vzorec pomocí neformátovaný čísel (konstanty). Není obecně dobré dát konstant ve vzorcích, protože může být obtížné vyhledání Pokud byste potřebovali aktualizovat později a budou více chybám při zadávání nesprávně. Je mnohem lepší vložte do buňky, jsou-li se v části otevření vaše konstanty a snadno odkazované.

Pokud se ve výpočtech nedá použít datový typ buňky, může vzorec vrátit neočekávané výsledky. Když třeba do buňky, která je formátovaná jako text, zadáte jednoduchý vzorec =2+3, Excel zadaná data nevypočítá. Všechno, co se vám v buňce zobrazí, bude =2+3. Problém opravíte tak, že následujícím postupem změníte datový typ buňky z Text na Obecný:

  1. Vyberte buňku.

  2. Klikněte na Domů, klikněte na šipku u pole Formát čísla (nebo stiskněte Ctrl + Shift + 1) a klikněte na Obecně.

  3. Stisknutím klávesy F2 přepněte buňku do režimu úprav a vzorec potvrďte stisknutím klávesy Enter.

Datum zadané do buňky, která používá datový typ Číslo, se může místo kalendářního data zobrazit jako číselná hodnota kalendářního data. Pokud chcete, aby se číslo zobrazovalo jako datum, vyberte v galerii Formát čísla formát Datum.

Jako operátor násobení se ve vzorcích celkem běžně používá znak x. To ale nejde, protože Excel používá hvězdičku (*). Pokud ve vzorci použijete konstanty, Excel zobrazí chybovou zprávu a vzorec může opravit tak, že znak x nahradí hvězdičkou (*).

Okno se žádostí, abyste u násobení nahradili znak x znakem *
Chybová zpráva při použití konstant s operátorem násobení x místo *

Pokud ale použijete odkazy na buňky, Excel vrátí chybu #NÁZEV?.

Chyba #NÁZEV? při použití znaku x místo znaku * pro násobení u odkazů na buňky
Chyba #NÁZEV? při použití odkazů na buňky s operátorem násobení x místo *

Pokud vytváříte vzorec, který obsahuje text, uzavřete text do uvozovek.

Například vzorec ="Dnes je " & TEXT(DNES();"dddd, dd. mmmm") kombinuje text „Dnes je“ s výsledkem funkce TEXT a DNES a vrací výsledek, jako je třeba Dnes je pondělí, 30. květen.

Ve vzorci je v zadání "Dnes je " mezera před koncovými uvozovkami, aby se ve výsledku zobrazila mezera mezi textem „Dnes je“ a „pondělí, 30. květen“. Bez uvozovek kolem textu může vzorec vrátit chybu #NÁZEV?.

V jednom vzorci můžete zadat (vnořit) až 64 úrovní funkcí.

Třeba vzorec =KDYŽ(ODMOCNINA(PI())<2;"Méně než dvě!";"Více než dvě!") má tři úrovně funkcí: Funkce PI je vnořená uvnitř funkce ODMOCNINA, která je dále vnořená uvnitř funkce KDYŽ.

Pokud zadáte odkaz na hodnoty nebo buňky v jiném listu a název daného listu obsahuje neabecední znak (třeba mezeru), uzavřete název do jednoduchých uvozovek (').

Pokud třeba chcete, aby se vám vrátila hodnota buňky D3 z listu s názvem Čtvrtletní data ve vašem sešitu, zadejte ='Čtvrtletní data'!D3. Bez uvozovek okolo názvu listu vzorec zobrazí chybu #NÁZEV?.

Na hodnoty nebo buňky v jiném listu můžete taky vytvořit odkaz ve vzorci tak, že na ně kliknete. Excel automaticky přidá uvozovky okolo názvů listů.

Když zadáte odkaz na hodnoty nebo buňky v jiném sešitě, uveďte název sešitu v hranatých závorkách ([]) a za ním název listu, ve kterém se požadované hodnoty nebo buňky nachází.

Pokud chcete třeba odkazovat na buňky A1 až A8 na listu Prodej v sešitu Operace Q2, který je otevřený v Excelu, zadejte =[Operace Q2.xlsx]Prodej!A1:A8. Bez hranatých závorek vzorec zobrazí chybu #REF!.

Pokud nemáte v Excelu sešit otevřený, zadejte úplnou cestu k souboru.

Zadejte například =ŘÁDKY('C:\My Documents\[Operace Q2.xlsx]Prodej'!A1:A8).

Poznámka:  Pokud celá cesta obsahuje mezery, uzavřete cestu do jednoduchých uvozovek (na začátku cesty a za název listu před vykřičníkem).

Tip: Nejjednodušší způsob, jak získat cesta k sešitu, je otevření druhého sešitu, pak z původního sešitu, zadejte = a pak pomocí Kombinace kláves Alt + Tab posunou tak, aby sešit a potom vyberte libovolnou buňku na požadovaný list. Zavřete zdrojový sešit. Vzorec se automaticky aktualizuje a zobrazí úplnou cestu a stylů názvu souboru spolu s požadované syntaxe. Můžete dokonce kopírovat a vložit cestu a použít kdekoli budete potřebovat.

Výsledkem dělení buňky jinou buňkou, která obsahuje nulu (0) nebo neobsahuje žádnou hodnotu, je chyba #DĚLENÍ_NULOU!.

Této chybě se dá vyhnout tak, přímo na adrese buňky otestujete existenci jmenovatele.

=KDYŽ(B1;A1/B1;0)

Znamená to: KDYŽ(B1 existuje, vyděl hodnotu v buňce A1 hodnotou v buňce B1, jinak vrať 0).

Než cokoli odstraníte, vždycky zkontrolujte, že nepoužíváte žádné vzorce, které odkazují na data v buňkách, rozsahy, definované názvy, listy nebo sešity. Potom můžete nahradit tyto vzorce jejich výsledky, než odeberete data, na která se odkazuje.

Když nemůžete vzorce nahradit jejich výsledky, pročtěte si tyto informace o chybách a o tom, jak je možné je vyřešit:

  • Pokud vzorec odkazuje na buňky, které byly odstraněné nebo nahrazené jinými daty, a vrátí chybu #REF!, vyberte buňku s chybou #REF!. V řádku vzorců vyberte text #REF! a smažte ho. Potom znovu zadejte rozsah vzorce.

  • Pokud chybí definovaný název a vzorec, který na něj odkazu, vrátí chybu #NÁZEV?, definujte nový název, který odkazuje na požadovaný rozsah, nebo změňte vzorce tak, aby odkazoval přímo na daných rozsah buněk (například A2:D8).

  • Pokud chybí list a vzorec, který na něj odkazuje, vrátí chybu #REF!, neexistuje bohužel žádný způsob, jak tuto chybu opravit. Odstraněný list se nedá obnovit.

  • Pokud chybí sešit, vzorec, který na něj odkazuje, zůstane nedotčený, dokud vzorec nezměníte.

    Pokud třeba máte vzorec =[Sešit1.xlsx]List1'!A1 a Sešit1.xlsx už nemáte, hodnoty, na které se odkazuje v daném sešitu, zůstanou dostupné. Když ale upravíte a uložíte vzorec, který odkazuje na daný sešit, Excel zobrazí dialogové okno Aktualizovat hodnoty a vyzve vás, abyste zadali název souboru. Klikněte na Zrušit a potom zkontroluje, že se neztratila data, když jste nahradili vzorce odkazující na chybějící sešit výsledky vzorců.

Někdy při kopírování obsahu buňky chcete vložit jenom hodnotu a ne příslušný vzorec, který se zobrazuje na řádku vzorců.

Zkopírujete třeba výslednou hodnotu vzorce do buňky na jiném listu. Nebo po zkopírování výsledné hodnoty do jiné buňky na listu odstraníte hodnoty, které jste použili ve vzorci. Obě uvedené akce způsobí, že se v cílové buňce zobrazí chyba neplatného odkazu na buňku (#ODKAZ!), protože na buňky obsahující hodnoty, které jste použili ve vzorci, už dál nejde odkazovat.

Této chybě se dá vyhnout tak, že vložíte výsledné hodnoty vzorců do cílových buněk bez vzorce.

  1. V listu vyberte buňky obsahující výsledné hodnoty vzorce, které chcete kopírovat.

  2. Na kartě Domů klikněte ve skupině Schránka na Kopírovat Obrázek tlačítka .

    Vzhled pásu karet aplikace Excel

    Klávesová zkratka: Stiskněte CTRL+C.

  3. Vyberte levou horní buňku Oblast pro vložení.

    Tip: Chcete-li výběr přesunout nebo zkopírovat do jiného listu nebo sešitu, klikněte na ouško jiného listu nebo přejděte do jiného sešitu a potom vyberte levou horní buňku oblasti pro vložení.

  4. Na kartě Domů klikněte ve skupině Schránka na Vložit Obrázek tlačítka a potom klikněte na Vložit hodnoty, anebo stiskněte klávesy Alt > Ů > V > H > Enter (ve Windows) nebo Option > Command > V > V > Enter (na počítači Mac).

Pokud chcete porozumět tomu, jak složitý nebo vnořený vzorec počítá konečný výsledek, můžete tento vzorec vyhodnotit.

  1. Vyberte vzorec, který chcete vyhodnotit.

  2. Klikněte na Vzorce > Vyhodnocení vzorce.

    Skupina Závislosti vzorců na kartě Vzorec

  3. Kliknutím na Vyhodnotit prozkoumejte hodnotu podtrženého odkazu. Výsledek vyhodnocení se zobrazí kurzívou.

    Dialogové okno Vyhodnocení vzorce

  4. Pokud podtržená část vzorce tvoří odkaz na jiný vzorec, kliknutím na Krok dovnitř zobrazte další vzorec v okně Vyhodnocení. Kliknutím na Krok ven se vraťte k předchozí buňce a vzorci.

    Tlačítko Krok dovnitř není dostupné při druhém zobrazení odkazu ve vzorci ani když vzorec odkazuje na buňku v jiném sešitu.

  5. Pokračujte, dokud nevyhodnotíte všechny části vzorce.

    Nástroj vyhodnocení vzorce nebude potřeby dozvíte, proč je nefunkční vzorec, ale můžou pomoct poukázat where. Může to být nástroji velmi užitečné v větší vzorce místo, kam ho jinak pravděpodobně obtížné vyhledat problém.

    Poznámky: 

    • Některé části funkcí KDYŽ a ZVOLIT se nevyhodnotí a v okně Vyhodnocení se může zobrazit chyba #N/A.

    • Prázdné odkazy se v okně Vyhodnocení zobrazují jako nulové hodnoty (0).

    • Funkce, které se přepočítávají při každé změně listu. Tyto funkce, mezi něž patří NÁHČÍSLO, POČET.BLOKŮ, INDEX, POSUN, BUŇKA, NEPŘÍMÝ.ODKAZ, ŘÁDKY, SLOUPCE, NYNÍ, DNES a RANDBETWEEN, můžou způsobit, že se v dialogovém okně Vyhodnocení vzorce budou zobrazovat výsledky, které se liší od skutečných výsledků v buňce na listu.

Potřebujete další pomoc?

Kdykoli se můžete zeptat některého odborníka v technické komunitě Excelu, získat podporu v komunitě pro odpovědi, případně navrhnout novou funkci nebo vylepšení ve fóru Excel User Voice.

Viz také

Přehled vzorců v Excelu

Zjišťování chyb ve vzorcích

Funkce Excelu (podle abecedy)

Funkce Excelu (podle kategorie)

Rozšiřte své dovednosti s Office
Projít školení
Získejte nové funkce jako první
Připojte se k účastníkům programu Office Insiders

Byly tyto informace užitečné?

Děkujeme vám za zpětnou vazbu.

Děkujeme vám za váš názor. Vypadá to, že bude užitečné, když vás spojíme s některým z našich agentů z podpory Office.

×