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
> var varAutoFirePV = 1; var varClickTracking = 1; var varCustomerTracking = 1; var Route = "76500"; var Ctrl = ""; document.write("