Der er en grim beregningsfejl, der har vist sig i Excel. Det ser ud til, at problemet går dybt ind i Excel-beregningsmotoren og ikke vil være let at rette.
Kernen i problemet er en simpel kendsgerning: Excel gemmer 15 cifre præcision i en celle. Du kan have tal, der har 20 cifre, men alle cifre mellem det sidste signifikante ciffer og decimalen skal være nul.
Jeg har for nylig set to tilfælde, hvor Excels beregningsmotor returnerede de forkerte resultater. Da jeg gravede ind i problemet og kiggede på den underliggende XML, blev jeg overrasket over at se, at Excel hemmeligt lagrede 17 cifre i XML.
Problemet er, at Excel kun viser 15 cifre. Så du tror, at du har et nummer gemt som 0.123456789012345, men det er virkelig gemt som 0.12345678901234567.
Du kan ikke se de sidste to cifre. Og de fleste af Excels funktioner ignorerer de sidste to cifre. Hvis * alle * funktionerne ignorerede de sidste to cifre, ville vi ikke have et problem. Men indtil videre bruger sortering, RANK og FREKVENS alle 17 cifre.
Nedenfor er et velkendt trick til rangordning af celler. Hvis du har brug for, at hver rang vises nøjagtigt en gang, kan du kombinere RANK og COUNTIF. På billedet nedenfor er Claire, Flo, Ivana og Lucy bundet til 115%. Ved hjælp af RANK + COUNTIF-formlen skal de rangeres 5, 6, 7 og 8.
Men formlen mislykkes. To rækker er rangeret som 7. Det sker aldrig. Fire formler i kolonne D sørger for, at 115% i B6, B9, B12 og B15 er de samme. Den =B6=B15formel rapporterer, at begge celler indeholder de samme data.
Da jeg forsøgte at isolere problemet, skal du bare se på RANK-funktionen. Det skal rapportere en 4-vejs uafgjort på 4. for folket med 115%. Men på en eller anden måde er Lucy i række 15 placeret foran de andre tre.
For at finde ud af det sendte jeg en anmodning om hjælp til de andre Excel MVP'er. Jan Karel Pieterse åbnede Excel-filen og kiggede i XML. I XML kan du se, at de gemmer 17 cifre præcision. De fire celler, der ligner et uafgjort i Excel, er ikke bundet i XML. En af de 115% er lagret som 1.1500000000000001, og de andre er 1.1499999999999999.
Indtil videre bruger sortering, rangordning og FREKVENS-funktionen de ekstra cifre. Hvorfor er det et problem? Fordi vi stoler på, at RANK og COUNTIF bruger det samme antal cifre. Med den ene funktion, der bruger 15 cifre, og den anden bruger 17 cifre, har du et problem.
For nu ser løsningen ud til at konvertere alle dine svar ved hjælp af =ROUND(A4,15).
Hver fredag undersøger jeg en fejl eller anden fiskeagtig opførsel i Excel. Denne beregningsfejl er svær at opdage og kvalificerer sig som en stor fisk.
Excel-tanken om dagen
Jeg har bedt mine Excel Master-venner om deres råd om Excel. Dagens tanke at tænke over:
"Hver gang du fusionerer celler, myrder du en killing"
Szilvia Juhasz
