Une vilaine erreur de calcul est apparue dans Excel. Il semble que le problème touche profondément le moteur de calcul Excel et ne sera pas facile à résoudre.
Au cœur du problème se trouve un fait simple: Excel stocke 15 chiffres de précision dans une cellule. Vous pouvez avoir des nombres de 20 chiffres, mais tous les chiffres entre le dernier chiffre significatif et la décimale doivent être zéro.
J'ai récemment vu deux cas où le moteur de calcul d'Excel renvoyait les mauvais résultats. Quand j'ai creusé le problème et regardé le XML sous-jacent, j'ai été surpris de voir qu'Excel stockait secrètement 17 chiffres dans le XML.
Le problème est qu'Excel n'affichera que 15 chiffres. Vous pensez donc que vous avez un numéro stocké sous la forme 0.123456789012345 mais il est vraiment stocké sous la forme 0.12345678901234567.
Vous ne pouvez pas voir ces deux derniers chiffres. Et la plupart des fonctions d'Excel ignorent ces deux derniers chiffres. Si * toutes * les fonctions ignoraient les deux derniers chiffres, nous n'aurions pas de problème. Mais jusqu'à présent, le tri, le RANG et la FRÉQUENCE utilisent les 17 chiffres.
Vous trouverez ci-dessous une astuce bien connue pour classer les cellules. Si vous avez besoin que chaque rang apparaisse exactement une fois, vous pouvez combiner RANK et COUNTIF. Dans l'image ci-dessous, Claire, Flo, Ivana et Lucy sont à égalité à 115%. En utilisant la formule RANK + COUNTIF, ils doivent être classés 5, 6, 7 et 8.
Mais la formule échoue. Deux lignes sont classées 7. Cela ne se produit jamais. Quatre formules de la colonne D garantissent que les 115% de B6, B9, B12 et B15 sont les mêmes. La =B6=B15formule indique que les deux cellules contiennent les mêmes données.
Pendant que j'essayais d'isoler le problème, ne regardez que la fonction RANK. Il devrait signaler une égalité à 4 points au 4e rang pour les personnes avec 115%. Mais d'une manière ou d'une autre, Lucy dans la rangée 15 est classée devant les trois autres.
Pour le comprendre, j'ai envoyé une demande d'aide aux autres MVP d'Excel. Jan Karel Pieterse a ouvert le fichier Excel et a regardé dans le XML. Dans le XML, vous pouvez voir qu'ils stockent 17 chiffres de précision. Les quatre cellules qui ressemblent à une cravate dans Excel ne sont pas liées dans le XML. L'un des 115% est stocké sous la forme 1.1500000000000001 et les autres sous la forme 1.1499999999999999.
Jusqu'à présent, le tri, le classement et la fonction FREQUENCY utilisent les chiffres supplémentaires. Pourquoi c'est un problème? Parce que nous comptons sur RANK et COUNTIF pour utiliser tous les deux le même nombre de chiffres. Avec une fonction utilisant 15 chiffres et l'autre utilisant 17 chiffres, vous avez un problème.
Pour l'instant, la solution semble être de convertir toutes vos réponses en utilisant =ROUND(A4,15).
Tous les vendredis, j'examine un bug ou un autre comportement louche dans Excel. Cette erreur de calcul est difficile à détecter et qualifie de gros poisson.
Excellente pensée du jour
J'ai demandé à mes amis Excel Master leurs conseils sur Excel. Pensée d'aujourd'hui à méditer:
"Chaque fois que vous fusionnez des cellules, vous assassinez un chaton"
Szilvia Juhasz
