C'è un brutto errore di calcolo che è apparso in Excel. Sembra che il problema vada in profondità nel motore di calcolo di Excel e non sarà facile da risolvere.
Al centro del problema c'è un semplice fatto: Excel memorizza 15 cifre di precisione in una cella. Puoi avere numeri composti da 20 cifre, ma qualsiasi cifra tra l'ultima cifra significativa e la cifra decimale deve essere zero.
Recentemente ho visto due casi in cui il motore di calcolo di Excel restituiva risultati errati. Quando ho approfondito il problema e ho esaminato l'XML sottostante, sono rimasto sorpreso di vedere che Excel memorizzava segretamente 17 cifre nell'XML.
Il problema è che Excel visualizzerà solo 15 cifre. Quindi pensi di avere un numero memorizzato come 0.123456789012345 ma in realtà è memorizzato come 0.12345678901234567.
Non puoi vedere quelle ultime due cifre. E la maggior parte delle funzioni di Excel ignora le ultime due cifre. Se * tutte * le funzioni ignorassero le ultime due cifre, non avremmo problemi. Ma finora, l'ordinamento, il RANK e la FREQUENZA utilizzano tutte e 17 le cifre.
Di seguito è riportato un noto trucco per classificare le celle. Se hai bisogno che ogni grado appaia esattamente una volta, puoi combinare RANK e COUNTIF. Nell'immagine sotto, Claire, Flo, Ivana e Lucy sono legate al 115%. Utilizzando la formula RANK + COUNTIF, dovrebbero essere classificati 5, 6, 7 e 8.
Ma la formula fallisce. Due righe sono classificate come 7. Ciò non accade mai. Quattro formule nella colonna D assicurano che il 115% in B6, B9, B12 e B15 siano gli stessi. La =B6=B15formula segnala che entrambe le celle contengono gli stessi dati.
Mentre cercavo di isolare il problema, guarda solo la funzione RANK. Dovrebbe segnalare un pareggio a 4 vie al 4 ° per le persone con il 115%. Ma in qualche modo, Lucy nella riga 15 è classificata davanti alle altre tre.
Per capirlo, ho inviato una richiesta di aiuto agli altri MVP di Excel. Jan Karel Pieterse ha aperto il file Excel e ha cercato nell'XML. Nell'XML, puoi vedere che memorizzano 17 cifre di precisione. Le quattro celle che sembrano un legame in Excel non sono legate nell'XML. Uno del 115% è memorizzato come 1.1500000000000001 e gli altri sono 1.1499999999999999.
Finora, l'ordinamento, la classifica e la funzione FREQUENZA utilizzano le cifre extra. Perchè questo è un problema? Perché contiamo su RANK e COUNTIF per utilizzare entrambi lo stesso numero di cifre. Con una funzione che utilizza 15 cifre e l'altra che utilizza 17 cifre, hai un problema.
Per ora, la soluzione sembra convertire tutte le tue risposte usando =ROUND(A4,15).
Ogni venerdì, esamino un bug o un altro comportamento sospetto in Excel. Questo errore di calcolo è difficile da rilevare e si qualifica come un grosso pesce.
Excel pensiero del giorno
Ho chiesto ai miei amici di Excel Master per i loro consigli su Excel. Pensiero di oggi su cui riflettere:
"Ogni volta che unisci le cellule uccidi un gattino"
Szilvia Juhasz
