如何使用Excel VBA四舍五入到一定数量的有效数字? [英] How to round to a certain number of significant figures using Excel VBA?

查看:211
本文介绍了如何使用Excel VBA四舍五入到一定数量的有效数字?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

此部分已解决!

所以我在正在做的程序中有很多计算,而且数字变得如此之长,以至于出现溢出错误.这是由于将50位数字除以另一个50位数字而产生的.我需要一种将特定数字四舍五入到一定数量的有效数字的方法.

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.

这里有更多信息.首先,我要四舍五入的所有数字将始终小于1.数字可以小到1E-50.我只关心前10个有效数字.我只需要一种方法就可以将其四舍五入到大约十个无花果,然后再进行下一次计算以防止溢出错误.那样的话,将数字增加到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加到计数器上,然后在遇到零以外的值时退出循环.然后,下一步可以将数字截断到该计数器,再加上我想要的许多有效数字.

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.

例如,对于.000001234567812345678.它将返回5个零.如果我想要十个有效数字,我将做五+十=15.该程序将仅保留小数点后的前15位数字,因此该数字将被截断为.000001234567812.

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.

另一个想法可能是使用日志.然后,新数字的幂将提高为有效数字前的零个数字.

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.

任何帮助将不胜感激!

这部分尚未解决.

我这么快再发表一次文章会感到很不好,所以我要在这里问这个问题.如果20分钟后仍未答复,也许我会再发表一次.为什么这行不通?

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?

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

在每个单元格中,我总是收到错误消息#NAME?这应该将每个数字四舍五入到四个有效数字.

I keep getting an error, #NAME?, in every single cell. This should round each number to four significant digits.

推荐答案

假设您的值在A2中,请尝试以下任一方法:

Assuming your value is in A2, try either of these:

=ROUND(A2, 3-(INT(LOG(A2))+1))

=ROUND(A2,3-LEN(INT(A2)))

来自此处.

这篇关于如何使用Excel VBA四舍五入到一定数量的有效数字?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆