Nefunkční vzorce a funkce v Excelu dokáží nejednomu Exceláři zkazit den. Najít chybu ve vzorci a odhalit, proč vzorce nepočítají může být dost úmorný a frustrující proces. Proto se v dnešním videu podíváme na 15 nejčastějších příčin, proč vám nefungují nebo nepočítají excelové funkce a vzorce. Dejte nám v komentářích pod videem vědět, zda se setkáváte i s jinými chybami a co nejčastěji u práce v Excelu trápí vás. Na toto video máme rovněž na Akademii Excelu doplňující video o excelových chybových hláškách, ve kterém si ukazujeme význam chybových hlášek, proč vznikají a jak se s nimi vypořádat.
Excelový soubor ke stažení
Proč nepočítají excelové funkce?
A rovnou se vrhneme na první chyby.
1. Závorky
Jedním z nejčastějších důvodů, proč na vás vyskočí chybová hláška v Excelu při tvorbě funkcí je to, že jste někde zapomněli na závorku. Tato chyba vás nejčastěji potká ve chvíli, kdy kombinujete excelové funkce dohromady. Pokud pracujete pouze s jednou funkcí, dejme tomu s funkcí SUMA a zapomenete na koncovou závorku, tak je Excel natolik chytrý, že za nás závorku doplní a funkce bude fungovat. Pokud kombinujete několik funkcí dohromady jako třeba INDEX & POZVYHLEDAT a zapomenete na konečnou závorku, tak vás Excel na chybu upozorní a závorku doplní za vás. U složitějších kombinací funkcí vám Excel může vrátit chybovou hlášku, pokud někde zapomenete na závorku. Abychom této chybě předešli, tak musíme sledovat počet závorek. Excel nám rovněž pomáhá barevným označením závorek. Stejně barevné závorky patří k sobě. A když ukončujeme závorky, tak nám Excel na druhé straně ukazuje, u jaké funkce jsme závorku ukončili. Pomoci si můžete i prostým spočítáním závorek.
2. Povinné parametry funkce
V excelových funkcích se vyskytují dva typy parametrů funkce. Povinné a nepovinné. Ty nepovinné poznáte podle toho, že když do buňky napíšete vybranou funkci, třeba funkci SVYHLEDAT, tak nepovinné parametry jsou uvedené v hranatých závorkách. Chyba u tvorby vzorců na vás vyskočí i tehdy, pokud zapomenete vyplnit povinný parametr. Chyba na vás může vyskočit, pokud zapomenete vyplnit povinný parametr funkce a nebo pokud jich vyplníte více než máte. Ve funkci SVYHLEDAT zapomeneme vyplnit třetí povinný parametr sloupec, funkci potvrdíme a vyskočí na nás upozornění, že jsme vyplnili málo argumentů funkce. Tedy, že jsme zapomněli na jeden z povinných parametrů funkce.
3. Textové hodnoty
Potrápit vás v Excelu mohou i textové hodnoty. Problém s textovými hodnotami je ten, že je nesmíme zapomenout uvést do uvozovek. Jako v následujícím příkladu, kde chceme pomocí funkce KDYŽ spočítat 5% z tržeb, pokud je produkt roven halenkám. Funkce KDYŽ by tedy vypadala tak, že ve funkci KDYŽ ověříme, že se buňka rovná Halenkám, a pokud ano, tak chceme tržbu vynásobit 5 % a pokud ne, tak chceme vrátit nulu. Funkci potvrdíme a funkce KDYŽ vrátí chybovou hlášku #NÁZEV?. A je to z toho důvodu, že jsme zapomněli slovo halenka uvést do uvozovek. Vrátíme se tedy k funkci a chybu opravíme. Zabalíme textovou hodnotu do uvozovek a funkci opět potvrdíme a stáhneme ji dolů a teď už funkce počítá.
4. Nesoulad ve formátu
Někdy se číselná a datumová hodnota může na první pohled jevit jako číslo, ale ve skutečnosti je buňka zformátována jako text. V takovém případě, pokud toto číslo nebo datum použijete v kalkulacích, tak výpočet nebude fungovat. To vidíme i na následujícím příkladu, kde máme pět čísel, když ale na sloupec použijeme funkci POČET, tak se místo hodnoty 5 vrátí hodnota 3. A je to proto, protože dvě čísla 100 a 400 jsou zformátována jako text a funkce POČET textové hodnoty ignoruje. Stejně tak když do podmínkové funkce KDYŽ napíšete, že výsledkem funkce je jednička, ale jedničku uvedete do uvozovek. Excel se teď k této jedničce bude chovat jako k textové hodnotě. Řešení je několik. Textovou hodnotu někdy poznáte na první pohled podle zarovnání. Excel řadí v základu textové hodnoty doleva a číselné doprava. Pokud je v rámci sloupce nesoulad v datových typech, tak vám je Excel většinou označí těmito zelenými rohy. Máte tak možnost kliknout na buňku a nechat textovou hodnotu převést na číslo.
5. Česká a anglická verze Excelu
U českých verzích Excelu se vám může stát, že omylem použijete správnou funkci ale špatný název. A to zejména pokud sledujete zahraniční návody, kde jsou funkce uvedené pod anglickými názvy. Klasicky tak můžete zaměnit funkci SVYHLEDAT a funkci VLOOKUP. Pokud se v české verzi Excelu budu snažit napsat funkci VLOOKUP, tak chybu poznám téměř okamžitě podle toho, že mi Excel nenabídne nápovědu v podobě parametrů u funkce. Pokud bych si chyby ani tak nevšimla, a funkci potvrdila, tak se vrátí chybová hláška #NÁZEV?. Nezbývá nám nic jiného než se k funkci vrátit a opravit její chybný název, případně si vyhledat překlad anglického názvu funkce.
6. Středník a čárka
U verzí Excelu ještě zůstaneme. Stejně jako se v různých jazykových verzích jmenují jinak funkce, tak se v různých verzích Excelu využívají i jiné znaky pro oddělení parametrů. Zatímco v anglických verzích Excelu se používají čárky, tak v české verzi Excelu se používá středník. Na tuto chybu vás ale Excel velmi rychle upozorní. Zkusíme napsat funkci SVYHLEDAT, kde po prvním parametru napíšeme čárku a Excel na nás okamžitě spustí hlášku, že máme chybu ve vzorci. Správnost oddělovačů si nejlépe ověříte u nápovědy funkce. Chybu odklikneme a vrátíme se k funkci a když se pořádně zaměříme na nápovědu, tak vidíme, že máme parametry funkce oddělené středníkem, což je správný znak na oddělení pro naši verzi Excelu.
7. Funkce se neaktualizují
V dalším příkladu máme sloupec hodnot, který si vedle sečteme pomocí funkce SUMA. Funkci potvrdíme a máme výsledek. Teď smažeme některé hodnoty a očekáváme, že se vzorec SUMA přepočítá. Když ale hodnoty smažeme, tak funkce SUMA stále ukazuje původní výsledek. A to i přesto, že se odkazuje na správné buňky, což ověříme tím, že do vzorce klikneme, čímž se označí buňky, které jsou zdrojem funkce. Chyba je v nastavení Excelu. Pokud se vám vzorce v Excelu nepřepočítávají, a to i přesto, že je evidentně máte správně, tak klikněte na kartu Vzorce a pod záložkou Možnosti výpočtů ověřte, že zde máte vybráno Automaticky a nikoliv Manuálně. To je právě i chyba současného Excelu. Máme zde vybráno manuálně, to znamená, že se vzorce automaticky nepřepočítávají. Pokud nastavení změníme, tak se vzorec SUMY okamžitě opraví. Pokud bychom z nějakého důvodu chtěli nechat nastavení na Ručně, tak bychom po každé změně museli na kartě Vzorce vybrat Přepočítat, abychom ručně donutili Excel k přepočítání.
8. Funkce nepočítá
Existuje několik základních důvodů, proč vám excelové funkce nemusejí počítat. Některé si teď ukážeme. V následujících třech sloupcích máme hodnoty a pod nimi máme funkci SUMA, pomocí které jsme chtěli hodnoty sečíst. Jak ale vidíme, tak ve všech třech případech se místo výsledku ukázala funkce SUMA, což není to, co jsme chtěli. Ve všech třech případech máme funkci SUMA použitou správně. U prvního sloupce je problém v tom, že buňka, ve které máme funkci SUMA je zformátována jako text. Což ověříme tak, že do buňky klikneme a na kartě Domů vidíme, že formát buňky je opravdu text. Řešením je změnit formát buňky na číslo nebo alespoň obecný formát. Změníme formát, klikneme na funkci a znovu ji potvrdíme pomocí ENTER. Teď už funkce počítá.
Ve druhém sloupci je podobný problém. Zde máme ale buňku naformátovanou jako číslo, takže ve formátu problém není. Po bližší inspekci zjistíme, že jsme před rovná se omylem napsali mezeru. Excel díky této nadbytečné mezeře nechápe, že se jedná o vzorec a chová se k obsahu v buňce jako k textu. Stačí smazat nadbytečnou mezeru a funkce začne počítat. Nadbytečná mezera by vám udělala i problém, pokud byste ji omylem napsali mezi funkci a závorky. Pokud mezi funkci SUMA a první závorku vložím mezeru, tak se Excel bude k funkci opět chovat jako k textu.
Ve třetím sloupci máme opět podobný problém. Nicméně tentokrát jsme omylem před rovná se napsali apostrof. Opět se k buňce Excel chová jako k textu a proto nenechá funkci SUMA vrátit správný výsledek. Řešením je smazat nadbytečný znak a funkci potvrdit.
Stejně tak se vám celý vzorec místo výpočtu ukáže i na následujícím listu. Zde máme opět funkci SUMA pro hodnoty ve sloupci a opět zde máme viditelnou funkci místo výsledku. Tentokrát je důvodem to, že na kartě Vzorce máme zaškrtnuté pole Zobrazit vzorce, což zobrazí všechny vzorce místo jejich výsledku. Pokud tlačítko odškrtneme, tak se funkce vrátí do své normální podoby a bude počítat.
Dalším důvodem, proč vám funkce nefunguje je to, že jste zapomněli, že se jedná o maticovou funkci, která se, ve straších verzích Excelu, potvrzuje místo klávesy ENTER spojením kláves CTRL+SHIFT+ENTER. Třeba u funkce TRANSPOZICE. Novější verze Excelu od 2019 se o tuto chybu nemusejí starat, jelikož v těchto nových verzích se již maticové funkce potvrzují pouze klávesou ENTER.
9. Nadbytečné mezery
Nejvíce problémů v Excelu způsobují nadbytečné mezery. Tak jako mohou ze vzorce udělat textovou hodnotu, tak jsou velmi častým důvodem, proč vám funkce nepočítají nebo nevyhledávají, a to i přesto, že máte funkci technicky správně. Excel se k nadbytečným mezerám chová jako k dalšímu znaku, který patří do obsahu buňky. Velmi často se tento problém vyskytuje u vyhledávacích funkcí. V příkladu máme funkci SVYHLEDAT na vyhledání produktu z tabulky. Funkce ovšem nepočítá, a to i přesto, že napsanou ji máme správně, stejně tak máme správně parametry funkce. Problémem je nadbytečná mezera u hledaného produktu. Pokud mezeru smažeme, funkce SVYHLEDAT okamžitě vrátí správný výsledek. Řešením je očistit text a hodnoty před použitím jakýchkoliv funkcí, a to buď ručně, pomocí excelových funkcí jako funkce PROČISTIT nebo pomocí Power Query.
10. Formátujte pomocí formátu
Chybu vám může způsobit i to, že do buňky k číslům přidáváte ručně symboly měn. Třeba dolaru nebo eura. Pokud před hodnoty tržeb napíšeme znak dolaru, tak Excel nepochopí, že se ve skutečnosti jedná o číslo a bude se k hodnotě chovat jako k textu, a tím pádem nebude s hodnotou počítat ve funkcích. Řešením je vpisovat do Excelu pouze čísla v obecném formátu a na formátování následně použít příslušný formát, například měny.
11. Nesprávná fixace
Velmi častým důvodem, proč vám nefungují vzorce je nesprávná fixace buněk. Jedná se o situaci, kdy máte funkci technicky správně, ale po potažení funkce vám funkce vrátí nesprávný výsledek a nebo nesmysl. Chybu poznáte snadno. Stačí do funkce kliknout, čímž se označí zdrojové buňky a hned je nám jasné, že nemáme buňky zafixované a tím pádem se nám buňky posunuly spolu s potažením vzorce. Řešením je vrátit se k první buňce a buňky správně zafixovat.
12. Cyklický odkaz
V dalším příkladu máme opět funkci SUMA, která nám ale místo správného výsledku vrací nulu. Důvod zjistíme, když klikneme do vzorce a necháme si zvýraznit zdrojové buňky. Funkce SUMA se odkazuje na buňky od A3 do A10, což je chyba, jelikož jsme do funkce SUMA omylem zahrnuli i samotný součtový řádek. A to je právě chyba cyklického odkazu. Řešením je funkci opravit. Na tuto chybu vás Excel ovšem upozorní. Smažeme funkci a uděláme chybu znovu, napíšeme SUMA a označíme buňky A3 až A10. Funkci potvrdíme a hned na nás vyskočí upozornění, že máme v sešitu cyklický odkaz. Pokud potvrdíme, tak funkce SUMA právě vrátí nulu.
13. Skryté sloupce a řádky
Potrápit vás v Excelu mohou i skryté řádky a sloupce. Jako v následujícím příkladu. Ve sloupci máme samé jedničky a v součtovém řádku je sčítáme. Správně by nám funkce SUMA měla vrátit hodnotu 10, jelikož vidíme deset jedniček. Funkce ovšem vrací hodnotu 12. Proč? Protože nám někdo skryl řádky, kterých jsme si nevšimli. Na sloupec jsme použili klasickou funkci SUMA, která počítá i se skrytými řádky a sloupci a proto se nám vrací hodnota 12, jelikož zde máme dvě jedničky skryté. Řešením je buď odhalit skryté buňky a nebo použít funkci SUBTOTAL, která umí pracovat s vyfiltrovanými a skrytými hodnotami.
14. Zaokrouhlování
V následujícím příkladu chceme k hodnotě 7,5 vyhledat produkt z tabulky. K vyhledání jsme použili vyhledávací kombinaci INDEX & POZVYHLEDAT, ale funkce vrací chybovou hlášku #NENÍ_K_DISPOZICI, a to i přesto, že vizuálně vidíme, že hodnota v tabulce je. Hodnota tam sice je, ale pouze na oko. Hodnota ve zdrojové tabulce je sice 7,5, ale na buňku je uplatnění zaokrouhlení na jedno desetinné místo pomocí formátu. Když buňky označíme a zvýšíme množství desetinných míst na 4, tak zjistíme, že ve skutečnosti není hodnota 7,5, ale 7,5254. Proto vyhledávací funkce číslo nenašla. Pokud bychom vyhledání změnily na přesné číslo 7,5254, tak funkce začne fungovat. Řešením je buď vyhledávat přesná čísla a nebo použít zaokrouhlovací funkce v Excelu. V tomto případě bychom tedy sloupec ze zdrojové tabulky ve funkci POZVYHLEDAT zabalili do funkce ZAOKROUHLIT, kde bychom zaokrouhlili na jedno desetinné místo. Funkci potvrdíme a máme správný výsledek.
15. Posloupnost závorek
Závorky vás nemusejí potrápit jen, když na ně zapomenete, ale musíte si dát rovněž pozor, že k sobě do závorek spojujete funkce tak, aby vrátili výsledek, který požadujete. Musí být dodržená nějaká logika posloupnosti jako v matematice. Následující vzorec má určit, na kterých řádcích se ve sloupci vyskytuje číslo sto. Funkce jsou použité správně, stejně jako logika, ale chyba je v závorkách. Teď vzorec vrací nepravdu na každém řádku. Správně totiž musí být jak tato první část ve vlastních závorkách, tak obě funkce řádek musejí být ve vlastních závorkách. Když vzorec opravíme a závorky dopíšeme, tak funkce začne fungovat. Teď nám již vrací pořadová čísla řádku, na kterých je číslo sto.
Jedna odpověď
😊