Berekeningen met drijvende komma geven onnauwkeurige resultaten in Excel

In dit artikel wordt beschreven hoe Microsoft Excel getallen met drijvende komma's opslaat en berekent. Dit kan invloed hebben op de resultaten van enkele getallen of formules als gevolg van afronden en/of afkappen van gegevens.

Overzicht

Microsoft Excel is voor wat het opslaan en het berekenen van getallen met drijvende komma's aangaat, ontworpen op basis van de IEEE 754-specificatie. 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 op grote schaal aangenomen specificatie waarin wordt beschreven hoe getallen met drijvende komma's in een binaire computer moeten worden opgeslagen. De specificatie is populair omdat deze een methode biedt waarbij getallen met drijvende komma's op een redelijke hoeveelheid ruimte worden opgeslagen en berekeningen relatief snel worden uitgevoerd. De 754-norm wordt gebruikt in de processors met drijvende-komma-eenheden en numerieke gegevens van bijna alle huidige pc-microprocessors die drijvende-kommaberekeningen implementeren, zoals Intel-, Motorola-, Sun- en MIPS-processors.

Bij de opslag van getallen kan elk getal of elke breuk worden weergegeven door een corresponderend binair getal. De breuk 1/10 kan in een systeem met decimale getallen bijvoorbeeld worden weergegeven met een eenvoudig decimaal getal, zoals 0,1. Hetzelfde getal wordt in een binaire indeling echter een herhalende binaire decimaal en kan een oneindig aantal keren worden herhaald. Dit getal kan niet worden weergegeven in een eindige (beperkte) ruimte. Het getal wordt daarom met ongeveer ?2,8E-17 naar beneden afgerond wanneer het wordt opgeslagen.

Er zijn echter enkele beperkingen van de IEEE 754-specificatie die in drie algemene categorieën kunnen worden onderverdeeld:

• maximum/minimumbeperkingen
• precisie
• herhalende binaire getallen

Maximum/minimumbeperkingen

Alle computers hebben een hoogste en laagste getal waarmee kan worden gewerkt. Omdat het aantal geheugenbits waarin het getal wordt opgeslagen, eindig is, betekent dit dat het hoogste of laagste getal dat kan worden opgeslagen, ook eindig is. Voor Excel is 1,79769313486232E+308 het grootste getal dat kan worden opgeslagen, en is 2,2250738585072E-308 het kleinste positieve getal dat kan worden opgeslagen.

Gevallen waarin IEEE 754 wordt gehanteerd

• Negatieve overloop: Negatieve overloop (underflow) treedt op wanneer een getal wordt gegenereerd dat te klein is om te worden weergegeven. In IEEE en Excel is het resultaat in dat geval 0 (met de uitzondering dat IEEE ook het begrip -0 kent en Excel niet).
• Overloop: Overloop treedt op wanneer een getal wordt gegenereerd dat te groot is om te worden weergegeven. Excel gebruikt in dit geval een eigen notatie (#GETAL!).

Gevallen waarin we ons niet houden aan IEEE 754

• 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. Het resultaat is dat u precisie verliest, en hoe kleiner het getal, des te onnauwkeuriger het resultaat. De uiterste getallen binnen dit bereik hebben een precisie van slechts één cijfer.
  Voorbeeld: Een genormaliseerd getal heeft een impliciete voorloop-1. Als de mantisse bijvoorbeeld 0011001 voorstelt, wordt het genormaliseerde getal 10011001 als gevolg van de impliciete voorloop-1. Een gedenormaliseerd getal heeft geen impliciete voorloop-1, dus in ons voorbeeld met 0011001 verandert het gedenormaliseerde getal niet. In dit geval heeft het genormaliseerde getal acht significante cijfers (10011001), terwijl het gedenormaliseerde getal vijf significante cijfers heeft (11001) met niet-significante voorloopnullen.
  
  Gedenormaliseerde getallen zijn in feite een manier om getallen mogelijk te maken die kleiner zijn dan de kleinste getallen die normaliter kunnen worden opgeslagen. Microsoft implementeert dit optioneel gedeelte van de specificatie niet omdat gedenormaliseerde getallen per definitie een variabel aantal significante cijfers hebben. Dit kan tot gevolg hebben dat er significante fouten in berekeningen worden geïntroduceerd.
• Positieve/negatieve oneindige getallen: Oneindige getallen treden op wanneer u door nul deelt. Excel ondersteunt geen oneindige getallen en geeft in dat geval de fout #DEEL/0! weer.
• Geen getal (NaN): NaN wordt gebruikt om ongeldige bewerkingen aan te duiden, zoals oneindig/oneindig, oneindig-oneindig of de vierkantswortel van -1). Dankzij NaN's kan een programma verder worden uitgevoerd bij een ongeldige bewerking. Excel genereert in dat geval onmiddellijk een fout, zoals #GETAL! of #DEEL/0!.

Precision

Een drijvende-kommagetal wordt binair in drie delen binnen een 65-bits bereik opgeslagen: het teken, de exponent en de mantisse. Het teken slaat het teken van het getal op (positief of negatief), de exponent bevat de macht van 2 waartoe het getal wordt gerezen of verlaagd (de maximum-/minimummacht 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 naast elkaar gelegen drijvende-kommagetallen bij elkaar kunnen liggen (met andere woorden: de precisie).

De mantisse en de exponent worden als afzonderlijke componenten opgeslagen. De mate van precisie die mogelijk is, is dus afhankelijk van de grootte van het getal (de mantisse) dat wordt gemanipuleerd. Hoewel Excel getallen kan opslaan van 1,79769313486232E308 tot 2,2250738585072E-308, is dit in Excel echter alleen mogelijk binnen een precisie van 15 cijfers. Deze beperking is een direct gevolg van een strikte naleving van de IEEE 754-specificatie. Het is geen beperking van Excel. Deze mate van precisie komt ook in andere spreadsheetprogramma's voor.

Drijvende-kommagetallen worden weergegeven in de volgende notatie, waarbij exponent de binaire exponent is: Breuk is het genormaliseerde fractionele gedeelte van het getal, dat wil zeggen, genormaliseerd omdat het zodanig is aangepast dat de voorloopbit altijd een 1 is. Op die manier hoeft deze niet te worden opgeslagen, wat weer één bit extra precisie oplevert. Daarom is er een impliciete bit. Dit komt overeen met de wetenschappelijke notatie, waar u de exponent zo manipuleert dat deze één cijfer links van het decimaalteken heeft. Met uitzondering van binaire getallen, kunt u de exponent altijd zodanig manipuleren dat de eerste bit een 1 is, omdat er alleen enen en nullen zijn.

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

Voorbeeld met zeer grote getallen

Voer het volgende in een nieuwe werkmap in: De resulterende waarde in cel C1 is 1,2E+200, dezelfde waarde als in cel A1. Als u de cellen A1 en C1 vergelijkt met de functie ALS, bijvoorbeeld ALS(A1=C1), is het resultaat WAAR. Dit komt door het feit dat de IEEE-specificatie maar 15 significante cijfers precisie opslaat. Excel zou minstens 100 cijfers precisie nodig hebben om de bovenstaande berekening te kunnen opslaan.

Voorbeeld met zeer kleine getallen

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

Precisiefouten corrigeren

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

Methode 1: De functie AFRONDEN

In het volgende voorbeeld worden de hierboven vermelde gegevens gebruikt om met behulp van de functie AFRONDEN een getal te beperken tot vijf cijfers. Op deze manier kunt u het resultaat met een andere waarde vergelijken.

Methode 2: Precisie zoals afgebeeld

In sommige gevallen kunt u voorkomen dat afrondingsfouten in getallen met drijvende komma invloed op uw werk hebben door de optie Precisie zoals afgebeeld te gebruiken. Met deze optie wordt de waarde van elk getal in het werkblad ingesteld op de weergegeven waarde. Ga als volgt te werk om deze optie in te schakelen:

1. In Excel 2003 en oudere versies opent u het menu Extra en klikt u op Opties.
2. Open het tabblad Berekenen en schakel het selectievakje Precisie zoals afgebeeld in.

1. In Excel 2007 klikt u op de knop Microsoft Office, klikt u op Opties voor Excel en vervolgens op de categorie Geavanceerd.
2. Selecteer in de sectie Bij het berekenen van deze werkmap de gewenste werkmap en schakel het selectievakje Precisie instellen zoals weergegeven in.

Als u bijvoorbeeld een getalnotatie met twee decimale posities selecteert en daarna de optie Precisie instellen zoals weergegeven inschakelt, gaat alle precisie buiten twee decimalen verloren wanneer u de werkmap opslaat. Deze optie heeft invloed op de actieve werkmap inclusief alle werkbladen. U kunt deze optie niet ongedaan maken en de verloren gegevens herstellen. Het wordt ten zeerste aanbevolen om de werkmap op te slaan voordat u deze optie inschakelt.

Herhalende binaire getallen en berekeningen met resultaten die nul benaderen

Een ander verwarrend probleem bij de binaire opslag van getallen met drijvende komma, is dat sommige getallen die in het decimale talstelsel eindige, niet-herhalende getallen zijn, na binaire opslag oneindige, herhalende getallen zijn geworden. Het meest voorkomende voorbeeld hiervan is de waarde 0,1 en varianten daarvan. Hoewel deze getallen in het decimale talstelsel perfect kunnen worden weergegeven, wordt hetzelfde getal in binaire notatie het volgende herhalende binaire getal wanneer het in de mantisse wordt opgeslagen: De IEEE 754-specificatie kent geen speciale voorziening voor elk getal. Er wordt zoveel mogelijk in de mantisse opgeslagen en de rest wordt afgekapt. Dit resulteert bij opslag in een fout van ongeveer -2,8E-17 of 0,000000000000000028.

Zelfs veelvoorkomende decimale breuken, zoals de decimale waarde 0,0001, kunnen binair niet exact worden weergegeven. (0,0001 is een herhalende binaire fractie met een decimaalteken van 104 bits). Om dezelfde reden kan de breuk 1/3 decimaal niet exact worden weergegeven (een herhalende 0,33333333333333333333).

Dit verklaart waarom een eenvoudig voorbeeld in Microsoft Visual Basic for Applications als uitvoer 0,999999999999996 geeft. De kleine fout in de binaire weergave van 0,0001 verspreidt zich naar de som.

Voorbeeld van het optellen van een negatief getal

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 op Cellen opmaken. Open het tabblad Getal en klik op Wetenschappelijk onder Categorie. Stel Decimalen in op 15.

In plaats van 0,9 geeft Excel de waarde 0,899999999999999. Aangezien (43,1-43,2) eerst wordt berekend, wordt tijdelijk -0,1 opgeslagen, en zo wordt de fout in de opslag van -0,1 in de berekening geïntroduceerd.

Voorbeeld van een waarde die nul benadert

1. Voer in Excel 95 of een eerdere versie 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 op Cellen opmaken. Open het tabblad Getal en klik op Wetenschappelijk onder Categorie. Stel Decimalen in op 15.

In plaats van 0 geeft Excel 95 de waarde -2,22044604925031E-16 weer.

In Excel 97 is echter een verbetering opgenomen waarmee dit probleem mogelijk kan worden opgelost. Als een optelling of aftrekking een waarde oplevert die nul zeer dicht benadert, wordt een fout die het gevolg is van de conversie van een operand naar en van binair, in Excel 97 en nieuwere versies gecompenseerd. Als het bovenstaande voorbeeld wordt gebruikt in Excel 97 of hoger, wordt de waarde 0 of de waarde 0,000000000000000E+00 in wetenschappelijke notatie correct weergegeven. Klik voor meer informatie op de volgende artikelnummers in de Microsoft Knowledge Base: Voor meer informatie over getallen met drijvende komma en de IEE 754-specificatie raadpleegt u de volgende websites van het World Wide Web Consortium:

Als u meer informatie wilt over het omzeilen van deze fouten, klikt u op het volgende artikelnummer in de Microsoft Knowledge Base: