Jak opravit běžné chyby ve vzorcích v Excelu

  • Identifikujte nejčastější chyby a naučte se interpretovat jejich sdělení.
  • Objevte praktická řešení pro opravu všech typů chyb ve vzorcích Excelu.
  • Používejte klíčové tipy, abyste předešli chybám a optimalizovali své tabulky.
Mayka JimenezAktualizováno dne

8 minut

Jak opravit chyby ve vzorcích v Excelu.

Práce se vzorci v Excelu může být docela náročná., zejména když narazíme na záhadné chybové zprávy nebo výsledky, které se prostě nesčítají. Pokud jste se někdy setkali s podivnými symboly, číslicemi nebo tajemnými varováními, jako je #DIV/0!, #VALUE! nebo #REF!, nebojte se: je to velmi běžné a existuje řešení.

Tento článek vám má nabídnout kompletního a praktického průvodce o tom, jak rozpoznat, porozumět a opravit nejčastější chyby, které se objevují při práci se vzorci v Excelu.

Hlavní chyby ve vzorcích v Excelu a jak je identifikovat

Při psaní vzorců v Excelu, Je důležité věnovat pozornost syntaxi a použitým datovým typům, protože většina chyb vzniká z drobných přehlédnutí nebo neznalosti základních pravidel funkcí.

Některé z nejčastějších poruch Dochází k nim mimo jiné, když se na začátku vzorce nepoužívá znaménko rovnosti, když se vynechávají závorky, když se pro oblasti buněk nesprávně používají dvojtečky nebo když se pro funkci zvolí nesprávné argumenty.

  • Vzorce vždy začínají znakem '='Pokud to neuděláte, Excel interpretuje zadaný text jako text nebo dokonce datum. Například „SUM(A1:A10)“ se zobrazí tak, jak je, a „11/2“ se může zobrazit jako 2. listopadu.
  • Zkontrolujte závorkyVe funkcích musíte správně otevírat a zavírat každou závorku. Pokud jednu vynecháte nebo zajdete příliš daleko, vzorec bude neplatný. Nesprávný příklad: =IF(B5<0);"Neplatný";B5*1,05)
  • Pro rozsahy použijte dvojtečky („:“).Toto je pro Excel zásadní pro pochopení rozsahu buněk. Pište =SUM(A1:A5), nikdy ne =SUM(A1:A5), což by vyvolalo chybu #NULL!.
  • Ujistěte se, že jste napsali všechny potřebné argumentyKaždá funkce vyžaduje přesný počet argumentů, ne více, ne méně a správného typu (číslo, text atd.).

Navíc existují drobné detaily, kterých si často nikdo nevšimne., například omezení vnořování na 64 funkcí, správné používání uvozovek při pojmenovávání listů s mezerami nebo speciálními znaky a používání vykřičníku (!), když vzorec volá jiný list. Pokud pracujete s externími sešity, musí být cesta a název správně uvedeny a při psaní čísel se vyhýbejte formátům (jako jsou čárky nebo symboly eura) a omezte se na čistě číselnou hodnotu.

8 nejčastějších chyb a jak je vyřešit

Opravte chyby ve vzorcích v Excelu.

Excel zobrazuje různé chybové kódy V závislosti na problému, se kterým se ve vzorci setkáte. Každá zpráva označuje jinou příčinu a má své vlastní řešení. Níže uvádíme ty nejběžnější a jejich možné opravy.

# N / A

Význam: Není dostupný. Nastane, když vyhledávací funkce nenajde požadovanou hodnotu.U funkcí VLOOKUP, HLOOKUP, MATCH nebo SEARCH je typické, že hodnota v hledaném rozsahu neexistuje.

řešení: Zkontrolujte, zda jsou odkazované rozsahy správné, zda jsou informace tam, kde mají být, a zda nebyly smazány. Pokud se tato chyba musí zobrazit, protože data ve skutečnosti neexistují, můžete k zobrazení užitečnějšího textu použít funkci pro ošetření chyb, například IFERROR, například: =SI.ERROR(BUSCARV(A10;A3:B5;2;0);»Valor no encontrado»).

#HODNOTA!

Význam: Neplatná hodnota. Tato chyba označuje, že vzorec interaguje s buňkami nebo datovými typy, které nejsou podporovány.Často se objevuje při použití textu, kde jsou vyžadována čísla nebo naopak, nebo když jsou v něm prázdné buňky nebo neočekávané znaky.

řešení: Zkontrolujte, zda jsou všechny argumenty správného typu (pokud se očekává číslo nebo text) a zda v rozsahu nejsou žádné mezery ani neobvyklé znaky. Pomocí nástroje Trasování precedentů zjistíte, odkud chyba pochází.

#REF!

Význam: Neplatný odkaz. Dochází k tomu, když vzorec odkazuje na odstraněnou buňku nebo na buňku, která byla vynechána z listu v důsledku nechtěného vyjmutí nebo vložení.

řešení: V ideálním případě byste měli vrátit zpět akci, která buňku smazala, nebo upravit vzorec tak, aby odkazoval na platné odkazy. Rozsah ve vyhledávacích funkcích můžete také upravit tak, aby odpovídal počtu dostupných sloupců.

Například vzorec =VYHLEDAT(A8;A2:D5,5;XNUMX;NEPRAVDA) zobrazí chybu, protože oblast A2:D5 má pouze 4 sloupce; měla by být =VYHLEDAT(A8;A2:E5,5;XNUMX;NEPRAVDA) nebo opravte index sloupce.

#JMÉNO?

Význam: Excel nerozpoznává text použitý ve vzorci jako platný název funkce nebo jako známý odkaz na buňku.

Časté příčiny: Překlep v názvu funkce nebo chybějící definice pojmenovaného rozsahu. Příklad: „=průměr(A1:A5)“ místo „=průměr(A1:A5)“.

řešení: Zkontrolujte, zda je funkce napsána správně, a abyste se vyhnuli překlepům, použijte možnost vložit funkci na kartě Vzorce.

#DIV/0!

Význam: Snažíte se dělit hodnotu nulou, což matematicky není možné.

řešení: Před dělením použijte funkci SI zkontrolovat, zda je dělitel nula. Například: =IF(B3=0;»»;A3/B3)Tím se zabrání zobrazení chyby a umožní se zobrazit alternativní výsledek nebo prázdná buňka.

#####

Chyby v Excelu.

Význam: Buňka není dostatečně široká pro zobrazení svého obsahu. Nejedná se o chybu vzorce, ale o chybu formátování.

řešení: Šířku sloupce upravte dvojitým kliknutím na pravý okraj záhlaví nebo vyberte tlačítko „Vybrat vše“ a v případě potřeby změňte velikost celého listu.

#NULA!

Význam: Byl odkazován neexistující nebo špatně definovaný rozsah, obvykle proto, že byl použit nesprávný operátor.

řešení: Pro označení rozsahů (například A2:C2) vždy používejte dvojtečku ':' a pro spojení více rozsahů ve funkci středník ';', například: =SUM(A2:A6;D2:D6).

#NUM!

Význam: Neplatné číslo, obvykle kvůli nemožným výpočtům nebo výsledkům mimo limity, které Excel dokáže zpracovat.

Příčiny: Vypočítejte druhou odmocninu záporného čísla nebo požádejte o výsledek tak velký nebo malý, že je mimo povolený rozsah (mezi -1×10307 a 1×10307).

řešení: Zkontrolujte hodnoty použité ve vzorci, ujistěte se, že nepracujete s nelogickými daty, a pokud používáte iterační funkce jako IRR nebo RATE, upravte možnosti iterace v pokročilé analytické nástroje.

Jak se vyhnout chybám při psaní složitých vzorců

Mnoha problémům lze předejít dodržováním řady doporučení při vytváření vzorců v Excelu:

  • Vždy zkontrolujte počet závorek a že jsou správně spárovány. Praktickou metodou je spočítat počáteční a závěrečná čísla před dokončením vzorce.
  • Zachovat logiku vnořováníVnořování více než 64 funkcí by nemělo být povoleno, protože Excel to neumožňuje a způsobí to chyby, které je obtížné vystopovat.
  • Správné odkazování na buňky mezi listy a sešityPokud název listu obsahuje mezery nebo neabecední znaky, vždy jej uzavřete do jednoduchých uvozovek („“). Také při odkazování na externí sešity použijte formát a přidejte cestu, pokud není otevřený.
  • Nezahrnujte formátování do číselPokud chcete zobrazit symbol eura, použijte ho v buňce následně, ne uvnitř vzorce.

Nástroje a triky pro Excel, jak rychle opravit chyby

Vzorce s chybami v Excelu: jak je opravit.

Excel má několik funkcí a utilit, které pomáhají detekovat a opravovat chyby ve vzorcích nebo datech v tabulkách. Jejich znalost vám může ušetřit spoustu času a zlepšit spolehlivost vašich tabulek.

  • Chyba při kontroleNa kartě „Vzorce“ máte přístup k této funkci, která analyzuje vzorec a vysvětluje možné chyby nebo řešení k jejich vyřešení. Je to obzvláště užitečné pro zprávy jako #NAME?
  • Sledování precedentů a závislých osobTento nástroj umožňuje zobrazit pomocí šipek, které buňky jsou součástí výpočtu, což pomáhá lokalizovat zdroj chyby #¡VALUE! nebo jiných podobných chyb.
  • Automatické vyplňování vzorcůPokud mezi buňkami existují nesrovnalosti, vyberte problematický vzorec a se stisknutou klávesou SHIFT rozbalte výběr a poté jej podle potřeby opravte nahoru, dolů, doprava nebo doleva pomocí klávesových zkratek nebo možností v nabídce Vyplnit.

Praktické případy a běžné situace

Abychom lépe porozuměli chybám ve vzorcích, podívejme se na několik příkladů a užitečných triků, které můžete použít.

  • Oprava chyby #####: Pokud do úzké buňky zadáte dlouhé datum nebo velké číslo, obsah se zobrazí s několika křížky. Rozbalte sloupec a data budou viditelná.
  • #REF! kvůli nedostatečnému dosahu: Pokud používáte funkce jako VLOOKUP a požadujete sloupec mimo rozsah, upravte rozsah nebo index tak, aby odpovídal.
  • Zpracování #DIV/0!: Před dělením zkontrolujte pomocí podmínky, zda je dělitel nula. Tímto způsobem se vyhnete chybě a ovládáte zobrazený výsledek.

Je zásadní aby chyby v Excelu byly včas opraveny, protože mohou ovlivnit závislé výpočty a šířit nesprávné výsledky v celé tabulce.