Del via

Flytende aritmetikk kan gi unøyaktige resultater i Excel

  • Gjelder for: Excel 2010, Excel 2013, Excel for Microsoft 365, Microsoft Excel for Mac 2011, Excel for Mac for Microsoft 365

Sammendrag

Denne artikkelen beskriver hvordan Microsoft Excel lagrer og beregner flyttall. Dette kan påvirke resultatene av enkelte tall eller formler på grunn av avrunding eller avkorting av data.

Oversikt

Microsoft Excel ble utformet rundt IEEE 754-spesifikasjonen for hvordan det lagrer og beregner flyttall. IEEE er Institute of Electrical and Electronics Engineers, et internasjonalt organ som blant annet bestemmer standarder for dataprogramvare og maskinvare. Spesifikasjonen 754 er en utbredt spesifikasjon som beskriver hvordan flytende punkttall skal lagres i en binær datamaskin. Det er populært fordi det gjør at flytende punkttall kan lagres i en rimelig mengde plass og beregninger forekomme relativt raskt. 754-standarden brukes i flyttallenheter og numeriske dataprosessorer for nesten samtlige av dagens PC-baserte mikroprosessorer som implementerer flyttalls-matematikk, inkludert Intel-, Motorola-, Sun- og MIPS-prosessorer.

Når tallene er lagret, kan et tilsvarende binærtall representere hvert tall eller brøktall. Brøken 1/10 kan for eksempel representeres i et desimaltallsystem som 0,1. Det samme tallet i binært format blir imidlertid følgende gjentagende binære desimaltall:

0001100110011100110011 (og så videre)

Dette kan gjentas uendelig. Dette tallet kan ikke representeres i en begrenset (begrenset) mengde plass. Dette tallet avrundes derfor ned med omtrent -2,8E-17 når det lagres.

Det er imidlertid noen begrensninger av IEEE 754-spesifikasjonen som faller inn i tre generelle kategorier:

  • Maksimums-/minimumsbegrensninger
  • Presisjon
  • Gjenta binære tall

Mer informasjon

Maksimums-/minimumsbegrensninger

Alle datamaskiner har et maksimums- og minimumsnummer som kan håndteres. Siden antall biter i minnet der tallet er lagret er avgrenset, følger det at det maksimale eller minste antallet som kan lagres, også er avgrenset. For Excel er det maksimale antallet som kan lagres, 1,79769313486232E+308 og det minste positive tallet som kan lagres, er 2,2250738585072E-308.

Tilfeller der vi overholder IEEE 754

  • Underflyt: Underflyt forekommer når et tall genereres som er for lite til å representeres. I IEEE og Excel er resultatet 0 (med unntak av at IEEE har et konsept på -0, og Excel gjør det ikke).
  • Overflyt: Overflyt forekommer når et tall er for stort til å representeres. Excel bruker sin egen spesielle representasjon for denne saken (#NUM!).

Tilfeller der vi ikke overholder IEEE 754

  • Denormaliserte tall: Et denormalisert tall angis med eksponenten 0. I så fall lagres hele tallet i mantisse, og mantisse har ingen implisitt innledende 1. Som et resultat mister du presisjon, og jo mindre tall desto mer presisjon går tapt. Tallene i den lille enden av dette området har bare ett siffer med presisjon.

    Eksempel: Et normalisert tall har en implisitt innledende 1. Hvis mantisse for eksempel representerer 0011001, blir det normaliserte tallet 10011001 på grunn av den implisitte innledende 1. Et denormalisert tall har ikke et implisitt innledende tall, så i vårt eksempel på 0011001 forblir det denormaliserte tallet det samme. I dette tilfellet har det normaliserte tallet åtte signifikante sifre (10011001), mens det denormaliserte tallet har fem signifikante sifre (11001) med foranstilte nuller som ubetydelig.

    Denormaliserte tall er i utgangspunktet en midlertidig løsning for å tillate lagring av tall som er mindre enn den normale nedre grensen. Microsoft implementerer ikke denne valgfrie delen av spesifikasjonen fordi denormaliserte tall av sin natur har et variabelt antall signifikante sifre. Dette kan gjøre det mulig å angi betydelige feil i beregninger.

  • Positive/negative uendelige: Uendelige forekommer når du deler med 0. Excel støtter ikke infiniteter, og det gir heller en #DIV/0! feil i disse tilfellene.

  • Not-a-Number (NaN): NaN brukes til å representere ugyldige operasjoner (for eksempel uendelig/uendelig, uendelig eller kvadratroten av -1). NaN-er tillater at et program kan fortsette forbi en ugyldig operasjon. Excel genererer i stedet umiddelbart en feil, for eksempel #NUM! eller #DIV/0!.

Presisjon

Et flyttall lagres i binærtall i tre deler innenfor et 65-biters område: fortegnet, eksponenten og mantisse.

Fortegnet Eksponenten Mantisse
1 signeringsbit 11-biters eksponent 1 underforstått bit + 52-biters brøk

Fortegnet lagrer fortegnet til tallet (positiv eller negativ), eksponenten lagrer potensen til 2 som tallet opphøyes eller senkes til (maksimum/minimumseffekt på 2 er +1 023 og -1 022), og mantisse lagrer det faktiske tallet. Det begrensede lagringsområdet for mantisse begrenser hvor nære to tilstøtende flytpunktnumre kan være (det vil si presisjonen).

Både mantisse og eksponenten lagres som separate komponenter. Derfor kan mulig presisjonsmengde variere avhengig av størrelsen på tallet (mantisse) som blir manipulert. Når det gjelder Excel, selv om Excel kan lagre tall fra 1,79769313486232E308 til 2,2250738585072E-308, kan det bare gjøre det innen 15 sifre med presisjon. Denne begrensningen er et direkte resultat av å følge IEEE 754-spesifikasjonen og er ikke en begrensning av Excel. Dette presisjonsnivået finnes også i andre regnearkprogrammer.

Flyttall representeres i følgende form, der eksponent er den binære eksponenten:

X = Brøk * 2^(eksponent - bias)

Brøk er den normaliserte brøkdelen av tallet, normalisert fordi eksponenten justeres slik at den innledende biten alltid er 1. På denne måten trenger den ikke å lagres, og du får enda en presisjon. Dette er grunnen til at det er en underforstått bit. Dette ligner på vitenskapelig notasjon, der du manipulerer eksponenten til å ha ett siffer til venstre for desimaltegnet. bortsett fra i binærtall, kan du alltid manipulere eksponenten slik at den første biten er en 1, fordi det bare er 1s og 0s.

Bias er skjevhetsverdien som brukes til å unngå å måtte lagre negative eksponenter. Bias for tall med enkel presisjon er 127 og 1 023 (desimal) for tall med dobbel presisjon. Excel lagrer tall ved hjelp av dobbel presisjon.

Eksempel ved bruk av svært store tall

Skriv inn følgende i en ny arbeidsbok:

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

Resultatverdien i celle C1 vil være 1,2E+200, samme verdi som celle A1. Hvis du sammenligner celle A1 og C1 ved hjelp av HVIS-funksjonen, for eksempel HVIS(A1=C1), blir resultatet SANN. Dette er forårsaket av IEEE-spesifikasjonen for lagring av bare 15 signifikante sifre med presisjon. Hvis du vil lagre beregningen ovenfor, måtte Excel minst krevd 100 sifre med presisjon.

Eksempel ved bruk av svært små tall

Skriv inn følgende i en ny arbeidsbok:

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

Resultatverdien i celle C1 vil være 1,00012345678901 i stedet for 1,000123456789012345. Dette er forårsaket av IEEE-spesifikasjonen for lagring av bare 15 signifikante sifre med presisjon. Excel krever minst 19 sifre for å kunne lagre beregningen ovenfor.

Korrigere presisjonsfeil

Excel har to grunnleggende metoder for å kompensere for avrundingsfeil: AVRUND-funksjonen og arbeidsbokalternativet Presisjon som vist eller Angi presisjon som vist.

Metode 1: AVRUND-funksjonen

Ved hjelp av de forrige dataene bruker følgende eksempel AVRUND-funksjonen til å tvinge et tall til fem sifre. Dette gjør at du kan sammenligne resultatet med en annen verdi.

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

Dette resulterer i 1,2E+200.

D1: =HVIS(C1=1,2E+200; SANN, USANN)

Dette resulterer i verdien SANN.

Metode 2: Presisjon som vist

I noen tilfeller kan det hende du kan hindre avrundingsfeil fra å påvirke arbeidet ditt ved å bruke alternativet Presisjon som vist. Dette alternativet tvinger verdien av hvert tall i regnearket til å være den viste verdien. For å slå på dette alternativet, følger du disse trinnene:

  1. På Fil-menyen, klikker du på Alternativer og derette på kategorien Avansert.
  2. I Ved beregning av denne arbeidsboken-delen, velger du arbeidsboken du vil bruke velger deretter avmerkingsboksen Angi presisjon som vist.

Hvis du for eksempel velger et tallformat som viser to desimaler, og deretter aktiverer alternativet Presisjon som vist, mistes all nøyaktighet utover to desimaler når du lagrer arbeidsboken. Dette alternativet påvirker den aktive arbeidsboken, inkludert alle regneark. Du kan ikke angre dette alternativet og gjenopprette tapte data. Vi anbefaler at du lagrer arbeidsboken før du aktiverer dette alternativet.

Gjenta binære tall og beregninger som har nesten null resultater

Et annet forvirrende problem som påvirker lagringen av flyttall i binærformat, er at noen tall som er begrensede, ikke-gjentatte tall i desimaltall 10, er uendelige, gjentatte tall i binærtall. Det vanligste eksemplet på dette er verdien 0,1 og dens variasjoner. Selv om disse tallene kan representeres perfekt i grunntall 10, blir det samme tallet i binærformat følgende gjentatte binærtall når det lagres i mantissa:

000110011001100110011 (og så videre)

IEEE 754-spesifikasjonen gir ingen spesielle tillegg for noen tall. Den lagrer hva den kan i mantisse og avkorter resten. Dette resulterer i en feil på om -2.8E-17, eller 0.0000000000000028 når den lagres.

Selv vanlige desimalfraksjoner, for eksempel desimaltall 0,0001, kan ikke representeres nøyaktig i binærtall. (0,0001 er en gjentatt binær brøk som har en periode på 104 biter). Dette ligner på hvorfor brøken 1/3 ikke kan representeres nøyaktig i desimal (gjentakende 0,33333333333333333333333333333333).

Vurder for eksempel følgende eksempel i Microsoft Visual Basic for Applications:

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

Dette SKRIVER ut 0,999999999999996 som utdata. Den lille feilen i å representere 0,0001 i binære overføringer til summen.

Eksempel: Legge til et negativt tall

  1. Skriv inn følgende i en ny arbeidsbok:

    A1: =(43.1-43.2)+1

  2. Høyreklikk på celle A1 og klikk deretter på Formater celler. Klikk på Vitenskapelig under Kategori på Tall-fanen. Angi Desimalplasser til 15.

I stedet for å vise 0,9 viser Excel 0,899999999999999. Fordi (43,1-43,2) beregnes først, lagres -0,1 midlertidig, og feilen fra lagring av -0,1 introduseres i beregningen.

Eksempel når en verdi når null

  1. I Excel 95 eller tidligere skriver du inn følgende i en ny arbeidsbok:

    A1: =1.333+1.225-1.333-1.225

  2. Høyreklikk på celle A1 og klikk deretter på Formater celler. Klikk på Vitenskapelig under Kategori på Tall-fanen. Angi Desimalplasser til 15.

I stedet for å vise 0, Excel 95 -2,22044604925031E-16.

Excel 97 innførte imidlertid en optimalisering som forsøker å rette opp for dette problemet. Hvis en addisjons- eller subtraksjonsoperasjon fører til en verdi ved eller svært nær null, vil Excel 97 og nyere kompensere for eventuelle feil som ble innført som et resultat av konvertering av en operand til og fra binær. Eksemplet ovenfor når det utføres i Excel 97 og nyere, viser 0 eller 0,000000000000000E+00 i vitenskapelig notasjon.

Hvis du vil ha mer informasjon om flyttall og IEEE 754-spesifikasjonen, kan du se følgende webområder for hele verden: