„Excel“ programoje atsirado negraži skaičiavimo klaida. Panašu, kad problema yra giliai į „Excel“ skaičiavimo variklį ir ją nebus lengva išspręsti.
Problemos esmė yra paprastas faktas: „Excel“ vienoje langelyje saugo 15 skaitmenų tikslumo. Galite turėti skaičių, turinčių 20 skaitmenų, tačiau visi skaitmenys tarp paskutinio reikšmingo skaitmens ir dešimtainio kablelio turi būti nulis.
Neseniai mačiau du atvejus, kai „Excel“ skaičiavimo variklis pateikė neteisingus rezultatus. Kai įsigilinau į problemą ir pažvelgiau į pagrindinę XML, nustebau pamatęs, kad „Excel“ XML slaptai saugo 17 skaitmenų.
Problema ta, kad „Excel“ rodys tik 15 skaitmenų. Taigi manote, kad turite numerį, išsaugotą kaip 0.123456789012345, bet jis tikrai saugomas kaip 0.12345678901234567.
Jūs negalite pamatyti tų dviejų paskutinių skaitmenų. Ir dauguma „Excel“ funkcijų nepaiso tų dviejų paskutinių skaitmenų. Jei * visos * funkcijos nepaisytų dviejų paskutinių skaitmenų, neturėtume problemų. Bet kol kas rūšiavimas, RANK ir FREQUENCY naudoja visus 17 skaitmenų.
Žemiau yra gerai žinomas triukas reitinguojant langelius. Jei norite, kad kiekvienas reitingas būtų rodomas tiksliai vieną kartą, galite sujungti RANK ir COUNTIF. Žemiau esančiame paveikslėlyje Claire, Flo, Ivana ir Lucy yra lygios 115%. Naudojant RANK + COUNTIF formulę, jie turėtų būti reitinguojami 5, 6, 7 ir 8.
Bet formulė nepavyksta. Dvi eilutės reitinguojamos kaip 7. Tai niekada neįvyksta. Keturios D stulpelio formulės užtikrina, kad 115% B6, B9, B12 ir B15 yra vienodi. =B6=B15Formulė praneša, kad abu elementai yra tie patys duomenys.
Bandydamas išskirti problemą, pažvelk tik į RANK funkciją. Tai turėtų pranešti apie 4 krypčių lygumą 4-oje vietoje žmonėms, turintiems 115 proc. Bet kažkaip Lucy 15-oje eilėje užima pirmąją vietą prieš kitas tris.
Norėdami tai išsiaiškinti, aš išsiunčiau pagalbos prašymą kitiems „Excel“ MVP. Jan Karel Pieterse atidarė „Excel“ failą ir pažvelgė į XML. XML galite pamatyti, kad jie saugo 17 skaitmenų tikslumą. Keturi langeliai, kurie „Excel“ atrodo lygūs, nėra susieti su XML. Vienas iš 115% saugomas kaip 1.1500000000000001, o kiti yra 1.1499999999999999.
Iki šiol rūšiavimas, reitingavimas ir FREQUENCY funkcija naudoja papildomus skaitmenis. Kodėl tai problema? Nes mes skaičiuojame, kad RANK ir COUNTIF naudoja tą patį skaičių skaičių. Turint problemą, kai viena funkcija naudojama 15 skaitmenų, o kita - 17 skaitmenų.
Kol kas atrodo, kad sprendimas konvertuoja visus jūsų atsakymus naudodamas =ROUND(A4,15).
Kiekvieną penktadienį „Excel“ ištiriu klaidą ar kitokį netinkamą elgesį. Šią skaičiavimo klaidą sunku nustatyti ir ji gali būti laikoma didele žuvimi.
„Excel“ dienos mintis
Aš paprašiau savo „Excel Master“ draugų patarimo apie „Excel“. Šiandienos mintis apmąstyti:
"Kiekvieną kartą, kai sujungiate ląsteles, nužudote kačiuką"
Szilvia Juhasz
