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

Překlady článku Překlady článku
ID článku: 78113 - Produkty, které se vztahují k tomuto článku.
Rozbalit všechny záložky | Minimalizovat všechny záložky

Na této stránce

Souhrn

Tento článek popisuje, jak aplikace Microsoft Excel ukládá a počítá čísla s plovoucí desetinnou čárkou. Z důvodu zaokrouhlení nebo zkrácení dat to může ovlivnit výsledky některých čísel nebo vzorců.

Přehled

Aplikace Microsoft Excel byla s ohledem na ukládání čísel s plovoucí desetinnou čárkou a výpočtů s nimi navržena na základě specifikace IEEE 754. Organizace IEEE (Institute of Electrical and Electronics Engineers) je 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 popisující, jak mají být v binárním počítači uložena čísla s plovoucí desetinnou čárkou. Je populární, protože umožňuje uložení čísel s plovoucí desetinnou čárkou v rozumném prostoru a relativně rychlé provádění výpočtů. Standard 754 je používán v jednotkách FPU (floating-point unit) a numerických procesorech téměř všech dnešních mikroprocesorů pro osobní počítače, které implementují matematiku s plovoucí desetinnou čárkou, včetně procesorů společností Intel, Motorola, Sun a MIPS.

Při ukládání čísel lze každé číslo nebo zlomek vyjádřit odpovídajícím binárním číslem. Například zlomek 1/10 lze v desítkové soustavě vyjádřit jako 0,1. Stejné číslo v binárním formátu je však periodické binární desetinné číslo
0001100110011100110011 (a tak dále do nekonečna)
. Toto číslo nelze vyjádřit v konečném (omezeném) prostoru. Toto číslo je proto při uložení zaokrouhleno dolů o přibližně -2,8E-17.

Specifikace IEE 754 však má některá omezení, která lze rozdělit do tří základních kategorií:
  • omezení maxima a minima,
  • přesnost,
  • periodická binární čísla.

Další informace

Omezení maxima a minima

Všechny počítače mají maximální a minimální číslo, které lze zpracovat. Protože je počet bitů paměti pro uložení čísla omezený, je omezené také maximální nebo minimální uložitelné číslo. V aplikaci Excel je maximální uložitelné číslo 1,79769313486232E+308 a minimální uložitelné kladné číslo je 2,2250738585072E-308.

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

  • Podtečení: K podtečení dochází, pokud je generováno číslo, které je příliš malé, než aby ho bylo možné vyjádřit. Ve standardu IEEE a v aplikaci Excel je výsledkem 0 (s tím rozdílem, že specifikace IEEE obsahuje koncept -0 a aplikace Excel nikoliv).
  • Přetečení: K přetečení dochází, pokud je číslo příliš velké, než aby ho bylo možné vyjádřit. Aplikace Excel pro tento případ používá svoje vlastní vyjádření (#NUM!).

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

  • Nenormalizovaná čísla: Nenormalizované číslo se vyznačuje exponentem 0. V takovém případě je celé číslo uloženo v mantise a mantisa neobsahuje implicitní počáteční číslici 1. Výsledkem je ztráta přesnosti. Čím je číslo menší, tím je ztraceno více přesnosti. Čísla na nejmenším konci tohoto rozsahu mají pouze jednu platnou číslici.
    Příklad: Normalizované číslo obsahuje implicitní počáteční číslici 1. Pokud například mantisa představuje číslo 0011001, je z důvodu implicitní počáteční číslice 1 normalizované číslo 10011001. Nenormalizované číslo nemá implicitní počáteční jedničku, v našem příkladu čísla 0011001 tedy nenormalizované číslo zůstává stejné. Normalizované číslo má v tomto případě osm platných číslic (10011001), zatímco nenormalizované číslo má pět platných číslic (11001), protože počáteční nuly nejsou platné.

    Nenormalizovaná čísla jsou v podstatě náhradním řešením umožňujícím uložit čísla menší, než je běžný dolní limit. Společnost Microsoft tuto volitelnou část specifikace neimplementuje, protože nenormalizovaná čísla mají ze své podstaty proměnlivý počet platných číslic. Do výpočtů tak může vstoupit významná chyba.
  • Kladné a záporné nekonečno: Nekonečna se vyskytují při dělení nulou. Aplikace Excel nekonečna nepodporuje, místo toho v těchto případech vrací chybu #DIV/0!.
  • NaN (Not-a-Number): Hodnota NaN se používá k vyjádření neplatných operací (jako je nekonečno/nekonečno, nekonečno-nekonečno nebo odmocnina z -1). Hodnoty NaN programu umožňují pokračovat po neplatné operaci. Aplikace Excel místo toho ihned generuje chybu, jako je #NUM! nebo #DIV/0!.

Přesnost

Číslo s plovoucí desetinnou čárkou je v binárním formátu uloženo ve třech částech v rozsahu 65 bitů: znaménko, exponent a mantisa.
Zmenšit tuto tabulkuRozšířit tuto tabulku
1 bit znaménka11 bitů exponentu1 implicitní bit52 bitů mantisy
Ve znaménku je uloženo znaménko čísla (kladné nebo záporné), exponent obsahuje mocninu dvou, kterou je číslo vynásobeno nebo vyděleno (číslo dvě lze umocnit maximálně na +1 023 a minimálně na -1 022), a v mantise je uloženo vlastní číslo. Omezená velikost prostoru pro mantisu limituje, jak blízko u sebe mohou být dvě sousedící čísla s plovoucí desetinnou čárkou (udává tedy přesnost).

Mantisa a exponent jsou uloženy jako samostatné součásti. Výsledkem je, že se možná přesnost může lišit v závislosti na velikosti zpracovávaného čísla (mantisy). Ačkoli může aplikace Excel uložit čísla od 1,79769313486232E308 do 2,2250738585072E-308, může tak učinit pouze v rámci 15 platných číslic. Toto omezení je přímým důsledkem striktního dodržování specifikace IEEE 754 a nejedná se o omezení aplikace Excel. Tato úroveň přesnosti se týká i jiných tabulkových procesorů.

Čísla s plovoucí desetinnou čárkou jsou reprezentována v následující formě, kde exponent je binární exponent:
X = zlomek * 2^(exponent - posun)
Zlomek je normalizovaná zlomková část čísla. Normalizovaná proto, že je exponent upraven tak, aby počáteční bit byl vždy 1. Tento bit tak není potřeba ukládat, čímž je získán další platný bit. To je důvod existence implicitního bitu. Podobá se to vědeckému zápisu, kde je exponent upraven tak, aby vlevo od desetinné čárky byla vždy pouze jedna číslice, s tím rozdílem, že v binární soustavě lze exponent vždy upravit tak, aby první bit byl 1 (protože se používají pouze číslice 1 a 0).

Posun je hodnota posunu používaná proto, aby nebylo potřeba ukládat záporné exponenty. Pro čísla s jednoduchou přesností je posun 127 a pro čísla s dvojitou přesností 1 023 (v desítkové soustavě). Aplikace Excel ukládá čísla s dvojitou přesností.

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

Zadejte do nového sešitu následující příklad:
   A1: 1,2E+200
   B1: 1E+100
   C1: =A1+B1
Výsledná hodnota v buňce C1 bude 1,2E+200, stejná hodnota jako v buňce A1. Pokud buňky A1 a C1 porovnáte pomocí funkce KDYŽ, například KDYŽ(A1=C1), výsledek bude PRAVDA. Je to způsobeno tím, že ve specifikaci IEEE je uloženo pouze 15 platných číslic. Aby bylo možné uložit výše uvedený výpočet, potřebovala by k tomu aplikace Excel alespoň 100 platných číslic.

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

Zadejte do nového sešitu následující příklad:
   A1: 0,000123456789012345
   B1: 1
   C1: =A1+B1
Výsledná hodnota v buňce C1 bude 1,00012345678901 (místo 1,000123456789012345). Je to způsobeno tím, že ve specifikaci IEEE je uloženo pouze 15 platných číslic. Aby bylo možné uložit výše uvedený výpočet, potřebovala by k tomu aplikace Excel alespoň 19 platných číslic.

Oprava chyb přesnosti

Aplikace Excel poskytuje dvě základní metody kompenzace zaokrouhlovacích chyb: funkci ZAOKROUHLIT a možnost sešitu Přesnost podle zobrazení nebo Nastavit přesnost podle zobrazení.

Metoda 1: Funkce ZAOKROUHLIT

Následující příklad s výše uvedenými daty používá funkci ZAOKROUHLIT k vynucení čísla s pěti číslicemi. Umožňuje to úspěšné porovnání výsledku s jinou hodnotou.
   A1: 1,2E+200
   B1: 1E+100
   C1: =ZAOKROUHLIT(A1+B1;5)
Výsledek 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 je ovlivnění práce zaokrouhlovacími chybami možné zabránit použitím možnosti Přesnost podle zobrazení. Tato možnost vynutí, aby každé číslo pracovního sešitu mělo zobrazenou hodnotu. Chcete-li tuto možnost zapnout, postupujte takto:
  1. V aplikaci Explorer 2003 a ve starších verzích klepněte v nabídce Nástroje na příkaz Možnosti.
  2. Na kartě Výpočty zaškrtněte políčko Přesnost podle zobrazení.
  1. V aplikaci Excel 2007 klepněte na Tlačítko Office a na tlačítko Možnosti aplikace Excel a pak klepněte na kategorii Upřesnit.
  2. V části Při výpočtu tohoto sešitu vyberte požadovaný sešit a pak zaškrtněte políčko Nastavit přesnost podle zobrazení.
Pokud zvolíte například formát čísla zobrazující dvě desetinná místa a pak zapnete možnost Přesnost podle zobrazení, bude při uložení sešitu ztracena všechna přesnost přesahující dvě desetinná čísla. Tato možnost má vliv na všechny listy aktivního sešitu. Tuto možnost není možné vrátit zpět, a obnovit tak ztracená data. Před povolením této možnosti doporučujeme sešit uložit.

Periodická binární čísla a výpočty s výsledky blízkými nule

Další matoucí problém s ukládáním čísel s plovoucí desetinnou čárkou v binárním formátu je, že některá konečná neperiodická čísla v desítkové soustavě jsou v binárním formátu nekonečná periodická čísla. Nejběžnějším příkladem takového čísla je hodnota 0,1 a její varianty. Ačkoli lze tato čísla perfektně zapsat v desítkové soustavě, v binárním formátu se ze stejného čísla při uložení do mantisy stane následující periodické binární číslo:
000110011001100110011 (a tak dále)
Specifikace IEEE 754 neobsahuje žádný zvláštní předpis pro jakékoli číslo. Vše, co lze uložit do mantisy, tam je uloženo a zbytek je zkrácen. V důsledku toho dojde při uložení k chybě přibližně -2,8E-17 (neboli 0,000000000000000028).

V binárním formátu nelze přesně reprezentovat ani běžná desetinná čísla, jako je desítkové číslo 0,0001. (0,0001 je periodický binární zlomek s periodou 104 bitů). Je to podobné, jako zápis zlomku 1/3 jako desetinného čísla (periodické číslo 0,33333333333333333333).

To vysvětluje, proč jednoduchý příklad v jazyce Microsoft Visual Basic for Applications (VBA)
   Sub Main()
      MujSoucet = 0
      For I% = 1 To 10000
         MujSoucet = MujSoucet + 0.0001
      Next I%
      Debug.Print MujSoucet
   End Sub
vypíše výstup 0,999999999999996. Malá chyba binárního zápisu čísla 0,0001 se propaguje do součtu.

Příklad přičtení negativního čísla

  1. Zadejte do nového sešitu následující příklad:
       A1: =(43,1-43,2)+1
  2. Klepněte pravým tlačítkem myši na buňku A1 a potom klepněte na příkaz Formát buněk. Na kartě Číslo klepněte v poli Typ na možnost Matematický. V poli Desetinná místa nastavte hodnotu 15.
Místo hodnoty 0,9 zobrazí aplikace Excel hodnotu 0,899999999999999. Nejdříve je totiž vypočítán výraz (43,1-43,2), hodnota -0,1 je dočasně uložena a do výpočtu vstoupí chyba z uložení hodnoty -0,1.

Příklad hodnoty blížící se k nule

  1. V aplikaci Excel 95 nebo starší zadejte do nového sešitu následující příklad:
       A1: =1,333+1,225-1,333-1,225
  2. Klepněte pravým tlačítkem myši na buňku A1 a potom klepněte na příkaz Formát buněk. Na kartě Číslo klepněte v poli Typ na možnost Matematický. V poli Desetinná místa nastavte hodnotu 15.
Místo hodnoty 0 zobrazí aplikace Excel 95 hodnotu -2,22044604925031E-16.

V aplikaci Excel 97 však byla zavedena optimalizace, která se tento problém pokouší napravit. Pokud je výsledkem operace součtu nebo rozdílu nulová hodnota nebo hodnota velmi blízká nule, provede aplikace Excel 97 a pozdější kompenzaci případné chyby vzniklé v důsledku převodu operandu do binárního formátu a zpět. Výše uvedený příklad v aplikaci Excel 97 a pozdější zobrazí správně 0 nebo ve vědeckém zápisu 0,000000000000000E+00. Další informace naleznete v následujících článcích znalostní báze Microsoft Knowledge Base:
172911 Nesprávný výsledek při mocnění čísla 10 na velmi vysoký nebo nízký exponent (Tento článek může obsahovat odkazy na anglický obsah (dosud nepřeložený).)
214373 Nesprávný výsledek při mocnění čísla 10 na velmi vysoký nebo nízký exponent (Tento článek může obsahovat odkazy na anglický obsah (dosud nepřeložený).)
Další informace o číslech s plovoucí desetinnou čárkou a specifikaci IEEE 754 naleznete na následujících webech:
http://www.ieee.org

http://stevehollasch.com/cgindex/coding/ieeefloat.html

Odkazy

Další informace o možnostech řešení těchto chyb naleznete v následujícím článku znalostní báze Microsoft Knowledge Base:
214118 Jak opravit zaokrouhlovací chyby v aritmetice s plovoucí desetinnou čárkou (Tento článek může obsahovat odkazy na anglický obsah (dosud nepřeložený).)

Vlastnosti

ID článku: 78113 - Poslední aktualizace: 13. května 2010 - Revize: 7.0
Informace v tomto článku jsou určeny pro produkt:
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 2004 for Mac
  • Microsoft Excel X pro počítače Macintosh
  • Microsoft Excel 2001 pro počítače Macintosh
  • Microsoft Excel 98 pro počítače Macintosh
Klíčová slova: 
kbinfo KB78113

Dejte nám zpětnou vazbu

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com