Загальна формула
=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))))
Це здається складним, тому давайте спочатку розглянемо, як формула повинна працювати для наведених прикладів. Пам’ятайте з КРУГЛИМ, що від’ємна кількість цифр працює зліва від десяткової коми. Отже, щоб округлити 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 обрізає десяткову частину показника ступеня
- Формула використовує показник ступеня та надані значущі цифри, щоб визначити потрібну кількість цифр, щоб отримати КРУГЛУ
- РУГЛО округлює число, використовуючи кількість наданих цифр
