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 Microsoft Excel hur du lagrar och beräknar flyttalsnummer. Det kan påverka resultatet av vissa tal eller formler på grund av avrundning eller trunkering av data.
Översikt
Microsoft Excel har utformats runt IEEE 754-specifikationen för att avgöra hur den lagrar och beräknar flyttal. IEEE är instituten för el- och elektroniktekniker, ett internationellt företag som bland annat avgör standarder för datorprogramvara 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. För Excel är det högsta tal 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 Excel är resultatet 0 (med undantag för att IEEE har begreppet -0 och Excel det 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 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 det 10011001 på grund av den underförstådda inledande siffran 1. Ett avmaliserat tal har inte någon implicit inledande siffra, vilket innebär att det avomaliserade talet förblir det samma i vårt exempel 0011001 tal. I det här fallet har det normaliserade talet åtta signifikanta siffror (10011001) medan det avmaliserade talet har fem signifikanta siffror (11001) med inledande nollor som inte har 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 infinier: Obestämlighet uppstår när du dividera med 0. Excel stöder inte infiniering ger den 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ändligheten/oändligheten eller kvadratroten av -1). Med NaNs kan ett program fortsätta att göra en ogiltig åtgärd. Excel i stället omedelbart genereras ett fel, till exempel #NUM! eller #DIV/0!.
Precision
Ett flyttal lagras i binär form i tre delar inom ett 65-bitars intervall: tecknet, exponenten och mantissa.
| Tecknet | Exponenten | Mantissa |
|---|---|---|
| 1-teckensbit | 11 bitars exponent | 1 underförstådda bitar + 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 kan ä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. För tal med dubbel precision är partionen för tal med enkel precision 127 och 1 023 (decimal). Excel 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 Excel minst 100 siffrors precision.
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 Excel minst 19 siffrors precision.
Korrigera precisionsfel
Excel två grundläggande metoder för att kompensera för avrundningsfel: funktionen AVRUNDA och visad precision eller alternativet Ange visad precision.
Metod 1: Funktionen AVRUNDA
Med föregående data används funktionen AVRUNDA i följande exempel 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.
- Klicka på Alternativ på Arkiv-menyn och klicka sedan på kategorin Avancerat.
- 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 period på 104 bitar). Det här påminner om varför decimaltecknet 1/3 inte kan representeras exakt i decimalformat (0,333333333333333333333333).
Tänk dig exempelvis 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
Ange följande i en ny arbetsbok:
A1: =(43,1-43,2)+1
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 Excel 0,89999999999999. 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
I Excel 95 eller tidigare anger du följande i en ny arbetsbok:
A1: =1,333+1,225-1,333-1,225
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 Excel 95-skärmar -2,22044604925031E-16.
Excel 97 har dock introducerat 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 på eller mycket nära noll kompenserar Excel 97 och senare för eventuella 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 vetenskaplig notation.
Mer information om flyttalsnummer och IEEE 754-specifikationen finns på följande webbplatser: