Există o eroare de calcul urâtă care a apărut în Excel. Se pare că problema merge adânc în motorul de calcul Excel și nu va fi ușor de rezolvat.
La baza problemei se află un fapt simplu: Excel stochează 15 cifre de precizie într-o singură celulă. Puteți avea numere care au 20 de cifre, dar orice cifre între ultima cifră semnificativă și zecimală trebuie să fie zero.
Am văzut recent două cazuri în care motorul de calcul Excel a dat rezultate greșite. Când am aprofundat problema și m-am uitat la XML-ul de bază, am fost surprins să văd că Excel stochează în secret 17 cifre în XML.
Problema este că Excel va afișa doar 15 cifre. Deci, credeți că aveți un număr stocat ca 0.123456789012345, dar este într-adevăr stocat ca 0.12345678901234567.
Nu puteți vedea ultimele două cifre. Și majoritatea funcțiilor Excel ignoră ultimele două cifre. Dacă * toate * funcțiile ar ignora ultimele două cifre, nu am avea nicio problemă. Dar până acum, sortarea, RANK și FRECVENȚA utilizează toate cele 17 cifre.
Mai jos este un truc bine cunoscut pentru clasarea celulelor. Dacă aveți nevoie de fiecare rang pentru a apărea exact o dată, puteți combina RANK și COUNTIF. În imaginea de mai jos, Claire, Flo, Ivana și Lucy sunt legate la 115%. Folosind formula RANK + COUNTIF, acestea ar trebui să fie clasate pe 5, 6, 7 și 8.
Dar formula eșuează. Două rânduri sunt clasate pe 7. Asta nu se întâmplă niciodată. Patru formule din coloana D asigură că 115% în B6, B9, B12 și B15 sunt aceleași. =B6=B15Formula raportează că ambele celule conțin aceleași date.
În timp ce încercam să izolez problema, uitați-vă doar la funcția RANK. Ar trebui să raporteze o egalitate de 4 sensuri pe locul 4 pentru persoanele cu 115%. Dar cumva, Lucy din rândul 15 este clasată înaintea celorlalte trei.
Pentru a-mi da seama, am trimis o cerere de ajutor celorlalți MVP Excel. Jan Karel Pieterse a deschis fisierul Excel și a căutat în XML. În XML, puteți vedea că acestea stochează 17 cifre de precizie. Cele patru celule care arată ca o cravată în Excel nu sunt legate în XML. Unul dintre cei 115% este stocat ca 1.1500000000000001, iar celelalte sunt 1.1499999999999999.
Până în prezent, sortarea, clasarea și funcția FRECVENȚĂ utilizează cifre suplimentare. De ce este asta o problemă? Deoarece ne bazăm pe RANK și COUNTIF pentru a folosi ambele același număr de cifre. Cu o funcție folosind 15 cifre și cealaltă folosind 17 cifre, aveți o problemă.
Deocamdată, soluția pare să transforme toate răspunsurile folosind =ROUND(A4,15).
În fiecare vineri, examinez o eroare sau alt comportament de pește în Excel. Această eroare de calcul este greu de detectat și se califică ca pește mare.
Gândul Excel al zilei
Le-am cerut prietenilor mei Excel Master sfatul lor despre Excel. Gândul de astăzi să medităm:
„De fiecare dată când îmbini celule, ucizi un pisoi”
Szilvia Juhasz
