Jak se vyhnout nefunkčním vzorcům v Excelu

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

To bohužel znamená, že Excel nerozumí tomu, co se pokoušíte udělat, takže budete muset vzorec aktualizovat nebo se ujistit, že funkci používáte správně.

Vraťte se do buňky s nefunkčním vzorcem, která bude v režimu úprav, a Excel zvýrazní místo, kde našel nějaký problém. Pokud ani tady nevíte, co udělat pro to, aby vzorec fungoval, a chcete začít znova, můžete znovu stisknout klávesu ESC nebo vybrat tlačítko Zrušit v řádku vzorců. Tím ukončíte režim úprav.

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 mohlo být špatně. Další informace získáte výběrem nadpisů.

Používáte ve vzorci správné oddělovače seznamu?

Vzorce s více argumenty používají k oddělení argumentů oddělovače seznamů. Použitý oddělovač se může lišit v závislosti na nastavení národního prostředí operačního systému a Excelu. Nejběžnějšími oddělovači seznamu jsou čárka (,) a středník (;).

Vzorec se přeruší, pokud některá z jeho funkcí používá nesprávné oddělovače.

Další informace najdete tady: Chyby ve vzorcích, když není správně nastavený oddělovač seznamu

Zobrazuje se vám ve vzorcích Excelu chyba s křížkem (#)?

Excel hází různé chyby křížků (#), jako jsou chyby #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? a #NULL! Pokud chcete naznačit, že něco ve vzorci nefunguje správně. Třeba příčinou chyby #HODNOTA! jsou nesprávné formátování nebo nepodporované datové typy v argumentech. Chyba #ODKAZ! se zase zobrazuje, když vzorec odkazuje na buňky, které jsou odstraněné nebo nahrazené jinými daty. U každé chyby se budou postupy pro řešení potíží lišit.

Podívejte se na některé z následujících témat odpovídajících chybě kříže, které se zobrazuje:

• Oprava chyby #ČÍSLO! chyba
• Oprava chyby #HODNOTA! chyba
• Oprava chyby #NENÍ_K_DISPOZICI
• Oprava chyby #DĚLENÍ_NULOU! #BUSY!
• Oprava chyby #ODKAZ! #BUSY!
• Oprava chyby #NÁZEV?
• Oprava chyby #NULL! chyba

Oprava přerušených odkazů ve vzorcích v Excelu

Pokaždé když otevřete tabulku, která obsahuje vzorce odkazující na hodnoty v jiných tabulkách, zobrazí se dotaz, jestli chcete odkazy aktualizovat nebo je nechat, jak jsou.

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 Možnosti > souboru > Rozšířeně > obecné a zrušíte zaškrtnutí políčka Potvrdit aktualizaci automatických propojení.

Formula shows syntax instead of value in Excel

Pokud se nezobrazuje hodnota vzorce, postupujte takto:

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

  Tip:

  Můžete taky použít klávesovou zkratku Ctrl + ` (klávesa nad klávesou Tab – při anglickém rozložení klávesnice). Když to uděláte, sloupce se automaticky rozšíří a zobrazí se v nich vzorce. Nemějte ale obavy, když přepnete zpátky na normální zobrazení, šířka sloupců se zase zmenší.

• Pokud se problém nepovede vyřešit ani podle předchozího kroku, je možné, že je buňka naformátovaná jako text. Můžete na buňku kliknout pravým tlačítkem myši a vybrat Formát buněk > – obecný (nebo Ctrl + 1) a pak stisknutím F2 > Enter formát změnit.

• Pokud máte sloupec s velkou oblastí buněk, které jsou naformátované jako text, můžete oblast vybrat, použít číselný formát podle svých potřeb a pak přejít na Data > a text do dokončení sloupce>. Tím se formát použije u všech vybraných buněk.

  

Povolení automatického výpočtu v sešitu, pokud se vzorec nepřepočítává v Excelu

Když se vzorec nepřepočítá, musíte zkontrolovat, jestli je v Excelu povolený automatický přepočet. Pokud je povolený ruční přepočet, vzorce se nepřepočítávají. Nastavení automatického přepočtu zkontrolujete takto.

1. Vyberte kartu Soubor, vyberte Možnosti a pak vyberte kategorii Vzorce.

2. V části Možnosti výpočtů v části Výpočet sešitu zkontrolujte, že je vybraná možnost Automaticky .

   

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

Ve vzorci je jeden nebo víc cyklických odkazů

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ých případech budete muset povolit odebrání nebo povolení cyklického odkazu.

Další informace o cyklických odkazech najdete v článku Odebrání nebo povolení cyklického odkazu.

Začíná funkce znakem rovnítka (=)?

Pokud zadání nezačíná symbolem rovná se, nejedná se o vzorec a nepočítá se, což je častá chyba.

Když do buňky zadáte třeba SUMA(A1:A10), Excel místo výsledku vzorce zobrazí textový řetězec SUMA(A1:A10). Pokud případně napíšete 11/2, místo toho, aby zobrazil výsledek dělení jedenácti dvěma, zobrazí Excel datum, třeba 11. 2. 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.

Jsou levé a pravé závorky párované?

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

Obsahuje syntaxe všechny povinné argumenty?

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

Některé funkce, jako třeba SUM, navíc vyžadují jenom číselné argumenty, zatímco jiné funkce, jako třeba NAHRADIT, vyžadují minimálně pro jeden ze svých argumentů textovou hodnotu. Pokud jako argument použijete nesprávný datový typ, můžou funkce vrátit neočekávané výsledky nebo zobrazit chybu #HODNOTA!.

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

Práce s neformátovanými čísly ve vzorcích v Excelu

Nezadávejte ve vzorcích čísla formátovaná znaky dolaru ($) nebo oddělovači desetinných míst (,), 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žijete formátovaná čísla v argumentech, zobrazí se neočekávané výsledky výpočtu, ale může se také zobrazit chyba #NUM! . Pokud například zadáte vzorec =ABS(-2,134) pro vyhledání absolutní hodnoty -2134, Excel zobrazí chybu #NUM!, protože funkce ABS přijímá pouze jeden argument a vidí argumenty -2 a 134 jako samostatné argumenty.

Mají odkazované buňky správný datový typ?

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. Vyberte Domů a výběrem šipky rozbalte skupinu Číslo nebo Formát čísla (nebo stiskněte Ctrl+1). Pak vyberte 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, které zadáte 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.

Pokoušíte se násobit bez zadání symbolu *?

Jako operátor násobení ve vzorci je poměrně běžné používat x, ale Excel může pro násobení přijmout jenom hvězdičku (*). Když ve vzorci použijete konstanty, Excel zobrazí chybovou zprávu a vzorec vím opraví tak, že x nahradí symbolem hvězdičky (*).

Pokud ale použijete odkazy na buňky, Excel vrátí #NAME? chyba.

Je text ve vzorcích uzavřený do uvozovek?

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

Je ve vzorci víc než 64 funkcí?

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

Například vzorec =KDYŽ(ODMOCNINA(PI())<2;"Méně než dvě!";"Více než dvě!") má 3 úrovně funkcí; Funkce PI je vnořená uvnitř funkce ODMOCNINA, která je zase vnořená uvnitř funkce KDYŽ.

Jsou názvy listů uzavřené do jednoduchých uvozovek?

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 kolem názvu listu vzorec zobrazí chybu #NAME?.

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

Oprava cest externích sešitů ve vzorcích Excelu

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: =[Q2 Operations.xlsx]Sales! Odpověď č. 1:A8. Bez hranatých závorek vzorec zobrazí chybu #REF!.

Pokud sešit není v Excelu otevřený, zadejte úplnou cestu k souboru.

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

Dělíte číselné hodnoty nulou?

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. Můžete použít:

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

Odkazuje vzorec na odstraněná data?

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, přeč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!. . Na řádku vzorců vyberte chybu #REF! a odstraňte ji. 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. Vyberte Zrušit a potom zkontroluje, že se neztratila data, když jste nahradili vzorce odkazující na chybějící sešit výsledky vzorců.

Zkopírovali jste a vložili do tabulky buňky, které jsou přidružené ke vzorci?

Někdy při kopírování obsahu buňky chcete vložit jenom hodnotu a ne příslušný vzorec, který se zobrazuje v řá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 (#REF!), 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ů ve skupině Schránka vyberte Kopírovat .
   
   Klávesová zkratka: Stiskněte CTRL+C.

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

   Tip:

   Chcete-li výběr přesunout nebo zkopírovat do jiného listu nebo sešitu, vyberte 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ů ve skupině Schránka vyberte Vložit a pak vyberte Vložit hodnoty nebo stiskněte Alt > ES > S > V > Enter pro Windows nebo příkaz Option >> V > V > Enter na Macu.

Pokud máte vnořený vzorec, vyhodnoťte ho po jednotlivých krocích

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. Vyberte vzorce,>vyhodnoťte vzorec.
   

3. Výběrem Vyhodnotit prozkoumejte hodnotu podtrženého odkazu. Výsledek vyhodnocení se zobrazí kurzívou.
   

4. Pokud podtržená část vzorce tvoří odkaz na jiný vzorec, výběrem Krok dovnitř zobrazte další vzorec v okně Vyhodnocení. Výběrem Vystoupit 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 vám nemusí vždycky říct, proč je vzorec nefunkční, může vám ale pomoct zjistit, kde by mohl být problém. Může být velice užitečný ve větších vzorcích, kde by v opačném případě mohlo být obtížné ho najít.

   Poznámka

   • Některé části funkcí KDYŽ a ZVOLIT se nevyhodnotí a v poli Vyhodnocení se může objevit chyba #N/A.
   • Prázdné odkazy se v okně Vyhodnocení zobrazují jako nulové hodnoty (0).
   • Některé 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 odborníka z technické komunity Excelu nebo získat podporu v komunitách.

Viz také

Přehled vzorců v Excelu

Nápověda & výuka k Excelu