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

A cikk fordítása A cikk fordítása
Cikk azonosítója: 78113 - A cikkben érintett termékek listájának megtekintése.
Az összes kibontása | Az összes összecsukása

A lap tartalma

Ö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.
A táblázat összecsukásaA táblázat kibontása
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:
http://www.ieee.org

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

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.)

Tulajdonságok

Cikk azonosítója: 78113 - Utolsó ellenőrzés: 2010. május 13. - Verziószám: 7.0
A cikkben található információ a következő(k)re vonatkozik:
  • 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
Kulcsszavak: 
kbinfo KB78113
A Microsoft tudásbázisban szolgáltatott információkat "az adott állapotban", bárminemű szavatosság vagy garancia nélkül biztosítjuk. A Microsoft kizár mindennemű, akár kifejezett, akár vélelmezett szavatosságot vagy garanciát, ideértve a forgalomképességre és az adott célra való alkalmasságra vonatkozó szavatosságot is. A Microsoft Corporation és annak beszállítói semmilyen körülmények között nem felelősek semminemű kárért, így a közvetlen, a közvetett, az üzleti haszon elmaradásából származó vagy speciális károkért, illetve a kár következményeként felmerülő költségek megtérítéséért, még abban az esetben sem, ha a Microsoft Corporationt vagy beszállítóit az ilyen károk bekövetkeztének lehetőségére figyelmeztették. Egyes államok joga nem teszi lehetővé bizonyos károkért a felelősség kizárását vagy korlátozását, ezért a fenti korlátozások az ön esetében esetleg nem alkalmazhatók.

Visszajelzés küldése

 

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