Chyby vzorce v Excelu a řešeních

„Chyby jsou příležitostí ke zlepšení“. V každém úkolu nebo systému dochází k chybám. Excel není výjimkou. Při pokusu udělat něco se vzorcem narazíte na různé typy chyb Excelu. Tyto chyby mohou způsobit, že váš vzorec/řídicí panel/sestavy bude naprostým odpadem. A pokud nevíte, proč došlo ke konkrétnímu typu chyby, možná budete muset na jejich vyřešení strávit hodiny.

Při práci na Excelu jsem narazil na mnoho chyb v Excelu. S určitým bojem a hledáním Google jsem tyto chyby vyřešil. V tomto článku vysvětlím některé běžné a otravné chyby Excelu, které se v Excelu vyskytují. Probereme, proč k těmto chybám dochází a jak je řešit.

Co jsou chyby vzorců aplikace Excel
Při aplikaci vzorce, který má za následek chyby definované v Excelu (#NA, #VALUE, #NAME atd.), Se říká chyby vzorců Excelu. Tyto chyby jsou zachyceny aplikací Excel a vytištěny na listech. Důvodem těchto chyb mohou být nedostupné hodnoty, nesprávné typy argumentů, dělení 0 atd. Lze je snadno zachytit a opravit.

Logické chyby nejsou v Excelu zachyceny a je velmi obtížné je opravit. Častým důvodem těchto chyb je nekonzistence v datech, nesprávné zadávání údajů, lidské chyby atd. Lze je také opravit, ale vyžadují čas a úsilí. Je lepší si svá data dokonale připravit, než na nich provedete operaci.
Chytání chyb vzorce Excel:

V Excelu existuje několik vyhrazených funkcí pro zachycení a zpracování konkrétního typu chyb (jako funkce ISNA). Ale funkce ISERROR a IFERROR jsou dvě funkce, které mohou zachytit jakýkoli druh chyby aplikace Excel (kromě logické).

Chyba aplikace Excel #NA
Pokud v aplikaci Excel není nalezena hodnota, dojde k chybě #NA. Jednoduše to znamená NENÍ K DISPOZICI. U funkce Excel VLOOKUP se často vyskytuje chyba #NA.

Na výše uvedeném obrázku dostáváme chybu #NA, když ve sloupci A hledáme „Divya“. Důvodem je, že „Divya není v seznamu.

Řešení chyby #NA

Pokud jste si jisti, že vyhledávací hodnota musí ve vyhledávacím seznamu existovat, první věcí, kterou byste měli udělat, je zkontrolovat vyhledávací hodnotu. Zkontrolujte, zda jsou hodnoty vyhledávání správně napsány. Pokud ne, opravte to.

Za druhé, můžete provést částečnou shodu pomocí funkce VLOOKUP nebo jakékoli vyhledávací funkce. Pokud jste si jisti, že se některé části textu musí shodovat, použijte toto.
Pokud si nejste jisti, zda tato hodnota existuje nebo ne, lze ji použít ke kontrole, zda hodnota v seznamu existuje nebo ne. Na výše uvedeném obrázku můžeme říci, že Divya není na seznamu.

Pokud chcete zachytit chybu #NA a místo tisku #NA tisknout nebo dělat něco jiného, můžete použít funkci Excel ISNA. Funkce ISNA vrací hodnotu TRUE, pokud funkce vrací chybu #NA. Díky tomu se můžeme vyhnout chybě #NA. ISNA funguje úžasně s funkcí VLOOKUP. Podívejte se na to zde.

Chyba Excelu #HODNOTA
#HODNOTA nastane, pokud zadaný argument není podporovaného typu. Pokud se například pokusíte přidat dva texty pomocí operátoru aritmetiky plus (+), zobrazí se chyba #HODNOTA. Totéž se stane, pokud se pokusíte získat rok neplatného formátu data pomocí funkce YEAR.

Jak opravit chybu #HODNOTA?

Nejprve potvrďte typ dat, na který odkazujete. Pokud vaše funkce vyžaduje číslo, ujistěte se, že na něj odkazujete. Pokud je číslo formátováno jako text, pak je pomocí funkce VALUE převeďte na číslo. Pokud funkce vyžaduje text (například funkce DATEVALUE) a vy odkazujete na číslo nebo datum, převeďte je na text.

Pokud očekáváte, že může dojít k chybě #HODNOTA a chcete je chytit, pak můžete použít ISERR, ISERROR nebo IFERROR k chycení a dělat něco jiného.

Chyba Excel #REF
‚Odkaz 'v #REF je zkratka pro referenci. K této chybě dochází, když vzorec odkazuje na místo, které neexistuje. K tomu dochází, když odstraníme buňky z rozsahů, na které vzorec také odkazuje.

V níže uvedeném gifu vzorec součtu odkazuje na A2 a B2. Když odstraním A2, vzorec se změní na chybu #REF.

Vyřešit chybu aplikace Excel #REF:
Nejlepší je být opatrný před odstraněním buněk v datech. Ujistěte se, že na tuto buňku neodkazuje žádný vzorec.

Pokud již máte chybu #REF, pak trasujte a odstraňte ji ze vzorce.

Jakmile například dostanete chybu #REF, váš vzorec bude vypadat takto.

= A2+#REF!

Můžete pouze odstranit #REF! Ze vzorce získáte vzorec bez chyb. Pokud to chcete udělat hromadně, použijte funkci Najít a nahradit. Stisknutím kláves CTRL+H otevřete hledání a nahrazení. Do vyhledávacího pole napište #REF. Pole pro výměnu nechte prázdné. Stiskněte tlačítko Nahradit vše.

Pokud chcete znovu upravit odkaz na novou buňku, proveďte to ručně a nahraďte #REF! S tím platným odkazem.

Excel #NAME Chyba
#NAME se vyskytuje v Excelu, když nemůže identifikovat text ve vzorci. Pokud například chybně napíšete název funkce, Excel zobrazí chybu #NAME. Pokud vzorec odkazuje na název, který na listu neexistuje, zobrazí se chyba #NÁZEV.

Na výše uvedeném obrázku má buňka B2 vzorec = PRÁVA (A2,2). POWERS není platná funkce v Excelu, proto vrací chybu #NAME.

V buňce B3 máme = SUM (čísla). SUM je platná funkce aplikace Excel, ale pojmenovaná oblast "čísel" na listu neexistuje. Proto excel vrací #NAME? Chyba.

Jak se vyhnout chybě #NAME v Excelu?

Abyste se vyhnuli chybě #NAME v Excelu, vždy správně zapište názvy funkcí. Pomocí návrhů aplikace Excel můžete mít jistotu, že používáte platnou funkci. Kdykoli zadáme znaky za znaménkem rovnosti, Excel zobrazí funkce a pojmenované rozsahy na listu, počínaje tímto znakem/znaky. Přejděte dolů na název funkce nebo název rozsahu v seznamu návrhů, stisknutím záložky tuto funkci použijete.

Excel #DIV/0! Chyba
Jak název napovídá, k této chybě dochází, když je výsledkem vzorce dělení nulou. K této chybě může také dojít, když odstraníte nějakou hodnotu z buňky, na které závisí vzorec dělení.

Jak vyřešit #DIV/0! Chyba.

To lze snadno vyřešit opatrností s daty a kontrolou, zda vzorec povede k #DIV/0! chyba. Zde je ukázkový vzorec, jak toho dosáhnout.

= IF (B2 = 0, A2, A2/B2)

Chybu DIV/0 budeme mít pouze v případě, že je dělitel 0 nebo prázdný. Zkontrolujeme tedy dělitel (B2), pokud je nula, vytiskneme A2, jinak rozdělíme A2 na B2. To bude fungovat i pro prázdné buňky.

Chyba aplikace Excel #NUM

K této chybě dochází, když číslo nelze zobrazit na obrazovce. Důvodem může být to, že je číslo příliš malé nebo příliš velké na to, aby se mohlo zobrazit. Dalším důvodem může být to, že s daným číslem nelze provést výpočet.

= SQRT (ABS (A3))

Tím se vrátí 4. Pokud chcete získat zápornou hodnotu, použijte před funkcí záporné znaménko. Pak samozřejmě můžete použít funkci zpracování chyb.
O řešení chyby #ČÍSLO máme vyhrazený článek. Můžete to zkontrolovat zde.

#NULL Chyba v Excelu
Toto je vzácný typ chyby. Chyba #NULL způsobená nesprávným odkazováním na buňku. Pokud například chcete uvést odkaz na rozsah A2: A5 ve funkci SUMA, ale omylem napíšete A2 A5. To vygeneruje chybu #NULL. Jak vidíte na obrázku níže, vzorec vrací chybu #NULL.
Pokud nahradíte mezeru sloupcem (:), chyba #NULL zmizí a získáte součet A2: A5. Pokud mezeru nahradíte čárkou (,), získáte součet A2 a A5.

Jak vyřešit chybu #NULL?

Jak víme, že chyba #NULL je způsobena překlepem. Chcete -li se tomu vyhnout, zkuste místo manuálního psaní vybrat rozsahy pomocí kurzoru.

Mohou nastat případy, kdy budete muset zadat adresu rozsahu z klávesnice. Vždy se vyvarujte sloupce spojovacích symbolů (:) nebo čárky (,).

Pokud máte existující chybu #NULL, zkontrolujte odkazy. S největší pravděpodobností jste vynechali sloupec (:) nebo čárku (,) mezi dvěma odkazy na buňky. Nahraďte je příslušným symbolem a můžete vyrazit.
###### Chyba v aplikaci Excel
Někdy si myslíme, že tato chyba je způsobena nedostatečným prostorem pro zobrazení hodnoty, ale není tomu tak. V tomto případě stačí rozšířit šířku buňky a zobrazit hodnotu v buňce. Nenazývám to chybou.
Ve skutečnosti tato chyba způsobila, když se pokoušíme ukázat zápornou časovou hodnotu (neexistuje nic jako záporný čas). Pokud se například pokusíme odečíst 1 od 12:21:00, vrátí se ######. V Excelu je první datum 1. 1. 1900 00:00. Pokud se pokusíte odečíst čas, který předchází, zobrazí se v Excelu chyba ######. Čím více šířku rozšíříte, tím více # získáte. Podrobně jsem to rozvedl v tomto článku.

Jak se vyhnout ###### chybě aplikace Excel?
Před provedením aritmetických výpočtů v Excelu s časovou hodnotou mějte na paměti tyto věci.

Minimální hodnota času je 1/1/1900 00:00. V Excelu nemůžete mít před tím platné datum
1 se rovná 24 hodinám (1 den) v Excelu. Při odečítání hodin, minut a sekund je převeďte na ekvivalentní hodnoty. Chcete -li například odečíst 1 hodinu od 12:21, musíte z ní odečíst 1/24.

Sledování chyb v aplikaci Excel
Nyní víme, jaké jsou běžné vzorce Excelu a proč se vyskytují. Také jsme diskutovali o tom, jaké možné řešení může být pro každý typ chyb aplikace Excel.
Někdy se ve zprávách aplikace Excel zobrazují chyby a my jsme nemohli vědět, odkud se chyba ve skutečnosti vyskytuje. Je těžké tyto druhy chyb vyřešit. Ke sledování těchto chyb poskytuje Excel funkci sledování chyb na kartě vzorců.

Podrobně jsem diskutoval trasování chyb v Excelu.

Řešení logických chyb ve vzorci

Logické chyby je těžké najít a vyřešit. Pokud máte ve své funkci logickou chybu, excel neukazuje žádnou masáž. Všechno vypadá dobře. Ale jen vy víte, že tam něco není v pořádku. Například při získávání procent z části celku byste dělili část celkem (= (část/celkem)*100). Mělo by být vždy stejné nebo menší než 100%. Když získáte více než 100%, víte, že něco není v pořádku.

To byla jednoduchá logická chyba. Někdy však vaše data pocházejí z několika zdrojů, v takovém případě je obtížné vyřešit logické problémy. Jedním ze způsobů je vyhodnotit vzorec.

Na kartě Vzorec je k dispozici možnost vyhodnotit vzorec. Vyberte svůj vzorec a klikněte na něj. Každý krok vašeho výpočtu vám ukáže, co vede k vašemu konečnému výsledku. Zde můžete zkontrolovat, kde k problému došlo nebo kde se výpočet pokazil.

Můžete také vysledovat závislé a precedenty, abyste zjistili, na kterých referencích váš vzorec závisí a jaké vzorce závisí na perikulární buňce.

Takže jo, lidi, to byly některé běžné typy chyb, s nimiž se setkává každý uživatel aplikace Excel. Dozvěděli jsme se, proč dochází k jednotlivým typům chyb a jak se jim můžeme vyhnout. Dozvěděli jsme se také o vyhrazené funkci excel pro zpracování chyb. V tomto článku máme odkazy na související stránky, které problém podrobně rozebírají. Můžete si je prohlédnout. Pokud máte nějaký konkrétní typ chyby, která vás obtěžuje, uveďte to v sekci komentáře níže. Řešení získáte laskavě.