Unngå avrundingsfeil i Formel Resultater

December 25  by Eliza

Nick bemerker at formelen "= 0.28 * 100-INT (0,28 * 100) = 0" returnerer False selv om det er åpenbart sant. Han mener problemet har å gjøre med avrunding og hvordan datamaskinen bruker binære aritmetikk, etc. Nick bruker formelen som en del av en større IF-setning, og han antar det er andre avrundingsfeil i Excel som kan nå ut og bite ham . Han lurer på om det er en enkel måte å unngå formel fallgruver som dette.

Før vi ser på enkle måter å unngå denne typen problemer, er det viktig å forstå hvorfor problemet eksisterer. Som Nick bemerker, det virkelig har å gjøre med binær aritmetikk og det faktum at Excel runder informasjon. Bak kulissene, Excel runder alltid informasjon til 15 sifre. Tenk beregningen at Nick jobber med:

= 0,28 * 100-INT (0,28 * 100) = 0

Når Excel først beregner dette, forrang fulgt av Excel beregner det på denne måten:

= (0.28 * 100-INT (0,28 * 100)) = 0

Legg merke til den ekstra sett med parenteser. Resultatet av alt til venstre for den siste likhetstegnet er 3.55E-15, noe som betyr at du ender opp med denne (longhand) formel:

= 0,00000000000000355 = 0

Dette er åpenbart ikke sant, det er derfor du får verdien False returnert. Det er flere måter å "fikse" denne situasjonen. I dette tilfelle kanskje den enkleste er å bare endre formel for å fjerne behovet for å sammenligne til null:

= 0,28 * 100 = INT (0,28 * 100)

Denne formelen returnerer Sann, som du forventer. Dette virker kanskje ikke for alle dine behov, imidlertid. Så, er en bedre tommelfingerregel for å unngå problemer å aldri stole på Excel avrunding. Du gjør dette ved å implementere din egen eksplisitt avrunding, som vist med disse formlene:

= INT (0,28 * 100) -INT (0,28 * 100) = 0
= ROUND (0,28 * 100,0) -INT (0,28 * 100) = 0
= ROUND (0,28 * 100,5) -ROUND (0,28 * 100,5) = 0

Legg merke til at du, i disse tilfellene, ikke slik at Excel til å utføre beregninger til full presisjon siden det kan føre til at noen uventede resultater når du sammenligner til en bestemt verdi, for eksempel null. I stedet er du tvinge Excel til å runde de verdi alle verdiene du arbeider med til uansett nivå av presisjon du trenger for sammenligning ditt.

En annen tilnærming er å ikke gjøre sammenligningen til en eksakt verdi lik null. I stedet gir mulighet for noen "fudge" -faktor i sammenligning, som gjør det mulig for avrunding problemer. For eksempel kan du bestemme at du bare bryr seg om sammenligningen er nøyaktig ett hundredel av hva enhetene du antar. I så fall kunne den opprinnelige formelen modifiseres på denne måte:

= 0,28 * 100-INT (0,28 * 100) <0,01

Dette returnerer Sann, som man ville forvente.

ExcelTips er din kilde for kostnadseffektiv Microsoft Excel trening. Dette tipset (8145) gjelder for Microsoft Excel 2007, 2010, og 2013. Du kan finne en versjon av dette tipset for den eldre menygrensesnittet av Excel her: Unngå avrundingsfeil i Formel resultater.