Excel má zabudovaný ladicí nástroj pro vzorce a téměř nikdo neví, že tam je

Pravděpodobně jste už zažili chvíli v Excelu, kdy vaše čísla prostě neseděla. Běžným řešením je projít každou buňku jednu po druhé nebo začít své výpočty znovu od začátku. Alespoň jsem si myslel, že to byly jediné možnosti, dokud jsem neobjevil, že Excel má speciální sadu nástrojů právě pro tuto situaci.

Přejděte na kartu Vzorce v desktopové aplikaci Excel, ne ve webové verzi. Mezi funkcemi Knihovna funkcí a Výpočty najdete skupinu nazvanou Kontrola vzorců. Je snadné ji přehlédnout, ale uvnitř je sedm nástrojů, které vám mohou pomoci diagnostikovat poškozené vzorce a zjistit přesně, kde se stala chyba.

Sledovat předchůdce a sledovat následníky

Zjistěte, odkud vaše čísla pocházejí a kam směřují

Tyto dvě funkce jsou přirozeným výchozím bodem. Trace Precedents vykresluje vizuální šipky z každé buňky, která přispívá daty do vámi vybrané formule, takže pokud vaše formule v buňce G5 čerpá hodnoty z B2, D3 a F4, uvidíte čáry je spojující. Trace Dependents funguje opačně: vyberte buňku, klikněte na tlačítko a Excel zobrazí každou formuli jinde na listu, která se spoléhá na hodnotu této buňky.

Barevné kódování činí obě funkce intuitivní. Modré šipky znamenají, že vše v daném řetězci je bez chyb, zatímco červené šipky ukazují na zdroj problému. Ve většině případů můžete jednoduše sledovat červenou čáru, abyste našli problém.

Pokud šipka ukazuje na ikonu listu místo na buňku, odkaz se nachází na jiném listu nebo sešitu, přičemž tento sešit musí být otevřený, aby ho Excel mohl sledovat. Můžete také stále kliknout na libovolné tlačítko, abyste odhalili další vrstvy a posunuli se každým kliknutím hlouběji do řetězce závislostí.

Až budete hotovi, klikněte na Odstranit šipky, abyste odstranili čáry z vašeho listu. Pokud chcete být výběrovější, rozbalte rozevírací nabídku a odstraňte pouze šipky předchůdců nebo následníků.

Zobrazit vzorce

Přepněte z výsledků na syntaxi, abyste odhalili nesrovnalosti

Tato funkce je další jednoduchá, ale užitečná. Když kliknete na Zobrazit vzorce (nebo stisknete Ctrl + `), každá buňka obsahující vzorec zobrazí syntaxi vzorce místo vypočítaného výsledku. Najdu to obzvláště užitečné pro dvě věci: rychle odhalit nesrovnalosti v řadě vzorců a zachytit buňky, kam někdo zadal hodnotu přímo místo použití správného vzorce. Například, pokud jedna buňka ukazuje prosté číslo, zatímco okolní buňky zobrazují vzorec SUM, budete vědět, že něco není v pořádku.

Jakmile potvrdíte, že syntaxe je správná, klikněte znovu na Zobrazit vzorce nebo použijte stejnou klávesovou zkratku k přepnutí všeho zpět do normálního režimu.

Kontrola chyb

Vestavěné kontroly pro poškozené odkazy, kruhovou logiku a zjevné chyby

Menu Kontrola chyb obsahuje tři nástroje, z nichž každý je navržen pro řešení jiného typu poškozeného vzorce. Hlavní dialog Kontrola chyb funguje trochu jako kontrola pravopisu - prohledává celý list kvůli běžným chybám ve vzorcích Excelu, jako jsou #DIV/0! nebo #REF!, a provází vás jimi jeden po druhém. Když najde problémy, budete mít možnosti je opravit, projít je krok za krokem nebo ignorovat.

Chyba trasování je cílenější funkce. Pokud konkrétní buňka již ukazuje chybu, Excel nakreslí červené šipky přes řetězec výpočtu, aby ukázal přesně, kde špatná data vstoupila do rovnice. Cyklické odkazy mezitím hledají buňky, které odkazují na sebe samy, buď přímo, nebo přes řetězec jiných buněk, a umožňují vám skočit přímo na problematický odkaz a rozplést ho.

Vyhodnotit vzorec

Sledujte, jak Excel rozkládá složité vzorce krok za krokem

Pokud je v této sadě nástrojů jeden nástroj, který je mým nejoblíbenějším, je to Vyhodnotit vzorec. Tato funkce je pravděpodobně nejbližší věcí k opravdovému krok-za-krokem ladicími nástrojem, který Excel má. Vyberte buňku obsahující XLOOKUPy, složité IF příkazy, vnořené funkce nebo jiné pokročilé vzorce, klikněte na Vyhodnotit vzorec a otevře se dialogové okno, které vás provede výpočtem krok za krokem.

Uvnitř dialogového okna se jedna část vzorce zobrazuje podtržená. Ta podtržená část je část, kterou se Excel chystá vyhodnotit. Pokaždé, když kliknete na Vyhodnotit, vybraná část se přemění na výsledek zobrazený kurzívou, zatímco Excel přejde k dalšímu kroku výpočtu. Pokračujte v klikání a nakonec se dostanete k finálnímu výsledku.

Když je k dispozici, můžete také použít Krok dovnitř k prozkoumání odkazované buňky nebo podvzorce a Krok ven k návratu k nadřazenému vzorci.

Okno sledování

Udržujte důležité hodnoty viditelné, aniž byste přecházeli mezi listy

Poslední nástroj ve skupině Kontrola vzorců je určen pro velké sešity s více listy. Okno sledování otevírá plovoucí panel nástrojů, kde můžete přidat libovolné buňky, které chcete sledovat, bez ohledu na to, na kterém listu se v sešitu nacházejí. Můžete dokonce sledovat buňky z úplně jiného sešitu.

Každý záznam zobrazuje adresu buňky, aktuální hodnotu a vzorec v jednom panelu, a okno sledování zůstává viditelné i při přesouvání mezi listy. Takže pokud upravujete vstupy na listu 1 a potřebujete vidět, jak se celkový součet na listu 5 mění v reálném čase, nemusíte stále přepínat mezi listy. Místo toho můžete všechno sledovat z jednoho místa. Také můžete na jakýkoli záznam v okně sledování dvojklikem přejít přímo na danou buňku, kdykoli potřebujete.

Když Excel začne fungovat podivně, začněte zde

Pravděpodobně nebudete schopni rozplést každý poškozený vzorec pouze pomocí skupiny Kontrola vzorců. Přesto vám tyto nástroje mohou ušetřit hodiny klikání mezi buňkami a doufání, že náhodou narazíte na problém. Existují z nějakého důvodu: Excel byl vytvořen pro práci s komplexností a komplexnost potřebuje odpovídající nástroje pro ladění.

Příště, až se sešit bude chovat zvláštně, přejděte na kartu Vzorce a otevřete skupinu Kontrola vzorců. Místo toho, abyste hádali, kde se stala chyba, můžete nechat Excel ukázat, co už ví.