Megosztás a következőn keresztül:

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

  • A következőre érvényes:: Excel 2010, Excel 2013, Excel for Microsoft 365, Microsoft Excel for Mac 2011, Excel for Mac for Microsoft 365

Összegzé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 specifikáció egy széles körben elterjedt specifikáció, amely leírja, hogyan kell bináris számítógépen tárolni a lebegőpontos számokat. Ez azért népszerű, mert lehetővé teszi a lebegőpontos számok ésszerű mennyiségű helyen való tárolását, és a számítások viszonylag gyorsan történnek. 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 jeleníthető meg véges (korlátozott) térben. Ezért ezt a számot a rendszer körülbelül -2,8E-17-sel lefelé kerekítve tárolja.

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-nek -0 fogalma van, és az Excel 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!)

Olyan esetek, amelyekben nem tartjuk be az IEEE 754-et

  • 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ám nem rendelkezik implicit vezetővel, így a 0011001 példánkban a denormalizált szám 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 implementálja a specifikáció ezen opcionális részét, mert a denormalizált számok természetüknél fogva változó számú jelentős számjegyből áll. 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égtelenségeket, hanem #DIV/0-t ad! 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 ehelyett azonnal létrehoz egy hibát, 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 A kitevő A mantissza
1 előjel bit 11 bit kitevő 1 implicit bit + 52 bit tört

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 következménye, é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á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. Így nem kell tárolni, és még egy kis pontosságot kap. Ezért van egy hallgatólagos bit. 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. A fenti számítás tárolásához az Excelnek legalább 19 számjegynyi pontosságra van szüksége.

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. Ezt a beállítást nem vonhatja vissza, és nem állíthatja helyre az elveszett adatokat. 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

Egy másik zavaró probléma, amely befolyásolja a lebegőpontos számok bináris formátumban való tárolását, hogy egyes számok, amelyek végesek, nem ismétlődő számok a decimális 10-es számban, végtelenek, és binárisan ismétlődő számok. 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ók a 10-es alapban, ugyanaz a szám bináris formátumban a következő ismétlődő bináris szám lesz, amikor a mantissa tárolja őket:

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 körülbelül -2.8E-17 vagy 0,000000000000028-ra vonatkozó hibát eredményez a tároláskor.

Még a gyakori tizedes törtek, például a decimális 0,0001 sem jeleníthetők meg pontosan binárisan. (A 0,0001 egy ismétlődő bináris törtszám, amelynek 104 bitből álló periódusa van). Ez hasonló ahhoz, hogy az 1/3 tört nem jeleníthető meg pontosan tizedesjelben (ismétlődő 0,333333333333333333333333333).

Vegyük például az alábbi példát a Microsoft Visual Basic for Applicationsben:

   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ámokkal és az IEEE 754 specifikációval kapcsolatos további információkért tekintse meg a következő világszintű webhelyeket: