Aritmetická operace s plovoucí desetinnou čárkou mohou v Excelu poskytovat nepřesné výsledky

  • Článek
  • Platí pro:
    Excel 2010, Excel 2013, Excel for Microsoft 365, Microsoft Excel for Mac 2011, Excel for Mac for Microsoft 365

Souhrn

Tento článek popisuje, jak aplikace Microsoft Excel ukládá a vypočítává čísla s plovoucí desetinnou čárkou. To může mít vliv na výsledky některých čísel nebo vzorců kvůli zaokrouhlení nebo zkrácení dat.

Přehled

Microsoft Excel se řídí specifikací IEEE 754 pro ukládání a výpočet čísel s plovoucí desetinnou čárkou. IEEE je Institut pro elektrotechnické a elektronické inženýrství, mezinárodní organizace, která mimo jiné určuje standardy pro počítačový software a hardware. Specifikace 754 je velmi široce přijímaná specifikace, která popisuje, jak by měla být čísla s plovoucí desetinnou čárkou uložena v binárním počítači. Je oblíbená, protože umožňuje ukládat čísla s plovoucí desetinnou čárkou na přiměřeném prostoru a výpočty probíhají relativně rychle. Standard 754 se používá v jednotkách s plovoucí desetinnou čárkou a číselných datových procesorech téměř všech dnešních mikroprocesorů založených na počítači, které implementují matematiku s plovoucí desetinnou čárkou, včetně procesorů Intel, Motorola, Sun a MIPS.

Při uložení čísel může odpovídající binární číslo představovat každé číslo nebo desetinné číslo. Například zlomek 1/10 může být v desítkovém číselném systému znázorněn jako 0,1. Stejné číslo v binárním formátu se však stane následujícím opakujícím se binárním desítkovým číslem:

0001100110011100110011 (a tak dále)

To se může nekonečně opakovat. Toto číslo nesmí být znázorněno v konečném (omezeném) prostoru. Proto je toto číslo při uložení zaokrouhleno dolů přibližně o -2,8E-17.

Existují ale určitá omezení specifikace IEEE 754, která spadají do tří obecných kategorií:

  • Maximální/minimální omezení
  • Přesnost
  • Opakující se binární čísla

Další informace

Maximální/minimální omezení

Všechny počítače mají maximální a minimální číslo, které je možné zpracovat. Vzhledem k tomu, že počet bitů paměti, ve kterých je číslo uložené, je konečný, znamená to, že maximální nebo minimální číslo, které je možné uložit, je také konečné. Pro Excel je maximální číslo, které je možné uložit, 1,79769313486232E+308 a minimální kladné číslo, které je možné uložit, je 2,2250738585072E-308.

Případy, ve kterých dodržujeme IEEE 754

  • Podtečení: Podtečení nastane, když se vygeneruje číslo, které je příliš malé na to, aby bylo znázorněno. V IEEE a Excelu je výsledek 0 (s výjimkou, že IEEE má koncept -0 a Excel ne).
  • Přetečení: Přetečení nastane, když je číslo příliš velké na to, aby bylo znázorněno. Excel pro tento případ používá vlastní zvláštní znázornění (#NUM!).

Případy, kdy nedodržujeme IEEE 754

  • Denormalizovaná čísla: Denormalizované číslo je označené exponentem 0. V takovém případě je celé číslo uloženo v mantise a mantisa nemá implicitní úvodní číslo 1. V důsledku toho ztratíte přesnost a čím menší je číslo, tím větší je ztráta přesnosti. Čísla na malém konci této oblasti mají pouze jednu číslici přesnosti.

    Příklad: Normalizované číslo má implicitní počáteční hodnotu 1. Pokud například mantisa představuje 0011001, normalizované číslo se stane 10011001 z důvodu implicitní počáteční hodnoty 1. Denormalizované číslo nemá implicitní počáteční číslo, takže v našem příkladu 0011001 zůstává denormalizované číslo stejné. V tomto případě má normalizované číslo osm platných číslic (10011001), zatímco denormalizované číslo má pět platných číslic (11001), přičemž počáteční nuly jsou nevýznamné.

    Denormalizovaná čísla jsou v podstatě alternativním řešením, které umožňuje ukládat čísla menší než normální dolní limit. Společnost Microsoft neimplementuje tuto volitelnou část specifikace, protože denormalizovaná čísla mají ze své podstaty proměnný počet platných číslic. To umožňuje, aby se do výpočtů dostala značná chyba.

  • Kladná/záporná nekonečna: Nekonečna nastanou, když vydělíte 0. Excel nepodporuje nekonečna, ale udává #DIV/0! v těchto případech.

  • Not-a-Number (NaN): NaN se používá k reprezentaci neplatných operací (jako je nekonečno/nekonečno, nekonečno-nekonečno nebo druhá odmocnina z -1). NaN umožňují programu pokračovat přes neplatnou operaci. Excel místo toho okamžitě vygeneruje chybu, například #NUM! nebo #DIV/0!.

Přesnost

Číslo s plovoucí desetinnou čárkou je uloženo v binárním formátu ve třech částech v 65bitovém rozsahu: znaménko, exponent a mantisa.

Znaménko Exponent Mantisa
1 znakový bit 11bitový exponent 1 implicitní bit + zlomek 52 bitů

Znaménko ukládá znaménko čísla (kladné nebo záporné), exponent ukládá mocninu 2, na kterou je číslo zvýšeno nebo sníženo (maximální/minimální mocnina 2 je +1 023 a -1 022) a mantisa ukládá skutečné číslo. Konečná úložná oblast pro mantisu omezuje, jak blízko mohou být dvě sousední čísla s plovoucí desetinnou čárkou (to znamená přesnost).

Mantisa a exponent jsou uloženy jako samostatné komponenty. V důsledku toho se míra možné přesnosti může lišit v závislosti na velikosti manipulovaného čísla (mantisy). V případě Excelu, ačkoli Excel může ukládat čísla od 1,79769313486232E308 do 2,2250738585072E-308, může tak učinit pouze s přesností na 15 číslic. Toto omezení je přímým důsledkem přísného dodržování specifikace IEEE 754 a není omezením aplikace Excel. Tato úroveň přesnosti se nachází i v jiných tabulkových aplikacích.

Čísla s plovoucí desetinnou čárkou jsou znázorněna v následujícím tvaru, kde exponent je binární exponent:

X = zlomek * 2^(exponent – odchylka)

Zlomek je normalizovaná zlomková část čísla, normalizovaná, protože exponent je upraven tak, aby byl počáteční bit vždy 1. Tímto způsobem se nemusí ukládat a získáte ještě o jeden bit větší přesnost. To je důvod, proč existuje implikovaný bit. To se podobá vědeckému zápisu, při kterém s exponentem manipulujete tak, abyste měli jednu číslici nalevo od desetinné čárky. S výjimkou binárního souboru můžete exponent vždy manipulovat tak, aby první bit byl 1, protože existují pouze 1 a 0.

Zkreslení je hodnota zkreslení používaná k tomu, aby nebylo nutné ukládat záporné exponenty. Zkreslení pro čísla s jednoduchou přesností je 127 a 1 023 (desetinné) pro čísla s dvojitou přesností. Excel ukládá čísla s dvojitou přesností.

Příklad použití velmi velkých čísel

Do nového sešitu zadejte následující:

A1: 1.2E+200
B1: 1E+100
C1: =A1+B1

Výsledná hodnota v buňce C1 by byla 1,2E+200, což je stejná hodnota jako buňka A1. Pokud porovnáte buňky A1 a C1 pomocí funkce KDYŽ, například KDYŽ(A1=C1), výsledek bude PRAVDA. Příčinou je specifikace IEEE pro ukládání pouze 15 platných číslic s přesností. Aby bylo možné uložit výše uvedený výpočet, Excel by vyžadoval minimálně 100 číslic přesnosti.

Příklad použití velmi malých čísel

Do nového sešitu zadejte následující:

A1: 0.000123456789012345
B1: 1
C1: =A1+B1

Výsledná hodnota v buňce C1 by byla 1,00012345678901 místo 1,000123456789012345. Příčinou je specifikace IEEE pro ukládání pouze 15 platných číslic s přesností. Aby bylo možné uložit výše uvedený výpočet, Excel by vyžadoval přesnost alespoň na 19 číslic.

Oprava chyb přesnosti

Excel nabízí dvě základní metody, které kompenzují chyby zaokrouhlení: funkci ZAOKROUHLIT a možnost Přesnost podle zobrazení nebo Nastavovat přesnost podle zobrazení.

Metoda 1: Funkce ZAOKROUHLIT

Následující příklad s použitím předchozích dat používá funkci ZAOKROUHLIT k vynucení čísla na pět číslic. To vám umožní úspěšně porovnat výsledek s jinou hodnotou.

A1: 1.2E+200
B1: 1E+100
C1: =ROUND(A1+B1,5)

Výsledkem je 1,2E+200.

D1: =KDYŽ(C1=1,2E+200; PRAVDA, NEPRAVDA)

Výsledkem je hodnota PRAVDA.

Metoda 2: Přesnost podle zobrazení

V některých případech můžete zabránit tomu, aby chyby zaokrouhlování ovlivnily váš výsledek, pomocí možnosti Přesnost podle zobrazení. Tato možnost vynutí, aby hodnota každého čísla v listu byla zobrazená hodnota. Chcete-li tuto možnost zapnout, postupujte takto.

  1. V nabídce Soubor klikněte na Možnosti a poté klikněte na kategorii Pokročilé.
  2. V části Při výpočtu tohoto sešitu vyberte požadovaný sešit a poté zaškrtněte políčko Nastavovat přesnost podle zobrazení.

Pokud například zvolíte formát čísla, který zobrazuje dvě desetinná místa, a poté zapnete možnost Přesnost podle zobrazení, při uložení sešitu se ztratí veškerá přesnost přesahující dvě desetinná místa. Tato možnost ovlivní aktivní sešit včetně všech listů. Tuto možnost nemůžete vrátit zpět a obnovit ztracená data. Před povolením této možnosti doporučujeme sešit uložit.

Opakující se binární čísla a výpočty, které mají výsledky blízké nule

Dalším matoucím problémem, který ovlivňuje ukládání čísel s plovoucí desetinnou čárkou v binárním formátu, je to, že některá čísla, která jsou konečná, neopakující se čísla v desítkové soustavě, jsou nekonečná, opakující se čísla v dvojkové soustavě. Nejběžnějším příkladem je hodnota 0,1 a její varianty. I když se tato čísla uvedená v desítkové soustavě stávají dokonale znázorněná, stejné číslo v binárním formátu se při uložení v mantise stane následujícím opakujícím se binárním číslem:

000110011001100110011 (a tak dále)

Specifikace IEEE 754 nemá žádné zvláštní limity pro jakékoliv číslo. Do mantisy uloží, co se dá, a zbytek zkrátí. Výsledkem je chyba o -2,8E-17 nebo 0,000000000000000028 při uložení.

Dokonce ani běžné desetinné zlomky, jako je desetinné číslo 0,0001, nelze přesně zobrazit v binární soustavě. (0,0001 je opakující se binární zlomek, který má periodu 104 bitů). Je to podobné tomu, proč nelze zlomek 1/3 přesně vyjádřit v desítkové soustavě (opakující se 0,33333333333333333333).

Zvažte například následující jednoduchý příklad v aplikaci Microsoft Visual Basic for Applications:

   Sub Main()
      MySum = 0
      For I% = 1 To 10000
         MySum = MySum + 0.0001
      Next I%
      Debug.Print MySum
   End Sub

Jako výstup se vytiskne 0.999999999999996. Malá chyba představující hodnotu 0,0001 v binární soustavě se propaguje do součtu.

Příklad: Přidání záporného čísla

  1. Do nového sešitu zadejte následující:

    A1: =(43,1-43,2)+1

  2. Klikněte pravým tlačítkem na buňku A1 a potom klikněte na Formát buněk. Na kartě Číslo klepněte v části Kategorie na Vědecké. Nastavte desetinná místa na 15.

Místo zobrazení 0,9 Excel 0,899999999999999. Protože (43,1-43,2) se počítá jako první, uloží se dočasně -0,1 a do výpočtu se zanese chyba z uložení -0,1.

Příklad, kdy hodnota dosáhne nuly

  1. V aplikaci Excel 95 nebo starší zadejte do nového sešitu následující:

    A1: =1,333+1,225-1,333-1,225

  2. Klikněte pravým tlačítkem na buňku A1 a potom klikněte na Formát buněk. Na kartě Číslo klepněte v části Kategorie na Vědecké. Nastavte desetinná místa na 15.

Místo zobrazení 0 Excel 95 zobrazí -2,22044604925031E-16.

Excel 97 však zavedl optimalizaci, která se pokouší tento problém opravit. Pokud by výsledkem operace sčítání nebo odčítání byla hodnota rovna nule nebo velmi blízko nule, aplikace Excel 97 a novější kompenzuje jakoukoli chybu způsobenou převodem operandu na binární a z binárního. Výše uvedený příklad při provedení v aplikaci Excel 97 a novější správně zobrazuje 0 nebo 0,000000000000000E+00 ve vědeckém zápisu.

Další informace o číslech s plovoucí desetinnou čárkou a specifikaci IEEE 754 najdete na následujících webových stránkách: