我正在使用excel,我想显示一定数量的有效数字的值 .
我尝试使用以下等式
=ROUND(value,sigfigs-1-INT(LOG10(ABS(value))))
Value 被我使用的数字所取代,sigfigs被我想要的有效数字所取代 .
这个公式有时会起作用,但有时却没有 .
例如,值18.036将变为18,其中有2位有效数字 . 解决这个问题的方法是更改源格式以保留1个小数位 . 但这可以引入一个更重要的数字 . 例如,如果结果为182,然后小数位将其更改为182.0,那么现在我将有4个sig figs而不是3 .
我如何获得excel为我设置sig figs的数量,所以我不必手动弄清楚?
10 回答
公式(A2包含值和B2 sigfigs)
可以在C2中为您提供您想要的号码 . 但如果最后一位数为零,则不会以通用格式显示 . 然后,您必须应用特定于该组合的数字格式(值,sigfigs),这是通过VBA . 以下应该有效 . 您必须传递三个参数
(val,sigd,trg)
,trg
是要格式化的目标单元格,您已经拥有所需的数字 .如果您不介意使用字符串而不是数字,那么您可以将格式字符串(例如,D2)视为
(这复制了VBA代码)然后使用(例如,E2)
其中单元格C2仍具有上述公式 . 您可以将单元格E2用于可视化目的,如果需要,可以使用C2中的数字进行其他数学运算 .
使用科学记数法,如果你有180000并且你需要4个sigfigs,唯一的方法是输入1.800x10 ^ 5
WARNING: crazy-long excel formula ahead
我也希望与重要人物合作,我无法使用VBA,因为电子表格似乎始终处理所有数字 . 我对接受的答案感兴趣并且它已经接近了但是一旦我的数字<0.1我就得到了 #value! 错误 . 我确定我可以修复它,但我已经走了一条路,只是按下了 .
Problem:
我需要在正面和负面模式下报告可变数量的有效数字,数字从10 ^ -5到10 ^ 5 . 此外,根据客户端(以及purple math),如果提供了 100 的值并且准确到/ - 1并且我们希望以3 sig figs呈现答案应该是'100.'所以我也包括它 .
Solution:
我的解决方案是一个excel公式,它返回正文和负数所需的有效数字的文本值 .
它是 long ,但根据我的测试(概述如下),无论要求的数量和有效数字如何,它似乎都会产生正确的结果 . 我'm sure it can be simplified but that isn'目前在范围内 . 如果有人想建议简化,请给我留言!
Note: 我有一个名为"sigfigs"的命名范围,我的数字从单元格A1开始
Test Results:
到目前为止,我已经针对wikipedia list of examples和我自己的例子进行了测试 . 我还测试了一些早期给我提出问题的值,所有这些值似乎都能产生正确的结果 .
我还测试了一些早期给我提出问题的值,现在所有人似乎都能产生正确的结果 .
3 Sig Figs Test
Notes:
Treating Negative Numbers
为了对待负数,如果小于0,我已经包含一个带负号的串联,并且对所有其他工作使用绝对值 .
Method of construction: 它最初分为excel中的大约6列,执行各个步骤,最后我将所有步骤合并为上面的一个公式 .
这是一个老问题,但我've modified sancho.s' VBA代码,以便它是一个函数有两个参数:1)你想用适当的签名图(
val
)显示数量,以及2)SIG的无花果的数量(sigd
) . 您可以将其保存为excel中的加载项函数,以用作普通函数:它似乎适用于我迄今为止尝试过的所有用例 .
四舍五入到有效数字是上面提到的一件事 . 格式化为特定数字的数字是另一个...我会在这里发布给你那些试图做我现在的事情并在这里结束的人(因为我将来可能再做一次)......
显示四位数的示例:
.
使用主页>样式>条件格式
新规则>仅格式化包含的单元格
细胞 Value >介于> -10> 10>格式编号3小数位
新规则>仅格式化包含的单元格
单元格值>介于> -100> 100>格式编号2小数位
新规则>仅格式化包含的单元格
单元格值>介于> -1000> 1000>格式编号1小数位
新规则>仅格式化包含的单元格
单元格值>不在> -1000> 1000>格式编号0小数位
.
确保这些按顺序排列并检查所有“Stop If True”框 .
我添加到您的公式中,因此它也会自动显示正确的小数位数 . 在下面的公式中,将数字"2"替换为您想要的小数位数,这意味着您需要进行四次替换 . 这是更新的公式:
例如,如果单元格A1的值= 1/3000,即0.000333333 ..,则上述公式写入输出0.00033 .
您可以尝试自定义格式 .
这是一个速成课程:https://support.office.com/en-nz/article/Create-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4?ui=en-US&rs=en-NZ&ad=NZ .
对于三个有效数字,我在自定义类型框中输入:[> 100] ## . 0; [<= 100]#,## 0
你可以试试
value ::您希望舍入的数字 .
sigfigs ::您想要舍入的有效数字的数量 .
下面的公式运行正常 . 有效数字的数量在第一个文本公式中设置 . 0.00s和4ss用于3sf,0.0和3用于2sf,0.0000和6用于5sf等 .
该公式对于E / -999有效,如果超过此数量,则增加最后三个零的数量,并将第二个4更改为零的数量1 .
请注意,显示的值将四舍五入为有效数字,并且仅用于显示/输出 . 如果要进一步计算,请使用A1中的原始值以避免传播次要错误 .
作为一个非常简单的显示措施,无需使用舍入功能,您只需更改数字的格式,并通过在数字后面添加小数点删除3个有效数字 .
即#,### . 会显示成千上万的数字 . #,### ..显示数百万的数字 .
希望这可以帮助