Excel arvutab „valesti“

Üks hää sõber jutustas mulle, et Excel kipub valesti arvutama – SUM ja SUMIF saavad erineva tulemuse.

Mina muidugi ei uskunud ja asusin kontrollima. Avasin Exceli ja tegin uue dokumendi. Veergu A:A lisasin miljon juhuslikku arvu (funktsioon =RAND()). Ning siis hakkasin neid liitma.

  • Ühte lahtrisse    =SUM(A:A)        // liidame kokku kõik arvud veerus A
  • Teise lahtrisse    =SUMIF(A:A;”<0,5″)    // liidame kokku kõik arvud, mis on väiksemad kui pool
  • Kolmandasse     =SUMIF(A:A;”>=0,5″)    // liidame kokku need, mis on suuremad kui pool (kaasa arvatud)
  • Neljandasse teise ja kolmanda summa    // liidame need kaks poolt ja võrdleme esimesega
  • Viiendasse esimese ja neljanda vahe – ja ongi:


Vahe pole suur aga ikkagi. On kohti (VLOOKUP näiteks), mis otsib täpseid asju

Hakkasin siis asja uurima – veebis soovitatakse kahte viisi – Precision as displayed ja ROUND. Paraku ei aita kumbki. Roundi, mis sa roundid, viga jääb alles. PRecision as displayed aga on saatanast ja teeb su kogu töö katki.

Vaatame seda roundimist – ümmardamist

  

5

=SUMPRODUCT((ROUND(A:A;$E$8)))

  

=SUMPRODUCT((ROUND(A:A;$E$8))*(A:A>0,5))

  

=SUMPRODUCT((ROUND(A:A;$E$8))*(A:A<=0,5))

  

=D10+D11

  

=D9-D12

  

 
 

Kirjutasin lahtrisse E8 5 (kohtade arv) ja kasutades müstilismugavat funktsiooni proovisin ümmardamisega 5 kohta peale koma. Viga on ikka alles

  

5

499887,0305

  

374597,1802

  

125289,8504

  

499887,0305

  

4,36557E-09

  

 
 

Muutes kohtade arvu ümmardades, selgus et viga kaob alles, kui ümmardada täisarvuni ( =ROUND(x;0) )

Natuke selgituseks. Excel EI SAAGI täpselt arvutada, kuna Excelis on kõik arvud UJUKOMA arvud. Veebis on rikkalikult näiteid, kuidas arvutamisel tekivad pisivead. Enamasti on need tähtsusetud, aga meie näites näe ei ole. Erandiks on aga arvud, mis on “täisarvud” (Excel tunneb sellised ära) ja nendega arvutades Excel “hoidub vigadest”. Kuidas ta seda teeb, on keerukam teema

Seega arv, millel on 5 kohta peale koma on umbmäärane, arv, mis täisarv aga täpne. Aga kui korrutaks arvu 100 000ga. Siis tal ju ei ole kohti peale koma? Mõeldud tehtud. Proovisin nii – korrutan oma juhuarvud saja tuhandega, ümmardan täisarvuni, liidan seejärel ja jagan tulemuse saja tuhandega. Peaks ju õnnestuma?

Proovisin:

  

100000

=SUMPRODUCT((ROUND(A:A*$E$15;0)))/$E$15

  

=SUMPRODUCT((ROUND(A:A*$E$15;0)*(A:A<0,5)))/$E$15

  

=SUMPRODUCT((ROUND(A:A*$E$15;0)*(A:A>=0,5)))/$E$15

  

=D17+D18

  

=D16-D19

  

 
 

Ja tulemus

  

100000

499887,03051

  

125289,85036

  

374597,18015

  

499887,03051

  

0

  

 
 

Seega – väikeste arvude asemel tegele suurtega ja väikeselt mõtlemise asemel mõtle suurelt

Panin näidise aadressile http://bit.ly/ExcelArvutabValesti – võid ise järele proovida. NB! Muutsin mõned valemid, et nad toimiks ka veebilehel. NB! Näidises tuleb valem =RAND() dubleerida lahtrini A1000000, muidu viga ei paista.

Henn – 9.9.2014

  

This entry was posted in Excel, Funktsioonid, Office. Bookmark the permalink.

Excel arvutab „valesti“ on saanud ühe vastuse

  1. Kenn ütles:

    Huvitav, et exceli ja R-i (www.r-project.org) arvutused on erinevad. Mõningaid R-i ujukoma’vigu’ ei õnnestu excelis korrata (http://stackoverflow.com/questions/6874867/floating-point-issue-in-r), kuid miljoni ja ka saja miljoni juhusliku arvu täpse liitmisega tuleb R see-eest üsna hästi toime.

    r = runif(1e+8) # `runif` tekitab yhtlase (uniform) jaotusega juhuslikke arve
    sr = sum(r)
    sr1 = sum(r[r>0.5])
    sr2 = sum(r[r<=0.5])
    vahe = sr – (sr1+sr2) # tulemuseks on peaaegu täpselt 0
    vahe == 0 # TRUE
    vahe < 1e-100 # TRUE
    vahe < 1e-323 # TRUE
    vahe < 1e-324 # FALSE (oih?)

Lisa kommentaar

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Muuda )

Twitter picture

You are commenting using your Twitter account. Log Out / Muuda )

Facebook photo

You are commenting using your Facebook account. Log Out / Muuda )

Google+ photo

You are commenting using your Google+ account. Log Out / Muuda )

Connecting to %s