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 nemůže pochopit, co chcete udělat, takže možná budete chtít začít znovu.

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

Vrátíte se do buňky s nefunkční vzorcem, která bude v režimu úprav, a Excel zvýrazní místo, kde je problém. Pokud pořád nevíte, co udělat a chcete začít znova, můžete znovu stisknout ESC nebo vybrat tlačítko Zrušit v řádku vzorců a tím ukončit režim úprav.

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

Pokud se chcete posunout dál, následující kontrolní seznam obsahuje kroky pro řešení potíží, které vám pomůžou zjistit, co by se pokazilo.

Poznámka: Pokud používáteOffice pro web, nemusí se stejné chyby zobrazit nebo nemusí platit řešení.

Excel vyvolá různé chyby libry (#), jako je třeba #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? a #NULL!, podle toho, že něco ve vzorci nefunguje správně. Příklad: #VALUE! způsobená nesprávným formátováním nebo nepodporovanými datovými typy v argumentech. Nebo uvidíte hlavní #REF! v případě, že vzorec odkazuje na buňky, které byly odstraněné nebo nahrazené jinými daty. Pokyny k řešení potíží se pro každou chybu 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 kterékoli z následujících témat odpovídajících chybě s křížkou, která se zobrazí:

Pokaždé, když otevřete tabulku, která obsahuje vzorce odkazující na hodnoty v jiných tabulkách, zobrazí se výzva k aktualizaci odkazů nebo jejich nechat, jak jsou.

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

Excel zobrazí výše uvedené dialogové okno, aby se ujistil, že vzorce v aktuální tabulce vždy odkazují na nejktualovanou hodnotu v 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. Pokud to chcete udělat, přejděte na Možnosti > > Advanced > General(Upřesnit) a zrušte zaškrtnutí políčka Ask to update automatic links (Aktualizovat automatické propojení).

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

Důležité informace: Pokud nefunkční odkazy řešíte poprvé, pokud si potřebujete osvěžit řešení nefunkčních odkazů nebo nevíte, jestli máte odkazy aktualizovat, podívejte se na ovládací prvek, když se aktualizují externí odkazy (propojení).

Pokud se nezobrazuje hodnota vzorce, postupujte takto:

  • Zkontrolujte, jestli je Excel nastavený tak, aby v tabulce zobrazovat vzorce. Pokud to chcete udělat, vyberte kartu Vzorce a ve skupině Auditování vzorců vyberte Zobrazit vzorce.

    Tip: Můžete taky použít klávesovou zkratku Ctrl+' (klávesa nad klávesou Tab). Když to použijete, sloupce se automaticky šířou, aby se vzorce zobrazí, ale nemusíte mít obavy, když přepínáte zpátky do normálního zobrazení, změní se velikost sloupců.

  • Pokud problém nevyřeší ani krok výše, je možné, že je buňka naformátovaná 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 rozsahem buněk, které jsou formátované jako text, můžete vybrat oblast, použít formát čísla podle 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ů

Když se vzorec nepřepočítá, budete muset zkontrolovat, jestli je v Excelu povolený automatický přepočet. Pokud je povolený ruční přepočet, vzorce se nepřepočítávají. Ke kontrole automatického výpočtu použijte tento postup:

  1. Vyberte kartu Soubor, vyberte Možnostia potom 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 tématu Změna přepočtu, iterace nebo přesnosti vzorce.

Cyklický odkaz nastane, když vzorec odkazuje na buňku, ve které se nachází. Opravíte to tak, že vzorec přesunete do jiné buňky nebo změníte syntaxi vzorce na syntaxi, která zabrání cyklických 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ém případě budete muset povolit funkci 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),Excel místo výsledku vzorce zobrazí textový řetězec SUMA(A1:A10). Pokud napíšete datum 2. 11.2009, excel místo dělení 11 krát 2 zobrazí datum, například 2.11.2009 nebo 11.11.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ží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ě závorky, ale jenom jednu počáteční 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 odkazy na funkci zobrazí argumenty funkce.

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žijete nesprávný datový typ, můžou funkce vrátit neočekávané výsledky nebo zobrazit #VALUE!.

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

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

Pokud v argumentech použijete formátovaná čísla, získáte neočekávané výsledky výpočtů, ale může se také zobrazit chyba #NUM!. Pokud například zadáte vzorec =ABS(-2;134) pro vyhledání absolutní hodnoty hodnoty -2134, zobrazí Excel #NUM! , protože funkce ABS akceptuje jenom jeden argument a jako samostatné argumenty se zobrazí -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). Obecně není vhodné zamisovat konstanty do vzorců, protože se těžko najdou, když je budete později potřebovat aktualizovat, a je větší, když je budete začíná s nesprávným psaním. Mnohem lepší je umístit konstanty do buněk, kde jsou otevřené a snadno se na ně odkazují.

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 Formátčísla nebo čísla (nebo stiskněte Ctrl + 1). Potom 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.

Jako operátor násobení se ve vzorci celkem běžně používá x, ale Excel může hvězdičku (*) použít jenom u násobení. Pokud ve vzorci použijete konstanty, Excel zobrazí chybovou zprávu a vzorec může opravit tak, že x nahradí 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Ž(ODCHYT(PI())<2;"Méně než dvě!";"Více než dvě!") má 3 úrovně funkcí; Funkce PI je vnořená uvnitř funkce SQRT,která je 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 chcete třeba vrátit hodnotu z buňky D3 listu s názvem Čtvrtletní data v sešitu, zadejte ='Čtvrtletní data'! D3. Bez uvozovek okolo názvu listu vzorec zobrazí #NAME?.

Můžete také vybrat hodnoty nebo buňky v jiném listu a odkazovat na ně ve vzorci. 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 třeba chcete odkazovat na buňky A1 až A8 na listu Prodej v sešitu Operace Q2, který je otevřený v Excelu, zadejte =[Operace Q2 Operations.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: Cestu k jinému sešitu získáte nejsnadněji tak, že druhý sešit otevřete, potom v původním sešitu stisknete =, stisknutím Alt+Tab se posunete do druhého sešitu. Vyberte na listu libovolnou buňku a pak 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.

Výsledkem dělení buňky jinou buňkou, která obsahuje nulu (0) nebo nemá žádnou hodnotu, je 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).

Než cokoli odstraníte, vždycky zkontrolujte, jestli nemáte nějaké 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 pokud vrátí chybu #REF!,vyberte buňku s #REF! . Na řádku vzorců vyberte #REF! a odstraňte ho. Potom znovu zadejte oblast pro vzorec.

  • Pokud chybí definovaný název a vzorec, který na tento název odkazuje, vrátí chybu #NAME?, definujte nový název, který odkazuje na oblast, kterou chcete použít, nebo změňte vzorec tak, aby odkaz měl přímo na oblast buněk (třeba 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 potom se ujistěte, že se neztratí tato data, nahrazením vzorců, které odkazují na chybějící sešit, výsledky vzorců.

Někdy můžete při kopírování obsahu buňky vložit jenom hodnotu a ne podkladový vzorec, který se zobrazí v Řádek vzorců.

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

Této chybě se můžete vyhnout vložením výsledných hodnot vzorců (bez vzorce) do cílových buněk.

  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 oušek jiných listů nebo přejděte do jiného sešitu a pak vyberte levou horní buňku oblasti pro vložení.

  4. Na kartě Domů ve skupině Schránka vyberte Vložit Obrázek tlačítkaa pak vyberte Vložit hodnoty nebo stiskněte Alt > E > S > > Enter pro Windows nebo Option > Command > V > V > Enter 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. Vyberte vzorce > vyhodnocení vzorce.

    Skupina Závislosti vzorců na kartě Vzorec

  3. Vyberte Vyhodnotit a prozkoumejte hodnotu podtržených odkazů. Výsledek vyhodnocení se zobrazí kurzívou.

    Dialogové okno Vyhodnocení vzorce

  4. Pokud podtržená část vzorce obsahuje odkaz na jiný vzorec, zobrazte další vzorec v okně Vyhodnocení výběrem možnosti Krok do. Výběrem možnosti Vystoupit se vrátíte k předchozí buňce a vzorci.

    Tlačítko Krok v není dostupné při druhém zobrazení odkazu ve vzorci – nebo pokud 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í nutně říct, proč je vzorec nefunkční, může vám ale pomoct zjistit, kde ho najdete. 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á 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?

Kdykoliv se můžete zeptat některého odborníka v technické komunitě Excelu nebo získat podporu v komunitě pro odpovědi.

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é znalosti a dovednosti
Projít školení
Získejte nové funkce jako první
Připojit se k programu Microsoft Insider

Byly tyto informace užitečné?

Jak jste spokojeni s kvalitou překladu?
Co ovlivnilo váš názor?

Děkujeme za váš názor!

×