Liukuluku aritmeettinen voi antaa epätarkkoja tuloksia Excelissä

Huomautus

Office 365 ProPlus nimetään uudelleen Microsoft 365 Apps for enterprise -sovellukseksi. Lisätietoja tästä muutoksesta on tässä blogikirjoituksessa.

Yhteenveto

Tässä artikkelissa kerrotaan, miten Microsoft Excel tallentaa ja laskee liukulukuja. Tämä voi vaikuttaa joidenkin lukujen tai kaavojen tuloksiin pyöristys- tai tietojen katkaisemisen vuoksi.

Yleistä

Microsoft Excel on suunniteltu IEEE 754 -määrityksen ympärille sen määrittämiseksi, miten se tallentaa ja laskee liukulukuja. IEEE on sähkö- ja elektroniikkainsinöörien laitos, kansainvälinen elin, joka muun muassa määrittää tietokoneohjelmistojen ja -laitteistojen standardit. 754-määritys on hyvin laajalti käytössä ollut määritys, joka kuvaa liukulukujen tallennusta binaaritietokoneeseen. Se on suosittu, koska sen avulla liukulukuja voi 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 TIETOKONE-pohjaisten mikroprosessorien kanssa, jotka käyttävät liukulukuja, mukaan lukien Intel-, Lg-, Sun- ja MIPS-suorittimet.

Kun luvut on tallennettu, sitä 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 binaariluvuksi:

000110011001110011 (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
  • Binaarilukujen toistaminen

Lisätietoja

Vähimmäis- ja enimmäisrajoitukset

Kaikilla tietokoneilla on suurin ja pienin mahdollinen käsittelymäärä. Koska muistin bittimäärä, johon luku on tallennettu, on äärellinen, se johtuu siitä, että tallennettava suurin tai pienin mahdollinen määrä on myös äärellinen. Excelissä tallennettava enimmäismäärä on 1,79769313486232E+308 ja tallennettava pienin positiivinen luku on 2,2250738585072E-308.

IEEE 754 -tapausten noudatet

  • Alivuoto: Alivuoto tapahtuu, kun luodaan luku, joka on liian pieni ja jota ei voida esittää. IEEE:ssa ja Excelissä tulos on 0 (lukuun ottamatta sitä, että IEEE:n käsite on -0, eikä Excel).
  • Ylivuoto: Ylivuoto tapahtuu, kun luku on liian suuri kuvattavaksi. Excel käyttää tässä tapauksessa omaa erikoismuotoa (#NUM!).

Tapaukset, joissa IEEE 754 ei noudata

  • Denormalisoidut luvut: Denormalisoitu luku osoitetaan luvun 0 eksponentilla. Tässä tapauksessa koko luku tallennetaan mantissaan eikä mantissassa ole implisiittistä etunumeroa 1. Tämän vuoksi menetät tarkkuuden ja mitä pienempi luku on, sitä enemmän tarkkuutta menetetään. Tämän alueen pienissä päässä luvuissa on vain yksi numeron tarkkuudella.

    Esimerkki: Normalisoitu luku sisältää implisiittisen luvun 1. Jos esimerkiksi mantissa edustaa lukua 0011001, normalisoitu luku on 10011001 implisiittisen luvun 1 vuoksi. Denormalisoidulla luvulla ei ole implisiittistä numeroa, joten esimerkissämme 0011001 denormalisoitu luku pysyy samana. Tässä tapauksessa normalisoidussa luvussa on kahdeksan merkitsevää numeroa (10011001), kun taas denormalisoidussa luvussa on viisi merkitsevää numeroa (11001), ja etunollat ovat merkityksettömiä.

    Denormalized numbers are basic a workaround to allow numbers smaller than the normal lower limit to be stored. Microsoft ei ota käyttöön tätä määrityksen valinnaista osaa, koska denormalisoiduilla luvuilla on muuttuva määrä merkitsevää numeroa. Tämä voi sallia merkittävän virheen kirjoittaessasi laskutoimituksia.

  • Positiivinen/negatiivinen infinities: Infinities tapahtuu, kun jaat nollalla. Excel ei tue epätäydyksiä, vaan antaa #DIV/0! -virheen näissä tapauksissa.

  • Not-a-Number (NaN): NaN-argumentilla voidaan esittää virheellisiä toimintoja (kuten ääretön/ääretön, ääretön tai luvun -1 neliöjuuri). NaNs allow a program to continue past an invalid operation. Excel luo sen sijaan välittömästi virheen, kuten #NUM! tai #DIV/0!.

Tarkkuus

Liukuluku tallennetaan binaarimuodossa 65-bittisen alueen kolmeen osaan: merkkiin, eksponenttiin ja mantissaan.

Merkki Eksponentti Mantissa
1-merkkinen bitti 11 bittiä eksponentti 1 implied bit + 52 bits fraction

Merkkiin tallennetaan luvun (positiivinen tai negatiivinen) merkki, eksponentti tallentaa luvun 2 potenssin korotettuna tai pieneneneenä (luvun 2 suurin/pienin potenssi on +1 023 ja -1 022) ja mantissa tallentaa todellisen luvun. Mantissan äärellinen tallennusalue rajoittaa kahden vierekkäisen liukulukujen sulkemista (tarkkuutta).

Mantissa ja eksponentti tallennetaan erillisinä osina. Tämän vuoksi mahdollinen tarkkuus voi vaihdella sen mukaan, kuinka monta lukua (mantissaa) on muokattu. Excelissä luvut 1,79769313486232E308–2,2250738585072E-308 voidaan tallentaa vain 15 numeron tarkkuudella. Tämä rajoitus on suora tulos, kun IEEE 754 -määritystä noudatetaan tiukasti eikä se ole Excelin rajoitus. Tämä tarkkuustaso löytyy myös muista taulukkolaskentaohjelmista.

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

X = Murtoluku * 2^(eksponentti - lauseke)

Murtoluku on luvun normalisoitu murtolukuosa, joka normalisoituu, koska eksponenttia säädetään siten, että johtava bitti on aina 1. Näin sitä ei tarvitse tallentaa ja tarkkuus on vielä yksi. 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 käsitellä eksponenttia siten, että ensimmäinen bitti on 1, koska niitä on vain 1s ja 0s.

Negatiivisten eksponenttien tallentamisen välttämiseksi käytetään negatiivisia eksponentteja. Yksitarkkuuisten lukujen 127 ja 1 023 (desimaali) tarkkuuden luvuissa on 127. Excel tallentaa luvut kaksoistarkkuusa käyttäen.

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ää numeroa tarkkuuden mukaan. Jotta voit tallentaa yllä olevan laskutoimituksen, Excel edellyttää 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, ei arvoa 1,000123456789012345. Tämä johtuu IEEE-määrityksestä, jossa tallennetaan vain 15 merkitsevää numeroa tarkkuuden mukaan. Jotta voit tallentaa yllä olevan laskutoimituksen, Excel edellyttää vähintään 19 numeron tarkkuutta.

Tarkkuusvirheiden korjaaminen

Excelissä on kaksi perusmenetelmää pyöristysvirheiden tasaamiseksi: PYÖRISTÄ-funktio ja Tarkkuus näyttönä tai Aseta tarkkuus työkirjan näyttönä -vaihtoehto.

Menetelmä 1: PYÖRISTÄ-funktio

Seuraavassa esimerkissä käytetään 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 näytöllä

Joissakin tapauksissa voit estää pyöristysvirheitä, jotka vaikuttavat työsi työhön, käyttämällä Tarkkuus kuten näkyvissä -asetusta. Tämä asetus pakottaa jokaisen laskentataulukon luvun arvon näytettäväksi arvoksi. Voit ottaa tämän asetuksen käyttöön seuraavasti.

  1. Valitse Tiedosto-valikossa Asetukset ja valitse sitten Lisäasetukset-luokka.
  2. Valitse Tätä työkirjaa laskettaessa -osassa haluamasi työkirja ja valitse sitten Aseta tarkkuus kuten näkyvissä -valintaruutu.

Jos esimerkiksi valitset lukumuotoilun, jossa näkyy kaksi desimaalia, ja otat sitten Käyttöön Tarkkuus kuten näkyvissä -asetuksen, kaikki kahden desimaalin tarkkuudella ylittävä tarkkuus menetetään, kun tallennat työkirjan. Tämä asetus vaikuttaa aktiiviseen työkirjaan, joka sisältää kaikki laskentataulukot. Tätä asetusta ei voi kumota ja kadonneet tiedot voi palauttaa. Suosittelemme, että tallennat työkirjan, ennen kuin otat tämän asetuksen käyttöön.

Binaarilukujen ja lähellä nollan tuloksen saamien laskutoimitusten toistaminen

Toinen sekava ongelma, joka vaikuttaa liukulukujen tallennukseen binaarimuodossa, on se, että jotkin äärelliset luvut, jotka eivät toistu desimaaliluvuissa 10, ovat loputtomia, toistuvia lukuja binaarimuodossa. Yleisin esimerkki tästä on arvo 0,1 ja sen muunnelmia. Vaikka nämä luvut voidaan esittää täydellisesti perusluvussa 10, sama luku binaarimuodossa tulee seuraava toistuva binaariluku, kun se tallennetaan mantissa:

00011001100110011 (ja niin edelleen)

IEEE 754 -määrityksessä ei ole erityisavua missään numerossa. Se tallentaa tiedot mantissaan ja katkaisee loput. Tällöin virhe on noin -2.8E-17 tai 0,0000000000000000028, kun se on tallennettu.

Edes yleisiä desimaalilukuja, kuten desimaalilukua 0,0001, ei voida esittää täsmälleen binaarimuodossa. (0,0001 on toistuva binaarinen murtoluku, jonka piste on 104 bittiä). Tämä muistuttaa sitä, miksi murtolukua 1/3 ei voida esittää täsmälleen desimaalimuodossa (toistuva 0,3333333333333333333333).

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

Tuloste on PRINT 0,99999999999996. Pieni virhe, joka edustaa binaarilukua 0,0001, välitety summaan.

Esimerkki: Negatiivisen luvun lisääminen

  1. Kirjoita uuteen työkirjaan seuraavat tiedot:

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

  2. Napsauta solua A1 hiiren kakkospainikkeella ja valitse Muotoile solut. Valitse Luku-välilehden Luokka-kohdassa Tieteellinen. Määritä Desimaalit-kohdan arvoksi 15.

0.9:n sijaan Excel näyttää 0,89999999999999. Koska (43.1–43.2) lasketaan ensin, -0,1 tallennetaan väliaikaisesti ja virheen tallentaminen -0,1 otetaan käyttöön laskutoimituksessa.

Esimerkki, kun arvo saavuttaa nollan

  1. Kirjoita Excel 95:ssä tai aiemmassa versiossa uuteen työkirjaan seuraavat tiedot:

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

  2. Napsauta solua A1 hiiren kakkospainikkeella ja valitse Muotoile solut. Valitse Luku-välilehden Luokka-kohdassa Tieteellinen. Määritä Desimaalit-kohdan arvoksi 15.

0:n sijaan Excel 95 näyttää -2.22044604925031E-16:n.

Excel 97:ssä on kuitenkin otettu käyttöön optimointi, joka yrittää korjata tämän ongelman. Jos lisäys- tai vähennyslaskun tuloksena on arvo, joka on nolla tai erittäin lähellä nollaa, Excel 97 ja uudemmat korvaavat virheen, joka ilmeni, kun operannd on muunnettu binaariluvuksi tai binaariluvusta. Yllä olevassa esimerkissä Excel 97:ssä ja sitä uudemmassa versiossa 0 tai 0,000000000000E+00 näkyy tieteellisessä muodossa.

Lisätietoja liukulukuista ja IEEE 754 -määrityksestä on seuraavissa Verkkosivustoissa: