Pontatlan számítási eredmények: amikor a nulla nem nulla

Öcsém mutatott pár napja egy érdekes hibát az Excelben. Összeadott néhány számot, az eredménynek 0-nak kellett volna lennie, e helyett azonban valami ilyesmit kapott: 1,4210854715202E-14

Például az alábbi nagyon egyszerű esetben, ahol egy SZUM formulával számoltuk az összeget:

Mi ez az E betűs furcsaság?

Az E betűs furcsaság a tudományos (Scientific) számformátum: az E betű után álló szám (-14) azt jelenti, hogy 10-nek ezzel a hatványával kell megszorozni az előtte álló számot. Az Excel automatikusan ebbe a formátumba tette a számot, mert a cellára nem volt más formázás beállítva.

Ha átállítod a formázást Számra és 30 tizedes jegyre, ezt fogod látni:

Tehát ez egy nagyon kicsit szám, egy majdnem-nulla érték. De mégsem nulla, pedig látjuk, hogy a fenti 4 szám összege pontosan nulla kellene, hogy legyen.

Miért nem nulla?

Azért, mert a számokat a számítógép kettes számrendszerben (vagyis bináris számként) tárolja, és ami a jól megszokott tizes számrendszerben egyszerű, véges tizedes tört (pl. 0,1) az kettes számrendszerben lehet végtelen szakaszos tört (0,0001100110011100110011…). Ilyen esetben pedig – mivel a számok tárolására szolgáló memória véges – a szám kerekítődik, csonkolódik. Ezzel a sok számjegyből álló bináris tört számmal végzünk aztán műveletet, s előfordulhat, hogy a tört rész megmarad a számítás végére is. Ezt aztán tizes számrendszerbe visszakonvertálva nem 0-t, hanem egy 0-hoz közeli számot kapunk.

Ez tehát egy sajátosság, amit megváltoztatni nem tudunk – azonban vannak egyszerű módszerek, amivel áthidalhatjuk a problémát.

MEGOLDÁSOK

Szám formátum beállítása

A legegyszerűbb eset az, amikor nem kell ezzel a majdnem-nulla számmal tovább számolni: ilyenkor elég, ha beállítod a Szám formátumot, és annyi tizedes jegyet, amennyi számodra fontos. A fenti esetben például egy tizedest:

A beállítást a Kezdőlap (Home)  közepén levő Szám csoportban tudod megadni. A legördülő listában válaszd a Szám-ot (Number), és állítsd be a tizedes jegyeket a kis nyilas-nullás gombokkal:

Kerekítés

A probléma súlyosabb, ha a majdnem-nulla számot fel szeretnéd használni valamire. Tipikus eset, hogy az ilyen számításokat ellenőrzésre használjuk, és be szeretnénk ágyazni egy HA (IF) formulába például:

=HA( B7=0 ; „rendben” ; „hiba” )

=IF( B7=0 , „rendben” , „hiba” )

Ez a formula a fenti esetben „hiba”-t ad vissza, mert az összeg csak majdnem-nulla, nem pontosan nulla. Ahhoz, hogy pontosan 0-t kapjunk, az eredeti összeget kerekíteni kell. Ezt legegyszerűbben a KEREKTÉS (ROUND) formulával tudod megtenni. Második paraméternek kell megadnod, hogy hány tizedes jegyre kerekítesz. Ez mindig legalább annyi legyen, mint ahány tizedes jegyű számokkal dolgozol. [Gábor: Így van ez – a hibaellenőrzésnél megmondjuk, hogy milyen kis hiba az, amit még tolerálunk. Persze általában az ember nem arra gondol, hogy maga az Excel fog hibázni… 😉 ]

=KEREKÍTÉS( SZUM(B2:B5) ; 2)

=ROUND( SZUM(B2:B5) , 2 )

Én itt biztos, ami biztos, két tizedesre kerekítettem.

Így már az ellenőrzés sem mutat hibát:

Mostmár akár feltételes formázást is építhetsz az összegző formulára… de ez majd egy következő cikk témája lesz! 🙂