Excel regner forkert? Kommatal og sikre sammenligninger

Excel gemmer tal som binære kommatal med 15 betydende cifre. Et tal som 0,1 kan ikke gemmes helt præcist på den måde, og derfor kan et regnestykke efterlade en rest på fx 0,0000000000000000555. Cellen viser 0, men en sammenligning kan alligevel give FALSK.

Løsningen er at afrunde, før du sammenligner:

fx =AFRUND((0,1+0,2)-0,3;10)=0

Microsoft forklarer baggrunden i artiklen om kommatal (engelsk: floating point) i Excel. Det er ikke en fejl i din formel, og det er ikke særligt for Excel på dansk. Men det er let at blive snydt af, fordi Excel nogle gange skjuler resten og andre gange ikke.

Samme regnestykke, forskellige svar

Alle formlerne regner på 0,1, 0,2 og 0,3:

Resultatet 0 i tredje og fjerde række er ikke et tegn på, at Excel regner præcist. Ifølge Microsoft har Excel siden Excel 97 rettet op på resultater af plus og minus, der ligger meget tæt på nul. I vores test skete det kun, når minus var det sidste, formlen gjorde. Står hele udtrykket i en parentes, eller ganges det med 1, kommer resten frem: 5,55112E-17, altså 0,0000000000000000555.

Det er også grunden til, at =(0,1+0,2)-0,3=0 giver FALSK. Her er sammenligningen det sidste trin, så resten bliver ikke rettet, før den sammenlignes med 0.

Andre tal gør det samme:

Du kan altså ikke se på cellen, om et resultat er præcist. 0,9 ser rigtigt ud, men er det ikke helt.

Når HVIS siger “Mangler”, selvom resten er 0,00

Et typisk sted, det går galt, er en liste over fakturaer og indbetalinger. Resten i kolonne D er beregnet med =A2-B2-C2, og kolonne E og F skal vise, om fakturaen er betalt:

Arket Betalinger. E har =HVIS(D2=0;"Betalt";"Mangler"). F har den samme test direkte på beløbene. G afrunder først.

Række	A	B	C	D	E	F	G
1	Faktura	Indbetaling 1	Indbetaling 2	Rest	HVIS på Rest	HVIS direkte	HVIS med AFRUND
2	150,30	100,10	50,20	0,00	Betalt	Mangler	Betalt
3	30,30	10,10	20,20	0,00	Betalt	Mangler	Betalt
4	0,80	0,70	0,10	0,00	Betalt	Mangler	Betalt
5	1.234,30	1.000,00	234,30	0,00	Betalt	Mangler	Betalt
6	99,90	33,30	66,60	0,00	Betalt	Mangler	Betalt
7	4,35	2,15	2,20	0,00	Betalt	Mangler	Betalt

Formlerne i række 2:

F2 fx =HVIS(A2-B2-C2=0;"Betalt";"Mangler")

G2 fx =HVIS(AFRUND(A2-B2-C2;2)=0;"Betalt";"Mangler")

Kolonne E virker, fordi =A2-B2-C2 står alene i D2, og minus er det sidste trin. Værdien i D2 er præcis 0; Excel har rettet resten. I kolonne F sker regnestykket inde i sammenligningen, og så bliver resten ikke rettet. Det er to formler, der ser ud til at gøre det samme, og kun den ene virker. Brug derfor altid AFRUND som i kolonne G. Så er du ikke afhængig af, hvordan formlen tilfældigvis er skrevet.

Lange summer og løbende saldo

Små rester kan også hobe sig op. Vi lagde 0,1 i 100 rækker og summerede:

En løbende saldo med =B2+A3 kopieret nedad gav det samme: Efter 10 rækker var =B11=1 SAND, men efter 100 rækker var =B101=10 FALSK, og =B101-10 gav -1,95399E-14. Med =AFRUND(B101;2)=10 blev svaret SAND.

Resten er langt under en øre og betyder intet for beløbet. Den betyder kun noget, når du sammenligner, fx med = i en HVIS.

Sikre sammenligninger

Afrund til den præcision, der giver mening for dine data. Til kroner og øre er det 2 decimaler:

• AFRUND på begge sider er det letteste at læse og passer til beløb.
• AFRUND på forskellen er praktisk i en HVIS, som i eksemplet med indbetalinger.
• En tolerance med ABS er nyttig, når du vil sige “tæt nok på”, fx ved målinger med mange decimaler.

Skal du afrunde til 50 øre eller hele kroner, så se guiden om afrunding af kroner og øre.

Lange numre mister cifre efter nummer 15

Grænsen på 15 betydende cifre gælder også hele tal. Skriver du et langt nummer i en celle med formatet Standard, gemmer Excel det som et tal og sætter cifrene efter det 15. til 0. Det bekræfter Microsoft på sin side om store tal, og vi så det samme:

Selv i en formel skete det: Vi skrev =1234567890123456789, og Excel ændrede formlen til =1234567890123450000.

Cifrene kommer ikke igen, når de først er væk. Numre, du ikke skal regne med, som kundenumre, kontonumre, kortnumre og lange id’er fra andre systemer, skal derfor gemmes som tekst fra starten: Formatér kolonnen som Tekst, før du skriver eller indsætter, eller skriv en apostrof foran. Kommer numrene fra en CSV-fil, skal kolonnen sættes til tekst under importen. Se CSV-filer fra banken.

Når tekstnumre alligevel bliver til tal

Også når numrene er gemt som tekst, kan en funktion lave dem om til tal undervejs og miste cifre. I arket Lange tal står tre opdigtede 16-cifrede numre som tekst i F2:F4. De er forskellige i sidste ciffer:

Arket Lange tal. Kolonne F er tekst. G, H og I tæller, hvor mange gange nummeret i samme række forekommer.

Række	F	G	H	I
1	Nummer (tekst)	TÆL.HVIS	TÆL.HVIS med &"*"	SUMPRODUKT
2	9900123456789011	3	1	1
3	9900123456789012	3	1	1
4	9900123456789013	3	1	1

=TÆL.HVIS($F$2:$F$4;F2) (engelsk: COUNTIF) gav 3 for alle tre numre. Hvis du bruger den til at finde dubletter, bliver tre forskellige numre markeret som dubletter. To formler gav det rigtige svar:

I2 fx =SUMPRODUKT(--($F$2:$F$4=F2))

H2 fx =TÆL.HVIS($F$2:$F$4;F2&"*")

Husk, at * betyder “hvad som helst bagefter”. Formlen tæller derfor også numre, der begynder med de samme cifre og er længere. Derfor er SUMPRODUKT det sikreste valg, når numrene kan have forskellig længde.

Det samme sker, hvis du selv laver teksten om til tal: =VÆRDI(A3)=VÆRDI(A4) gav SAND for 9900123456789017 og 9900123456789018, mens =A3=A4 gav FALSK. Mere om tal og tekst finder du i tal gemt som tekst.

“Angiv vist nøjagtighed”: løsningen, der sletter dine decimaler

Excel har en indstilling, der får projektmappen til at regne med de tal, der vises, i stedet for de gemte. Ifølge Microsofts danske supportside hedder afkrydsningsfeltet Angiv vist nøjagtighed og findes under Filer › Indstillinger › Avanceret i afsnittet Ved beregning af denne projektmappe.

Den lyder som en nem løsning, men Microsoft advarer om, at den ændrer de gemte værdier permanent til det, der vises, og at de fulde værdier ikke kan hentes tilbage bagefter. Den gælder hele projektmappen og alle ark.

Vi slog den til i en ny testmappe, som ikke blev gemt, og slog den fra igen:

Tallet i B1 var formateret uden decimaler, så det blev til et helt tal, og formlen i C1, der viste fire decimaler, regnede derefter med 1235. At slå indstillingen fra igen gav ikke de oprindelige tal tilbage.