Excel nepočítá vzorce zobrazující 0. Problémy se vzorci v aplikaci Microsoft Excel

Datum: 15. listopadu 2018 Kategorie:

Dobrý den, přátelé. Zažili jste někdy, že po zadání vzorce se v buňce místo výsledku výpočtu zobrazí samotný vzorec? To je trochu odrazující, protože jste udělali všechno správně, ale nedostali jste co. Jak donutit program, aby v tomto případě vypočítal vzorec?

Nejprve musíte pochopit důvody tohoto chování a poté situaci napravit. A důvody mohou být následující.

Zobrazit vzorce povoleny

Excel má režim výpočtu. Když je zapnutá, na listu vidíte vzorce. Jak a proč použít zobrazení vzorců, jsem řekl v . Zkontrolujte, zda je aktivován, a poté jej deaktivujte. Na stuze je knoflík Vzorce - Závislosti vzorců - Zobrazit vzorce. Pokud je povoleno, kliknutím na něj jej deaktivujete.

Často se zobrazení vzorců zapíná omylem stisknutím kombinace kláves Ctrl+` .

Vzorec je programem vnímán jako text

Pokud si Excel myslí, že buňka obsahuje textový řetězec, nevypočítá hodnotu, ale jednoduše zobrazí obsah na obrazovce. Může to mít několik důvodů:

Uvedl jsem všechny běžné případy, kdy se místo výsledku zobrazí vzorec. Myslím, že váš případ bude ve výše uvedeném seznamu, nebude těžké problém vyřešit. Pokud by to nevyšlo, napište do komentářů.

Slibuje, že bude velmi zajímavý. Podíváme se na několik jednoduchých technik pro rychlé psaní vzorců. Pokud je přivedete k automatizaci, bude vaše práce jednodušší a rychlejší. Přihlaste se k odběru aktualizací, ať vám nic neunikne!

U chybných výpočtů vzorce místo hodnot zobrazují několik typů chyb. Zvažme je na praktických příkladech v procesu pracovních vzorců, které poskytly chybné výsledky výpočtů.

Chyby vzorce Excel zobrazené v buňkách

Tato lekce popíše chybové hodnoty vzorců, které mohou buňky obsahovat. Když znáte význam každého kódu (například: #HODNOTA!, #DIV/0!, #ČÍSLO!, #N/A!, #JMÉNO!, #PRÁZDNÝ!, #REFERENCE!), můžete snadno zjistit, jak chybu ve vzorci a odstranit ji.

Jak odstranit #DIV/0 v Excelu

Jak vidíte, při dělení buňkou s prázdnou hodnotou to program vnímá jako dělení 0. Ve výsledku dává hodnotu: #DIV/0! To lze ověřit pomocí nápovědy.

V jiných aritmetických výpočtech (násobení, sčítání, odčítání) je prázdná buňka také nulová hodnota.

Výsledek chybného výpočtu je #ČÍSLO!

Špatné číslo: #NUMBER! je chyba nemožnosti provést výpočet ve vzorci.

Několik praktických příkladů:

Chyba: #NUMBER! nastane, když je číselná hodnota příliš velká nebo příliš malá. K této chybě může dojít také při pokusu o získání odmocniny záporného čísla. Například =ROOT(-25).

V buňce A1 je číslo příliš velké (10^1000). Excel neumí pracovat s tak velkými čísly.

Buňka A2 má stejný problém s velkými čísly. Zdálo by se, že 1000 je malé číslo, ale když vrátíte jeho faktoriál, ukáže se, že je to příliš velká číselná hodnota, kterou Excel nezvládne.

V buňce A3 nemůže být odmocnina ze záporného čísla a program tento výsledek zobrazil se stejnou chybou.

Jak odstranit ND v Excelu

Hodnota není k dispozici: #N/A! - znamená, že hodnota není pro vzorec k dispozici:

Zapsaný vzorec v B1: =MATCH("Maxim", A1:A4) hledá textový obsah "Maxim" v oblasti buněk A1:A4. Obsah nalezen ve druhé buňce A2. Funkce tedy vrátí výsledek 2. Druhý vzorec hledá textový obsah "Andrey", pak rozsah A1:A4 takové hodnoty neobsahuje. Proto funkce vrátí chybu #N/A (žádná data).

Chyba #NAME! v Excelu

Být klasifikován jako chyba v psaní funkcí. Neplatné jméno: #NAME! - znamená, že Excel nerozpoznal text zapsaný ve vzorci (název funkce = SUM () je mu neznámý, je zapsán s chybou). Toto je výsledek syntaktické chyby při psaní názvu funkce. Například:

Chyba #BLANK! v Excelu

Prázdná sada: #EMPTY! jsou chyby nastaveného operátora křižovatky. V Excelu existuje něco jako průnik množin. Používá se k rychlému načtení dat z velkých tabulek dotazem na průsečík vertikálního a horizontálního rozsahu buněk. Pokud se rozsahy neprotínají, program zobrazí chybnou hodnotu - #PRÁZDNÝ! Nastavený operátor křižovatky je jedna mezera. Odděluje vertikální a horizontální rozsahy uvedené v argumentech funkce.

V tomto případě je průsečíkem rozsahů buňka C3 a funkce zobrazí jeho hodnotu.

Uvedené argumenty ve funkci: =SUM(B4:D4 B2:B3) – netvoří průnik. Funkce tedy dává hodnotu s chybou - #PRÁZDNÝ!

#ODKAZ! - Chyba odkazu na buňku Excelu

V tomto příkladu došlo k chybě, když byl vzorec zkopírován nesprávně. Máme 3 rozsahy buněk: A1:A3, B1:B4, C1:C2.

V první oblasti zadejte do buňky A4 sčítací vzorec: =SUM(A1:A3). A pak zkopírujte stejný vzorec pod druhou oblastí, do buňky B5. Vzorec, jako dříve, sečte pouze 3 buňky B2:B4 a vynechá hodnotu prvního B1.

Když byl stejný vzorec zkopírován do třetího rozsahu, funkce vrátila chybu #REF! v buňce C3. Protože nad buňkou C3 mohou být pouze 2 buňky a nikoli 3 (jak vyžaduje původní vzorec).

Poznámka. V tomto případě je nejpohodlnější stisknout kombinaci horkých kláves ALT + = pod každým rozsahem před zahájením zadávání. Poté vložte součtovou funkci a automaticky určete počet sčítacích buněk.

Jak opravit VALUE v Excelu

#HODNOTA! - chyba ve významu. Pokud se pokusíme přidat číslo a slovo v Excelu, dostaneme #HODNOTA! Zajímavostí je, že pokud bychom se pokusili pomocí funkce =SUM() sečíst dvě buňky, ve kterých je hodnota první číslo a druhá text, pak k chybě nedojde a text nabude hodnoty 0 při výpočtu. Například:

Mřížky v buňce Excelu

Řádek sloupců místo hodnoty buňky ###### – tato hodnota není chybou. Je to jen informace, že šířka sloupce je příliš úzká na to, aby vyhovovala správnému zobrazení obsahu buňky. Stačí rozbalit sloupec. Například poklepejte levým tlačítkem myši na okraj záhlaví sloupců dané buňky.

Takže mřížky (######) místo hodnot buněk lze vidět se záporným datem. Například se snažíme odečíst nové datum od starého data. A jako výsledek výpočtu je formát buněk "Datum" (a ne "Obecné").

Nesprávný formát buňky může také místo hodnot zobrazit řadu hash značek (######).

Jak vypočítat součet hodnot v Excelu

Řekněme, že máte sloupec s hodnotami a potřebujete znát jejich součet. Nejjednodušší způsob - můžete vybrat požadované buňky a v pravém dolním rohu Excelu ve stavovém řádku uvidíte součet hodnot, jejich počet a dokonce i aritmetický průměr.

Pokud potřebujete přijatou částku někde použít nebo ji jednoduše zobrazit v buňce, použijeme funkci SUM. Může pracovat jak v manuálním režimu, tak v automatickém režimu. Vyberte požadované buňky a klikněte na tlačítko "AutoSum", nachází se na kartě "Domů". Vzorec s výpočtem částky se objeví v další buňce pod těmi, které jste vybrali. Chcete-li vložit částku na libovolné místo, vyberte libovolnou volnou buňku a zadejte „=SUM(“ bez uvozovek, poté vyberte požadovaný rozsah pomocí myši a stiskněte klávesu Enter na klávesnici.

Proč se součet nemusí brát v úvahu v Excelu

Nejpravděpodobnější důvody jsou dva.

1. Chybný oddělovač desetinných míst. Například ve Windows je čárka a v tabulce máte tečku. Zkuste nahradit oddělovací znak pro dvojici hodnot a sečíst je. Chcete-li zjistit, který znak je v systému, přejděte na "Ovládací panely", vyberte "Regionální a jazykové nastavení" -> "Pokročilé možnosti":

2. Vaše buňky jsou v textovém formátu a kromě čísel mají další znaky, například mezery. Vyberte je a stiskněte pravé tlačítko myši. Vyberte z nabídky "Formát buněk" a nastavte formát na "Číslo", pokud tomu tak není. Zkontrolujte, zda v buňkách není nic kromě čísel.

Máte-li jakékoli dotazy nebo potřebujete upřesnění – dotaz, naši odborníci na váš požadavek obratem odpoví.

Hlavní funkcí programu Excel je práce se vzorci. K tomu se používá nejčastěji. Někdy se stane, že vzorce v Excelu nefungují, v takovém případě je potřeba něco udělat. O tom bude řeč dále.

Vyřešíme problém

Existuje mnoho důvodů, proč vzorec v Excelu nefunguje. Možná jste špatně nastavili nastavení programu nebo konkrétní rozsah nebo je špatně zadán vzorec.

Vzorce při přetahování nefungují

Podstatou chyby je, že se vytvoří několik sloupců, například 2, a výsledek by se měl zobrazit ve třetím. Vzorec se tam zadá a přetáhne dolů, aby pokryl celý rozsah, ale nakonec se hodnota z první buňky jednoduše přenese do další. V tomto případě existuje několik způsobů, jak vše opravit:

Stisknutím tlačítka F9 spustíte přepočet listu.
Změňte formát buňky na obecný. Chcete-li to provést, vyberte jej a klepněte pravým tlačítkem myši. Otevře se nabídka, kde byste měli vybrat "Formát buňky" a změnit na "Obecné".
Otevřete možnosti výpočtu vzorce a zkontrolujte, zda jsou značky stejné jako na snímku obrazovky.

Vzorec se nepočítá

Existuje několik důvodů, proč se vzorec Excel nepočítá:

Neplatný formát buňky. Chcete-li jej změnit, musíte vybrat požadovaný rozsah buněk a vybrat "Formát buňky".

Je aktivován režim "Zobrazit vzorce". Chcete-li jej vypnout, přejděte do sekce "Vzorce" na horním panelu, klikněte na "Závislosti vzorců" a vypněte "Zobrazit vzorce".

Vzorec nebo jeho syntaxe byly zadány nesprávně. V tomto případě se místo hodnoty zobrazí vzorec. Musíte to pečlivě zkontrolovat.

To jsou některé z hlavních důvodů, proč Excel nepočítá vzorce.

Program nezohledňuje vzorec zadaný v buňce

To se často stává, protože formát buňky je nastaven na text. Jak jej změnit na jiný již bylo popsáno dříve.

Částka se nepočítá

Existují dvě možnosti, proč aplikace Excel nevypočítá částku:

Formát buňky je testovací.
Výpočet součtu je deaktivován. Je nutné pomocí pravého tlačítka myši kliknout na speciální panel a nastavit značku na "Částka".

Proto se na součet nepočítá.

Data buňky nejsou správně sečtena

Někteří uživatelé se setkali s tím, když Excel nesprávně vypočítá součet vybraných buněk. Často je to způsobeno tím, že některé buňky, nebo dokonce většina z nich, jsou v textovém formátu. Z tohoto důvodu nejsou shrnuta všechna data a výsledek je nesprávný. Chcete-li problém vyřešit, musíte změnit formát na „Obecné“.

Vypnutý přepočet

Stává se, že uživatel vypočítal všechna data, ale když jsou opraveny v buňkách spojených se vzorcem, výsledek se nezmění. To znamená, že nesprávně nastavené parametry:

Otevřete nastavení programu.
Zde byste měli přejít do sekce "Vzorce". Nahoře bude blok "Parametry výpočtů" a tam parametr "Výpočty v knize", kde by měla být vybrána pozice "Automaticky". Pokud ne, nastavte jej správně a klikněte na OK.

Problémy se vzorci v aplikaci Microsoft Excel

Nyní, pokud se nějaká hodnota změní, bude výsledek přepočítán.

Z toho všeho je vidět, že existuje mnoho různých důvodů, proč Excel počítá vzorec špatně nebo je odmítá vypočítat vůbec. Pokud má uživatel vzorec zobrazený tam, kde má být výsledek, pak je formát buňky nastaven na text, nebo došlo k chybě v samotném vzorci. Možná byl aktivován režim zobrazení. Uživatelé také často zapomínají vložit „=“ před začátek vzorce. Pokud se hodnoty v souvisejících buňkách změní, ale výsledek zůstane stejný, musíte zkontrolovat, zda je aktivována automatická aktualizace. V některých případech se v buňkách se součty zobrazí chyba, pak byste měli zkontrolovat všechny hodnoty.

Můžete přizpůsobit zobrazení nulových hodnot v buňce nebo použít sadu standardů formátování v tabulce, které vyžadují skrytí nulových hodnot. Nulové hodnoty můžete zobrazit a skrýt různými způsoby.

Skrytí a zobrazení všech hodnot null v listu

Vybrat Soubor > Možnosti > dodatečně.

Ve skupině

Zobrazit nuly v buňkách, které obsahují nulové hodnoty.

Skrytí hodnot null ve vybraných buňkách

Chcete-li skrýt hodnoty null ve vybraných buňkách pomocí číselného formátu, postupujte takto. Pokud se hodnota v jedné z těchto buněk změní na nenulovou hodnotu, zobrazí se v buňce a její formát bude podobný obecnému číselnému formátu.

Zobrazit skryté hodnoty.

Vyberte buňky se skrytými nulovými hodnotami.

Můžete stisknout tlačítka CTRL+1 nebo na kartě Domov klikněte Formát > Formátovat buňky.

Chcete-li použít výchozí formát čísel, vyberte Číslo > Obecné a stiskněte tlačítko OK.

Skrytí hodnot null vrácených vzorcem

Na kartě Domov Podmíněné formátování a vyberte Pravidla výběru buněk > Rovná se.

Do levého pole zadejte 0 .

V pravém poli vyberte Vlastní formát.

V terénu Formát buňky otevřít kartu Písmo.

Uvedeno Barva vyberte bílou barvu a stiskněte tlačítko OK.

Zobrazte nuly jako mezery nebo pomlčky

K vyřešení tohoto problému použijte funkci IF.

Pokud buňka obsahuje hodnoty null, pomocí vzorce, jako je tento, vrátíte prázdnou buňku:

KDYŽ(A2-A3=0;"";A2-A3)

Zde je návod, jak číst vzorec. Pokud je výsledek výpočtu (A2-A3) "0", nezobrazí se nic, včetně "0" (to je označeno dvojitými uvozovkami ""). Jinak se zobrazí výsledek výpočtu A2-A3. Pokud nepotřebujete ponechat buňky prázdné, ale zobrazit něco jiného než "0", vložte mezi dvojité uvozovky pomlčku "-" nebo jiný znak.

Vyberte sestavu kontingenční tabulky.

Na kartě Analýza ve skupině kontingenční tabulka Možnosti a vyberte položku Možnosti.

Přejděte na kartu Označení a formát

V terénu Formát zaškrtněte políčko Pro zobrazení chyb

Zaškrtávací políčko U prázdných buněk zobraz

Potřeba zobrazovat nulové hodnoty (0) na listech nevzniká vždy. Ať už formátovací standardy vyžadují, abyste vy nebo vaše vlastní preference zobrazili nebo skryli hodnoty null, existuje několik způsobů, jak implementovat všechny tyto požadavky.

Vybrat Soubor > Možnosti > dodatečně.

Ve skupině Zobrazit možnosti pro další list vyberte list a poté proveďte jednu z následujících akcí:

Chcete-li v buňkách zobrazit nulové (0) hodnoty, zaškrtněte políčko Zobrazit nuly v buňkách, které obsahují nulové hodnoty.

Chcete-li zobrazit hodnoty null jako prázdné buňky, zrušte zaškrtnutí Zobrazit nuly v buňkách, které obsahují nulové hodnoty.

Můžete stisknout tlačítka CTRL+1 nebo na kartě Domov klikněte Formát > Formátovat buňky.

V terénu Typ vstoupit 0;-0;;@

Poznámky:

Skryté hodnoty se zobrazí v řádku vzorců nebo v buňce pouze v případě, že upravujete její obsah. Tyto hodnoty se netisknou.

Chcete-li znovu zobrazit skryté hodnoty, vyberte buňky a stiskněte klávesy CTRL+1 nebo na kartě Domov ve skupině buňky umístěte kurzor na prvek Formát a vyberte Formát buňky. Chcete-li použít výchozí formát čísla, v seznamu Kategorie vybrat Všeobecné. Chcete-li znovu zobrazit datum a čas, vyberte na kartě příslušný formát data a času Číslo.

Vyberte buňku obsahující nulovou (0) hodnotu.

Na kartě Domov ve skupině Styly klikněte na šipku vedle položky Podmíněné formátování, najeďte myší na prvek Pravidla výběru buněk a vyberte možnost Rovná se.

Do levého pole zadejte 0 .

V pravém poli vyberte Vlastní formát.

V dialogovém okně Formát buňky otevřít kartu Písmo.

V terénu Barva zvolte bílou.

K provedení tohoto úkolu použijte funkci LI.

Příklad

Kopírování příkladu

funkce IF.

Skrytí hodnot null v sestavě kontingenční tabulky

Na kartě Možnosti ve skupině Možnosti kontingenční tabulky klikněte na šipku vedle příkazu Možnosti a vyberte položku Možnosti.

Přejděte na kartu Označení a formát a poté proveďte následující:

Změna způsobu zobrazení chyb. V terénu Formát zaškrtněte políčko Pro zobrazení chyb. Do pole zadejte hodnotu, kterou chcete zobrazit místo chyb. Chcete-li chyby zobrazit jako prázdné buňky, odstraňte z pole veškerý text.

Změňte způsob zobrazení prázdných buněk. Zaškrtávací políčko U prázdných buněk zobraz. Do pole zadejte hodnotu, kterou chcete zobrazit v prázdných buňkách. Chcete-li je ponechat prázdné, odstraňte z pole veškerý text. Chcete-li zobrazit hodnoty null, zrušte zaškrtnutí tohoto políčka.

Zobrazit nebo skrýt všechny nulové hodnoty v listu

Skrytí hodnot null ve vybraných buňkách pomocí číselného formátu

Tyto akce umožňují skrýt hodnoty null ve vybraných buňkách. Pokud se hodnota v jedné z buněk stane nenulovou, její formát bude podobný obecnému formátu čísel.

Vyberte buňky obsahující hodnoty null (0), které chcete skrýt.

Můžete stisknout tlačítka CTRL+1 nebo na kartě Domov ve skupině buňky klikněte Formát > Formátovat buňky.

V terénu Typ vstoupit 0;-0;;@

Poznámky:

Skrytí hodnot null vrácených vzorcem pomocí podmíněného formátování

Vyberte buňku obsahující nulovou (0) hodnotu.

Na kartě Domov ve skupině Styly klikněte na šipku vedle tlačítka Podmíněné formátování a vyberte Pravidla výběru buněk > Rovná se.

Do levého pole zadejte 0 .

V pravém poli vyberte Vlastní formát.

V dialogovém okně Formát buňky otevřít kartu Písmo.

V terénu Barva zvolte bílou.

Použití vzorce k zobrazení nul jako mezer nebo pomlček

K provedení tohoto úkolu použijte funkci IF.

Příklad

Pro snazší pochopení tohoto příkladu jej zkopírujte na prázdný list.

Kopírování příkladu

Zvýrazněte příklad uvedený v tomto článku.

Důležité: Nezvýrazňujte záhlaví řádků nebo sloupců.

Zvýrazněte příklad v nápovědě

Stiskněte CTRL+C.

V Excelu vytvořte prázdný sešit nebo list.

Vyberte buňku A1 na listu a stiskněte CTRL+V.

Důležité: Aby příklad fungoval správně, musí být vložen do buňky A1.

Chcete-li přepnout mezi zobrazením výsledků a zobrazením vzorců, které tyto výsledky vracejí, stiskněte CTRL+` (znak diakritického znaménka) nebo na kartě Vzorce ve skupině Závislosti vzorců klepněte na Zobrazit vzorce.

Zkopírováním příkladu na prázdný list jej můžete upravit tak, jak chcete.

Další informace o použití této funkce najdete v článku funkce IF.

Skrytí hodnot null v sestavě kontingenční tabulky

Klepněte na sestavu kontingenční tabulky.

Na kartě Možnosti ve skupině Možnosti kontingenční tabulky klikněte na šipku vedle příkazu Možnosti a vyberte položku Možnosti.

Přejděte na kartu Označení a formát a poté proveďte následující: