Jak se vyhnout nefunkčním vzorcům

Jak se vyhnout nefunkčním vzorcům

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 nepovažuje to, co zkoušíte udělat, abyste mohli jenom začít znovu.

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

Vrátíte se do buňky s přerušeným vzorcem, který bude v režimu úprav, a Excel zvýrazní místo, kde má problém. Pokud pořád nevíte, co z nich dělat a chcete začít znovu, můžete znovu stisknout klávesu ESC nebo vybrat tlačítko Zrušit na řádku vzorců, které se zobrazí v režimu úprav.

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

Pokud chcete přejít vpřed, následující kontrolní seznam popisuje postup řešení potíží, který vám pomůže zjistit, co se nepovedlo.

Poznámka: Pokud používáteOffice pro web, může se stát, že se nezobrazovaly stejné chyby nebo se řešení nemusí použít.

V Excelu se hodí řada chyb (#), jako je třeba #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? Například #VALUE! chyba způsobuje nesprávné formátování nebo nepodporované datové typy v argumentech argumenty. Nebo uvidíte #REF! Chyba, pokud vzorec odkazuje na buňky, které byly odstraněny nebo nahrazeny jinými daty. Pokyny pro řešení potíží se u jednotlivých chyb liší.

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ů

Podívejte se na některá z následujících témat, která odpovídají chybám, které vidíte:

Při každém otevření tabulky, která obsahuje vzorce odkazující na hodnoty v jiných tabulkách, se zobrazí výzva k aktualizaci odkazů nebo k jejich opuštění.

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

Excel zobrazí dialogové okno nad, abyste měli jistotu, že vzorce v aktuální tabulce vždy odkazují na nejvíce aktualizovanou hodnotu v případě, že se změní referenční hodnota. 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. Chcete-li to provést, přejděte na možnosti > souborů > upřesnit > obecnéa zrušte zaškrtnutí políčka Potvrdit aktualizaci automatických vazeb .

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

Důležité informace: Pokud budete při práci s nefunkčními odkazy ve vzorcích chtít poprvé, že budete potřebovat aktualizační program pro řešení nefunkčních odkazů nebo Pokud nevíte, jestli se mají odkazy aktualizovat, přečtěte si téma řízení při aktualizaci externích odkazů (propojení).

Pokud se nezobrazuje hodnota vzorce, postupujte takto:

  • Ujistěte se, že je Excel nastavený tak, aby zobrazoval vzorce v tabulce. To provedete tak, že vyberete kartu vzorce a ve skupině závislosti vzorců vyberete Zobrazit vzorce.

    Tip: Můžete také použít klávesovou zkratku CTRL + ' (klávesa nad kartou). Když to provedete, budou se sloupce automaticky rozšiřovat, aby se vzorce zobrazily, ale Nemějte obavy, když přepnete zpátky do normálního zobrazení, změní se velikost sloupců.

  • Pokud výše uvedený krok problém nevyřeší, je možné, že se jedná o buňku formátovanou jako text. Můžete na buňku kliknout pravým tlačítkem myši a výběrem možností Formát buněk > Obecný (nebo stisknutím kombinace kláves Ctrl + 1) a následným stisknutím kláves F2 > Enter formát změnit.

  • Pokud máte sloupec s velkou oblastí buněk, která je formátovaná jako text, můžete vybrat oblast, použít formát čísla dle svého výběru a přejít na Data > text do sloupce > dokončit. Tím se formát použije u všech vybraných buněk.

    Obrázek dialogu Data > Text do sloupců

Pokud vzorec nepočítá, musíte zkontrolovat, jestli je v Excelu povolený automatický výpočet. Vzorce se nepočítají, pokud je povolen ruční výpočet. Chcete-li zkontrolovat Automatický výpočet, postupujte podle těchto pokynů.

  1. Vyberte kartu soubor , vyberte Možnostia pak vyberte 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 nastane, když vzorec odkazuje na buňku, ve které se nachází. Oprava znamená přesunout vzorec do jiné buňky nebo změnit syntaxi vzorce tak, aby se předešlo cyklickým odkazům. 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 musíte povolit možnost Odebrat nebo povolit cyklický odkaz.

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

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.

Když napíšete něco jako SUMA (a1: A10), zobrazí Excel jako výsledek vzorce text SUMA (a1: A10) . Pokud zadáte 11/2, Excel zobrazuje datum, jako je třeba 2 – listopadu nebo 11/02/2009, namísto dělení 11 o 2.

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

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) nefunguje, protože jsou k dispozici dvě uzavírací závorky, ale jenom jedna levá závorka. 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: Argumenty funkce uvedené na plovoucím panelu nástrojů s informacemi o funkci najdete pod vzorcem při psaní.

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

Některé funkce, jako třeba SUMA, navíc potřebují výhradně číselné argumenty, zatímco jiné funkce, jako například NAHRADIT, vyžadují minimálně pro jeden ze svých argumentů textovou hodnotu. Pokud používáte nesprávný datový typ, funkce můžou vracet neočekávané výsledky nebo zobrazit chybu #VALUE! .

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

Nezadávejte čísla formátovaná pomocí znaků dolaru ($) nebo desetinných oddělovačů (,) ve vzorcích, protože znaky dolaru označují absolutní odkazy a čárky jsou oddělovače argumentů. Místo zadání $1,000 zadejte ve vzorci 1000.

Pokud v argumentech argumenty použijete formátovaná čísla, dostanete neočekávané výsledky výpočtu, ale může se zobrazit taky Chyba #NUM! . Pokud například zadáte vzorec = ABS (-2 134) , abyste našli absolutní hodnotu-2134, Excel zobrazí #NUM! došlo k chybě, protože funkce ABS může obsahovat pouze jeden argument a jako samostatné argumenty se zobrazuje hodnota-2 a 134.

Poznámka: Výsledek vzorce můžete naformátovat oddělovači tisíců a symboly měny až potom, když zadáte vzorec pomocí neformátovaných čísel (konstant). Obvykle není vhodné vkládat konstanty ve vzorcích, protože je možné, že je obtížné najít, jestli je potřeba aktualizovat později a že jsou náchylnější k nesprávnému psaní. Je velmi lepší vkládat konstanty do buněk, kde jsou v otevřeném 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. Tento problém můžete vyřešit tak, že změníte datový typ buňky z Text na Obecný. Takto:

  1. Vyberte buňku.

  2. Vyberte Domů a kliknutím na šipku rozbalte skupinu číslo nebo číslo (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 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.

Ve vzorci je poměrně běžné používat x jako operátor násobení, ale Excel může přijmout pouze hvězdičku (*) pro násobení. Pokud ve vzorci používáte konstanty, Excel zobrazí chybovou zprávu a vzorec pro vás může opravit nahrazením řetězce x hvězdičkou (*).

Okno se žádostí, abyste u násobení nahradili znak x znakem *

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

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

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

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

Například vzorec = když (ODmocnina (pi ()) <2; "méně než 2!"; "více než 2 !") má 3 úrovně funkcí; funkce PI je vnořená do funkce Sqrt, která je ve funkci kdyžvnořená.

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

Chcete-li například vrátit hodnotu z buňky D3 do listu s názvem čtvrtletní data v sešitu, zadejte: = ' čtvrtletní data '! D3. Bez uvozovek názvu listu zobrazí vzorec #NAME? chyba.

Můžete také vybrat hodnoty nebo buňky v jiném listu a ve vzorci na ně odkazovat. 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í.

Chcete-li například odkazovat na buňky a1 až A8 na listu prodej v sešitě v sešitu Q2, který je otevřený v aplikaci Excel, zadejte: = [Q2 Operations.xlsx] prodej! A1: A8. Bez hranatých závorek se ve vzorci zobrazí chyba #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šším způsobem, jak získat cestu k druhému sešitu, je otevřít druhý sešit, pak z původního sešitu zadat = a použít kombinaci kláves ALT + TAB pro přesunutí do druhého sešitu. Vyberte libovolnou buňku na listu a potom zdrojový sešit zavřete. Váš vzorec se automaticky aktualizuje a bude obsahovat úplnou cestu k souboru a název listu – se správnou syntaxí. Cestu pak můžete případně zkopírovat a vložit ji, kam potřebujete.

Při dělení buňky jinou buňkou, která má nulovou hodnotu (0) nebo žádná hodnota, se zobrazí chyba #DIV/0!.

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

Před odstraněním všech vzorců, které odkazují na data v buňkách, oblastech, definovaných názvech, listech nebo sešitech, vždy zkontrolujte, jestli máte nějaké vzorce. 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ěny nebo nahrazeny jinými daty, a pokud vrátí hodnotu #REF!Vyberte buňku se #REFem. . V řádku vzorců vyberte #REF! a odstraňte ho. Potom znovu zadejte rozsah pro vzorec.

  • Pokud není definován definovaný název a vzorec, který odkazuje na tento název, vrátí #NAME? chyba, definujte nový název, který odkazuje na požadovanou oblast, nebo změňte vzorec tak, aby odkazoval přímo na oblast 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šita zkontrolujte, jestli se data neztratila, a to tak, že nahradíte vzorce, které v chybějícím sešitu odkazují na výsledek vzorce.

Někdy, když kopírujete obsah buňky, chcete vložit jenom hodnotu a ne podkladový vzorec, který se zobrazí v Řádek vzorců.

Můžete například chtít zkopírovat výslednou hodnotu vzorce do buňky na jiném listu. Nebo můžete odstranit hodnoty, které jste použili ve vzorci, po zkopírování výsledné hodnoty do jiné buňky v listu. Obě tyto akce způsobují chybu neplatného odkazu na buňku (#REF!). zobrazí se v cílové buňce, protože buňky obsahující hodnoty, které jste použili ve vzorci, už nejdou odkazovat.

Této chybě se můžete vyhnout tak, že do cílových buněk vložíte výsledné hodnoty vzorců 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 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: Pokud chcete výběr přesunout nebo zkopírovat do jiného listu nebo sešitu, vyberte jinou kartu listu nebo přepně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 Obrázek tlačítka a pak vyberte Vložit hodnoty, nebo stiskněte ALT > E > S > V >příkaz > v > v > zadejte na Macu.

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. Výběr vzorců > vyhodnocení vzorce

    Skupina Závislosti vzorců na kartě Vzorec

  3. Výběrem možnosti vyhodnotit Prozkoumejte hodnotu podtrženého odkazu. Výsledek vyhodnocení se zobrazí kurzívou.

    Dialogové okno Vyhodnocení vzorce

  4. Pokud je podtržená část vzorce odkazem na jiný vzorec, vyberte možnost krok v a zobrazte další vzorec v poli vyhodnocení . Pokud chcete přejít zpátky na předchozí buňku a vzorec, vyberte Krok ven .

    Tlačítko krok v není k dispozici, když se odkaz ve vzorci zobrazuje podruhé, nebo Pokud vzorec odkazuje na buňku v jiném sešitu.

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

    Nástroj vyhodnotit vzorec nebude nutně informovat o tom, proč je vzorec poškozený, ale může pomoci Ukázat, kde. 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á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).

    • Některé funkce 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í na 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)

Potřebujete další pomoc?

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! Pravděpodobně bude užitečné, když vás spojíme s některým z našich agentů podpory Office.

×