Общая формула
=ROUND(number,digits-(1+INT(LOG10(ABS(number)))))
Резюме
Если вам нужно округлить число до заданного (переменного) количества указанных цифр или цифр, вы можете сделать это с помощью элегантной формулы, которая использует функции ROUND и LOG10.
В показанном примере формула в D6 выглядит следующим образом:
=ROUND(B6,C6-(1+INT(LOG10(ABS(B6)))))
Объяснение
Эта формула может быть устрашающей, если у вас нет хорошего математического опыта, но давайте рассмотрим ее шаг за шагом.
Во-первых, когда у вас есть такая формула, в которой одна функция (в данном случае ROUND) оборачивается вокруг всех остальных, часто бывает полезно работать извне внутрь. Итак, по сути, эта формула округляет значение в B6 с помощью ROUND функция:
=ROUND(B6,x)
Где x - необходимое количество значащих цифр. Сложная часть этой формулы - вычислить x. Это переменная, потому что она будет меняться в зависимости от округляемого числа. x вычисляется с помощью этого бита:
C6-(1+INT(LOG10(ABS(B6))))
Это кажется сложным, поэтому давайте сначала посмотрим, как должна работать формула для данных примеров. Помните, что при использовании ROUND отрицательное количество цифр работает с левой частью десятичной дроби. Итак, чтобы округлить 1234567 до большего числа значащих цифр, мы получим:
=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
Итак, основная проблема заключается в том, как вычислить -6, -5, -4 и так далее в зависимости от числа, которое мы округляем.
Ключ в понимании того, как эти числа могут быть выражены с помощью экспонент, например, в научной записи:
=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
Обратите внимание, что показатель степени равен 6 во всех случаях, что определяется этим битом:
INT(LOG10(ABS(B6)))
Итак, остальная часть формулы просто использует вычисленное значение экспоненты, чтобы вычислить правильное число, чтобы дать ОКРУГЛЕНИЕ в зависимости от желаемого количества значащих цифр:
=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
Итак, в итоге:
- ABS преобразует значение в абсолютное (положительное) значение
- LOG10 получает показатель степени, в данном случае 6 с десятичным значением
- INT обрезает десятичную часть экспоненты
- Формула использует показатель степени и указанные значащие цифры, чтобы вычислить правильное количество цифр, чтобы получить ОКРУГЛ.
- ОКРУГЛ округляет число, используя указанное количество цифр.
