Aritmetisk flyttal kan ge felaktiga resultat i Excel

Anteckning

Office 365 ProPlus byter namn till Microsoft 365-appar för företag. Mer information om den här ändringen finns i det här blogginlägget.

Sammanfattning

I den här artikeln beskrivs hur Microsoft Excel lagrar och beräknar flyttal. Det kan påverka resultatet av vissa tal eller formler på grund av avrundning eller trunkering av data.

Översikt

Microsoft Excel utformades runt IEEE 754-specifikationen för att avgöra hur den lagrar och beräknar flyttal. IEEE är instituten för el- och tekniktekniker, ett internationellt företag som bland annat avgör standarder för datorprogram och maskinvara. 754-specifikationen är en mycket används ofta specifikation som beskriver hur flyttal ska lagras i en binär dator. Det är populärt eftersom det gör att flyttalsnumren lagras i ett rimligt utrymme och beräkningar är relativt snabbt. 754-standarden används i flyttalsenheter och numeriska dataprocessorer hos nästan alla dagens PC-baserade mikroprocessorer som implementerar flyttalsmatematiska, inklusive Intel-, Intel-, Sun- och MIPS-processorerna.

När tal lagras kan ett motsvarande binärt tal representera varje tal eller decimaltal. Bråket 1/10 kan till exempel representeras som 0,1 i ett decimaltalssystem. Samma tal i binärt format blir emellertid följande upprepade binära decimaler:

0001100110011100110011 (och så vidare)

Det kan upprepas oändligt. Talet kan inte representeras i ett ändlig (begränsat) utrymme. Därför avrundas det här talet nedåt med ungefär -2,8E-17 när det lagras.

Det finns emellertid några begränsningar i IEEE 754-specifikationen som indelar i tre allmänna kategorier:

  • Max-/minimibegränsningar
  • Precision
  • Upprepa binära tal

Mer information

Max-/minbegränsningar

Alla datorer har ett högsta och ett lägsta antal som kan hanteras. Eftersom antalet bitar av minne där talet lagras är ändlig följer det att det högsta eller lägsta tal som kan lagras också är ändlig. I Excel är det högsta talet som kan lagras 1,79769313486232E+308 och det lägsta positiva tal som kan lagras är 2,2250738585072E-308.

Fall där vi följer IEEE 754

  • Underflow: Underflow inträffar när ett tal skapas som är för litet för att representeras. I IEEE och Excel blir resultatet 0 (med undantag för att IEEE har begreppet -0 och Excel inte).
  • Spill: Spill uppstår när ett tal är för stort för att representeras. Excel använder en egen särskild representation för detta ärende (#NUM!).

Fall där vi inte följer IEEE 754

  • Avmaliserade tal: Ett avmaliserat tal anges med en exponent på 0. I så fall lagras hela talet i mantissa och mantissa har ingen implicit inledande 1. På grund av detta förlorar du precisionen och ju lägre tal, desto mer precision går du förlorad. Talen i den lilla delen av intervallet har endast en precisionssiffror.

    Exempel: Ett normaliserat tal har en implicit inledande 1. Om mantissa till exempel representerar 0011001 blir det normaliserade talet 10011001 på grund av den underförstådda inledande 1. Ett avmaliserat tal har inte någon implicit inledande siffra, så i vårt exempel 0011001 finns det avmaliserade talet kvar. I det här fallet har det normaliserade talet åtta signifikanta siffror (10011001) medan det avomaliserade talet har fem signifikanta siffror (11001) med inledande nollor som inte är till någon betydelse.

    Avmaliserade tal är i princip en lösning för att tillåta att tal som är mindre än den normala undre gränsen lagras. Microsoft implementerar inte den här valfria delen av specifikationen eftersom avmaliserade tal har ett varierande antal signifikanta siffror. Det kan göra att stora fel kan skrivas in i beräkningar.

  • Positiva/negativa infiniteter: Obestämlighet uppstår när du dividera med 0. Excel stöder inte infiniering utan ger istället ett #DIV/0! i dessa fall.

  • Inte ett tal (NaN): NaN används för att representera ogiltiga åtgärder (t.ex. oändlighet/oändlighet eller kvadratroten av -1). Med NaNs kan ett program fortsätta att göra en ogiltig åtgärd. Excel genererar i stället omedelbart ett fel, till exempel #NUM! eller #DIV/0!.

Precision

Ett flyttal lagras i binär form i tre delar inom ett 65-bitarsintervall: tecknet, exponenten och mantissa.

Tecknet Exponenten Mantissa
1-teckensbit 11 bitars exponent 1 underförstådd bit + 52 bitars bråk

Tecknet lagrar tecknet för talet (positivt eller negativt), exponenten lagrar exponenten för 2 som talet upphöjs eller sänks till (den högsta/lägsta exponenten för 2 är +1 023 och -1 022), och mantissa lagrar det faktiska talet. Det ändliga lagringsplatsen för mantissa begränsar hur nära två intilliggande flyttal kan vara (d.v.s. precisionen).

Både mantissa och exponent lagras som separata komponenter. Därför kan mängden eventuell precision variera beroende på storleken på numret (mantissa) som ändras. När det gäller Excel, även om Excel kan lagra tal från 1,79769313486232E308 till 2,2250738585072E-308 kan det bara göra med 15 siffrors precision. Den här begränsningen är ett direkt resultat av att strikt följa IEEE 754-specifikationen och är inte en begränsning av Excel. Denna precisionsnivå finns även i andra kalkylprogram.

Flyttal representeras i följande form, där exponenten är den binära exponenten:

X = Decimal * 2^(exponent - partion)

Bråktal är den normaliserade decimaldelen av talet, normaliserad eftersom exponenten justeras så att den inledande biten alltid är 1. På så sätt behöver den inte lagras och du får en bit precision till. Det är därför det finns en underförstådd bit. Det här liknar vetenskaplig notation, där du ändrar exponenten till att ha en siffra till vänster om decimaltecknet. förutom i binära kan du alltid ändra exponenten så att den första biten är en 1, eftersom det bara finns 1 och 0.

Partiskhet är det partiske värde som används för att undvika att behöva lagra negativa exponenter. Partionen för tal med enkel precision är 127 och 1 023 (decimal) för tal med dubbel precision. Excel lagrar tal med dubbel precision.

Exempel med mycket stora tal

Ange följande i en ny arbetsbok:

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

Resultatvärdet i cell C1 blir 1,2E+200, samma värde som cell A1. Om du jämför cellerna A1 och C1 med funktionen OM, till exempel OM(A1=C1), blir resultatet SANT. Detta orsakas av IEEE-specifikationen för lagring av endast 15 signifikanta siffrors precision. För att beräkningen ska kunna lagras ovan krävs minst 100 siffrors precision i Excel.

Exempel med mycket små tal

Ange följande i en ny arbetsbok:

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

Resultatvärdet i cell C1 blir 1,00012345678901 i stället för 1,000123456789012345. Detta orsakas av IEEE-specifikationen för lagring av endast 15 signifikanta siffrors precision. För att beräkningen ska kunna lagras ovan behöver Excel minst 19 siffrors precision.

Korrigera precisionsfel

Excel erbjuder två grundläggande metoder för att kompensera för avrundningsfel: funktionen AVRUNDA och alternativet Precision som visas eller Ange visad precision som visad arbetsbok.

Metod 1: Funktionen AVRUNDA

Med hjälp av föregående data använder följande exempel funktionen AVRUNDA för att tvinga fram ett tal till fem siffror. Då kan du jämföra resultatet med ett annat värde.

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

Detta resulterar i 1,2E+200.

D1: =OM(C1=1,2E+200; SANT; FALSKT)

Detta resulterar i värdet SANT.

Metod 2: Precision som visas

I vissa fall kan du förhindra att avrundningsfel påverkar ditt arbete genom att använda alternativet Precision som visas. Det här alternativet tvingar värdet för varje tal i kalkylbladet till det värde som visas. Följ de här anvisningarna för att aktivera det här alternativet.

  1. Klicka på AlternativArkiv-menyn och klicka sedan på kategorin Avancerat.
  2. Markera den arbetsbok du vill använda i avsnittet Vid beräkning av den här arbetsboken och markera sedan kryssrutan Ange visad precision.

Om du till exempel väljer ett talformat som visar två decimaler och sedan aktiverar alternativet Visad precision försvinner all noggrannhet utöver två decimaler när du sparar arbetsboken. Det här alternativet påverkar den aktiva arbetsboken inklusive alla kalkylblad. Du kan inte ångra det här alternativet och återskapa förlorade data. Vi rekommenderar att du sparar arbetsboken innan du aktiverar det här alternativet.

Upprepa binära tal och beräkningar som har resultat nära noll

Ett annat förvirrande problem som påverkar lagringen av flyttalstal i binärt format är att vissa tal som är ändliga, icke-upprepade tal i decimalbas 10, är oändliga och upprepar tal i binärt format. Det vanligaste exemplet på det här är värdet 0,1 och dess variationer. Även om dessa tal kan representeras perfekt i bas 10, blir samma tal i binärt format följande upprepade binära tal när det lagras i mantissa:

000110011001100110011 (och så vidare)

IEEE 754-specifikationen gäller inga särskilda tillåtna nummer. Den lagrar vad den kan i mantissa och trunkerar resten. Det resulterar i ett fel om -2,8E-17 eller 0,00000000000000028 när den lagras.

Även vanliga decimaltal, t.ex. decimaltal 0,0001, kan inte representeras exakt i binärt format. (0,0001 är ett upprepande binärt bråktal som har en punkt på 104 bitar). Det här påminner om varför bråket 1/3 inte kan representeras exakt i decimalformat (ett upprepande 0,33333333333333333333).

Tänk dig till exempel följande enkla exempel 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

0,9999999999999996 skrivs ut. Det lilla felet för att representera 0,0001 i binära spridningar till summan.

Exempel: Lägga till ett negativt tal

  1. Ange följande i en ny arbetsbok:

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

  2. Högerklicka på cell A1 och klicka sedan på Formatera celler. På fliken Tal klickar du på Vetenskapligt under Kategori. Ställ in Decimaler till 15.

I stället för att visa 0,9 visas 0,899999999999999 i Excel. Eftersom (43.1-43.2) beräknas först lagras -0.1 tillfälligt och felet från att lagra -0.1 införs i beräkningen.

Exempel när ett värde blir noll

  1. I Excel 95 eller tidigare anger du följande i en ny arbetsbok:

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

  2. Högerklicka på cell A1 och klicka sedan på Formatera celler. På fliken Tal klickar du på Vetenskapligt under Kategori. Ställ in Decimaler till 15.

I stället för att visa 0 visas -2,22044604925031E-16 i Excel 95.

Excel 97 introducerade dock en optimering som försöker korrigera för det här problemet. Om en additions- eller subtraktionsåtgärd resulterar i ett värde som ligger nära eller mycket nära noll kompenserar Excel 97 och senare för alla fel som infördes till följd av konvertering av en operand till och från binär. I exemplet ovan när det utförs i Excel 97 och senare visas korrekt 0 eller 0,00000000000000E+00 i vetenskapliga notation.

Mer information om flyttalsnummer och IEEE 754-specifikationen finns på följande webbplatser: