A lebegőpontos aritmetika pontatlan eredményeket adhat az Excelben

Összefoglalás

Ebből 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és vagy az adatok csonkolása miatt hatással lehet egyes számok vagy képletek eredményére.

Áttekintés

Az Excel a lebegőpontos számok tárolására és kiszámítására vonatkozó IEEE 754 specifikációt követi. Az IEEE a Villamos- és Elektronikai Mérnökök Intézete, amely egy nemzetközi szervezet, amely többek között a számítógépes szoftverek és hardverek szabványait határozza meg. A 754-es specifikáció egy széles körben elterjedt specifikáció, amely leírja, hogy a lebegőpontos számokat hogyan kell bináris számítógépen tárolni. Ez azért népszerű, mert lehetővé teszi, hogy a lebegőpontos számokat észszerű nagyságú helyen tárolják, a számításokat pedig viszonylag gyorsan elvégezzék. A 754-es szabvány szinte minden mai, lebegőpontos matematikát megvalósító, PC-alapú mikroprocesszor lebegőpontos egységében és numerikus adatprocesszorában használatos, beleértve az Intel, a Motorola, a Sun és a MIPS processzorokat.

A számok tárolásakor a megfelelő bináris szám minden számot vagy törtszámot képviselhet. Az 1/10 törtszám például 0,1 tizedes törtként ábrázolható a tizedesszám rendszerben. Bináris formátumban ugyanakkor ugyanaz a szám az alábbi ismétlődő bináris decimális szám lesz:

0001100110011100110011 (és így tovább)

Ez végtelenül ismétlődhet. Ez a szám nem ábrázolható véges (korlátozott) területen. Ez a szám tehát tároláskor körülbelül -2,8E-17-tel lesz lekerekítve.

Az IEEE 754 specifikációnak azonban vannak bizonyos korlátai, amelyek három általános kategóriába esnek:

• Maximum/Minimum korlátozások
• Pontosság
• Ismétlődő bináris számok

További információ

Maximum/Minimum korlátozások

Minden számítógépnél van egy maximális és minimális szám, amit kezelni képes. Mivel a számot tároló memória bitszáma véges, ezért a tárolható memória maximális vagy minimális száma szintén véges. Az Excel esetében a maximális tárolható szám legfeljebb 1,79769313486232E+308, minimális pozitív szám pedig 2,2250738585072E-308 lehet.

Esetek, ahol tartjuk magunkat az IEEE 754-hez

• Alulcsordulás: Alulcsordulás akkor fordul elő, ha olyan számot generálunk, amely túl kicsi a ábrázoláshoz. Az IEEE-ben és az Excelben az eredmény 0 (azzal a kivétellel, hogy az IEEE fenntartja a -0 fogalmát, az Excel pedig nem).
• Túlcsordulás: Túlcsordulás akkor fordul elő, ha egy szám túl nagy a ábrázoláshoz. Az Excel saját speciális ábrázolást alkalmaz erre az esetre (#NUM!)

Esetek, ahol nem tartjuk magunkat az IEEE 754-hez

• Denormalizált számok: A denormalizált számokat egy 0 kitevő jelzi. Ebben az esetben a teljes számot a mantissza tárolja, és a mantissza nem rendelkezik implicit kezdő 1-gyel. Ennek következtében elveszítjük a pontosságot, és minél kisebb ez a szám, annál nagyobb pontosságot veszítünk el. A tartomány kicsi végén a számok csak egy számjegy pontossággal rendelkeznek.

  Példa: Egy normalizált szám implicit kezdő 1-gyel rendelkezik. Ha például a mantissza 0011001-et képvisel, a normalizált szám 10011001 az implicit kezdő 1 miatt. A denormalizált számnak nincs implicit kezdőszáma, ezért példánkban a 0011001, denormalizált számként változatlan marad. Ebben az esetben a normalizált szám nyolc fontos számjegyből áll (10011001), míg a denormalizált szám öt fontos számjegyből áll (11001), ahol a kezdő nullák nem lesznek fontosak.

  A denormalizált számok alapvetően egy kerülő megoldást képviselnek, amely lehetővé teszi a normál alsó határnál kisebb számok tárolását. A Microsoft nem valósítja meg a specifikáció ezen opcionális részét, mert a denormalizált számok természetüknél fogva változó számú fontos számjegyet tartalmaznak. Ezzel jelentős hibák kerülhetnek bele a számításokba.

• Pozitív/negatív végtelenek: Végtelenek 0-val való osztáskor fordulnak elő. Az Excel nem támogatja a végteleneket, hanem azt egy #DIV/0! képlettel jelzi hiba ezekben az esetekben.

• Nem szám (NaN): A NaN-t érvénytelen műveletek (például végtelen/végtelen, végtelen-végtelen vagy -1 négyzetgyöke) ábrázolására használják. A NaN-ek lehetővé teszik, hogy egy program továbblépjen egy érvénytelen műveleten. Az Excel ezzel szemben azonnal hibát generál, például #NUM! vagy #DIV/0!.

Pontosság

A lebegőpontos számot egy 65 bites tartományon belül három részben tárolja binárisan: előjel, kitevő és a mantissza.

Az előjel tárolja a szám előjelét (pozitív vagy negatív), a kitevő a 2 hatványát tárolja, amelyre a számot emelni vagy csökkenteni kell (a 2 maximum/minimum hatványa +1023 és -1022), a mantissza pedig a tényleges számot tárolja. A mantissza véges tárolóterülete korlátozza, hogy milyen közel lehet két szomszédos lebegőpontos szám (ez a pontosság).

A mantissza és a kitevő is külön összetevőként van tárolva. Ennek következtében a lehetséges pontosság mértéke a módosítani kívánt szám (mantissza) méretétől függően változhat. Az Excel esetében, bár az Excel 1,79769313486232E308 és 2,2250738585072E-308 között tud számokat tárolni, ezt csak 15 számjegy pontosságon belül tudja megtenni. Ez a korlátozás az IEEE 754 specifikáció szigorú követésének közvetlen eredménye, és nem az Excel korlátja. Ez a pontossági szint más táblázatkezelő programokban is megtalálható.

A lebegőpontos számokat a következő alakban kell ábrázolni, ahol a kitevő a bináris kitevő:

X = Tört * 2^(kitevő - torzítás)

Tört a szám normalizált törtrésze, amely normalizálva van, mivel a kitevő úgy van beállítva, hogy a kezdő bit mindig 1 legyen. Ily módon ezt nem kell tárolni, és még egy bit pontosságot kapunk. Ez az oka ennek az implicit bitnek. Ez hasonló a tudományos jelöléshez, ahol a kitevőt úgy módosítja, hogy egy számjegy legyen a tizedesvesszőtől balra; a bináris rendszer kivételével a kitevőt mindig módosíthatja úgy, hogy az első bit 1-es, mert csak az 1-es és a 0-ás értékek vannak.

Torzítás az a torzítási érték, amelyet annak elkerülésére használnak, hogy a negatív kitevőket kelljen tárolni. Az egyszeres pontosságú számok torzítása 127, a kétszeres pontosságú számoké pedig 1023 (decimális). Az Excel kétszeres pontosságot használó számokat tárolja.

Nagyon nagy számokat használó példa

Írja be az alábbiakat egy új munkafüzetbe:

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

A C1 cellában lévő eredmény 1,2E+200 lesz, ugyanaz az érték, mint az A1 cellában. Valójában ha az A1 és a C1 cellát hasonlítja össze a HA függvény használatával, például HA(A1=C1), az eredmény IGAZ lesz. Ezt az IEEE specifikációja okozza, amely csupán 15 fontos számjegy pontossággal tárol. Ahhoz, hogy a fenti számítást tárolni tudja, az Excel legalább 100 számjegy pontosságot igényelne.

Nagyon kicsiny számokat használó példa

Írja be az alábbiakat egy új munkafüzetbe:

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

A C1 cellában lévő eredmény 1,00012345678901 lesz 1,000123456789012345 helyett. Ezt az IEEE specifikációja okozza, amely csupán 15 fontos számjegy pontossággal tárol. Ahhoz, hogy a fenti számítást tárolni tudja, az Excel legalább 19 számjegy pontosságot igényelne.

Pontossági hibák javítása

Excel a kerekítési hibák kiegyenlítéséhez két alapvető módszert kínál: a KEREKÍTÉS függvényt és a Megjelenített pontosság vagy a Pontosság beállítása a megjelenítés szerint munkafüzet beállítást.

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

Az előző adatokat használva, a következő példa a KEREKÍTÉS függvényt alkalmazza, hogy egy számot öt számjegyre korlátozzon. Így az eredményt sikeresen összehasonlíthatja egy másik értékkel.

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

Az eredmény 1,2E+200 lesz.

D1: =HA(C1=1,2E+200; IGAZ, HAMIS)

Ennek eredménye IGAZ.

2. módszer: Megjelenített pontosság

Bizonyos esetekben a Megjelenített pontosság beállítással megakadályozhatja, hogy kerekítési hibák hatással legyenek munkájára. Ez a beállítás arra kényszeríti a munkalapon található egyes számértékeket, hogy a megjelenített értékeket vegyék fel. A beállítás bekapcsolásához kövesse az alábbi lépéseket:

1. Kattintson a Fájl menü Beállítások pontjára, majd válassza a Speciális kategóriát.
2. A Munkafüzet kiszámításakor csoportban jelölje ki a kívánt munkafüzetet, majd jelölje be a Megjelenített pontosság jelölőnégyzetet.

Ha például olyan számformátumot választ, amely két tizedesjegyet jelenít meg, majd bekapcsolja a Megjelenítés szerinti pontosság beállítást, a munkafüzet mentésekor a két tizedesjegyet követően minden pontosság elveszik. Ez a beállítás, az összes munkalapot is beleértve hatással van az aktív munkafüzetre. Ez a beállítás nem vonható vissza és nem állíthatóak helyre az elveszett adatok. Azt javasoljuk, hogy a beállítás engedélyezése előtt mentse el a munkafüzetet.

Ismétlődő bináris számok és közel nulla eredményű számítások

A lebegőpontos számok bináris formátumban való tárolását befolyásoló másik megtévesztő probléma, hogy a decimális 10-es számrendszerben a véges, nem ismétlődő számok némelyike végtelen, ismétlődő szám a bináris számrendszerben. A leggyakoribb példa erre a 0,1 érték és annak variációi. Bár ezek a számok tökéletesen ábrázolhatóak a 10-es alapú számrendszerben, ugyanaz a szám lesz bináris formátumban a következő ismétlődő bináris szám, amikor a mantisszában tároljuk:

000110011001100110011 (és így tovább)

Az IEEE 754 specifikáció nem tesz külön kedvezményt semmilyen szám esetében. Tárolja, amit tud a mantisszában, a többit pedig csonkolja. Ez -2,8E-17 vagy 0,000000000000000028 hibát jelez a tároláskor.

Még a közönséges decimális törtek, például a decimális 0,0001 sem ábrázolhatóak pontosan a bináris rendszerben. (A 0,0001 egy ismétlődő bináris törtszám, amelynek 104 bitből álló periódusa van). Ez hasonló ahhoz, mint amikor az 1/3 tört nem ábrázolható pontosan a decimális rendszerben (ismétlődő 0,33333333333333333333).

Vegyük például az alábbi egyszerű példát a Microsoft Visual Basic for Applications programban:

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

Ez a 0,999999999999996 eredményt fogja KINYOMTATNI A bináris rendszerben 0,0001 értékeket képviselő kis hiba átkerül az összegbe.

Példa: Negatív szám hozzáadása

1. Írja be az alábbiakat egy új munkafüzetbe:

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

2. Kattintson a jobb gombbal az A1 cellára, majd a Cellák formázása elemre. A Szám lapon kattintson a Tudományos elemre a Kategória csoportban. Állítsa a Tizedesjegyek értéket 15-re.

0,9 helyett 0,899999999999999 jelenik meg Excelben. Mivel a program először a (43,1-43,2) képletet számítja ki, a -0,1-et ideiglenesen tárolja, és a tárolásból fakadó -0,1-es hiba jelenik meg a számításban.

Példa arra, amikor egy érték eléri a nullát

1. A Excel 95-ös vagy korábbi verzióban írja be az alábbiakat egy új munkafüzetbe:

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

2. Kattintson a jobb gombbal az A1 cellára, majd a Cellák formázása elemre. A Szám lapon kattintson a Tudományos elemre a Kategória csoportban. Állítsa a Tizedesjegyek értéket 15-re.

0 helyett az Excel 95-ben -2,22044604925031E-16 jelenik meg.

Az Excel 97 azonban bevezetett egy optimalizálást, amely megkísérli kijavítani a problémát. Ha egy összeadási vagy kivonási művelet nullát vagy nullához nagyon közeli értéket eredményez, akkor az Excel 97 és újabb verziói kompenzálják az egy operandus binárisra vagy binárisról történő konvertálása eredményeként bevitt bármely hibát. Ha a fenti példát az Excel 97-ben és újabb verzióiban végezzük el, az helyesen 0 vagy 0,000000000000000E+00 értéket mutat tudományos jelölésben.

A lebegőpontos számokról és az IEEE 754 specifikációról további információt a következő webcímeken szerezhet:

• https://www.ieee.org
• https://steve.hollasch.net/cgindex/coding/ieeefloat.html