Det er en stygg beregningsfeil som har dukket opp i Excel. Det ser ut til at problemet går dypt inn i Excel-beregningsmotoren og ikke vil være lett å fikse.
Kjernen i problemet er et enkelt faktum: Excel lagrer 15 sifre med presisjon i en celle. Du kan ha tall som har 20 sifre, men alle sifre mellom det siste sifferet og desimaltallet må være null.
Jeg har nylig sett to tilfeller der Excel-beregningsmotoren returnerte feil resultat. Da jeg gravde meg inn i problemet og så på den underliggende XML, ble jeg overrasket over å se at Excel hemmelig lagret 17 sifre i XML.
Problemet er at Excel bare viser 15 sifre. Så du tror at du har et nummer lagret som 0.123456789012345, men det lagres virkelig som 0.12345678901234567.
Du kan ikke se de to siste sifrene. Og de fleste av Excel-funksjonene ignorerer de to siste sifrene. Hvis * alle * funksjonene ignorerte de to siste sifrene, ville vi ikke ha noe problem. Men så langt bruker sortering, RANK og FREKVENS alle 17 sifrene.
Nedenfor er et kjent triks for rangering av celler. Hvis du trenger at hver rangering skal vises nøyaktig en gang, kan du kombinere RANK og COUNTIF. På bildet nedenfor er Claire, Flo, Ivana og Lucy bundet til 115%. Ved å bruke RANK + COUNTIF-formelen, bør de rangeres 5, 6, 7 og 8.
Men formelen mislykkes. To rader er rangert som 7. Det skjer aldri. Fire formler i kolonne D sørger for at 115% i B6, B9, B12 og B15 er de samme. Det =B6=B15formel rapporterer at begge celler inneholder de samme data.
Da jeg prøvde å isolere problemet, så se bare på RANK-funksjonen. Det skal rapportere en 4-veis uavgjort på fjerde for folket med 115%. Men på en eller annen måte er Lucy på rad 15 rangert foran de andre tre.
For å finne ut av det sendte jeg ut en forespørsel om hjelp til de andre Excel MVP-ene. Jan Karel Pieterse sprengte Excel-filen og så i XML. I XML kan du se at de lagrer 17 sifre med presisjon. De fire cellene som ser ut som et slips i Excel er ikke bundet i XML. En av de 115% er lagret som 1.1500000000000001, og de andre er 1.1499999999999999.
Så langt bruker sortering, rangering og FREKVENS-funksjonen de ekstra sifrene. Hvorfor er det et problem? Fordi vi stoler på at RANG og TELLER hvis begge bruker samme antall sifre. Med en funksjon som bruker 15 sifre og den andre bruker 17 sifre, har du et problem.
For nå ser løsningen ut til å konvertere alle svarene dine ved hjelp av =ROUND(A4,15).
Hver fredag undersøker jeg en feil eller annen fiskeaktig oppførsel i Excel. Denne beregningsfeilen er vanskelig å oppdage og kvalifiserer som en stor fisk.
Excel-tanken om dagen
Jeg har bedt mine Excel Master-venner om deres råd om Excel. Dagens tanke å tenke på:
"Hver gang du slår sammen celler myrder du en kattunge"
Szilvia Juhasz
