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

Összefoglaló

A cikk azt tárgyalja, hogyan tárolja és számítja ki a Microsoft Excel a lebegőpontos számokat. Ez hatással lehet néhány szám vagy képlet eredményeire a kerekítés vagy az Adatcsonkítás miatt.

Áttekintés

A Microsoft Excel az IEEE 754 specifikáció köré készült annak meghatározásához, hogy a lebegőpontos számokat hogyan tárolja és számolja ki. Az IEEE nemzetközi szervezet, a Villamosmérnöki és elektronikai mérnöki Intézet, többek között meghatározza a számítógépes szoftverek és hardverek szabványait. A 754 specifikáció egy nagyon széles körben elfogadott specifikáció, amely leírja, hogyan lebegőpontos számokat kell tárolni egy bináris számítógép. Ez népszerű mert megenged úszó-pont számok-hoz lenni raktározott-ban egy méltányos összeg-ból hely és számítások-hoz előfordul viszonylag gyorsan. A 754 szabványt használják a lebegőpontos egységek és numerikus adatfeldolgozók szinte minden mai PC-alapú mikroprocesszorok, hogy végre lebegőpontos matematika, beleértve az Intel, a Motorola, a Sun, és MIPS processzorok.

A számok tárolásakor a megfelelő bináris szám minden számot vagy tört számot jelenthet. Például a 1/10 frakció ábrázolható decimális számrendszerben 0,1. A bináris formátumban megadott szám azonban a következő ismétlődő bináris decimális számúvá válik:

0001100110011100110011 (és így tovább)

Ez végtelenül ismételhető. Ez a szám nem ábrázolható véges (korlátozott) tárhelyen. Ezért ezt a számot a tároláskor megközelítőleg 2.8 E-17 kerekíti.

Bármennyire, vannak némely korlátok-ból IEEE 754 részletezés amit esés-ba három általános Kategóriák:

  • Maximális/minimális korlátok
  • Precíziós
  • Ismétlődő bináris számok

Bővebb információ

Maximális/minimális korlátozások

Minden számítógépnek van egy maximális és egy minimális száma, amit kezelni lehet. Mivel a szám tárolási bitjeinek száma véges, ebből következik, hogy a tárolható maximális vagy minimális szám is véges. Az Excel esetében a tárolható maximális szám 1.79769313486232 E + 308, és a minimálisan tárolható pozitív szám 2.2250738585072 E-308.

Olyan esetek, amikor betartjuk az IEEE 754

  • Aluláramlás: Aluláramlás akkor történik, amikor egy szám keletkezik, amely túl kicsi ahhoz, hogy képviseltesse magát. Az IEEE és az Excel, az eredmény 0 (azzal a kivétellel, hogy az IEEE fogalma-0, és az Excel nem).
  • Túlcsordulás: túlcsordulás, ha egy szám túl nagy ahhoz, hogy képviseltesse. Az Excel ebben az esetben (#NUM!) saját különleges képviseletet használ.

Olyan esetek, amikor nem tartják be az IEEE 754

  • Denormalizált számok: A denormalizált számot 0 exponens jelöli. Ebben az esetben az egész számot a mantissza tárolja, a mantissza pedig nincs benne a vezető 1. Ennek eredményeként elveszíti a pontosságot, és minél kisebb a szám, annál nagyobb a pontosság. Az e tartomány kis végén lévő számoknak csak egy számjegye van a pontosságból.

    Példa: egy normalizált szám implicit sorkezdő 1. Például, ha a mantissza 0011001-et képvisel, akkor a normalizált szám válik 10011001, mert az implikált vezető 1. A denormalizált szám nincs implicit vezető egy, így a példában a 0011001, a denormalizált szám ugyanaz marad. Ebben az esetben a normalizált számnak nyolc értékes számjegye van (10011001), míg a denormalizált számnak öt fontos számjegye van (11001), és a vezető nullák jelentéktelenek.

    A denormalizált számok lényegében egy olyan megoldás, amely lehetővé teszi a normál alsó korlátnál kisebb számok tárolását. A Microsoft nem hajtja végre a specifikáció ezen opcionális részét, mivel a denormalizált számok jellegüknél fogva változó számú értékes számjegyből állnak. Ezzel jelentős hiba lép fel a számításokban.

  • Pozitív/negatív Infinities: végtelen fordul elő, ha osztani 0-ra. Az Excel nem támogatja a végtelen, hanem, hogy ad egy #DIV/0! hibát ezekben az esetekben.

  • Nem-a-szám (NaN): NaN használják, hogy képviselje az érvénytelen műveletek (mint például a végtelen/végtelen, végtelen-végtelen, vagy a négyzetgyök az-1). A NaNs lehetővé teszi, hogy a program folytassa az érvénytelen művelet korábbi műveletét. Excel helyett azonnal generál egy hiba, mint a #NUM! vagy #DIV/0!.

Precíziós

A lebegőpontos számok a bináris formátumban, egy 65 bites tartományon belül három részre vannak tárolva: a jel, az exponens és a mantissa. ||||| |---|---|---|---| |1 jelbit|11 bites exponens|1 hallgatólagos bit|52 bit Mantissa|

A tábla tárolja a szám előjelét (pozitív vagy negatív), az exponens a 2-es számot, amelyhez a szám emelik vagy lejjebb kerül (a maximális/minimális teljesítmény 2 + 1 023 és-1 022), a mantsza pedig a tényleges számot tárolja. A mantsza véges tárolóterülete korlátozza a két szomszédos lebegőpontos szám közelpontját (azaz a pontosságot).

A mantissza és a kitevő egyaránt külön összetevőként tárolódik. Ennek eredményeképpen a lehetséges pontosság mértéke a manipulált szám (a mantissa) méretétől függ. Abban az esetben, Excel, bár az Excel képes tárolni számokat 1.79769313486232 E308, hogy 2.2250738585072 E-308, ez csak akkor lehet, 15 számjegy pontossággal. Ez a korlátozás szigorúan az IEEE 754 specifikációnak megfelelően történik, és nem az Excel korlátozása. Ez a pontossági szint más táblázatkezelő programokban is megtalálható.

A lebegőpontos számok a következő formában jelennek meg, ahol a kitevő a bináris kitevő:

X = tört * 2 ^ (exponens-torzítás)

Törtvonal a normalizált tört része a számnak, mivel az exponenst korrigálja úgy, hogy a kezdő bit mindig 1 legyen. Így, nem kell tárolni, és kapsz még egy kis pontossággal. Ezért van az implikált bit. Ez hasonlít a tudományos jelöléssé, ahol az exponenst úgy kezeli, hogy a tizedesvesszőtől balra egy számjegy legyen; a bináris szám kivételével az exponenst mindig manipulálhatja úgy, hogy az első bit 1 legyen, mert csak 1s és 0s lehet.

Az elfogultság a negatív exponensek tárolása elkerülésére használt torzítás. Az egyszeri pontosságú számok elfogultság értéke 127 és 1 023 (decimális) a dupla pontosságú számokhoz. Az Excel dupla pontosságú számokat tárol.

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 

A C1 cellában eredményül kapott érték 1,2 E + 200 lesz, az a1 cellához hasonló értékkel. Valójában ha az a1 és a C1 cellákat a ha függvénnyel hasonlítjuk össze, például ha (a1 = C1), az eredmény igaz lesz. Ezt az okozza, hogy az IEEE specifikációja csak 15 értékes számjegyből álló pontosságot tárol. A fenti számítás tárolásához az Excel legalább 100 számjegyből álló pontosságot igényel.

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 

A C1 cellában az eredményül kapott érték a 1.000123456789012345 helyett 1.00012345678901 lenne. Ezt az okozza, hogy az IEEE specifikációja csak 15 értékes számjegyből álló pontosságot tárol. A fenti számítás tárolásához az Excel legalább 19 számjegyből álló pontosságot igényel.

Pontossági hibák javítása

Az Excel két alapvető módszert kínál a kerekítési hibák kompenzálására: a kerekítés függvényt és a mutatott pontosságot , illetve a pontosság beállításához a munkafüzet beállítást.

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

Az előző adatok felhasználásával a következő példa a ROUND függvényt használja arra, hogy egy számot öt számjegyre kényszerít. Ez lehetővé teszi, hogy az eredményt sikeresen összevesse egy másik értékkel.

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

Ennek eredménye 1.2 e + 200.

D1: = IF (C1 = 1.2 E + 200, IGAZ, HAMIS)

Ennek eredménye az igaz érték.

2. módszer: a mutatott pontosság

Bizonyos esetekben megakadályozhatja, hogy a kerekítés a munkákért a * * pontosság látható * * opcióval történjen. Ez a beállítás kényszeríti a munkalap minden egyes számának értékét a megjelenített értékként. 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ásokparancsára, majd a speciális kategóriára.
  2. A munkafüzet számítása szakaszban jelölje ki a kívánt munkafüzetet, majd jelölje be a pontosság beállítása megjelenített jelölőnégyzetet.

Ha például olyan számformátumot választ, amely két tizedesjegyet jelenít meg, és a pontosság látható , akkor a munkafüzet mentésekor a két tizedeshelyen túli pontosság elvesznek. Ez a beállítás hatással van az aktív munkafüzetre, beleértve az összes munkalapot. Ezt a beállítást nem vonhatja vissza, és nem állíthatja helyre az elveszett adatokat. Javasoljuk, hogy a beállítás engedélyezése előtt mentse a munkafüzetet.

A közel nulla eredményt tartalmazó ismétlődő bináris számok és számítások

A lebegőpontos számok bináris formátumban történő tárolását egy másik zavaró probléma az, hogy bizonyos számok, amelyek a 10 decimális bázisú véges, nem ismétlődő számok, bináris formában végtelen, ismétlődő számok. A leggyakoribb példa erre az érték 0,1 és variációi. Bár ezek a számok is tökéletesen képviseli a bázis 10, azonos számú bináris formátumban lesz a következő ismétlődő bináris szám, ha tárolják a mantissa: 

000110011001100110011 (és így tovább)

Az IEEE 754 specifikációja semmilyen külön engedményt nem tesz a számnál. Azt tárolja, amit tud a mantissza és levágja a többit. Emiatt a-2.8 E-17, vagy a 0,000000000000000028-es hibát eredményezi.

Még a gyakori tizedes törtek, mint például a decimális 0,0001, nem ábrázolhatók pontosan bináris formátumban. a (0,0001 egy ismétlődő bináris törtvonal, amelynek időtartama 104 bit). Ez hasonló ahhoz, hogy miért a frakció 1/3 nem pontosan képviseli a decimális (ismétlődő 0.33333333333333333333).

Vegyük például a következő egyszerű példát a Microsoft Visual Basic for Applications-ben: 

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

Ez 0.999999999999996 nyomtat kimenetként. A kis hiba a képviselő 0,0001 a bináris szaporítja az összeget. 

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

  1. Írja be a következőt egy új munkafüzetbe:

    A1: = (43.1-43.2) + 1

  2. Kattintson a jobb gombbal az a1 cellára, majd kattintson a Cellák formázásaparancsra. A szám lapon kattintson a kategória területen a tudományos elemre. Állítsa be a tizedesjegyeket 15-re.

A 0,9 megjelenítése helyett az Excel megjeleníti a 0.899999999999999. Mert (43.1-43.2) van számított első,-0,1 van raktározott ideiglenesen és a hiba-ból raktározó-0,1 van bemutatott levegőbe számítás. 

Például, ha egy érték eléri a nullát

  1. Az Excel 95-as vagy korábbi verzióiban az alábbi új munkafüzetbe írja be a következőt:

    A1: = 1.333 + 1.225-1.333-1.225

  2. A jobb oldali egérgombbal jelölje ki az A1 cellát, és kattintson a Cellák formázásaparancsra. A szám lapon kattintson a kategória területen a tudományos elemre. Állítsa be a tizedesjegyeket 15-re.

A 0, Excel 95 kijelzők helyett a-2.22044604925031 E-16 értéket jeleníti meg.

Kitűnik 97, bármennyire, bemutatott egy Optimization amit kísérlet-hoz kijavít részére ez probléma. Ha egy összeadás vagy kivonási művelet eredménye egy érték, vagy nagyon közel nulla, az Excel 97-es és újabb verziói kompenzálják a hiba következtében bevezetett, és az operandus bináris. A fenti példában az Excel 97 és későbbi verzióiban helyesen jelennek meg a 0 vagy a 0.000000000000000 E + 00 tudományos jelöléssel.

A lebegőpontos számokkal és az IEEE 754 specifikációval kapcsolatos további információért kérjük, olvassa el az alábbi World Wide Web oldalakat: