Microsoft Excel je výkonná a univerzální tabulková aplikace, která je skvělá pro sledování a správu všeho od podnikových zásob, přes rozpočty malých podniků až po osobní zdatnost. Jednou z výhod Excelu je, že můžete nastavit vzorce předem, které se automaticky aktualizují při zadávání nových dat. Některé vzorce jsou bohužel matematicky nemožné bez potřebných dat, což má za následek chyby v tabulce, jako jsou # DIV / 0 !, #VALUE !, #REF! A #NAME ?. I když to nemusí být nutně škodlivé, tyto chyby se budou zobrazovat v tabulce, dokud nebudou opraveny nebo dokud nebudou zadána požadovaná data, což může způsobit, že celková tabulka bude méně atraktivní a obtížnější ji pochopit. Naštěstí, alespoň v případě chybějících dat, můžete skrýt chyby Excelu pomocí nějaké pomoci z funkcí IF a ISERROR. Zde je návod, jak to udělat.
Jako příklad typu tabulky používáme tabulku sledování ztráty hmotnosti jako příklad tabulky, která by při čekání na nová data (následná vážení) způsobila chybu výpočtu (výpočet procenta ztráty hmotnosti).
Náš příklad tabulky čeká na zadání ve sloupci Hmotnost a poté automaticky aktualizuje všechny ostatní sloupce na základě nových dat. Problém je v tom, že sloupec Procento ztracených se spoléhá na hodnotu Změnit, která nebyla aktualizována týdny, ve kterých ještě nebyla zadána hmotnost, což vede k # DIV / 0! chyba, která označuje, že se vzorec pokouší dělit nulou. Tuto chybu můžeme vyřešit třemi způsoby:
- Můžeme odstranit vzorec z týdnů, ve kterých nebyla zadána žádná hmotnost, a poté je každý týden ručně přidat zpět. To by v našem příkladu fungovalo, protože tabulka je relativně malá, ale nebyla by ideální ve větších a složitějších tabulkách.
- Můžeme vypočítat procento ztracených pomocí jiného vzorce, který se nedělí nula. V našem příkladu je to opět možné, ale nemusí to vždy záviset na tabulce a datové sadě.
- Můžeme použít funkci ISERROR, která ve spojení s příkazem IF nám umožňuje definovat alternativní hodnotu nebo výpočet, pokud počáteční výsledek vrací chybu. Toto je řešení, které vám dnes ukážeme.
Funkce ISERROR
ISERROR sám testuje určenou buňku nebo vzorec a vrací „true“, pokud je výsledkem výpočtu nebo hodnota buňky chyba, a „false“, pokud není. ISERROR můžete použít jednoduše zadáním výpočtu nebo buňky do závorek následujících po funkci. Například:
ISERROR ((B5-B4) / C5)
Pokud výpočet (B5-B4) / C5 vrátí chybu, ISERROR vrátí „true“, když je spárován s podmíněným vzorcem. I když to lze využít mnoha různými způsoby, jeho pravděpodobně nejužitečnější role je, když je spárována s funkcí IF.
Funkce IF
Funkce IF se používá umístěním tří testů nebo hodnot do závorek oddělených čárkami: IF (testovaná hodnota, hodnota true, hodnota false). Například:
IF (B5> 100, 0, B5)
Ve výše uvedeném příkladu, pokud je hodnota v buňce B5 větší než 100 (což znamená, že test je pravdivý), bude jako hodnota buňky zobrazena nula. Pokud je však B5 menší nebo roven 100 (což znamená, že test je nepravdivý), zobrazí se skutečná hodnota B5.
Kombinace IF a ISERROR
Způsob, jakým kombinujeme funkce IF a ISERROR, je pomocí testu ISERROR jako testu pro příkaz IF. Vraťme se jako příklad k naší tabulce hubnutí. Důvod, proč buňka E6 vrací # DIV / 0! chyba je proto, že její vzorec se pokouší rozdělit celkovou hmotnost ztracenou na hmotnosti minulého týdne, která ještě není k dispozici pro všechny týdny a která účinně funguje jako pokus o dělení nulou.
Ale pokud použijeme kombinaci IF a ISERROR, můžeme říct Excel ignorovat chyby a stačí zadat 0% (nebo jakoukoli hodnotu chceme), nebo jednoduše dokončit výpočet, pokud nejsou přítomny žádné chyby. V našem příkladu toho lze dosáhnout pomocí následujícího vzorce:
IF (ISERROR (D6 / B5), 0, (D6 / D5))
S touto funkcí na místě ji můžete zkopírovat do zbývajících buněk a veškeré chyby budou nahrazeny nulami. Když však v budoucnu zadáte nová data, postižené buňky se automaticky aktualizují na správné hodnoty, protože chybový stav již nebude pravdivý.
Nezapomeňte, že při pokusu skrýt chyby aplikace Excel můžete použít téměř libovolnou hodnotu nebo vzorec pro všechny tři proměnné v příkazu IF; nemusí to být nulové nebo celé číslo jako v našem příkladu. Alternativy zahrnují odkazování na zcela samostatný vzorec nebo vložení mezery pomocí dvou uvozovek („“) jako vaší „skutečné“ hodnoty. Pro ilustraci by následující vzorec v případě chyby místo nuly zobrazil prázdné místo:
IF (ISERROR (D6 / B5), "", (D6 / D5))
Nezapomeňte, že příkazy IF se mohou rychle stát zdlouhavými a komplikovanými, zejména pokud jsou spárovány s ISERROR, a v takových situacích je snadné umístit závorky nebo čárku. Nejnovější verze vzorců barevných kódů Excel při jejich zadávání, které vám pomohou sledovat hodnoty buněk a závorky.
