excelperfect
标签:VBA
在分析测量数据时,通常以固定数量的数字显示结果,称为有效数字。数字的数量取决于测量本身的准确性。虽然Excel有几种方法可以对结果进行四舍五入,但没有一种方法可以将四舍五舍五入处理到指定的位数。
下面的程序将选择区域中的所有数字四舍五入到指定的位数:
Sub RoundToDigits()
Dim dDigits As Double
Dim iCount As Integer
Dim iRoundDigits As Integer
Dim rArea As Range
Dim rCell As Range
Dim rRangeToRound As Range
Dim sFormatstring As String
Dim vAnswer As Variant
On Error Resume Next
Set rRangeToRound = Selection
If rRangeToRound Is Nothing Then Exit Sub
vAnswer = InputBox("多少位数字?", "四舍五入函数")
If TypeName(vAnswer) = "Boolean" Then Exit Sub
If vAnswer = "" Then Exit Sub
iRoundDigits = CInt(Application.Max(1, vAnswer))
On Error GoTo 0
For Each rArea In rRangeToRound.Cells
For Each rCell In rArea
If IsNumeric(rCell.Value) And rCell.Value <> "" Then
sFormatstring = "0"
If rCell.Value = 0 Then
dDigits = 3
Else
dDigits = Log(Abs(rCell.Value)) / Log(10)
dDigits = -Int(dDigits) + iRoundDigits - 1
dDigits = Application.Min(Len(Abs(rCell.Value)), dDigits)
End If
If dDigits >= 1 Then
If Int(rCell.Value) = 0 Then
sFormatstring = sFormatstring & "." & String(dDigits - 1, "0")
Else
sFormatstring = sFormatstring & "." & String(dDigits, "0")
End If
ElseIf dDigits < 0 Then
sFormatstring = sFormatstring & "." _
& String(iRoundDigits - 1, "0") & "E+00"
End If
rCell.NumberFormat = sFormatstring
End If
Next rCell
Next rArea
End Sub
注:代码来源于jkp-ads.com,供参考。
欢迎在下面留言,完善本文内容,让更多的人学到更完美的知识。