7 tipů pro práci se vzorci Excelu

Několik prvků vám může pomoci být co nejproduktivnější při psaní a opravách vzorců aplikace Excel. Můžete zobrazit všechny své vzorce najednou a opravit chyby jednu po druhé. Při psaní funkcí můžete použít průvodce doplňkem.

Priorita hlavního operátora

Jedním z nejdůležitějších faktorů při psaní vzorců je správné uvedení operátorů, a tím nemyslím operátory telefonních společností. To má co do činění s matematickými operátory – víte, s malými detaily, jako jsou znaménka plus a znaménka pro násobení a kam jdou závorky. Priorita operátorů — pořadí, ve kterém jsou operace prováděny — může mít velký rozdíl ve výsledku. Máte snadný způsob, jak udržet prioritu operátora v pořádku. Jediné, co si musíte zapamatovat, je "Prosím, omluvte moji drahou tetu Sally."

Tato fráze je mnemotechnická pomůcka pro následující:

• Závorky

• Exponenty

• Násobení

• Divize

• Přidání

• Odčítání

Závorky tedy mají první (nejvyšší) prioritu a odčítání má poslední prioritu. Abych byl upřímný, násobení má stejnou prioritu jako dělení a sčítání má stejnou prioritu jako odčítání, ale rozumíte tomu!

Například vzorec =1 + 2 × 15 se rovná 31. Pokud si myslíte, že by se měl rovnat 45, jděte raději za tetou! Odpověď se rovná 45, pokud uvedete závorky, například: =(1 + 2) × 15.

Správné pořadí operátorů je rozhodující pro dobrý stav vašeho listu. Excel generuje chybu, když se čísla otevřených a uzavřených závorek neshodují, ale pokud chcete před násobením sečíst dvě čísla, Excel neví, že jste závorky prostě vynechali!

Několik minut osvěžení paměti na příkaz operátora vám může ušetřit spoustu bolesti hlavy.

Zobrazit vzorce

V případě, že jste si toho nevšimli, je docela těžké prohlížet si vzorce, aniž byste je omylem upravili. Je to proto, že kdykoli jste v režimu úprav a aktivní buňka má vzorec, vzorec může obsahovat adresu jakékoli jiné buňky, na kterou kliknete. Tohle totálně kazí věci.

Nebylo by snadné, kdybyste se mohli podívat na všechny své vzorce? Existuje způsob! Je to jednoduché. Klikněte na Soubor v levé horní části pracovního prostoru Excelu, klikněte na Možnosti, klikněte na kartu Upřesnit a přejděte dolů k části Možnosti zobrazení pro tento list.

Možnosti nastavení.

Všimněte si zaškrtávacího políčka Zobrazit vzorce v buňkách místo jejich vypočtených výsledků. Toto pole říká Excelu, že u všech buněk, které mají vzorce, se má namísto vypočítaného výsledku zobrazit samotný vzorec. Následující obrázek ukazuje list, který zobrazuje vzorce. Chcete-li se vrátit k normálnímu zobrazení, opakujte tyto kroky a zrušte výběr možnosti. Tato možnost umožňuje snadno zjistit, jaké jsou všechny vzorce!

Prohlížení vzorců snadným způsobem.

Funkce můžete náhodně upravit, i když jste vybrali možnost Zobrazit vzorce. Buďte opatrní při klikání po pracovním listu.

Opravte vzorce

Předpokládejme, že váš list obsahuje nějaké chyby. Nepropadejte panice! Stává se to i těm nejdůvtipnějším uživatelům a Excel vám může pomoci zjistit, co je špatně. Na kartě Vzorce v části Auditování vzorců je tlačítko Kontrola chyb. Kliknutím na tlačítko se zobrazí dialogové okno Kontrola chyb, jak je znázorněno zde. To znamená, že dialogové okno se zobrazí, pokud váš list obsahuje nějaké chyby. V opačném případě pouze vyskočí hláška, že kontrola chyb je dokončena. Je to tak chytré!

Kontrola chyb.

Když tam jsou chyby, se zobrazí dialogové okno box a tyčinky kolem při práci na každou chybu. Tlačítka Další a Předchozí umožňují procházet všechny chyby, než se dialogové okno zavře. Pro každou chybu, kterou najde, zvolíte, jakou akci podniknete:

• Nápověda k této chybě: Vede k systému nápovědy a zobrazuje téma pro konkrétní typ chyby.

• Zobrazit kroky výpočtu: Otevře se dialogové okno Vyhodnotit vzorec a můžete krok za krokem sledovat, jak se vzorec počítá. To vám umožní identifikovat konkrétní krok, který způsobil chybu.

• Ignorovat chybu: Možná je Excel špatně. Chybu ignorujte.

• Upravit v řádku vzorců: Toto je rychlý způsob, jak sami opravit vzorec, pokud nepotřebujete žádnou další pomoc.

Dialogové okno Kontrola chyb má také tlačítko Možnosti. Klepnutím na tlačítko otevřete kartu Vzorce v dialogovém okně Možnosti aplikace Excel. Na kartě Vzorce můžete vybrat nastavení a pravidla pro rozpoznávání a spouštění chyb.

Používejte absolutní odkazy

Pokud se chystáte použít stejný vzorec pro spoustu buněk, jako jsou ty, které jdou dolů po sloupci, nejlepší metodou je napsat vzorec jednou a poté jej přetáhnout dolů do ostatních buněk pomocí úchytu výplně. Problém je v tom, že když vzorec přetáhnete do nových umístění, všechny relativní odkazy se změní.

Často je to záměr. Pokud existuje jeden sloupec dat a sousední sloupec vzorců, obvykle každá buňka ve sloupci vzorce odkazuje na svého souseda ve sloupci dat. Ale pokud všechny vzorce odkazují na buňku, která nesousedí, záměrem obvykle je, aby všechny buňky vzorce odkazovaly na neměnný odkaz na buňku. Aby to fungovalo správně, použijte absolutní odkaz na buňku.

Chcete-li použít absolutní odkaz na buňku, použijte znak dolaru ($) před číslem řádku, před písmenem sloupce nebo před oběma. Udělejte to, když píšete první vzorec, před jeho přetažením do jiných buněk, nebo budete muset aktualizovat všechny vzorce.

Nepište například toto:

=A4 x (B4 + A2)

Místo toho to napište takto:

=A4 x (B4 + $A$2)

Tímto způsobem všechny vzorce odkazují na A2 bez ohledu na to, kam je zkopírujete, místo toho, aby se tento odkaz změnil na A3 a A4 a tak dále.

Použijte auditování vzorců

Existují precedenty a závislosti. Existují externí reference. Všude je interakce. Jak můžete sledovat, odkud a kam odkazy na vzorce pocházejí?

Použijte nástroje pro audit vzorců, tak to je! Na kartě Vzorce je část Kontrola vzorců. V této části jsou různá tlačítka, která ovládají viditelnost šipek auditování trasování.

Kontrolní vzorce.

Panel nástrojů pro auditování vzorců má několik funkcí, které vám umožňují procházet vzorce. Kromě zobrazení šipek trasování vám panel nástrojů také umožňuje kontrolovat chyby, vyhodnocovat vzorce, kontrolovat neplatná data a přidávat komentáře do listů.

Použijte podmíněné formátování

Stejně jako funkce IF vrací určitou hodnotu, když je podmínka prvního argumentu pravdivá, a jinou hodnotu, když je nepravdivá, podmíněné formátování vám umožňuje použít na buňku určitý formát, když je podmínka pravdivá. Na kartě Domů v části Styly je rozevírací nabídka s mnoha možnostmi podmíněného formátování.

Tento obrázek ukazuje některé hodnoty, které byly ošetřeny podmíněným formátováním. Podmíněné formátování vám umožňuje nastavit podmínku a vybrat formát, který se použije, když je podmínka splněna. Můžete například určit, že se buňka zobrazí tučnou kurzívou, pokud je hodnota, kterou obsahuje, větší než 100.

Použití formátu, když je splněna podmínka.

Podmínky jsou nastaveny jako pravidla. Typy pravidel jsou

• Naformátujte všechny buňky na základě jejich hodnot.

• Formátovat pouze buňky, které obsahují . . . .

• Formátovat pouze nejvyšší nebo nejspodnější hodnoty.

• Formátovat pouze hodnoty, které jsou nad nebo pod průměrem.

• Formátujte pouze jedinečné nebo duplicitní hodnoty.

• Pomocí vzorce určete, které buňky se mají formátovat.

Pokud je podmínka pravdivá, formátování může ovládat následující:

• Hranice

• Nastavení písma (styl, barva, tučné písmo, kurzíva atd.)

• Výplň (barva pozadí buňky nebo vzor)

Buňky lze také formátovat pomocí barevných schémat nebo obrázků ikon umístěných v buňce.

Použijte ověření dat

Na kartě Data v části Nástroje pro data je Ověření dat. Ověření dat vám umožňuje aplikovat pravidlo na buňku (nebo buňky), takže položka musí odpovídat pravidlu. Buňku lze například nastavit tak, aby přijímala pouze celočíselnou položku mezi 50 a 100.

Nastavení ověřování dat.

Pokud záznam nevyhoví pravidlu, zobrazí se zpráva.

Přistižen při špatném vstupu.

Chybovou zprávu lze přizpůsobit. Pokud například někdo zadá špatné číslo, zobrazená chybová zpráva může znít Noodlehead — naučte se počítat! Jen ať to šéf nevidí.