Tutorial Omgaan met rekenfouten van Excel

  1. inleiding
  2. voorbeelden
  3. oorzaak
  4. gevolg
  5. remedie

1. INLEIDING

Wellicht op het eerste gezicht een vreemde titel van deze tutorial. Maakt Excel dan rekenfouten? Jazeker, Excel maakt rekenfouten en ze worden meestal niet eens aangemerkt als bugs. Volgens de specificatie rekent Excel met een nauwkeurigheid van 15 getallen. Dat zijn het aantal getallen voor en na de komma bij elkaar opgeteld. Dus te verwachten is dat als met getallen gewerkt wordt in de buurt van dit aantal, dat er dan afrondingsverschillen zullen ontstaan. Dat is dus niet vreemd maar wat wél vreemd is dat in sommige gevallen al veel eerder rare afrondingsverschillen ontstaan. In deze workshop komen een aantal voorbeelden aan de orde van deze ‘rekenfouten’ inclusief een stuk uitleg over oorzaak, gevolg en wat eraan gedaan kan worden.

 

2. VOORBEELDEN

Hier volgen er een aantal voorbeelden van deze afrondingsproblemen. Deze voorbeelden zijn getest in de 32-bits Excel-versies 2007, 2010 en 2013 maar komen waarschijnlijk in (vrijwel) alle Excel-versies voor.

In onderstaand Excel-sheet is in A1 het getal -1,23 ingevoerd en in A2 het getal 1,12. Vervolgens worden deze getallen opgeteld. Het resultaat is -0,11. Als daar weer 0,11 wordt opgeteld dan is het resultaat niet exact gelijk aan 0, zoals te zien is in A5.

rekenfouten1_180x150

Een ander vergelijkbaar voorbeeld. Voer in A1 tot en met A3 de respectievelijk de getallen 28,552, 27,399 en 26,246 in. En zet in kolom B de formules zoals die zijn aangegeven in kolom C. Ook in dit geval is het verschil niet exact gelijk aan 0.

rekenfouten2_250x140

Of voer eens in een willekeurige cel het getal 7745,11319561 in. Te zien is dat Excel er dan automatisch van maakt 7745,113195609990. Om dat te kunnen zien moet wel het aantal weergegeven decimalen worden aangepast. Een ander voorbeeld is om in A1 het getal 0,1 in te voeren. Zet vervolgens in A2 de formule =A1+A$1 en kopieer deze formule minimaal 60 rijen naar beneden. Kijk nu in in cel A60 en zet het aantal weer te geven decimalen op minimaal 14. Normaal gesproken zou de uitkomst van de berekening 6 moeten zijn, maar het is in werkelijkheid 5,99999999999999. Nog een paar laatste voorbeelden die niet exact de juiste uitkomst opleveren:

=7,11-7,1

=7,11-7,01

=1*(0,5-0,4-0,1)

=43,1-43.2

 

3. OORZAAK

Deze rekenfouten worden veroorzaakt door afronding, afkappen van decimalen en de wijze van opslag van getallen. Dit is overigens niet een specifiek probleem van Excel, maar dat geldt voor veel, zo niet alle spreadsheetprogramma’s. Met name de wijze van opslag van getallen kan soms rare onverwachte afrondingsverschillen tot gevolg hebben. De oorzaak van deze rekenfouten heeft te maken met de wijze waarop Excel getallen intern binair opslaat. Het komt er in grote lijnen op neer dat getallen achter de komma binair worden opgeslagen als een optelling van breuken 1/2n, waarbij n het positienummer is. De eerste positie achter de komma staat dus voor het getal 1/2, de tweede positie voor 1/4, de derde voor 1/8 enz. In binaire notatie betekent een 0 op een positie dat het betreffende getal niet aanwezig is en een 1 betekent dat dit getal wél aanwezig is. Dus het getal 0,5 wordt binair weergegeven als 0,1, het getal 0,25 als 0,01 en het getal 0,75 als 0,11 (=0,5 + 0,25).

Net zoals in het decimale stelsel bijvoorbeeld een breuk als 1/6 niet exact kan worden weergegeven, omdat de zessen in de uitkomst 0,1666 oneindig herhaald worden, zijn er ook decimale getallen die binair niet exact kunnen worden weergegeven, zoals 1/10. Decimaal kan dit getal exact worden weergegeven met 0,1 maar binair wordt dit weergegeven als 0,0001100110011… Dit is een reeks die niet eindig is. Het komt er dus op neer dat het decimale getal 0,1 niet exact kan worden weergegeven door een optelling van breuken 1/2n. Hierdoor ontstaan kleine verschillen in cijfers ver achter de komma. Normaal gesproken is het moeilijk te voorspellen wanneer zich dit probleem voordoet.

 

4. GEVOLG

Maar wat zijn nu de gevolgen van deze fouten? Tenzij met getallen wordt gewerkt in de buurt van de precisie van Excel (15 karakters), zullen de gevolgen normaal gesproken niet zo groot zijn, want het gaat maar om hele kleine verschillen. Maar waar dit probleem wel parten kan spelen is bij vergelijk- en zoekfuncties zoals VERT.ZOEKEN, ALS en VERGELIJKEN. Dan geven deze functies een foutmelding of een verkeerd resultaat omdat de zoekwaarde niet gevonden kan worden. Er wordt bijvoorbeeld gezocht op het getal 78 en er bestaat een getal 78 en toch wordt een foutmelding gegeven dat de waarde niet gevonden kan kan worden. Dit komt dan omdat er geen exacte match is omdat door geschetste problemen het getal 78 geen 78 blijkt te zijn maar bijvoorbeeld 78,0000000000045.

 

5. REMEDIE

De oplossing van deze fouten is eigenlijk betrekkelijk simpel. Het is gewoon een kwestie van het afronden van het betreffende getal met de functie AFRONDEN(getal;aantal decimalen). Let wel, het gaat hierbij om de functie AFRONDEN en niet om het aantal decimalen dat ingesteld kan worden bij de celeigenschappen. Bij de celeigenschappen wordt namelijk het onderliggende getal niet veranderd, maar alleen de opmaak van het getal. Het gaat hierbij dus om een optische verandering, terwijl bij de functie AFRONDEN het getal wél wordt veranderd.

Voor de volledigheid hierbij nog een andere rigoureuze methode die nog wel eens genoemd wordt om dit te verhelpen. Dit kan door bij de menukeuze Bestand→Opties→Geavanceerd een vinkje te plaatsen bij ‘Precisie zoals weergegeven’ (er zijn Excel-versies waar deze keuze op een andere plek is te vinden). Hierdoor worden alle getallen afgerond zoals weergegeven op het werkblad. Let op: dit kan niet meer ongedaan gemaakt worden. Het advies is om hier zich voorzichtig mee om te gaan, want aan de ene kant is hierdoor misschien het probleem met de binaire rekenfouten verdwenen, maar aan de andere kant ontstaan er weer rekenfouten omdat alle getallen in het werkblad hun nauwkeurigheid verliezen. Advies: deze optie alleen gebruiken als de gevolgen bekend en beheersbaar zijn.

 

Interesse in andere tutorials? Ga dan naar ons tutorials-overzicht of neem een kijkje op onze pagina met tips&trucs.

Reacties op onze tutorials en tips&trucs zijn van harte welkom. Dat kan door hieronder een reactie toe te voegen of door rechtstreeks contact met ons op te nemen.

4 reacties op Omgaan met rekenfouten van Excel

  • Let met afronden op dat het Amerikaanse system van afronden wordt gehandteerd. Dus 5 en hoger wordt naar boven afgerond in plaats van 5 naar beneden. Als het getal op de eerste achter de komma wordt afgerond is dit op te lossen door afronden(getal+0.1;1). Dan heb je het Europese model.

    • Bij normaal afronden wordt een 5 altijd naar boven afgerond (zowel in Amerika als in Europa). Dat volgens jou in het ‘Europese model’ een 5 naar beneden wordt afgerond, lijkt mij onjuist. Er zijn wel andere afrondmethoden. zoals Bankers Rounding, waar de 5 anders wordt behandeld, maar dat is niet het gebruikelijke afrond systeem.

  • in Libre Office wordt wel netjes gewerkt!!!!

    • Het heeft niets met netjes werken te maken, maar met de wijze waarop decimale getallen intern omgezet worden naar binaire getallen. Het gaat daarbij om hele geringe afwijkingen die voor kunnen komen in getallen ver achter de komma. Ik heb het niet getest met Libre Office, maar ik verwacht dat daar dezelfde problemen spelen.

Geef een reactie

Het e-mailadres wordt niet gepubliceerd.