Berekeningen met drijvende komma geven onnauwkeurige resultaten in Excel

Samenvatting

In dit artikel wordt beschreven hoe Microsoft Excel opslaan en berekenen van getallen met drijvende komma. Dit kan invloed op de resultaten van enkele getallen of formules door afronding of gegevens afgekapt.

Overzicht

Microsoft Excel is ontworpen rond de specificatie IEEE 754 bepaalt hoe het opslaan en berekenen van getallen met drijvende komma. IEEE is het Institute of Electrical and Electronics Engineers, een internationaal orgaan dat onder andere normen bepaalt voor computersoftware en -hardware. De 754-specificatie is een grote schaal aangenomen specificatie waarin wordt beschreven hoe met drijvende komma nummers in een binaire computer moet worden opgeslagen. Het is populair omdat hiermee de getallen met drijvende komma worden opgeslagen in een redelijke hoeveelheid ruimte en berekeningen relatief snel worden uitgevoerd. De 754-norm wordt gebruikt in de drijvende komma-eenheden en numerieke gegevens processors van bijna alle huidige PC-microprocessors die drijvende-kommaberekeningen, met inbegrip van de Intel-, Motorola-, Sun- en MIPS-processors implementeren.

Wanneer getallen worden opgeslagen, kan een corresponderend binair getal vertegenwoordigen elk getal of breuk. Zo kan de breuk 1/10 in een decimaal talstelsel worden weergegeven als 0,1. Echter, wordt hetzelfde getal in binaire notatie het volgende repeterende binaire decimale getal:
0001100110011100110011 (enzovoort)
Dit kan eindeloos worden herhaald. Dit getal kan niet worden weergegeven in een eindige (beperkte) ruimte. Daarom dit getal wordt naar beneden afgerond met ongeveer – 2, 8E-17 wanneer het wordt opgeslagen.

Er zijn echter enkele beperkingen van de IEEE 754-specificatie die in drie algemene categorieën vallen:
  • Maximale/minimale beperkingen
  • Precisie
  • Herhalende binaire getallen

Meer informatie

Maximale/minimale beperkingen

Alle computers hebben een hoogste en laagste getal waarmee kan worden verwerkt. Omdat het aantal bits van het geheugen waarin het nummer wordt opgeslagen, eindig is, betekent dit dat de hoogste of laagste getal dat kan worden opgeslagen is echter ook beperkt. Voor Excel, dat kan worden opgeslagen niet meer dan 1, 79769313486232E + 308 en het kleinste positieve getal dat kan worden opgeslagen is 2, 2250738585072E-308.

Gevallen waarin we ons aan IEEE 754 houden

  • Negatieve overloop: Onderloop treedt op wanneer een getal wordt gegenereerd dat te klein is om te worden weergegeven. In IEEE en Excel is het resultaat 0 (met de uitzondering dat IEEE ook het begrip - 0 en Excel niet).
  • Overloop: Overloop treedt op wanneer een getal is te groot om te worden weergegeven. Excel gebruikt een eigen notatie voor deze aanvraag (#NUM!).

Gevallen waarin we niet aan IEEE 754 houden

  • Gedenormaliseerde getallen: een gedenormaliseerd getal wordt aangeduid met exponent 0. In dat geval wordt het hele getal opgeslagen in de mantisse en heeft de mantisse geen impliciete voorloop-1. Als gevolg hiervan u precisie verliest, en hoe kleiner het getal, de precisie meer verloren. Getallen aan het kleine eind van dit bereik hebben slechts één cijfer van precisie.
    Voorbeeld: Een genormaliseerd getal heeft een impliciete voorloop-1. Bijvoorbeeld, als de mantisse 0011001 voorstelt, 10011001 het genormaliseerde getal als gevolg de impliciete voorloop-1. Een gedenormaliseerd getal heeft geen impliciete voorloop-1, dus in ons voorbeeld met 0011001 verandert het gedenormaliseerde getal hetzelfde blijft. In dit geval wordt heeft het genormaliseerde getal acht significante cijfers (10011001) terwijl het gedenormaliseerde getal vijf significante cijfers (11001) heeft met voorloopnullen.

    Gedenormaliseerde getallen zijn in feite een tijdelijke oplossing om getallen kleiner dan de normale ondergrens kunnen worden opgeslagen. Microsoft implementeert dit optioneel gedeelte van de specificatie niet omdat gedenormaliseerde getallen door hun aard een variabel aantal significante cijfers hebben. Hierdoor kunnen significante fouten in berekeningen.
  • Positieve/negatieve oneindige getallen: Oneindige getallen treden op wanneer u door 0 delen. Excel ondersteunt geen oneindige getallen, en geeft een #DIV/0! Fout in deze gevallen.
  • Geen getal (NaN): NaN wordt gebruikt voor ongeldige bewerkingen (zoals oneindig/oneindig, oneindig-oneindig of de vierkantswortel van -1). Dankzij NaN's kan een programma verder een ongeldige bewerking. Excel genereert in plaats daarvan onmiddellijk een fout, zoals #NUM! of #DIV/0!.

Precisie

Een getal met drijvende komma wordt binair in drie delen binnen een 65-bits bereik opgeslagen: het teken, de exponent en de mantisse.
1 tekenbit11-bits Exponent1 impliciete Bit52-bits mantisse
Het teken slaat het teken van het getal (positief of negatief), de exponent bevat de macht van 2 waartoe het getal wordt verhoogd of verlaagd (de maximale/minimale macht van 2 is + 1,023 en -1,022), en de mantisse bevat het feitelijke getal. De eindige opslagruimte voor de mantisse bepaalt hoe dicht twee aangrenzende getallen met drijvende komma is (dat wil zeggen, de precisie).

De mantisse en de exponent worden als afzonderlijke componenten opgeslagen. Als gevolg hiervan, afhankelijk de mate van precisie mogelijk van de grootte van het getal (de mantisse) dat wordt gemanipuleerd. In Excel, hoewel Excel getallen kan opslaan van 1, 79769313486232E308 tot 2, 2250738585072E-308, deze kan alleen doen binnen een precisie van 15 cijfers. Deze beperking is een rechtstreeks gevolg van een strikte naleving van de IEEE 754-specificatie en is niet een beperking van Excel. Deze mate van precisie wordt gevonden in andere spreadsheetprogramma's.

Drijvende-kommagetallen worden weergegeven in de volgende notatie, waarbij exponent de binaire exponent is:
X = breuk * 2 ^ (exponent - bias)
Noemer is het genormaliseerde fractionele deel van het getal omdat de exponent is aangepast, zodat de belangrijkste bit altijd 1 is. Op deze manier niet hoeft te worden opgeslagen en u krijgt een meer bits precisie. Daarom is er een impliciete bit. Dit is vergelijkbaar met de wetenschappelijke notatie, waarbij het manipuleren van de exponent één cijfer links van de decimale komma; behalve in binaire notatie, kunt u altijd bewerken de exponent zodat de eerste bit een 1 is, omdat er alleen 1s en 0s.

Bias is de bias-waarde gebruikt om te voorkomen dat u voor de opslag van negatieve exponenten. De bias voor getallen met enkele precisie is 127 en 1,023 (decimaal) voor getallen met dubbele precisie. Excel worden getallen met dubbele precisie opgeslagen.

Voorbeeld met zeer grote getallen

Voer het volgende in een nieuwe werkmap:
   A1: 1.2E+200
B1: 1E+100
C1: =A1+B1
De resulterende waarde in cel C1 is 1, 2E + 200, dezelfde waarde als de cel A1. In feite als u de cellen A1 en C1 vergelijkt met de functie als, bijvoorbeeld als(a1=C1), het resultaat is TRUE. Dit wordt veroorzaakt door de IEEE-specificatie maar 15 significante cijfers precisie opslaat. Excel zou minstens 100 cijfers precisie nodig hebben om te kunnen opslaan van de bovenstaande berekening.

Voorbeeld met zeer kleine getallen

Voer het volgende in een nieuwe werkmap:
   A1: 0.000123456789012345
B1: 1
C1: =A1+B1
De resulterende waarde in cel C1 zou 1,00012345678901 in plaats van 1,000123456789012345. Dit wordt veroorzaakt door de IEEE-specificatie maar 15 significante cijfers precisie opslaat. Excel zou minstens 19 cijfers precisie nodig hebben om te kunnen opslaan van de bovenstaande berekening.

Precision fouten corrigeren

Excel biedt twee basismethoden om afrondingsfouten te compenseren: de functie afronden en de Precisie zoals afgebeeld of
Werkmapoptie Precisie instellen zoals weergegeven .

Methode 1: De functie afronden

Het volgende voorbeeld wordt de functie AFRONDEN om een getal vijf cijfers met behulp van de voorgaande gegevens. Hiermee kunt u het resultaat met een andere waarde vergelijken.
   A1: 1.2E+200
B1: 1E+100
C1: =ROUND(A1+B1,5)
Dit resulteert in 1, 2E + 200.
   D1: =IF(C1=1.2E+200, TRUE, FALSE) 
Dit resulteert in de waarde TRUE.

Methode 2: Precisie zoals afgebeeld

In sommige gevallen kunt u mogelijk voorkomen dat afrondingsfouten in invloed op uw werk hebben door de optie Precisie zoals afgebeeld . Deze optie zorgt ervoor dat de waarde van elk getal in het werkblad op de weergegeven waarde. Ga als volgt te werk om deze optie inschakelen.



 
  1. Klik op Optiesin het menu bestand en klik vervolgens op de categorie Geavanceerd .
  2. In de sectie bij het berekenen van deze werkmap de werkmap die u wilt selecteren en selecteer vervolgens het selectievakje Precisie instellen zoals weergegeven in.

Bijvoorbeeld, als u een getalnotatie met twee decimale plaatsen, schakelt u de optie Precisie zoals afgebeeld , gaat alle precisie buiten twee decimalen verloren wanneer u de werkmap opslaat. Deze optie heeft invloed op de actieve werkmap inclusief alle werkbladen. U kan deze optie ongedaan maken en de verloren gegevens herstellen. Het is raadzaam dat u de werkmap opslaan voordat u deze optie inschakelt.

Herhalende binaire getallen en berekeningen die in de buurt van nul resultaten

Een ander verwarrend probleem die gevolgen heeft voor de opslag van getallen met drijvende komma in binaire indeling is dat sommige getallen die decimaal grondtal 10, beperkte, niet-herhalende getallen zijn oneindige, herhalende binaire getallen. De meest voorkomende voorbeeld hiervan is de waarde 0,1 en varianten ervan. Hoewel deze getallen perfect kunnen worden weergegeven in het grondtal 10, wordt hetzelfde getal in binaire notatie het volgende herhalende binaire getal wanneer het in de mantisse wordt opgeslagen:
000110011001100110011 (enzovoort)
De IEEE 754-specificatie is geen speciale vergoeding voor een willekeurig getal. Het wordt zoveel mogelijk in de mantisse en de rest wordt afgekapt. Dit resulteert in een fout van ongeveer - 2, 8E-17 of 0,000000000000000028 het is.

Zelfs veelvoorkomende decimale breuken, zoals de decimale waarde 0,0001, kunnen niet precies in binaire notatie worden weergegeven. (0,0001 is een herhalende binaire fractie met een periode van 104 bits). Dit is vergelijkbaar met waarom de breuk 1/3 niet exact kan worden weergegeven in decimale notatie (een herhalende 0,33333333333333333333).

Bekijk het volgende eenvoudige voorbeeld in 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
Dit 0,999999999999996 geeft als output. De kleine fout in de binaire weergave van 0,0001 doorgegeven aan het totaal.

Voorbeeld: Een negatief getal optellen

  1. Voer het volgende in een nieuwe werkmap:
       A1: =(43.1-43.2)+1 
  2. Klik met de rechtermuisknop in cel A1 en klik vervolgens op Celeigenschappen. Klik op wetenschappelijk onder categorieop het tabblad getal . De cijfers achter de komma wordt ingesteld op 15.
In plaats van 0,9 geeft Excel 0,899999999999999. Omdat (43,1-43,2) eerst wordt berekend, is die tijdelijk -0,1 opgeslagen, en de fout van de opslag van-0,1 in de berekening wordt geïntroduceerd.

Voorbeeld van een waarde die nul benadert

  1. In Excel 95 of eerder, voer het volgende in een nieuwe werkmap:
       A1: =1.333+1.225-1.333-1.225 
  2. Rechts clickcell A1, en klik vervolgens op Celeigenschappen. Klik op wetenschappelijk onder categorieop het tabblad getal . De cijfers achter de komma wordt ingesteld op 15.
In plaats van 0, Excel 95 wordt weergegeven - 2.22044604925031E-16.

Excel 97 is echter een verbetering opgenomen waarmee wordt geprobeerd om te corrigeren voor dit probleem. Moet een bewerking optellen of aftrekken, resulteert in een waarde op of zeer dicht bij nul, Excel 97 en later een fout die als gevolg van de conversie van een operand naar en van binair wordt gecompenseerd. Als het bovenstaande voorbeeld uitgevoerd in Excel 97 en later juist weer 0 of 0.000000000000000E + 00 in wetenschappelijke notatie. Klik op de volgende artikelnummers om de artikelen in de Microsoft Knowledge Base voor meer informatie:
172911 onjuist resultaat bij verhogen van 10 tot zeer grote/kleine macht
214373 onjuist resultaat bij verhogen van 10 tot zeer grote/kleine macht
Voor meer informatie over getallen met drijvende komma en de IEEE 754-specificatie raadpleegt u de volgende World Wide Web-sites:

Referenties

Voor meer informatie over het omzeilen van deze fouten, klikt u op het volgende artikel in de Microsoft Knowledge Base:
214118 het afrondingsfouten in getallen met drijvende komma corrigeren
Opmerking In dit artikel geldt ook voor Microsoft Excel voor Mac voor Office 365.
Eigenschappen

Artikel-id: 78113 - Laatst bijgewerkt: 14 feb. 2017 - Revisie: 2

Microsoft Excel 2010, Microsoft Excel 2013, Excel for Office 365, Microsoft Excel 2011 for Mac, Excel for Mac for Office 365

Feedback