Jelenleg nem kapcsolódik az internethez. Várakozás a kapcsolat helyreállítására

Előfordulhat, hogy a lebegőpontos aritmetika pontatlan eredményeket ad az Excel alkalmazásban

Összefoglaló
A cikkből megtudhatja, hogy a Microsoft Excel miként tárolja és számítja ki a lebegőpontos számokat. Ez a kerekítésnek, illetve az adatcsonkításnak köszönhetően érintheti néhány számítás vagy képlet eredményét.

Áttekintés

A Microsoft Excel alkalmazást az IEEE 754-es specifikációnak megfelelően, a lebegőpontos számok tárolásának és kiszámításának figyelembevételével tervezték. A nemzetközi testületként működő IEEE (Institute of Electrical and Electronics Engineers) többek között számítógépszoftverek és -hardverek szabványainak kidolgozásával foglalkozik. A széles körben elterjedt 754-es specifikáció a lebegőpontos számok bináris számítógépen történő tárolását írja le. Népszerűségét annak köszönheti, hogy a lebegőpontos számok helytakarékosságot és viszonylag gyors számítást tesznek lehetővé. A 754-es szabványt a legtöbb mai lebegőpontos számításra képes mikroprocesszor használja, így az Intel, a Motorola, a Sun és a MIPS lebegőpontos egységei és matematikai processzorai is.

A számok tárolásakor minden szám vagy törtszám kifejezhető egy megfelelő bináris számmal. Az 1/10 tört például decimális számrendszerben a következőképpen ábrázolható: 0,1. Ugyanez a szám azonban bináris formátumban a
0001100110011100110011 (stb.)
végtelen szakaszos bináris törtszámmal fejezhető ki. Ez a szám nem ábrázolható véges (behatárolt) térben, ezért tároláskor -2,8E-17-tel lefelé kerekítődik.

Az IEEE 754-es specifikációnak azonban korlátai is vannak, amelyek három általános kategóriába sorolhatók:
  • maximális/minimális korlátok
  • pontosság
  • szakaszos bináris számok
További információ

Maximális/minimális korlátok

Minden számítógép esetében van egy kezelhető maximális és minimális szám. Mivel a szám tárolásához szükséges memóriabitek száma véges, a tárolható maximális vagy minimális szám szintén véges. Az Excel esetében a tárolható maximális szám az 1,79769313486232E+308, a tárolható minimális szám pedig a 2,2250738585072E-308.

Azok az esetek, amikor mérvadónak tekintjük az IEEE 754-es szabványt

  • Alulcsordulás. Alulcsordulás következik be, ha egy létrehozott szám olyan kicsi, hogy nem jeleníthető meg. Ebben az esetben az IEEE szabványban és az Excel alkalmazásban az eredmény egyaránt 0 (hozzátéve, hogy az IEEE szabványban létezik a -0 fogalma, míg az Excel alkalmazásban nem).
  • Túlcsordulás. Túlcsordulás következik be, ha egy szám olyan nagy, hogy nem jeleníthető meg. Ebben az esetben az Excel saját jelölést (#NUM!) használ.

Azok az esetek, amikor nem tekintjük mérvadónak az IEEE 754-es szabványt

  • Denormalizált számok. A denormalizált számot a 0 exponens jelzi. Ilyen esetben a teljes számot a mantissza tárolja, és a mantissza elején nem áll implicit vezető 1. Ez azt eredményezi, hogy csökken a pontosság, és minél kisebb a szám, annál nagyobb mértékben csökken. E tartomány legkisebb számai egy számjegy pontosságúak.
    Példa: A normalizált számok elején implicit vezető 1 áll. Ha például a mantissza 0011001, a normalizált szám 10011001 lesz az implicit vezető 1 miatt. A denormalizált számok elején nem áll implicit vezető 1, tehát a példában szereplő 0011001 esetében a denormalizált szám ugyanaz marad. A normalizált szám tehát nyolc értékes számjegyből (10011001), míg a denormalizált szám – mivel a vezető nullák értéktelenek – öt értékes számjegyből áll.

    A denormalizált számok alapjában véve kerülő megoldást biztosítanak ahhoz, hogy a normál alsó korlátnál kisebb számok tárolhatók legyenek. A Microsoft nem építi be termékeibe ezt az opcionális részt a specifikációból, mivel a denormalizált számok természetüknél fogva változó számú értékes számjegyből állnak, ami jelentős számítási hibákat eredményezhet.
  • Pozitív/negatív végtelen számok. Ezek a 0-val való osztáskor jöhetnek létre. Az Excel nem támogatja a végtelen számokat. Ilyen esetekben a #ZÉRÓOSZTÓ! hibaértéket jeleníti meg.
  • Nem szám (NaN). A NaN értékek érvénytelen műveletek (például végtelen/végtelen, végtelen-végtelen vagy -1 négyzetgyöke) jelzésére szolgálnak. A NaN értékek lehetővé teszik, hogy a programok az érvénytelen műveletek után folytatódjanak. Az Excel ehelyett azonnal hibát jelez (például #SZÁM! vagy #ZÉRÓOSZTÓ!).

Pontosság

A lebegőpontos számok a bináris számrendszerben történő tároláskor három részből tevődnek össze egy 65 bites tartományon belül: az előjelből, az exponensből és a mantisszából.
1 előjelbit11 bites exponens1 implicit bit52 bites mantissza
Az előjel a szám előjelét (pozitív vagy negatív), az exponens a 2 azon hatványát, amelyre a számot emelni vagy csökkenteni kell (2 maximális és minimális hatványa +1,023 és -1,022), a mantissza pedig a tényleges számot tárolja. A mantissza véges tárolóterülete határt szab annak, hogy milyen közel eshet egymáshoz két szomszédos lebegőpontos szám (pontosság).

Mind a mantissza, mind az exponens külön tárolt összetevő. Ennek eredményeként a lehetséges pontosság mennyisége a módosított szám (a mantissza) méretétől függően változhat. Az Excel az 1,79769313486232E308 és 2,2250738585072E-308 közötti számokat képes tárolni, de csak 15 számjegy pontosságon belül. Ez nem az Excel korlátja, hanem az IEEE 754-es szabványhoz való szigorú ragaszkodás közvetlen eredménye. Ez a pontossági szint más táblázatkezelő alkalmazásokban is megtalálható.

A lebegőpontos számokat a következő alakban ábrázolják, ahol az exponens a bináris exponens:
X = Törtrész * 2^(exponens - eltolás) A
Törtrész a szám normalizált törtrésze, és azért normalizált, mert az exponens úgy van beállítva, hogy a vezető bit mindig 1. Ily módon a vezető bitet nem kell tárolni, ami egy bittel nagyobb pontosságot tesz lehetővé. Ez az oka az implicit bit használatának. Mindez hasonlít a tudományos jelöléshez, ahol úgy módosítják az exponenst, hogy a tizedes ponttól balra egy számjegyet tartalmazzon. A bináris formátumot kivéve az exponens mindig módosítható annak érdekében, hogy a vezető bit 1 legyen, mivel nincs más szám, mint 1 és 0.

Az eltolás a negatív exponensek tárolásának elkerülésére használt eltolási érték. Az eltolás egyszeres pontosságú számok esetén 127, dupla pontosságú számok esetén pedig 1,023 (tizedes tört). Az Excel dupla pontosságot használ a számok tárolásakor.

Példa nagyon nagy számok használatával

Írja be a következőt egy új munkafüzetbe:
   A1: 1,2E+200   B1: 1E+100   C1: =A1+B1
Az eredményérték a C1 cellában 1,2E+200 lesz, amely azonos az A1 cella értékével. Sőt, ha a HA függvény használatával összehasonlítja az A1 és C1 cellát (például IF(A1=C1)), az eredmény IGAZ lesz. Ennek az az oka, hogy az IEEE specifikációban a tárolás csak 15 értékes számjegy pontosságú. A fenti számítás tárolásához az Excel alkalmazásnak legalább 100 számjegy pontosságúnak kellene lennie.

Példa nagyon kis számok használatával

Írja be a következőt egy új munkafüzetbe:
   A1: 0,000123456789012345   B1: 1   C1: =A1+B1
Az eredményérték a C1 cellában 1,000123456789012345 helyett 1,00012345678901 lesz. Ennek az az oka, hogy az IEEE specifikációban a tárolás csak 15 értékes számjegy pontosságú. A fenti számítás tárolásához az Excel alkalmazásnak legalább 19 számjegy pontosságúnak kellene lennie.

A pontossági hibák kijavítása

Az Excel két alapvető módszert kínál a kerekítési hibák kiküszöbölésére: a KEREKÍTÉS függvényt és A mutatott pontosság szerint vagy a Megjelenés szerinti pontosság beállítása munkafüzet-beállítást.

1. módszer: A KEREKÍTÉS függvény

A fenti adatokat használó következő példa egy szám öt számjegyre történő kerekítésének kényszerítését szemlélteti. Ez lehetővé teszi az eredmény sikeres összehasonlítását egy másik értékkel.
   A1: 1,2E+200   B1: 1E+100   C1: =KEREKÍTÉS(A1+B1,5)
Eredmény: 1,2E+200.
   D1: =HA(C1=1,2E+200, IGAZ, HAMIS)
Eredmény: IGAZ érték.

2. módszer: A mutatott pontosság szerint

Egyes esetekben A mutatott pontosság szerint beállítás használatával kiküszöbölhetők a kerekítési hibák a számításokból. Ezzel a beállítással kikényszeríthető, hogy a munkafüzetben szereplő számok értéke a megjelenített érték legyen. A beállítás bekapcsolása:
  1. Az Excel 2003-as és korábbi verzióiban kattintson az Eszközök menü Beállítások parancsára.
  2. A Számolás lapon jelölje be A mutatott pontosság szerint jelölőnégyzetet.
  1. Az Excel 2007 alkalmazásban kattintson a Microsoft Office gombra, válassza Az Excel beállításai lehetőséget, majd kattintson a Speciális kategóriára.
  2. Az E munkafüzet számításakor területen jelölje ki a kívánt munkafüzetet, majd jelölje be A mutatott pontosság szerint jelölőnégyzetet.
Ha például két tizedesjegyet megjelenítő számformátumot választ, majd bekapcsolja A mutatott pontosság szerint beállítást, a számok két tizedesjegyet meghaladó pontossága elvész a munkafüzet mentésekor. Ez a beállítás az aktív munkafüzet összes munkalapjára hatással van. Az ezzel a beállítással végrehajtott műveletek nem vonhatók vissza, és nem állíthatók helyre az elveszett adatok. A beállítás engedélyezése előtt ajánlott menteni a munkafüzetet.

Szakaszos bináris számok és nullához közeli eredményt adó számítások

A lebegőpontos számok bináris formátumban történő tárolásakor zavaró problémát jelent az is, hogy bizonyos számok, amelyek a 10-es alapú decimális számrendszerben véges, nem szakaszos számok, bináris formátumban végtelen, szakaszos számokként tárolhatók. A leggyakoribb példa erre a 0,1 érték és annak variánsai. Bár e számok tökéletesen ábrázolhatók a 10-es alapú számrendszerben, a fenti számból bináris formátumban a következő szakaszos szám lesz a mantisszában tárolva:
000110011001100110011 (stb.)
Az IEEE 754-es specifikáció semmilyen számmal kapcsolatban nem tartalmaz külön kitételt; amit tud, azt tárol a mantisszában, a többit pedig csonkolja. Ez megközelítőleg -2,8E-17, vagy tároláskor 0,000000000000000028 értékű hibát eredményez.

Még a közönséges tizedes törtek (például a 0,0001) sem ábrázolható pontosan bináris formátumban. (a 0,0001 104 bites szakaszokat tartalmazó bináris törtszám). Ennek oka nagyjából ugyanaz, mint amiért az 1/3 tört nem fejezhető ki pontosan a decimális számrendszerben (csak a 0,33333333333333333333 szakaszos törttel).

Mindez arra is magyarázatot ad, hogy a Microsoft Visual Basic for Applications alkalmazásban az alábbi egyszerű példának
   Sub Main()      MySum = 0      For I% = 1 To 10000         MySum = MySum + 0,0001      Next I%      Debug.Print MySum   End Sub
miért 0,999999999999996 a nyomtatási kimenete. A 0,0001 bináris ábrázolásában keletkező kis hiba megjelenik az összegben.

Példa negatív szám hozzáadására

  1. Írja be az alábbi értékeket egy új munkafüzetbe:
       A1: =(43,1-43,2)+1
  2. Kattintson a jobb gombbal az A1 cellára, majd kattintson a Cellaformázás parancsra. A Szám lap Kategória listájában kattintson a Tudományos elemre. A Tizedesjegyek beállítás értékét állítsa 15-re.
Az Excel a 0,9 helyett a 0,899999999999999 értéket jeleníti meg. Mivel a program a (43,1-43,2) képletet számította ki először, ideiglenesen tárolta a -0,1 értéket, és a -0,1 tárolásából keletkező hiba megjelent a számításban.

Példa nullát elérő értékre

  1. Az Excel 95-ös vagy korábbi verziójában írja be az alábbi értéket egy új munkafüzetbe:
       A1: =1,333+1,225-1,333-1,225
  2. Kattintson a jobb gombbal az A1 cellára, majd kattintson a Cellaformázás parancsra. A Szám lap Kategória listájában kattintson a Tudományos elemre. A Tizedesjegyek beállítás értékét állítsa 15-re.
Az Excel 95 0 helyett a -2.22044604925031E-16 értéket jeleníti meg.

Egy Excel 97 alkalmazásban bevezetett optimalizálás azonban már megkísérli kiküszöbölni ezt a problémát. Ha egy hozzáadási vagy kivonási művelet eredménye nulla vagy ahhoz közeli érték, az Excel 97-es vagy újabb verziója minden olyan hibát kijavít, amely az operandusok bináris formátumba, illetve formátumból történő konvertálása során keletkezett. A fenti példát az Excel 97-es vagy újabb verziójában végrehajtva a 0 vagy 0,000000000000000E+00 érték helyesen jelenik meg a tudományos jelölésben. A Microsoft Tudásbázis kapcsolódó cikkei:
172911 A 10 nagyon magas vagy nagyon alacsony értékű hatványkitevőre emelése hibás eredményt ad (Előfordulhat, hogy a hivatkozás részben vagy teljes egészében angol nyelvű tartalomra mutat.)
214373 XL2000: A 10 nagyon magas vagy nagyon alacsony értékű hatványkitevőre emelése hibás eredményt ad (Előfordulhat, hogy a hivatkozás részben vagy teljes egészében angol nyelvű tartalomra mutat.)
A lebegőpontos számokról és az IEEE 754-es specifikációról további információt a következő webhelyeken talál:
Hivatkozások
A hibák kerülő megoldásáról a Microsoft Tudásbázis alábbi cikkében tájékozódhat:
214118 A lebegőpontos aritmetika kerekítési hibáinak kiküszöbölése (Előfordulhat, hogy a hivatkozás részben vagy teljes egészében angol nyelvű tartalomra mutat.)
XL98 XL97 XL7 XL5 XL4 XL3 2.0 2.01 2.1 2.10 2.2 2.20 2.21 3.0 4.0 5.00a 5.00c math error w_works XL2007
Tulajdonságok

Cikkazonosító: 78113 - Utolsó ellenőrzés: 05/13/2010 15:49:00 - Verziószám: 7.0

  • 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 for Mac
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel 98 for Macintosh
  • kbinfo KB78113
Visszajelzés
/c.microsoft.com/ms.js'" + "'><\/script>");