Postoji ružna pogreška u izračunu koja se pojavila u Excelu. Čini se da problem zalazi duboko u Excel izračunski mehanizam i neće ga biti lako riješiti.
U srži problema je jednostavna činjenica: Excel pohranjuje 15 znamenki preciznosti u jednu ćeliju. Možete imati brojeve koji imaju 20 znamenki, ali bilo koja znamenka između posljednje značajne znamenke i decimalnog mjesta mora biti nula.
Nedavno sam vidio dva slučaja kada je Excelov mehanizam za izračunavanje vraćao pogrešne rezultate. Kad sam se pozabavio problemom i pogledao temeljni XML, iznenadio sam se kad sam vidio da Excel potajno pohranjuje 17 znamenki u XML.
Problem je što će Excel prikazati samo 15 znamenki. Dakle, mislite da imate broj pohranjen kao 0,123456789012345, ali stvarno je pohranjen kao 0,12345678901234567.
Ne možete vidjeti te dvije posljednje znamenke. A većina Excel-ovih funkcija ignorira te dvije posljednje znamenke. Da * sve * funkcije ignoriraju posljednje dvije znamenke, ne bismo imali problema. Ali do sada sortiranje, RANK i FREQUENCY koriste svih 17 znamenki.
Ispod je poznati trik za rangiranje stanica. Ako vam je potrebno da se svaki rang pojavi točno jednom, možete kombinirati RANK i COUNTIF. Na donjoj slici Claire, Flo, Ivana i Lucy izjednačene su na 115%. Pomoću formule RANK + COUNTIF trebali bi biti rangirani na 5, 6, 7 i 8.
Ali formula ne uspijeva. Dva reda su rangirana kao 7. To se nikad ne događa. Četiri formule u stupcu D osiguravaju da je 115% u B6, B9, B12 i B15 isto. =B6=B15Formula izvještava da su obje stanice sadrže iste podatke.
Dok sam pokušavao izolirati problem, pogledajte samo funkciju RANK. Trebao bi prijaviti četverosmjerno izjednačenje na četvrtom mjestu za ljude sa 115%. Ali nekako je Lucy u 15. redu rangirana ispred ostale tri.
Da bih to shvatio, poslao sam zahtjev za pomoć ostalim Excel MVP-ima. Jan Karel Pieterse otvorio je Excel datoteku i pogledao XML. U XML-u možete vidjeti da pohranjuju 17 znamenki preciznosti. Četiri stanice koje u Excelu izgledaju poput kravate nisu povezane u XML. Jedan od 115% pohranjen je kao 1.1500000000000001, a drugi su 1.1499999999999999.
Do sada, sortiranje, rangiranje i funkcija FREQUENCY koriste dodatne znamenke. Zašto je to problem? Budući da računamo na RANK i COUNTIF da oboje koriste isti broj znamenki. Imate problem s jednom funkcijom koja koristi 15 znamenki, a druga s 17 znamenki.
Za sada se čini da rješenje pretvara sve vaše odgovore pomoću =ROUND(A4,15).
Svakog petka ispitujem grešku ili neko drugo riblje ponašanje u programu Excel. Ovu je pogrešku u izračunavanju teško otkriti i kvalificira se kao velika riba.
Excel misao dana
Pitao sam svoje prijatelje Excel Master za savjet o Excelu. Današnja misao za razmišljanje:
"Svaki put kad spojite stanice ubijete mače"
Szilvia Juhasz
