Aritmeettinen liukuluku voi antaa epätarkkoja tuloksia Excelissä

  • Artikkeli
  • Koskee seuraavia::
    Excel 2010, Excel 2013, Excel for Microsoft 365, Microsoft Excel for Mac 2011, Excel for Mac for Microsoft 365

Yhteenveto

Artikkelissa käsitellään, miten Microsoft Excel tallentaa ja laskee liukulukuja. Tämä voi vaikuttaa joidenkin lukujen tai kaavojen tuloksiin pyöristämisen tai tietojen katkaisemisen takia.

Yleiskatsaus

Microsoft Excel suunniteltiin noudattamaan IEEE 754 -määritystä, jolla täsmennetään, miten ohjelma säilöö ja laskee liukulukuja. IEEE, eli Institute of Electrical and Electronics Engineers, on kansainvälinen elin, joka muun muassa määrittää tietokoneohjelmistojen ja -laitteistojen standardit. 754-määritys on hyvin laajalti käytössä oleva määritys, joka kuvaa, kuinka liukuluvut tulisi tallentaa binaaritietokoneeseen. Määrityksen suosio johtuu siitä, että sen avulla liukulukuja voidaan tallentaa kohtuulliseen tilaan ja laskutoimitukset tapahtuvat suhteellisen nopeasti. 754-standardia käytetään liukulukuyksiköissä ja numeerisissa tiedonkäsittelyissä lähes kaikkien nykypäivän PC-pohjaisten mikroprosessorien kanssa, jotka käyttävät liukulukuja, mukaan lukien Intel-, Motorola-, Sun- ja MIPS-suorittimet.

Kun luvut on tallennettu, vastaava binaariluku voi kuvata jokaista lukua tai murtolukua. Esimerkiksi murtoluku 1/10 voidaan esittää desimaalilukujärjestelmässä lukuna 0,1. Sama luku binaarimuodossa muuttuu kuitenkin seuraavaksi toistuvaksi binaaridesimaaliksi:

0001100110011100110011 (ja niin edelleen)

Tämä voi olla loputtomasti toistuva. Tätä lukua ei voi esittää äärellisen (rajoitetun) tilan määränä. Tämän vuoksi luku pyöristetään alaspäin noin -2,8E-17, kun se tallennetaan.

IEEE 754 -määrityksellä on kuitenkin joitain rajoituksia, jotka kuuluvat kolmeen yleisluokkaan:

  • Enimmäis- ja vähimmäisrajoitukset
  • Tarkkuus
  • Toistuvat binaariluvut

Lisätietoja

Enimmäis- ja vähimmäisrajoitukset

Kaikilla tietokoneilla on suurin ja pienin mahdollinen käsittelymäärä. Koska muistin bittimäärä, johon luku on tallennettu, on äärellinen, myös suurin ja pienin mahdollinen tallennettava numero ovat äärellisiä. Excelissä suurin mahdollinen tallennettava luku on 1,79769313486232E+308 ja pienin mahdollinen on 2,2250738585072E-308.

Tapaukset, joissa me noudatamme IEEE 754 -määritystä

  • Alivuoto: Alivuotoa tapahtuu, kun luodaan luku, joka on liian pieni esitettäväksi. IEEE:ssä ja Excelissä tulos on 0 (poikkeuksena IEEE:ssä on käsite -0, mutta Excelissä ei).
  • Ylivuoto: Ylivuotoa tapahtuu, kun luku on liian suuri esitettäväksi. Excel käyttää tässä tapauksessa omaa erikoismuotoaan (#NUM!).

Tapaukset, joissa emme noudata IEEE 754 -määritystä

  • Denormalisoidut luvut: denormalisoitu luku osoitetaan luvun 0 eksponentilla. Tässä tapauksessa koko luku tallennetaan mantissaan eikä mantissassa ole implisiittistä etunumeroa 1. Tämän seurauksena menetetään tarkkuutta, ja mitä pienempi luku on, sitä enemmän tarkkuutta menetetään. Tämän lukualueen pienemmässä päässä olevien lukujen tarkkuus rajoittuu vain yhteen numeroon.

    Esimerkki: Normalisoitu luku sisältää implisiittisen etuluvun 1. Jos esimerkiksi mantissa edustaa binaaria 0011001, normalisoidusta luvusta tulee 10011001 sen oletetun etunumeron 1 takia. Denormalisoidulla luvulla ei ole implisiittistä etulukua 1, joten esimerkissämme denormalisoitu luku 0011001 pysyy samana. Tässä tapauksessa normalisoidussa luvussa on kahdeksan merkitsevää lukua (10011001), kun taas denormalisoidussa luvussa on viisi merkitsevää lukua (11001), ja etunollat ovat merkityksettömiä.

    Denormalisoidut luvut ovat käytännössä menetelmä, jolla sallitaan tavallista alarajaa pienempien numeroiden tallentaminen. Microsoft ei noudata tätä määrityksen valinnaista osaa, koska denormalisoiduilla luvuilla on muuttuva määrä merkitseviä numeroita. Tämä voi johtaa siihen, että laskutoimitukseen päätyy merkittävä virhe.

  • Positiiviset ja negatiiviset äärettömät: äärettömiä syntyy 0:lla jaettaessa. Excel ei tue äärettömiä lukuja, vaan antaa #DIV/0! -virheen näissä tapauksissa.

  • Muu kuin luku (NaN): NaN esittää virheellisiä toimintoja (kuten ääretön / ääretön, ääretön - ääretön, tai luvun -1 neliöjuuri). NaN:t sallivat ohjelman jatkaa virheellisestä toiminnosta huolimatta. Excel aiheuttaa sen sijaan välittömästi virheen, kuten #NUM! tai #DIV/0!.

Tarkkuus

Liukuluku tallennetaan binaarimuodossa kolmeen osaan 65-bittiselle alueelle: merkkiin, eksponenttiin ja mantissaan.

Merkki Eksponentti Mantissa
1-merkkinen bitti 11 bitin eksponentti 1 implisiittinen bitti + 52 bitin murtoluku

Luvun (positiivinen tai negatiivinen) merkki tallennetaan merkkiin, eksponentti tallentaa luvun 2 potenssin korotettuna tai alennettuna (luvun 2 suurin/pienin potenssi on +1 023 ja -1 022), ja mantissa tallentaa todellisen luvun. Koska mantissan tallennusalue on äärellinen, se rajoittaa, kuinka lähellä toisiaan kaksi vierekkäistä liukulukua voivat olla (eli tarkkuutta).

Mantissa ja eksponentti tallennetaan erillisinä osina. Tämän vuoksi tarkkuus voi vaihdella sen mukaan, kuinka suuri muokattava luku (mantissa) on. Excelin tapauksessa: vaikka Excel voi tallentaa lukuja väliltä 1,79769313486232E308–2,2250738585072E-308, se voi tehdä tämän vain 15 numeron tarkkuudella. Tämä rajoitus on suoraa seurausta IEEE 754 -määrityksen tiukasta noudattamisesta, eikä kyseessä ole Excelin rajoitus. Sama tarkkuustaso löytyy myös muista taulukkolaskentaohjelmista.

Liukuluvut esitetään seuraavassa muodossa, jossa eksponentti on binaarinen eksponentti:

X = Murtoluku * 2^(eksponentti - harha)

Murtoluku on luvun normalisoitu murtolukuosa, joka normalisoidaan, koska eksponenttia säädetään siten, että johtava bitti on aina 1. Näin sitä ei tarvitse tallentaa, ja saat vielä yhden bitin tarkkuutta lisää. Tästä syystä siinä on implisiittinen bitti. Tämä muistuttaa tieteellistä notaatiota, jossa eksponenttia muokataan niin, että desimaalipilkun vasemmalla puolella on yksi numero. Lukuun ottamatta binaarilukuja voit aina muokata eksponenttia siten, että ensimmäinen bitti on 1, koska käytössä on vain ykkösiä ja nollia.

Harha on harhaisuuden arvo, jota käytetään negatiivisten eksponenttien tallentamisen välttämiseksi. Yksitarkkuuksisten lukujen harha on 127 ja kaksitarkkuuksisten 1 023 (desimaali). Excel tallentaa luvut kaksoistarkkuudella.

Esimerkki hyvin suurten lukujen käyttämisestä

Kirjoita uuteen työkirjaan seuraavat tiedot:

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

Solun C1 tulos on 1,2E+200, joka on sama kuin solussa A1. Jos vertaat soluja A1 ja C1 JOS-funktiolla, esimerkiksi JOS(A1=C1), tulos on TOSI. Tämä johtuu IEEE-määrityksestä, jossa tallennetaan vain 15 merkitsevän numeron tarkkuudella. Jotta voit tallentaa yllä olevan laskutoimituksen, Excel vaatisi vähintään 100 numeron tarkkuutta.

Esimerkki hyvin pienten lukujen käyttämisestä

Kirjoita uuteen työkirjaan seuraavat tiedot:

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

Solun C1 tuloksena saatava arvo olisi 1,00012345678901 eikä 1,000123456789012345. Tämä johtuu IEEE-määrityksestä, jossa tallennetaan vain 15 merkitsevän numeron tarkkuudella. Jotta voit tallentaa yllä olevan laskutoimituksen, Excel vaatisi vähintään 19 numeron tarkkuutta.

Tarkkuusvirheiden korjaaminen

Excel tarjoaa kaksi perusmenetelmää virheiden pyöristämisen korvaamiseksi: PYÖRISTÄ-funktion ja Tarkkuus kuten näytössä tai Aseta tarkkuus kuten näytössä -vaihtoehdon työkirjalle.

Menetelmä 1: PYÖRISTÄ-funktio

Seuraavassa esimerkissä edellä käytettyihin tietoihin sovelletaan PYÖRISTÄ-funktiota, joka pakottaa luvun viiteen numeroon. Näin voit vertailla tulosta onnistuneesti toiseen arvoon.

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

Tulos on 1,2E+200.

D1: =JOS(C1=1,2E+200, TOSI, EPÄTOSI)

Tämä johtaa arvoon TOSI.

Menetelmä 2: Tarkkuus kuten näytössä

Joissakin tapauksissa voit ehkä estää työhösi vaikuttavia pyöristysvirheitä käyttämällä Tarkkuus kuten näytössä -asetusta. Tällä asetuksella jokaisen laskentataulukon luvun arvo pakotetaan näytettäväksi arvoksi. Voit ottaa toiminnon käyttöön seuraavasti.

  1. Napsauta Tiedosto-valikossa Asetukset ja napsauta sitten Lisäasetukset-luokkaa.
  2. Valitse Työkirjoja laskettaessa -osiossa haluamasi työkirja ja valitse sitten Aseta tarkkuus kuten näkyvissä -valintaruutu.

Jos valitset esimerkiksi lukumuotoilun, jossa näkyy kaksi desimaalia ja otat sitten käyttöön Tarkkuus kuten näkyvissä -asetuksen, kaikki kahden desimaalin ylittävä tarkkuus menetetään, kun tallennat työkirjan. Tämä asetus vaikuttaa aktiiviseen työkirjaan ja sen jokaiseen laskentataulukkoon. Tätä asetusta ei voi kumota eikä sitä kautta palauttaa kadonneita tietoja. Suosittelemme työkirjan tallentamista, ennen kuin otat tämän asetuksen käyttöön.

Binaarilukujen ja lähellä nollaa tuloksena saatavien laskutoimitusten toistaminen

Toinen hämmentävä ongelma, joka vaikuttaa liukulukujen tallennukseen binaarimuodossa, on että jotkin äärelliset luvut, jotka eivät toistu kymmenjärjestelmässä, ovat äärettömiä toistuvia lukuja binaarimuodossa. Yleisin esimerkki tästä on arvo 0,1 ja sen muunnelmat. Vaikka nämä luvut voidaan esittää täydellisesti kymmenjärjestelmässä, samasta luvusta binaarimuodossa tulee seuraava toistuva binaariluku mantissaan tallennettaessa:

000110011001100110011 (ja niin edelleen)

IEEE 754 -määritys ei tunne poikkeuksia millekään numeroille. Se tallentaa mantissaan ne tiedot, jotka pystyy ja katkaisee loput. Tämä johtaa virheeseen, joka on tallentamisen jälkeen noin -2.8E-17 tai 0,000000000000000028.

Edes yleisiä desimaalilukuja, kuten desimaalilukua 0,0001, ei voida esittää binaarimuodossa täsmällisesti. (0,0001 on toistuva binaarinen murtoluku, jonka jakso on 104 bittiä). Syy on sama, miksi murtolukua 1/3 ei voida esittää täsmälleen desimaalimuodossa (toistuva 0,33333333333333333333).

Tarkastellaan esimerkiksi seuraavaa yksinkertaista esimerkkiä Microsoft Visual Basic for Applicationsissa:

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

Tämä TULOSTAA 0,999999999999996 tulosteena. Binaariluvussa 0,0001 oleva pieni virhe välittyy summaan.

Esimerkki: Negatiivisen luvun lisääminen

  1. Kirjoita uuteen työkirjaan seuraavat tiedot:

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

  2. Napsauta solua A1 hiiren kakkoispainikkeella ja napsauta sitten Muotoile solut. Valitse Luku-välilehden Luokka-kohdassa Tieteellinen. Määritä Desimaalien määrän arvoksi 15.

Sen sijaan, että Excel näyttäisi 0,9, se näyttää 0,899999999999999. Koska (43.1-43.2) lasketaan ensin, -0,1 tallennetaan väliaikaisesti, ja luvun -0,1 tallentamisesta johtuva virhe välittyy laskutoimitukseen.

Esimerkki, kun arvo saavuttaa nollan

  1. Kirjoita Excel 95:ssä tai sitä aiemmissa versioissa uuteen työkirjaan seuraavasti:

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

  2. Napsauta solua A1 hiiren kakkoispainikkeella ja napsauta sitten Muotoile solut. Valitse Luku-välilehden Luokka-kohdassa Tieteellinen. Määritä Desimaalien määrän arvoksi 15.

0:n sijaan Excel 95 näyttää luvun -2,22044604925031E-16.

Excel 97:ssä otettiin kuitenkin käyttöön optimointi, jolla ongelmaan pyritään vastaamaan. Jos lisäys- tai vähennyslaskun tuloksena on arvo, joka on nolla tai erittäin lähellä nollaa, Excel 97 ja sitä uudemmat versiot korvaavat kaikki virheet, jotka johtuvat operannin muuntamisesta binaariluvuksi tai binaariluvusta. Kun yllä oleva esimerkki suoritetaan Excel 97:ssä tai uudemmassa versiossa, tulos näytetään oikein muodossa 0 tai 0,000000000000000E+00 tieteellisellä notaatiolla.

Lisätietoja liukuluvuista ja IEEE 754 -määrityksestä on seuraavilla verkkosivustoilla: