Formule générique
=ROUND(number,digits-(1+INT(LOG10(ABS(number)))))
Sommaire
Si vous devez arrondir un nombre à un nombre (variable) donné de chiffres ou de chiffres spécifiés, vous pouvez le faire avec une formule élégante qui utilise les fonctions ROUND et LOG10.
Dans l'exemple illustré, la formule en D6 est la suivante:
=ROUND(B6,C6-(1+INT(LOG10(ABS(B6)))))
Explication
Cela peut être une formule intimidante si vous n'avez pas de bonnes connaissances en mathématiques, mais travaillons-y étape par étape.
Premièrement, lorsque vous avez une formule comme celle-ci où une fonction (dans ce cas ROUND) s'enroule autour de toutes les autres, il est souvent utile de travailler de l'extérieur vers l'intérieur. Donc, au fond, cette formule arrondit la valeur dans B6 en utilisant le ROUND fonction:
=ROUND(B6,x)
Où x est le nombre de chiffres significatifs requis. La partie délicate de cette formule est de calculer x. Il s'agit d'une variable, car elle changera en fonction du nombre arrondi. x est calculé avec ce bit:
C6-(1+INT(LOG10(ABS(B6))))
Cela semble compliqué, alors regardons d'abord comment la formule doit fonctionner pour les exemples donnés. Rappelez-vous avec ROUND qu'un nombre négatif de chiffres fonctionne sur le côté gauche de la décimale. Ainsi, pour arrondir 1234567 à un nombre croissant de chiffres significatifs, nous aurions:
=ROUND(1234567,-6) = 1000000 // 1 sig. digit =ROUND(1234567,-5) = 1200000 // 2 sig. digits =ROUND(1234567,-4) = 1230000 // 3 sig. digits =ROUND(1234567,-3) = 1235000 // 4 sig. digits
Donc, le principal problème est de savoir comment calculer -6, -5, -4 et ainsi de suite en fonction du nombre que nous arrondissons.
La clé est de comprendre comment ces nombres pourraient être exprimés à l'aide d'exposants, comme en notation scientifique:
=ROUND(1234567,-6) = 1000000 = 1.0*10^6 =ROUND(1234567,-5) = 1200000 = 1.2*10^6 =ROUND(1234567,-4) = 1230000 = 1.23*10^6 =ROUND(1234567,-3) = 1235000 = 1.235*10^6
Notez que l'exposant est 6 dans tous les cas, ce qui est déterminé avec ce bit:
INT(LOG10(ABS(B6)))
Ainsi, le reste de la formule utilise simplement la valeur d'exposant calculée pour déterminer le bon nombre à donner ROUND en fonction du nombre de chiffres significatifs souhaité:
=ROUND(1234567,-6) // 1-(1+6) = -6 =ROUND(1234567,-5) // 2-(1+6) = -5 =ROUND(1234567,-4) // 3-(1+6) = -4 =ROUND(1234567,-3) // 4-(1+6) = -3
Donc, en résumé:
- ABS convertit la valeur en une valeur absolue (positive)
- LOG10 obtient l'exposant, dans ce cas 6 avec une valeur décimale
- INT coupe la partie décimale de l'exposant
- La formule utilise l'exposant et les chiffres significatifs fournis pour déterminer le bon nombre de chiffres à donner ROUND
- ROUND arrondit le nombre en utilisant le nombre de chiffres fournis
