Excel中四舍五入到有效数字

fanjy

发布于 2023-12-13 15:38:04

1940

发布于 2023-12-13 15:38:04

文章被收录于专栏：完美Excel

标签：Excel技巧，VBA

如何告诉Excel在不使用指数表示法的情况下四舍五入到指定数的有效数字？可以使用下面的公式：

=ROUND(value,sigfigs-(1+INT(LOG10(ABS(value)))))

其中：

value：想要四舍五入的数字。

sigfigs：要四舍五入到的有效位数。

这个公式的诀窍来自于对科学记数法的理解。带有三个有效数字的数字12783将是1.28E4或1.28*10^4或基数*10^指数。

我们想使用ROUND函数作为开始。但是，需要知道要舍入到的数字的“位置”。记住，ROUND函数在Excel中的工作方式是，将12783舍入到100位意味着使用-2或12800=ROUND(12783，-2)。如果我们想要3个有效数字，我们只需要创建一个公式，根据第一个有效数字或1+指数的位置给出-2。

12783的指数的公式为：

4 =INT(LOG10(ABS(12783)))

即：3 - （1+4） = -2

公式中的ROUND函数也可以使用ROUNDDOWN函数或ROUNDUP函数代替。

下面的自定义函数将数字四舍五入到有效数字：

Function ROUNDSIG(num As Variant, sigs As Variant)
 Dim exponent As Double
 If IsNumeric(num) And IsNumeric(sigs) Then
   If sigs < 1 Then
     ' 返回" #NUM"错误
     ROUNDSIG = CVErr(xlErrNum)
   Else
     If num <> 0 Then
       exponent = Int(Log(Abs(num)) / Log(10#))
     Else
       exponent = 0
     End If
     ROUNDSIG = WorksheetFunction.Round(num, sigs - (1 + exponent))
   End If
 Else
   ' 返回" #N/A "错误
   ROUNDSIG = CVErr(xlErrNA)
 End If
End Function

例如：ROUNDSIG(-0.04589,2) = -0.046

使用上述公式时，小数后面的零不一定能正确显示。尽管该值是正确的，但Excel会自动格式化一个带有5个符号的数字如23.300，显示为23.3（除非显示格式设置为“0.000”）。

下面是一个对有效数字进行四舍五入的VBA自定义函数。函数将值作为字符串返回，因此在其他公式中使用该值时，可以使用VALUE函数将字符串转换为数值。

Function ROUNDSF(num As Variant, sigs As Variant) As String
 Dim exponent As Integer
 Dim decplace As Integer
 Dim fmt_left As String
 Dim fmt_right As String
 Dim numround As Double
 If IsNumeric(num) And IsNumeric(sigs) Then
   If sigs < 1 Then
     ' 返回"  #NUM "错误
     ROUNDSF = CVErr(xlErrNum)
   Else
     numround = WorksheetFunction.Text(num, "." & _
       String(sigs, "0") & "E+000")
     If num = 0 Then
       exponent = 0
     Else
       '当数字 = 10, 100, 1000, 等时需要圆整来修复或截断.
       exponent = Round(Int(Log(Abs(numround)) / Log(10)), 1)
     End If
     decplace = (sigs - (1 + exponent))
     If decplace > 0 Then
       fmt_right = String(decplace, "0")
       fmt_left = "0."
     Else
       fmt_right = ""
       fmt_left = "0"
     End If
     ROUNDSF = WorksheetFunction.Text(numround, _
       fmt_left & fmt_right)
   End If
 Else
   ' 返回"  #N/A "错误
   ROUNDSF = CVErr(xlErrNA)
 End If
End Function

注：本文学习整理自vertex42.com，供有兴趣的朋友研究。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2023-12-12，如有侵权请联系 cloudcommunity@tencent.com 删除

excel