vba 如何使用Excel VBA四舍五入到一定数量的有效数字？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11742994/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to round to a certain number of significant figures using Excel VBA?
提问by TheTreeMan
Edit: This part is solved!
编辑：这部分解决了！
So I have a bunch of calculations in a program I'm making, and the numbers are getting so long that I'm getting overflow errors. It's resulting from things like dividing a 50 digit number by another 50 digit number. I need a way to round specific numbers to a certain amount of significant digits.
所以我在我正在制作的程序中进行了大量计算，并且数字变得如此之长，以至于我遇到了溢出错误。它是由一个 50 位数字除以另一个 50 位数字的结果。我需要一种方法将特定数字四舍五入到一定数量的有效数字。
Here's some more information. First of all, all of the numbers I will be rounding will always be less than 1. The numbers can get as small as to the 1E-50. I only care about the first 10 or so significant digits. I just need a way to round it off to around ten sig figs before moving on to the next calculation in order to prevent overflow errors. That, and having a number go out to 50 figures is very useless.
这里有更多信息。首先，我将四舍五入的所有数字始终小于 1。数字可以小到 1E-50。我只关心前 10 个左右的有效数字。在继续进行下一个计算之前，我只需要一种方法将其四舍五入到大约十个无花果，以防止溢出错误。那，并且有一个数字达到 50 位数是非常无用的。
I was thinking of maybe looping through the number, digit by digit? The program could add 1 to a counter when the number is equal to zero, and then break out of the loop once it hits anything other than zero. Then the next step could truncate the number to that counter plus however many significant digits I want.
我在想也许一个数字一个数字地循环？当数字等于 0 时，程序可以将计数器加 1，然后在遇到除零以外的任何值时跳出循环。然后下一步可以将数字截断到该计数器加上我想要的许多有效数字。
So for .000001234567812345678 for example. It would return 5 zeroes. If I wanted ten significant figures, I would do five + ten = 15. The program would then only keep the first 15 digits after the decimal, so the number would be truncated to .000001234567812.
例如，对于 .000001234567812345678。它将返回 5 个零。如果我想要十位有效数字，我会做五 + 十 = 15。然后程序将只保留小数点后的前 15 位数字，因此该数字将被截断为 .000001234567812。
Another idea might be using the log. Then the power the new number would be raised to would be the number of zeroes in front of the significant digits.
另一个想法可能是使用日志。那么新数字的幂将是有效数字前面的零数。
What I don't know is, first of all, how to go through a number digit by digit, and checking if each number is a zero. I also don't know how to truncate a number to a certain amount of digits.
我不知道的是，首先，如何逐位遍历一个数字，并检查每个数字是否为零。我也不知道如何将数字截断为一定数量的数字。
Any help would be greatly appreciated!
任何帮助将不胜感激！
Edit: This part is not solved.
编辑：这部分没有解决。
I would feel bad making another post so soon, so I'm going to ask the question here. If it's not answered in 20 minutes or so, maybe I'll make another post. Why isn't this working?
这么快就发另一篇文章我会觉得不好意思，所以我要在这里问这个问题。如果在 20 分钟左右没有回答，也许我会再发一个帖子。为什么这不起作用？
Sub Rounding()
Tracker = 0
For i = 1 To 10
Tracker = Tracker + 1
Cells(1, Tracker) = Rnd
Cells(1, Tracker) = Application.Evaluate("=Round(Cells(1, Tracker), 4 - (Int(Log(Cells(1,Tracker))) + 1))")
Next
Columns("A:J").EntireColumn.AutoFit
End Sub
I keep getting an error, #NAME?, in every single cell. This should round each number to four significant digits.
我在每个单元格中不断收到错误，#NAME?。这应该将每个数字四舍五入为四位有效数字。
回答by LittleBobbyTables - Au Revtheitroad
回答by Siddharth Rout
Why would you want to loop when there is already a function which gives you what you want?
当已经有一个函数可以提供你想要的东西时，你为什么要循环？
Sub Sample()
Dim someNumber As Double
'~~> .000001234567812345678
someNumber = 1.23456781234568E-06
'~~> This will give you .000001234567812
Debug.Print Round(someNumber, 15)
End Sub
回答by Alan K.
This will not work if 3-(Int(Log(Range("A2"))/log(10#))+1)) is negative (i.e. if there are more significant figures before the decimal point than you wish to round the number to). An alternative is
如果 3-(Int(Log(Range("A2"))/log(10#))+1)) 为负数（即，如果小数点前的有效数字比您希望四舍五入的数字更多），这将不起作用数）。另一种选择是
Public Function round_sigfig(ByVal myval As Double, ByVal fignum As Integer) As Double
factor = 10 ^ (fignum - (Int(Log(myval) / Log(10#))) - 1)
round_sigfig = Round(myval * factor, 0) / factor
End Function
回答by muwo
I found out that in excel worksheet, one can use: =ROUND(A2, 3-(INT(LOG(A2))+1))
我发现在excel工作表中，可以使用：=ROUND(A2, 3-(INT(LOG(A2))+1))
but in VBA, the code becomes: =Round(Range("A2"), 3-(Int(Log(Range("A2"))/log(10#))+1))
但在 VBA 中，代码变为： =Round(Range("A2"), 3-(Int(Log(Range("A2"))/log(10#))+1))
