Vzorce v Excelu se nepřepočítávají automaticky? Opravte to ve 3 jednoduchých krocích

Pokud se vzorce v Excelu neaktualizují, když změníte čísla, na kterých závisí, nejprve zkontrolujte režim výpočtu. Ve většině případů, kdy je mnoho vzorců zaseknutých na starých hodnotách, je nejrychlejším řešením Vzorce > Možnosti výpočtu > Automatický. Microsoft uvádí Automatický jako výchozí nastavení výpočtu v Excelu; ruční výpočet říká Excelu, aby čekal, dokud ručně nespustíte přepočet.

Toto rychlé řešení je vhodné, pokud se sešit dříve počítal normálně a nyní se několik součtů, procent, vyhledávání nebo jiných závislých vzorců po úpravě zdrojových buněk nezmění. Pokud se chová špatně pouze jeden vzorec, nebo buňka doslova zobrazuje něco jako =B2*C2 místo výsledku, přejděte rovnou ke kroku 3, protože problém může být v samotné buňce, nikoliv v režimu výpočtu sešitu.

Proč Excel přestane automaticky přepočítávat vzorce?

Excel má několik režimů výpočtu. V režimu Automatický se závislé vzorce přepočítají, když se změní příslušné hodnoty, vzorce nebo názvy. V režimu Ruční Excel čeká na příkaz k ručnímu výpočtu. Microsoft také poskytuje režim, který vylučuje datové tabulky z běžného automatického přepočtu, což může být důležité v sešitech používajících datové tabulky analýzy Co-když.

Na stolní verzi Excelu pro Windows Microsoft uvádí, že změna možností výpočtu ovlivňuje všechny otevřené sešity. Ve webové verzi Excelu se možnost výpočtu vztahuje pouze na aktuální sešit. Tento rozdíl je důležitý, pokud se zdá, že sešit během relace na desktopu přebírá neočekávané chování.

Existuje další třída problémů, která vypadá podobně, ale nejedná se o problém s režimem výpočtu. Vzorec může být uložen jako text, ve listu může být zapnuta možnost Zobrazit vzorce, vzorec mohl být přepsán pevnou hodnotou nebo může obsahovat chybu. Níže uvedený tříkrokový proces tyto případy rozlišuje, místo aby považoval každý příznak za stejný problém.

Krok 1: Nastavte výpočet sešitu na Automatický

Začněte zde, pokud se několik vzorců po změně vstupních buněk neaktualizuje.

1. Vyberte kartu Vzorce.
2. Zvolte Možnosti výpočtu.
3. Vyberte Automatický.

Nastavení můžete najít také v Excelu pro Windows prostřednictvím Soubor > Možnosti > Vzorce, kde se podívejte do sekce Možnosti výpočtu a Výpočet sešitu. Současná dokumentace Microsoftu popisuje režim Automatický jako nastavení, které přepočítává závislé vzorce pokaždé, když se změní příslušná hodnota, vzorec nebo název.

Příklad: Předpokládejme, že buňka D2 obsahuje =B2*C2. B2 je množství a C2 je jednotková cena. Pokud se B2 změní z 10 na 12, D2 by se měla aktualizovat bez nutnosti další akce, pokud automatický výpočet funguje normálně.

Kdy tento krok pravděpodobně vyřeší problém: Mnoho výsledků vzorců je zastaralých, stisknutí F9 je aktualizuje, nebo sešit, který se dříve počítal okamžitě, nyní čeká na ruční zásah.

Kdy to nemusí stačit: Je ovlivněna pouze jedna buňka, vzorec je viditelný jako text, buňka zobrazuje chybu Excelu, jako je #HODNOTA! nebo #ODKAZ!, nebo výsledek závisí na externím sešitu nebo datovém připojení, které se neobnovilo.

Pro úplné vysvětlení dostupných režimů výpočtu a jejich rozsahu od Microsoftu viz Podpora Microsoftu: změna přepočtu vzorců, iterací nebo přesnosti v Excelu.

Krok 2: Vynutit přepočet a sledovat, co se změní

Po přepnutí na Automatický proveďte jeden čistý přepočet. To je užitečné, protože sešit může obsahovat hodnoty, které byly vypočítány, zatímco byl aktivní režim Ruční.

Na kartě Vzorce vyberte Přepočítat nyní. Microsoft také dokumentuje tyto užitečné klávesové zkratky:

• F9 přepočítá vzorce, které se změnily od předchozího výpočtu, plus vzorce, které na nich závisí, napříč otevřenými sešity.
• Shift+F9 přepočítá změněné vzorce a závislé vzorce v aktivním listu.
• Ctrl+Alt+F9 přepočítá všechny vzorce ve všech otevřených sešitech, bez ohledu na to, zda si Excel myslí, že se změnily, nebo ne.
• Ctrl+Shift+Alt+F9 znovu zkontroluje závislosti a poté přepočítá všechny vzorce ve všech otevřených sešitech.

Pro běžný sešit začněte s Přepočítat nyní nebo F9. Použijte Ctrl+Alt+F9, pokud běžný přepočet neobnoví sešit, který by měl být plně řízen vzorci. Zkratka pro znovuvytvoření závislostí je spíše nástroj pro řešení problémů než rutinní příkaz.

Nyní proveďte jednoduchý test. Změňte jednu zřejmou vstupní hodnotu používanou ve vzorci. Například pokud =B2*C2 aktuálně vrací 150 $ z množství 10 a jednotkové ceny 15 $, změňte B2 na 12. V režimu Automatický by se výsledek měl okamžitě změnit na 180 $.

Pokud k tomu dojde, výpočetní engine funguje znovu. Pokud F9 změní výsledek, ale pozdější úprava ne, znovu zkontrolujte krok 1, protože sešit nebo relace na desktopu mohou být stále v neautomatickém režimu. Pokud se nic nezmění, přejděte ke kroku 3.

Krok 3: Opravte buňku se vzorcem, pokud je problém lokální

Pokud většina vzorců funguje, ale jeden nebo několik ne, zkontrolujte tyto buňky přímo. Microsoft uvádí dvě obzvláště běžné příčiny, když vzorec zobrazuje svou syntaxi místo vypočtené hodnoty: může být povoleno Zobrazit vzorce nebo může být buňka formátována jako Text.

Pokud Excel zobrazuje vzorec místo výsledku

Nejprve otevřete kartu Vzorce a zkontrolujte Zobrazit vzorce. Pokud je zapnuto, vypněte ho. Klávesová zkratka Ctrl+` přepíná toto zobrazení.

Pokud se vzorec stále zobrazuje jako text, vyberte ovlivněnou buňku a změňte její číselný formát z Text na Obecný. Microsoft pak doporučuje stisknout F2 a následně Enter, aby Excel znovu vložil obsah jako vzorec. Pouhá změna zobrazeného číselného formátu nemusí sama o sobě převést již zadaný textový vzorec.

Pokud buňka obsahuje chybu nebo pevnou hodnotu

Klikněte na buňku a zkontrolujte řádek vzorců. Potvrďte, že obsah skutečně začíná znakem = a stále obsahuje zamýšlený vzorec. Vzorec může být náhodně nahrazen vloženou hodnotou, což znamená, že pro Excel nezůstalo nic k přepočtu.

Pokud buňka zobrazuje chybu, jako je #HODNOTA!, #ODKAZ! nebo #NÁZEV?, automatický výpočet pravděpodobně funguje; vzorec nebo jeho vstupy potřebují opravu. Textová hodnota tam, kde je očekáváno číslo, smazaný odkaz nebo překlep v názvu funkce mohou všechny způsobit chyby, i když je režim výpočtu správný.

Stránka řešení problémů Microsoftu poskytuje oficiální postupy pro Zobrazit vzorce i pro buňky formátované jako Text: Podpora Microsoftu: jak se vyhnout poškozeným vzorcům v Excelu.

Jak ověřit, že oprava skutečně fungovala

Nekončete poté, co uvidíte, že se aktuální výsledek jednou změnil. Ověřte automatické chování pomocí kontrolované úpravy:

1. Vyberte jednoduchý vzorec, jehož vstupy jsou snadno identifikovatelné.
2. Zapište si aktuální výsledek.
3. Změňte jednu vstupní hodnotu.
4. Potvrďte, že se výsledek vzorce okamžitě změní bez stisknutí F9 nebo Přepočítat nyní.
5. Pokud je nutné, vraťte testovací hodnotu zpět a potvrďte, že se vzorec vrátí do původního stavu.

Pokud se vzorec okamžitě aktualizuje, původní problém „vzorec v Excelu se nepřepočítává automaticky“ je pro danou cestu sešitu vyřešen. V novějších sestaveních Microsoft 365 může Microsoft také označit výsledek vzorce jako zastaralý, když se podkladová data změnila, ale přepočet ještě neproběhl, zejména v režimech Ruční nebo Částečný výpočet. Indikátor zastaralosti je nápovědou, že zobrazená hodnota by ještě neměla být považována za aktuální. Viz Podpora Microsoftu: formátování zastaralých hodnot pro aktuální chování.

Co když je automatický výpočet zapnutý, ale sešit je stále špatný?

V tom okamžiku nemusí být problém samotný automatický výpočet. Zkontrolujte podmínky kolem vzorce.

• Odkazy na externí sešity: vzorec může počítat správně, ale stále používat starou hodnotu ze zdrojového sešitu, který se neobnovil.
• Datové tabulky analýzy Co-když: pokud sešit používá možnost, která vylučuje datové tabulky z automatického přepočtu, běžné vzorce a datové tabulky se mohou chovat odlišně.
• Kruhové odkazy: vzorec, který se nakonec odkazuje sám na sebe, je jiný výpočetní problém. Nezapínejte iterativní výpočet pouze proto, abyste odstranili varování, pokud model záměrně nevyžaduje iteraci a nerozumíte nastavením konvergence.
• Chyby ve vzorcích: chybný výsledek naznačuje, že Excel něco vypočítal; opravte vzorec nebo jeho vstupy, místo abyste opakovaně vynucovali přepočet.
• Hodnoty vložené přes vzorce: automatický režim nemůže obnovit vzorec, který byl nahrazen konstantou. Obnovte ho ze sousední buňky, historie verzí, zálohy nebo původní logiky sešitu.
• Datová připojení a dotazy: obnovení externích dat je oddělené od přepočtu vzorců na listu. Vzorec může být plně přepočítán proti datům, která jsou sama o sobě zastaralá.

Rychlá rozhodovací příručka

Tříkroková oprava za jednu minutu

Pro běžný případ je řešení přímočaré: 1) nastavte Vzorce > Možnosti výpočtu > Automatický; 2) spusťte Přepočítat nyní nebo jednou stiskněte F9; 3) pokud jednotlivé buňky stále selhávají, zkontrolujte Zobrazit vzorce, formátování Text a samotný vzorec.

Důležitý je rozdíl v rozsahu. Selhání celého sešitu ukazuje nejprve na režim výpočtu. Selhání jedné buňky ukazuje nejprve na vzorec nebo formátování buňky. Použití tohoto rozlišení zabraňuje zbytečnému ladění a usnadňuje určit, zda je výpočetní engine Excelu skutečně problém.