Rekenen met drijvende komma kan onnauwkeurige resultaten geven in Excel

Samenvatting

In dit artikel wordt beschreven hoe Microsoft Excel getallen met drijvende komma opslaat en berekent. Dit kan de resultaten van sommige getallen of formules beïnvloeden vanwege afronding of gegevensafkapping.

Overzicht

Microsoft Excel is ontworpen rond de IEEE 754-specificatie om te bepalen hoe getallen met drijvende komma worden opgeslagen en berekend. IEEE is het Institute of Electrical and Electronics Engineers, een internationale organisatie die onder andere standaarden voor computersoftware en hardware bepaalt. De 754-specificatie is een zeer algemeen aanvaarde specificatie die beschrijft hoe getallen met drijvende komma moeten worden opgeslagen in een binaire computer. Het is populair omdat het ervoor zorgt dat getallen met drijvende komma in een redelijke hoeveelheid ruimte kunnen worden opgeslagen en berekeningen relatief snel kunnen plaatsvinden. De 754-standaard wordt gebruikt in de drijvende-komma-eenheden en numerieke gegevensprocessors van bijna alle huidige pc-gebaseerde microprocessors die drijvende-komma-wiskunde implementeren, inclusief de Intel-, Motorola-, Sun- en MIPS-processors.

Wanneer getallen worden opgeslagen, kan een overeenkomstig binair getal elk getal of breuknummer vertegenwoordigen. De breuk 1/10 kan bijvoorbeeld in een decimaal getalsysteem worden weergegeven als 0,1. Hetzelfde getal in binaire notatie wordt echter het volgende herhalende binaire decimaal getal:

0001100110011100110011 (enz.)

Dit kan oneindig worden herhaald. Dit getal kan niet worden weergegeven in een eindige (beperkte) hoeveelheid ruimte. Dit getal wordt daarom 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:

• Maximum-/minimumbeperkingen
• Precisie
• Herhalende binaire getallen

Meer informatie

Maximum-/minimumbeperkingen

Alle computers hebben een maximum- en minimumnummer dat kan worden verwerkt. Omdat het aantal bits geheugen waarin het nummer wordt opgeslagen eindig is, volgt hieruit dat het maximale of minimale aantal dat kan worden opgeslagen ook eindig is. Voor Excel is het maximum aantal dat kan worden opgeslagen 1,79769313486232E+308 en het minimum positieve getal dat kan worden opgeslagen is 2,2250738585072E-308.

Gevallen waarin we ons houden aan IEEE 754

• Onderstroom: onderstroom treedt op wanneer een getal wordt gegenereerd dat te klein is om te worden weergegeven. In IEEE en Excel is het resultaat 0 (met uitzondering dat IEEE een concept van -0 heeft en Excel niet).
• Overloop: overloop treedt op wanneer een getal te groot is om te worden weergegeven. Excel gebruikt een eigen speciale weergave voor dit geval (#GETAL!).

Gevallen waarin we ons niet houden aan IEEE 754

• Gedenormaliseerde getallen: een gedenormaliseerd getal wordt aangegeven door een exponent van 0. In dat geval wordt het hele getal in de mantisse opgeslagen en heeft de mantisse geen impliciete leidende 1. Als gevolg hiervan verliest u precisie en hoe kleiner het getal, hoe meer precisie verloren gaat. Getallen aan de kleine kant van dit bereik hebben slechts één cijfer van precisie.

  Voorbeeld: een genormaliseerd getal heeft een impliciete leidende 1. Als de mantisse bijvoorbeeld 0011001 vertegenwoordigt, wordt het genormaliseerde getal 10011001 vanwege de impliciete leidende 1. Een gedenormaliseerd getal heeft geen impliciete voorloop, dus in ons voorbeeld van 0011001 blijft het gedenormaliseerde getal hetzelfde. In dit geval heeft het genormaliseerde getal acht significante cijfers (10011001), terwijl het gedenormaliseerde getal vijf significante cijfers (11001) heeft, waarbij voorloopnullen onbeduidend zijn.

  Gedenormaliseerde getallen zijn in feite een tijdelijke oplossing om getallen die kleiner zijn dan de normale ondergrens, op te slaan. Microsoft implementeert dit optionele gedeelte van de specificatie niet omdat gedenormaliseerde getallen door hun aard een variabel aantal significante cijfers hebben. Hierdoor kunnen er significante fouten worden gemaakt bij het invoeren van berekeningen.

• Positieve/negatieve oneindigheden: oneindigheden treden op wanneer u deelt door 0. Excel ondersteunt geen oneindigheden, maar geeft een #DIV/0! in deze gevallen.

• Niet-een-nummer (NaN): NaN wordt gebruikt om ongeldige bewerkingen weer te geven (zoals oneindig/oneindig, oneindig-oneindig of de vierkantswortel van -1). Met NaN's kan een programma doorgaan na een ongeldige bewerking. Excel genereert in plaats daarvan onmiddellijk een fout zoals #GETAL! of #DIV/0!.

Precisie

Een getal met drijvende komma wordt binair opgeslagen in drie delen binnen een bereik van 65 bits: het teken, de exponent en de mantisse.

Het teken slaat het teken van het getal op (positief of negatief), de exponent slaat de macht van 2 op waartoe het getal verhoogd of verlaagd wordt (de maximum/minimum macht van 2 is +1,023 en -1,022) en de mantisse slaat het eigenlijke getal op. Het eindige opslaggebied voor de mantisse beperkt hoe dicht twee aangrenzende drijvende-kommagetallen kunnen zijn (dat wil zeggen, de precisie).

De mantisse en de exponent worden beide als afzonderlijke componenten opgeslagen. Hierdoor kan de mogelijke precisie variëren, afhankelijk van de grootte van het getal (de mantisse) dat wordt gemanipuleerd. In het geval van Excel, hoewel Excel nummers kan opslaan van 1.79769313486232E308 tot 2.2250738585072E-308, kan het dit alleen doen met een precisie van 15 cijfers. Deze beperking is een direct gevolg van het strikt volgen van de IEEE 754-specificatie en is geen beperking van Excel. Dit niveau van precisie is ook te vinden in andere spreadsheetprogramma's.

Drijvende-kommagetallen worden weergegeven in de volgende vorm, waarbij exponent de binaire exponent is:

X = Breuk * 2^(exponent - bias)

De breuk is het genormaliseerde fractionele deel van het getal, genormaliseerd omdat de exponent zo is aangepast dat de leidende bit altijd een 1 is. Op deze manier hoeft het niet te worden opgeslagen en krijgt u nog een beetje precisie. Daarom is er een impliciete bit. Dit is vergelijkbaar met wetenschappelijke notatie, waarbij u de exponent manipuleert om één cijfer links van de komma te hebben. Behalve in binair, kunt u de exponent altijd manipuleren zodat het eerste bit een 1 is, omdat er alleen enen en nullen zijn.

De afwijking is de waarde van de afwijking die wordt gebruikt om te voorkomen dat negatieve exponenten moeten worden opgeslagen. De afwijking voor getallen met enkele precisie is 127 en 1023 (decimaal) voor getallen met dubbele precisie. Excel slaat getallen op met dubbele precisie.

Voorbeeld met zeer grote getallen

Voer het volgende in een nieuwe werkmap in:

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

De resulterende waarde in cel C1 is 1,2E+200, dezelfde waarde als cel A1. Als u de cellen A1 en C1 vergelijkt met behulp van de ALS-functie, bijvoorbeeld IF(A1=C1), is het resultaat WAAR. Dit wordt veroorzaakt door de IEEE-specificatie van het opslaan van slechts 15 significante cijfers voor precisie. Om de bovenstaande berekening te kunnen opslaan, zou Excel een nauwkeurigheid van minimaal 100 cijfers nodig hebben.

Voorbeeld met zeer kleine getallen

Voer het volgende in een nieuwe werkmap in:

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

De resulterende waarde in cel C1 zou 1.00012345678901 zijn in plaats van 1.000123456789012345. Dit wordt veroorzaakt door de IEEE-specificatie van het opslaan van slechts 15 significante cijfers voor precisie. Om de bovenstaande berekening te kunnen opslaan, zou Excel ten minste 19 cijfers nauwkeurig moeten hebben.

Nauwkeurigheidsfouten corrigeren

Excel biedt twee basismethoden om afrondingsfouten te compenseren: de functie AFRONDEN en de werkmapoptie Precisie zoals weergegeven of Precisie instellen zoals weergegeven.

Methode 1: de functie AFRONDEN

Gebruikmakend van de vorige gegevens, gebruikt het volgende voorbeeld de functie AFRONDEN om een getal tot vijf cijfers te forceren. Hiermee kunt u het resultaat vergelijken met een andere waarde.

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

Dit resulteert in 1,2E+200.

D1: =ALS(C1=1,2E+200, WAAR, ONWAAR)

Dit resulteert in de waarde WAAR.

Methode 2: Precisie zoals weergegeven

In sommige gevallen kunt u voorkomen dat afrondingsfouten uw werk beïnvloeden door de optie Precisie zoals weergegeven te gebruiken. Deze optie dwingt de waarde van elk getal in het werkblad om de weergegeven waarde te zijn. Volg deze stappen om deze optie in te schakelen.

1. Klik in het menu Bestand op Opties en vervolgens op de categorie Geavanceerd.
2. Selecteer in het gedeelte Bij het berekenen van deze werkmap de gewenste werkmap en schakel vervolgens het selectievakje Precisie instellen zoals weergegeven in.

Als u bijvoorbeeld een getalformat kiest waarin twee decimalen worden weergegeven en u vervolgens de optie Precisie als weergegeven inschakelt, gaat alle nauwkeurigheid na twee decimaalpunten verloren wanneer je de werkmap opslaat. Deze optie is van invloed op de actieve werkmap, inclusief alle werkbladen. Je kunt deze optie niet ongedaan maken en de verloren gegevens herstellen. We raden aan je werkmap op te slaan voordat je deze optie inschakelt.

Binaire getallen en berekeningen herhalen die bijna-nulresultaten hebben

Een ander verwarrend probleem dat van invloed is op de opslag van zwevende puntnummers in binaire indeling, is dat sommige getallen die eindige, niet-herhalende getallen in decimale basis 10 zijn, oneindige herhalende getallen in binair getal zijn. Het meest voorkomende voorbeeld hiervan is de waarde 0,1 en de variaties. Hoewel deze getallen perfect kunnen worden weergegeven in basis 10, wordt hetzelfde getal in binaire notatie het volgende herhalende binaire getal wanneer het wordt opgeslagen in de mantissa:

000110011001100110011 (en zo verder)

De IEEE 754-specificatie maakt geen speciale toelating voor een getal. Het slaat op wat het kan in de mantissa en kapt de rest af. Dit resulteert in een fout van ongeveer -2,8E-17 of 0,000000000000000028 wanneer deze wordt opgeslagen.

Zelfs veelgebruikte decimale breuken, zoals decimaal 0,0001, kunnen niet exact in binair worden weergegeven. (0,0001 is een herhalende binaire breuk met een periode van 104 bits). Dit is vergelijkbaar met de reden waarom breuk 1/3 niet exact in decimaal kan worden weergegeven (een herhalende 0,33333333333333333333).

Denk bijvoorbeeld aan 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 zal 0,999999999999996 als uitvoer AFDRUKKEN. De kleine fout bij het vertegenwoordigen van 0,0001 in binair wordt doorgegeven aan de som.

Voorbeeld: een negatief getal toevoegen

1. Voer het volgende in een nieuwe werkmap in:

   A1: =(43.1-43.2)+1

2. Klik met de rechtermuisknop op cel A1 en klik vervolgens op Format cellen. Klik op het tabblad Getal op Wetenschappelijk onder Categorie. Stel de decimalen in op 15.

In plaats van 0,9, wordt Excel 0,899999999999999 weergegeven. Omdat (43.1-43.2) eerst wordt berekend, wordt -0,1 tijdelijk opgeslagen en wordt de fout bij het opslaan van -0,1 in de berekening ingevoerd.

Voorbeeld wanneer een waarde nul bereikt

1. Voer in Excel 95 of eerder het volgende in een nieuwe werkmap in:

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

2. Klik met de rechtermuisknop op cel A1 en klik vervolgens op Format cellen. Klik op het tabblad Getal op Wetenschappelijk onder Categorie. Stel de decimalen in op 15.

In plaats van 0 weer te geven, geeft Excel 95 -2,22044604925031E-16 weer.

Excel 97 heeft echter een optimalisatie geïntroduceerd die dit probleem probeert op te lossen. Als een optellings- of aftrekkingsbewerking resulteert in een waarde op of zeer dicht bij nul, compenseert Excel 97 en hoger elke fout die is opgetreden als gevolg van het converteren van een operande naar en van binair. In het bovenstaande voorbeeld, wanneer uitgevoerd in Excel 97 en hoger wordt 0 of 0,000000000000000E+00 later correct weergegeven in wetenschappelijke notatie.

Zie de volgende wereldwijde websites voor meer informatie over zwevende puntnummers en de IEEE 754-specificatie:

• https://www.ieee.org
• https://steve.hollasch.net/cgindex/coding/ieeefloat.html