Vzorce Excelu nefungují: Možné důvody a jak to opravit!

Pokud pracujete se vzorci v Excelu, dříve nebo později narazíte na problém, kdy vzorce v Excelu vůbec nefungují (nebo dávají špatný výsledek).

I když by to bylo skvělé, kdyby existovalo jen několik možných důvodů pro nefunkční vzorce. Bohužel je příliš mnoho věcí, které se mohou pokazit (a často se stávají).

Ale protože žijeme ve světě, který se řídí Paretovým principem, pokud zkontrolujete nějaké běžné problémy, pravděpodobně vyřeší 80% (nebo možná dokonce 90% nebo 95% problémů, kde vzorce v Excelu nefungují).

A v tomto článku zdůrazním ty běžné problémy, které jsou pravděpodobně příčinou vašich Nefungují vzorce v Excelu.

Začněme tedy!

Nesprávná syntaxe funkce

Začnu tím, že poukážu na zjevné.

Každá funkce v aplikaci Excel má specifickou syntaxi - například počet argumentů, které může přijmout, nebo typ argumentů, které může přijmout.

A v mnoha případech může být důvodem, proč vaše vzorce v Excelu nefungují nebo mají špatný výsledek, nesprávný argument (nebo chybějící argumenty).

Například funkce VLOOKUP přebírá tři povinné argumenty a jeden nepovinný argument.

Pokud zadáte nesprávný argument nebo neurčíte volitelný argument (tam, kde je potřeba, aby vzorec fungoval), přinese vám to špatný výsledek.

Předpokládejme například, že máte sadu dat, jak je uvedeno níže, kde potřebujete znát skóre známky v testu 2 (v buňce F2).

Pokud použiji níže uvedený vzorec, získám špatný výsledek, protože ve třetím argumentu používám špatnou hodnotu (ten, který požaduje číslo indexu sloupce).

= VLOOKUP (A2, A2: C6,2, FALSE)

V tomto případě vzorec vypočítá (protože vrací hodnotu), ale výsledek je nesprávný (místo skóre v testu 2 dává skóre v testu 1).

Dalším příkladem, kde musíte být opatrní ohledně argumentů, je použití VLOOKUP s přibližnou shodou.

Vzhledem k tomu, že k určení, kde má VLOOKUP provést přesnou nebo přibližnou shodu, je třeba použít volitelný argument, může způsobování problémů způsobit neuvedení tohoto (nebo použití nesprávného argumentu).

Níže je uveden příklad, kde mám údaje o známkách pro některé studenty a chci extrahovat známky u zkoušky 1 pro studenty v tabulce vpravo.

Když použiji níže uvedený vzorec VLOOKUP, zobrazí se u některých jmen chyba.

= VLOOKUP (E2, $ A $ 2: $ C $ 6,2)

K tomu dochází, protože jsem neuvedl poslední argument (který se používá k určení, zda provést přesnou nebo přibližnou shodu). Pokud nezadáte poslední argument, automaticky ve výchozím nastavení provede přibližnou shodu.

Protože jsme v tomto případě potřebovali provést přesnou shodu, vzorec vrací u některých jmen chybu.

I když jsem vzal příklad funkce VLOOKUP, v tomto případě je to něco, co lze použít pro mnoho vzorců aplikace Excel, které mají také volitelné argumenty.

Také si přečtěte: Identifikujte chyby pomocí ladění vzorců aplikace Excel

Extra mezery způsobující neočekávané výsledky

Úvodní, koncové mezery je těžké zjistit a mohou způsobit problémy při použití buňky, která je ve vzorcích obsahuje.

Pokud se například v níže uvedeném příkladu pokusím použít VLOOKUP k načtení skóre pro Marka, zobrazí se chyba #N/A (chyba, která není k dispozici).

I když vidím, že vzorec je správný a název „Mark“ je jasně uveden, seznam je, ale nevidím, že v buňce je koncové místo, které má název (v buňce D2).

Excel nepovažuje obsah těchto dvou buněk za stejný, a proto jej považuje za nesoulad při načítání hodnoty pomocí VLOOKUP (nebo to může být jakýkoli jiný vzorec pro vyhledávání).

Chcete -li tento problém vyřešit, je třeba odstranit tyto mezery.

To lze provést některou z následujících metod:

1. Před použitím ve vzorcích vyčistěte buňku a odstraňte všechny úvodní/koncové mezery
2. Pomocí funkce TRIM ve vzorci zajistěte, aby byly ignorovány všechny úvodní/koncové/dvojité mezery.

Ve výše uvedeném příkladu můžete místo toho použít níže uvedený vzorec, abyste se ujistili, že funguje.

= VLOOKUP (TRIM (D2), $ A $ 2: $ B $ 6,2,0)

I když jsem vzal příklad VLOOKUP, je to také běžný problém při práci s funkcemi TEXT.

Pokud například použiji funkci LEN k počítání celkového počtu znaků v buňce, pokud jsou mezery na začátku nebo na konci, budou také započítány a poskytnou špatný výsledek.

Take away / How to Fix: Pokud je to možné, před použitím ve vzorcích vyčistěte svá data odstraněním jakýchkoli úvodních/koncových nebo dvojitých mezer. Pokud nemůžete změnit původní data, použijte funkci TRIM ve vzorcích, abyste se o to postarali.

Použití ručního výpočtu místo automatického

Toto jedno nastavení vás může zbláznit (pokud nevíte, co je příčinou všech problémů).

Excel má dva režimy výpočtu - Automatický a Manuál.

Ve výchozím nastavení je automatický režim povolen, což znamená, že v případě, že použiji vzorec nebo provedu nějaké změny ve stávajících vzorcích, automaticky (a okamžitě) provede výpočet a dá mi výsledek.

Toto je nastavení, na které jsme všichni zvyklí.

V automatickém nastavení vždy, když na listu provedete jakoukoli změnu (například zadání nového vzorce do některého textu v buňce), Excel vše automaticky přepočítá (ano, všechno).

V některých případech však lidé povolují nastavení ručního výpočtu.

To se většinou provádí, když máte těžký soubor aplikace Excel se spoustou dat a vzorců. V takových případech možná nebudete chtít, aby Excel přepočítal vše, když provedete malé změny (protože to může trvat několik sekund nebo dokonce minut), než se tento přepočet dokončí.

Pokud povolíte ruční výpočet, Excel nebude počítat, pokud ho k tomu nedonutíte.

A to vás může přimět myslet si, že váš vzorec není vypočítavý.

Vše, co musíte v tomto případě udělat, je buď nastavit výpočet zpět na automatiku, nebo vynutit přepočet stisknutím klávesy F9.

Níže jsou uvedeny kroky ke změně výpočtu z ručního na automatický:

1. Klikněte na kartu Vzorec
2. Klikněte na Možnosti výpočtu
3. Vyberte Automaticky

Důležité: V případě, že měníte výpočet z manuálního na automatický, je vhodné vytvořit si zálohu sešitu (pro případ, že by sešit zablokoval nebo by došlo k selhání Excelu)

Take away / How to Fix: Pokud si všimnete, že vaše vzorce neposkytují očekávaný výsledek, zkuste něco jednoduchého v libovolné buňce (například přidání 1 do stávajícího vzorce. Jakmile identifikujete problém jako ten, kde je třeba změnit režim výpočtu, proveďte výpočet síly pomocí F9.

Mazání řádků/sloupců/buněk vedoucích k #REF! Chyba

Jedna z věcí, která může mít zničující dopad na vaše stávající vzorce v Excelu, je, když odstraníte jakýkoli řádek/sloupec, který byl použit ve výpočtech.

Když k tomu dojde, někdy Excel upraví samotný odkaz a zajistí, aby vzorce fungovaly dobře.

A někdy… to nemůže.

Naštěstí jednou jasnou indikací, kterou získáte, když se vzorce při odstraňování buněk/řádků/sloupců rozbijí, je #REF! chyba v buňkách. Toto je referenční chyba, která vám říká, že je nějaký problém s odkazy ve vzorci.

Ukážu vám, co mám na mysli na příkladu.

Níže jsem použil vzorec SUM k přidání buněk A2: A6.

Pokud nyní odstraním některou z těchto buněk/řádků, vzorec SUMA vrátí #REF! chyba. K tomu dochází, protože když jsem odstranil řádek, vzorec neví, na co nyní odkazovat.

Můžete vidět, že třetím argumentem ve vzorci se stal #REF! (který dříve odkazoval na buňku, kterou jsme odstranili).

Take away / How to Fix: Před odstraněním všech dat, která se používají ve vzorcích, se ujistěte, že po odstranění nejsou žádné chyby. Doporučuje se také, abyste si pravidelně vytvářeli zálohu své práce, abyste se ujistili, že máte vždy na co vzpomínat.

Nesprávné umístění závorky (BODMAS)

Když se vaše vzorce začínají zvětšovat a být složitější, je vhodné použít závorku, abyste si byli naprosto jisti, která část patří k sobě.

V některých případech můžete mít závorku na špatném místě, což vám může dát buď špatný výsledek, nebo chybu.

A v některých případech se doporučuje použít závorku, abyste se ujistili, že vzorec chápe, co je třeba nejprve seskupit a vypočítat.

Předpokládejme například, že máte následující vzorec:

=5+10*50

Ve výše uvedeném vzorci je výsledek 505, protože Excel nejprve provede násobení a poté sčítání (protože u operátorů existuje pořadí priority).

Pokud chcete, aby nejprve provedl sčítání a poté násobení, musíte použít níže uvedený vzorec:

=(5+10)*50

V některých případech vám může fungovat pořadí přednosti, ale přesto se doporučuje použít závorku, aby nedošlo k záměně.

Pokud vás to také zajímá, níže je pořadí priorit pro různé operátory často používané ve vzorcích:

Operátor	Popis	Řád přednosti
: (dvojtečka)	Rozsah	1
(jedno místo)	Průsečík	2
, (čárka)	unie	3
-	Negace (jako v -1)	4
%	Procento	5
^	Umocňování	6
* a /	Násobení a dělení	7
+ a -	Sčítání a odčítání	8
&	Concatnenation	9
= =	Srovnání	10

Take away / How to Fix: Vždy používejte závorky, aby nedošlo k záměně, i když znáte pořadí přednosti a používáte ho správně. Díky závorkám je snazší audit vzorců.

Nesprávné použití absolutních/relativních odkazů na buňky

Když zkopírujete a vložíte vzorce v aplikaci Excel, automaticky upraví odkazy. Někdy je to přesně to, co chcete (většinou když kopírujete vzorce do sloupce), a někdy nechcete, aby se to stalo.

Absolutní odkaz je, když opravíte odkaz na buňku (nebo odkaz na rozsah), aby se nezměnil, když zkopírujete a vložíte vzorce, a relativní odkaz se změní.

Více o absolutních, relativních a smíšených referencích si můžete přečíst zde.

V případě, že zapomenete změnit odkaz na absolutní (nebo naopak), můžete získat nesprávný výsledek. To se mi stává docela často, když používám vyhledávací vzorce.

Ukážu vám příklad.

Níže mám datovou sadu, kde chci načíst skóre v testu 1 pro jména ve sloupci E (jednoduchý případ použití VLOOKUP)

Níže je vzorec, který používám v buňce F2 a poté zkopíruji do všech buněk pod ní:

= VLOOKUP (E2, A2: B6,2,0)

Jak vidíte, tento vzorec v některých případech dává chybu.

To se stává, protože jsem nezamkl argument pole tabulky - je A2: B6 v buňce F2, zatímco to mělo být $ A $ 2: $ B $ 6

Tím, že mám v odkazu na buňku tyto znaky dolaru před číslem řádku a abecedou sloupců, nutím Excel, aby tyto odkazy na buňky nechal pevné. Takže i když zkopíruji tento vzorec dolů, pole tabulky bude i nadále odkazovat na A2: B6

Profesionální tip: Chcete -li převést relativní odkaz na absolutní, vyberte odkaz na buňku v buňce a stiskněte klávesu F4. Všimli byste si, že se to mění přidáním znaků dolaru. Můžete pokračovat v stisknutí klávesy F4, dokud nezískáte požadovanou referenci.

Nesprávný odkaz na názvy listů / sešitů

Když ve vzorci odkazujete na jiné listy nebo sešity, musíte dodržovat konkrétní formát. A v případě, že je formát nesprávný, zobrazí se chyba.

Pokud například chci odkazovat na buňku A1 v listu 2, bude to odkaz = List2! A1 (kde je za názvem listu vykřičník)

A v případě, že je v názvu listu více slov (řekněme, že je to příklad dat), bude odkaz = ‘Ukázkové údaje’! A1 (kde je název uzavřen v jednoduchých uvozovkách následovaných vykřičníkem).

V případě, že odkazujete na externí sešit (řekněme, že odkazujete na buňku A1 v ‘Příkladovém listu’ v sešitu s názvem ‘Příklad sešitu‘), bude odkaz následující:

= '[Příklad Workbook.xlsx] Příklad listu'! $ A $ 1

A v případě, že sešit zavřete, změní se odkaz tak, aby zahrnoval celou cestu sešitu (jak je uvedeno níže):

= 'C: \ Users \ sumit \ Desktop \ [Příklad Workbook.xlsx] Příklad listu'! $ A $ 1

V případě, že nakonec změníte název sešitu nebo listu, na který vzorec odkazuje, získáte #REF! chyba.

Také si přečtěte: Jak najít externí odkazy a reference v aplikaci Excel

Kruhové reference

Kruhový odkaz je, když odkazujete (přímo nebo nepřímo) na stejnou buňku, kde se vypočítává vzorec.

Níže je jednoduchý příklad, kde používám vzorec SUM v buňce A4 a zároveň jej používám v samotném výpočtu.

= SUM (A1: A4)

Ačkoli vám Excel zobrazí výzvu s informacemi o kruhovém odkazu, nebude to dělat pro každou instanci. A to vám může poskytnout špatný výsledek (bez jakéhokoli varování).

V případě, že máte podezření na kruhový odkaz ve hře, můžete zkontrolovat, které buňky jej mají.

Chcete-li to provést, klikněte na kartu Vzorec a ve skupině „Kontrola vzorců“ klikněte na rozevírací ikonu Kontrola chyb (malá šipka směřující dolů).

Umístěte kurzor na možnost Kruhová reference a zobrazí se buňka, která má problém s kruhovou referencí.

Buňky formátované jako text

Pokud se ocitnete v situaci, kdy jakmile zadáte vzorec jako klávesu Enter, uvidíte vzorec místo hodnoty, je to jasný případ, kdy je buňka formátována jako text.

Když je buňka naformátována jako text, považuje vzorec za textový řetězec a ukazuje jej takový, jaký je.

Nenutí to vypočítat a dát výsledek.

A má snadnou opravu.

1. V části „Text“ změňte formát na „Obecné“ (nachází se na kartě Domů ve skupině Čísla)
2. Přejděte do buňky, která má vzorec, přejděte do režimu úprav (použijte F2 nebo dvakrát klikněte na buňku) a stiskněte Enter

Pokud výše uvedené kroky problém nevyřeší, další věcí, kterou je třeba zkontrolovat, je, zda má buňka na začátku apostrof. Mnoho lidí přidává apostrof pro převod vzorců a čísel na text.

Pokud existuje apostrof, můžete jej jednoduše odstranit.

Text se automaticky převádí na data

Excel má tento zlozvyk převádět, který vypadá jako datum na skutečné datum. Pokud například zadáte 1/1, Excel jej převede na 1. leden aktuálního roku.

V některých případech to může být přesně to, co chcete, a v některých případech to může fungovat proti vám.

A protože Excel ukládá hodnoty data a času jako čísla, jakmile zadáte 1/1, převede je na číslo představující 1. leden aktuálního roku. V mém případě, když to udělám, převede to na číslo 43831 (pro 01-01-2020).

Pokud tyto buňky používáte jako argument ve vzorci, mohlo by to ve vašich vzorcích být nepořádek.

Jak to opravit?

Opět nechceme, aby nám formát automaticky vybral Excel, takže musíme formát jasně určit sami.

Níže jsou uvedeny kroky ke změně formátu na text, aby text automaticky nepřeváděl na data:

1. Vyberte buňky/rozsah, kde chcete změnit formát
2. Klikněte na kartu Domů
3. Ve skupině Číslo klikněte na rozevírací nabídku Formát
4. Klikněte na text

Nyní, kdykoli do vybraných buněk zadáte cokoli, bude to považováno za text a nebude automaticky změněno.

Poznámka: Výše ​​uvedené kroky by fungovaly pouze pro data zadaná po změně formátování. Nezmění žádný text, který byl převeden na datum před provedením této změny formátování.

Dalším příkladem, kde to může být opravdu frustrující, je zadání textu/čísla s úvodními nulami. Excel tyto úvodní nuly automaticky odstraní, protože je považuje za zbytečné. Pokud například do buňky zadáte 0001, Excel jej změní na 1. V případě, že chcete zachovat tyto úvodní nuly, použijte výše uvedené kroky.

Skryté řádky/sloupce mohou poskytnout neočekávané výsledky

Nejedná se o případ vzorce, který vám poskytne špatný výsledek, ale o použití špatného vzorce.

Předpokládejme například, že máte datovou sadu, jak je uvedeno níže, a chci získat součet všech viditelných buněk ve sloupci C.

V buňce C12 jsem použil funkci SUMA k získání celkové hodnoty prodeje pro všechny tyto dané záznamy.

Zatím je vše dobré!

Nyní použiji filtr na sloupec položky, abych zobrazil pouze záznamy pro prodeje tiskáren.

A tady je problém - vzorec v buňce C12 stále ukazuje stejný výsledek - tj. Součet všech záznamů.

Jak jsem řekl, vzorec nedává špatný výsledek. Ve skutečnosti funkce SUM funguje dobře.

Problém je v tom, že jsme zde použili špatný vzorec.

Funkce SUMA nemůže zohlednit filtrovaná data a poskytnout vám výsledek pro všechny buňky (skryté nebo viditelné). Pokud chcete získat pouze součet/počet/průměr viditelných buněk, použijte funkce SUBTOTAL nebo AGGREGATE.

Klíč s sebou - Pochopte správné používání a omezení funkce v aplikaci Excel.

To jsou některé z běžných příčin, které jsem viděl, kde jsou vaše Vzorce aplikace Excel nemusí fungovat nebo mohou poskytovat neočekávané nebo nesprávné výsledky. Jsem si jistý, že existuje mnoho dalších důvodů, proč vzorec aplikace Excel nefunguje nebo neaktualizuje.

Pokud víte o nějakém jiném důvodu (možná vás něco často rozčiluje), dejte mi vědět v sekci komentáře.

Doufám, že jste našli tento návod užitečný!