Обща формула
=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 отрязва десетичната част на експонентата
- Формулата използва степента и предоставените значими цифри, за да разбере точния брой цифри, за да даде КРЪГЛО
- КРЪГЛО закръглява номера, като използва броя на предоставените цифри
