Vzorce Excelu nefungujú: Možné dôvody a ako to opraviť!

Ak pracujete so vzorcami v programe Excel, skôr alebo neskôr sa stretnete s problémom, kde vzorce v Exceli vôbec nefungujú (alebo poskytujú nesprávny výsledok).

Aj keď by to bolo skvelé, keby existovalo iba niekoľko možných dôvodov nesprávne fungujúcich vzorcov. Bohužiaľ, existuje príliš veľa vecí, ktoré sa môžu pokaziť (a často sa stávajú).

Ale pretože žijeme vo svete, ktorý sa riadi Paretovým princípom, ak skontrolujete niektoré bežné problémy, pravdepodobne vyrieši 80% (alebo možno dokonca 90% alebo 95% problémov, kde vzorce v programe Excel nefungujú).

A v tomto článku poukážem na tie bežné problémy, ktoré sú pravdepodobne príčinou vášho Vzorce programu Excel nefungujú.

Začnime teda!

Nesprávna syntax funkcie

Na úvod poukážem na zrejmé.

Každá funkcia v programe Excel má špecifickú syntax - napríklad počet argumentov, ktoré môže prijať, alebo typ argumentov, ktoré môže prijať.

A v mnohých prípadoch dôvodom, prečo vaše vzorce v Exceli nefungujú alebo poskytujú nesprávny výsledok, môže byť nesprávny argument (alebo chýbajúce argumenty).

Napríklad funkcia VLOOKUP má tri povinné argumenty a jeden voliteľný argument.

Ak zadáte nesprávny argument alebo neurčíte voliteľný argument (tam, kde je potrebný na to, aby vzorec fungoval), poskytne vám to nesprávny výsledok.

Predpokladajme napríklad, že máte množinu údajov, ako je uvedené nižšie, kde potrebujete poznať skóre známky v skúške 2 (v bunke F2).

Ak použijem nižšie uvedený vzorec, dostanem nesprávny výsledok, pretože v treťom argumente (ten, ktorý pýta stĺpcové indexové číslo) používam nesprávnu hodnotu.

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

V tomto prípade vzorec vypočíta (vráti hodnotu), ale výsledok je nesprávny (namiesto skóre v teste 2 dáva skóre v teste 1).

Ďalším príkladom, kedy musíte byť s argumentmi opatrní, je použitie funkcie VLOOKUP s približnou zhodou.

Pretože na určenie, kde má VLOOKUP vykonať presnú alebo približnú zhodu, musíte použiť voliteľný argument, nešpecifikácia (alebo použitie nesprávneho argumentu) môže spôsobiť problémy.

Nasleduje príklad, kde mám údaje o známkach pre niektorých študentov a chcem extrahovať známky na skúške 1 pre študentov v tabuľke vpravo.

Keď použijem nižšie uvedený vzorec VLOOKUP, pri niektorých menách sa zobrazí chyba.

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

Stáva sa to, pretože som nezadal posledný argument (ktorý sa používa na určenie, či sa má vykonať presná alebo približná zhoda). Ak nezadáte posledný argument, automaticky sa v predvolenom nastavení vykoná približná zhoda.

Pretože sme v tomto prípade potrebovali urobiť presnú zhodu, vzorec vráti pri niektorých menách chybu.

Aj keď som vzal príklad funkcie VLOOKUP, v tomto prípade je to niečo, čo môže byť použiteľné pre mnohé vzorce programu Excel, ktoré majú tiež voliteľné argumenty.

Prečítajte si tiež: Identifikujte chyby pomocou ladenia vzorcov programu Excel

Extra medzery spôsobujúce neočakávané výsledky

Vedúce, koncové medzery je ťažké zistiť a pri použití bunky, ktorá ich obsahuje vo vzorcoch, môže spôsobiť problémy.

Ak sa napríklad v nižšie uvedenom príklade pokúsim použiť VLOOKUP na načítanie skóre pre Marka, zobrazí sa chyba #N/A (nedostupná chyba).

Aj keď vidím, že vzorec je správny a názov „Označiť“ je jasný, že v zozname je, nevidím však to, že v bunke je koncový priestor s názvom (v bunke D2).

Excel nepovažuje obsah týchto dvoch buniek za rovnaký, a preto ho považuje za nesúlad pri načítaní hodnoty pomocou funkcie VLOOKUP (alebo môže ísť o iný vzorec vyhľadávania).

Na vyriešenie tohto problému je potrebné odstrániť tieto medzery.

Môžete to urobiť ktoroukoľvek z nasledujúcich metód:

1. Pred použitím vo vzorcoch očistite bunku a odstráňte všetky začiatočné/koncové medzery
2. Použitím funkcie TRIM vo vzorci zaistíte, že budú ignorované všetky úvodné/koncové/dvojité medzery.

Vo vyššie uvedenom príklade môžete namiesto toho použiť nižšie uvedený vzorec, aby ste sa uistili, že funguje.

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

Aj keď som vzal príklad VLOOKUP, je to tiež bežný problém pri práci s funkciami TEXT.

Ak napríklad použijem funkciu LEN na spočítanie celkového počtu znakov v bunke, ak sú medzery na začiatku alebo na konci, budú sa započítavať aj tieto a poskytnú nesprávny výsledok.

Odobrať / Ako opraviť: Ak je to možné, vyčistite údaje odstránením všetkých začiatočných/koncových alebo dvojitých medzier pred ich použitím vo vzorcoch. Ak nemôžete zmeniť pôvodné údaje, postarajte sa o to pomocou funkcie TRIM vo vzorcoch.

Použitie manuálneho výpočtu namiesto automatického

Toto jedno nastavenie vás môže vyviesť z miery (ak neviete, čo spôsobuje všetky problémy).

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

Štandardne je povolený automatický režim, čo znamená, že v prípade, že použijem vzorec alebo urobím zmeny v existujúcich vzorcoch, automaticky (a okamžite) vykoná výpočet a poskytne mi výsledok.

Toto je nastavenie, na ktoré sme všetci zvyknutí.

V automatickom nastavení vždy, keď v hárku vykonáte akúkoľvek zmenu (napríklad zadanie nového vzorca do niektorého textu v bunke), Excel všetko automaticky prepočíta (áno, všetko).

V niektorých prípadoch však ľudia povoľujú nastavenie manuálneho výpočtu.

Väčšinou sa to robí, keď máte ťažký súbor programu Excel s veľkým počtom údajov a vzorcov. V takýchto prípadoch možno nebudete chcieť, aby Excel pri malých zmenách (pretože môže trvať niekoľko sekúnd alebo dokonca minút), aby sa tento prepočet dokončil, všetko prepočítal.

Ak povolíte ručný výpočet, Excel nebude počítať, pokiaľ ho k tomu nedonútite.

A to vás môže prinútiť myslieť si, že váš vzorec nie je vypočítavý.

Všetko, čo musíte v tomto prípade urobiť, je buď nastaviť výpočet späť na automatický, alebo vynútiť prepočet stlačením klávesu F9.

Nasledujú kroky na zmenu výpočtu z manuálneho na automatický:

1. Kliknite na kartu Vzorec
2. Kliknite na položku Možnosti výpočtu
3. Vyberte položku Automaticky

Dôležité: V prípade, že meníte výpočet z manuálneho na automatický, je vhodné vytvoriť si zálohu zošita (len pre prípad, že by sašit zablokoval alebo by došlo k zlyhaniu Excelu)

Odobrať / Ako opraviť: Ak si všimnete, že vaše vzorce neposkytujú očakávaný výsledok, vyskúšajte niečo jednoduché v ľubovoľnej bunke (napríklad pridanie 1 k existujúcemu vzorcu. Keď problém identifikujete ako problém, v ktorom je potrebné zmeniť režim výpočtu, vykonajte výpočet sily pomocou F9.

Odstránenie riadkov/stĺpcov/buniek vedúcich k #REF! Chyba

Jednou z vecí, ktoré môžu mať zničujúci vplyv na vaše existujúce vzorce v programe Excel, je odstránenie ľubovoľného riadka/stĺpca, ktorý bol použitý vo výpočtoch.

Keď sa to stane, Excel niekedy upraví samotnú referenciu a uistí sa, že vzorce fungujú správne.

A niekedy… to nemôže.

Našťastie jeden jasný náznak, ktorý získate, keď sa vzorce pri odstraňovaní buniek/riadkov/stĺpcov rozbijú, je #REF! chyba v bunkách. Toto je referenčná chyba, ktorá vám hovorí, že existuje problém s referenciami vo vzorci.

Ukážem vám, čo mám na mysli, na príklade.

Ďalej som použil vzorec SUM na pridanie buniek A2: A6.

Ak teraz odstránim niektorú z týchto buniek/riadkov, vzorec SUMA vráti #REF! chyba. Stáva sa to preto, že keď som riadok odstránil, vzorec nevie, na čo sa má teraz odkazovať.

Môžete vidieť, že tretím argumentom vo vzorci sa stal #REF! (ktorý predtým odkazoval na bunku, ktorú sme vymazali).

Odobrať / Ako opraviť: Pred odstránením údajov, ktoré sa používajú vo vzorcoch, sa uistite, že po odstránení nie sú žiadne chyby. Odporúčame tiež vytvárať si pravidelnú zálohu práce, aby ste sa mali vždy na čo obrátiť.

Nesprávne umiestnenie zátvorky (BODMAS)

Keď vaše vzorce začínajú byť stále väčšie a komplexnejšie, je vhodné použiť zátvorky, aby ste si úplne uvedomili, ktorá časť patrí k sebe.

V niektorých prípadoch môžete mať zátvorku na nesprávnom mieste, čo vám môže poskytnúť nesprávny výsledok alebo chybu.

A v niektorých prípadoch sa odporúča použiť zátvorky, aby sa zaistilo, že vzorec porozumie tomu, čo je potrebné najskôr zoskupiť a vypočítať.

Predpokladajme napríklad, že máte nasledujúci vzorec:

=5+10*50

Vo vyššie uvedenom vzorci je výsledok 505, pretože Excel najskôr vykoná násobenie a potom sčítanie (pretože pri operátoroch existuje poradie).

Ak chcete, aby najskôr vykonal sčítanie a potom násobenie, musíte použiť nasledujúci vzorec:

=(5+10)*50

V niektorých prípadoch vám poradie prednosti môže fungovať, ale napriek tomu sa odporúča použiť zátvorku, aby ste sa vyhli nejasnostiam.

V prípade, že vás to zaujíma, nižšie je uvedené poradie priorít pre rôzne operátory často používané vo vzorcoch:

Operátor	Popis	Poradie prednosti
: (dvojbodka)	Rozsah	1
(jeden priestor)	Križovatka	2
, (čiarka)	zväz	3
-	Negácia (ako v -1)	4
%	Percento	5
^	Umocnenie	6
* a /	Násobenie a delenie	7
+ a -	Sčítanie a odčítanie	8
&	Concatnenation	9
= =	Porovnanie	10

Odobrať / Ako opraviť: Vždy používajte zátvorky, aby ste sa vyhli nejasnostiam, aj keď poznáte poradie prednosti a správne ho používate. Vďaka zátvorke je jednoduchšia kontrola vzorcov.

Nesprávne použitie absolútnych/relatívnych odkazov na bunky

Keď kopírujete a prilepujete vzorce v programe Excel, automaticky upraví odkazy. Niekedy je to presne to, čo chcete (väčšinou keď kopírujete vzorce do stĺpca), a niekedy nechcete, aby sa to stalo.

Absolútna referencia je, keď opravíte referenciu na bunku (alebo referenciu na rozsah), aby sa nezmenila pri kopírovaní a vkladaní vzorcov, a relatívna referencia sa zmení.

Tu si môžete prečítať viac o absolútnych, relatívnych a zmiešaných odkazoch.

V prípade, že zabudnete zmeniť odkaz na absolútny (alebo naopak), môžete dostať nesprávny výsledok. To sa mi stáva pomerne často, keď používam vyhľadávacie vzorce.

Ukážem vám príklad.

Nasleduje súbor údajov, kde chcem načítať skóre v skúške 1 pre mená v stĺpci E (jednoduchý prípad použitia VLOOKUP)

Nasleduje vzorec, ktorý používam v bunke F2, a potom skopírujem do všetkých buniek pod ňou:

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

Ako vidíte, tento vzorec v niektorých prípadoch spôsobuje chybu.

Stáva sa to preto, že som nezamkol argument poľa tabuľky - je to tak A2: B6 v bunke F2, pričom to tak malo byť $ A $ 2: $ B $ 6

Tým, že mám tieto znaky dolára pred číslom riadka a stĺpcovou abecedou v referencii na bunku, nútim Excel, aby nechal tieto odkazy na bunky pevné. Takže aj keď skopírujem tento vzorec nadol, pole tabuľky bude naďalej odkazovať na A2: B6

Pro tip: Ak chcete previesť relatívny odkaz na absolútny, vyberte odkaz na bunku v bunke a stlačte kláves F4. Všimli ste si, že sa to zmení, pridaním znakov dolára. Môžete pokračovať v stláčaní klávesu F4, kým nezískate požadovanú referenciu.

Nesprávny odkaz na názvy hárkov / zošitov

Keď vo vzorci odkazujete na iné listy alebo zošity, musíte postupovať podľa konkrétneho formátu. A v prípade, že je formát nesprávny, zobrazí sa chyba.

Ak by som napríklad chcel odkazovať na bunku A1 v hárku 2, odkaz by bol = List2! A1 (kde za názvom hárka je výkričník)

A v prípade, že v názve hárka je viac slov (povedzme, že ide o vzorové údaje), bude referencia = 'Ukážkové údaje'! A1 (kde je názov uzavretý do jednoduchých úvodzoviek, za ktorými nasleduje výkričník).

V prípade, že sa odvolávate na externý zošit (povedzme, že odkazujete na bunku A1 v ‘Príkladovom hárku’ v zošite s názvom „Príklad pracovného zošita”), odkaz bude nasledujúci:

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

A v prípade, že zošit zatvoríte, odkaz sa zmení tak, aby zahrnoval celú cestu zošita (ako je uvedené nižšie):

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

V prípade, že nakoniec zmeníte názov zošita alebo pracovného hárka, na ktoré sa vzorec vzťahuje, získate #REF! chyba.

Prečítajte si tiež: Ako nájsť externé odkazy a referencie v programe Excel

Kruhové referencie

Kruhový odkaz je, keď odkazujete (priamo alebo nepriamo) na rovnakú bunku, v ktorej sa vzorec vypočítava.

Nasleduje jednoduchý príklad, kde používam vzorec SUM v bunke A4 a zároveň ho používam pri samotnom výpočte.

= SUM (A1: A4)

Aj keď vám program Excel zobrazí výzvu s upozornením na kruhovú referenciu, nebude to fungovať pre všetky inštancie. A to vám môže poskytnúť nesprávny výsledok (bez akéhokoľvek varovania).

V prípade, že máte podozrenie na kruhový odkaz v hre, môžete skontrolovať, ktoré bunky ho majú.

Ak to chcete urobiť, kliknite na kartu Vzorec a v skupine „Audit podľa vzorca“ kliknite na rozbaľovaciu ikonu Kontrola chýb (malá šípka smerujúca nadol).

Ukážte kurzorom na možnosť Kruhová referencia a zobrazí sa bunka, v ktorej je problém s kruhovou referenciou.

Bunky formátované ako text

Ak sa ocitnete v situácii, keď ihneď po zadaní vzorca ako príklepu Enter uvidíte vzorec namiesto hodnoty, je to jasný prípad, keď sa bunka formátuje ako text.

Keď je bunka naformátovaná ako text, vzorec považuje za textový reťazec a zobrazuje ho taký, aký je.

Nenúti ho to vypočítať a poskytnúť výsledok.

A má jednoduchú opravu.

1. V časti „Text“ zmeňte formát na „Všeobecné“ (nachádza sa na karte Domov v skupine Čísla)
2. Prejdite do bunky, ktorá má vzorec, vstúpte do režimu úprav (použite kláves F2 alebo dvakrát kliknite na bunku) a stlačte kláves Enter

V prípade, že vyššie uvedené kroky problém nevyriešia, ďalšou vecou, ​​ktorú je potrebné skontrolovať, je, či má bunka na začiatku apostrof. Mnoho ľudí pridáva apostrof na prevádzanie vzorcov a čísel na text.

Ak existuje apostrof, môžete ho jednoducho odstrániť.

Text sa automaticky prevádza do dátumov

Program Excel má tento zlozvyk prevodu, ktorý vyzerá ako dátum na skutočný dátum. Ak napríklad zadáte 1/1, Excel ho skonvertuje na 1. január aktuálneho roka.

V niektorých prípadoch to môže byť presne to, čo chcete, a v niektorých prípadoch to môže fungovať proti vám.

A keďže Excel ukladá hodnoty dátumu a času ako čísla, hneď ako zadáte 1/1, skonvertuje ho na číslo predstavujúce 1. január aktuálneho roka. V mojom prípade, keď to urobím, prevedie to na číslo 43831 (pre 01-01-2020).

Ak tieto bunky používate ako argument vo vzorci, môže to mať problémy s vašimi vzorcami.

Ako to opraviť?

Opäť nechceme, aby nám formát automaticky vybral Excel, takže musíme formát jasne určiť sami.

Nasledujú kroky na zmenu formátu na text tak, aby text automaticky nekonvertoval na dátumy:

1. Vyberte bunky/rozsah, v ktorých chcete zmeniť formát
2. Kliknite na kartu Domov
3. V skupine Číslo kliknite na rozbaľovaciu ponuku Formát
4. Kliknite na text

Teraz, kedykoľvek do vybraných buniek zadáte čokoľvek, bude to považované za text a nebude sa to automaticky meniť.

Poznámka: Vyššie uvedené kroky budú fungovať iba pre údaje zadané po zmene formátovania. Nezmení sa žiadny text, ktorý bol prevedený na dátum pred vykonaním tejto zmeny formátovania.

Ďalším príkladom, kde to môže byť skutočne frustrujúce, je zadanie textu/čísla s úvodnými nulami. Excel tieto úvodné nuly automaticky odstráni, pretože ich považuje za zbytočné. Ak napríklad do bunky zadáte 0001, Excel ho zmení na 1. V prípade, že chcete zachovať tieto úvodné nuly, postupujte podľa vyššie uvedených krokov.

Skryté riadky/stĺpce môžu poskytnúť neočakávané výsledky

Nejde o prípad vzorca, ktorý vám poskytne nesprávny výsledok, ale o použitie nesprávneho vzorca.

Predpokladajme napríklad, že máte množinu údajov, ako je uvedené nižšie, a chcem získať súčet všetkých viditeľných buniek v stĺpci C.

V bunke C12 som použil funkciu SUMA na získanie celkovej hodnoty predaja pre všetky tieto dané záznamy.

Zatiaľ je všetko dobré!

Teraz do stĺpca položky použijem filter, aby sa zobrazili iba záznamy pre predaje tlačiarní.

A tu je problém - vzorec v bunke C12 stále ukazuje rovnaký výsledok - tj. Súčet všetkých záznamov.

Ako som povedal, vzorec nedáva zlý výsledok. V skutočnosti funkcia SUM funguje dobre.

Problém je v tom, že sme tu použili nesprávny vzorec.

Funkcia SUMA nemôže zohľadniť filtrované údaje a poskytnúť vám výsledok pre všetky bunky (skryté alebo viditeľné). Ak chcete získať iba súčet/počet/priemer viditeľných buniek, použite funkcie SUBTOTAL alebo AGGREGATE.

Kľúč so sebou - Pochopiť správne používanie a obmedzenia funkcie v programe Excel.

Toto sú niektoré z bežných príčin, ktoré som zaznamenal Vzorce programu Excel nemusia fungovať alebo môžu poskytovať neočakávané alebo nesprávne výsledky. Som si istý, že existuje mnoho ďalších dôvodov, prečo vzorec programu Excel nefunguje alebo neaktualizuje.

V prípade, že poznáte nejaký iný dôvod (možno vás to často znepokojuje), dajte mi vedieť v sekcii komentárov.

Dúfam, že ste našli tento návod užitočný!