![Excel 小数精度秘密超过 15 位并破坏 VLOOKUP](https://linux22.com/image/1556915/Excel%20%E5%B0%8F%E6%95%B0%E7%B2%BE%E5%BA%A6%E7%A7%98%E5%AF%86%E8%B6%85%E8%BF%87%2015%20%E4%BD%8D%E5%B9%B6%E7%A0%B4%E5%9D%8F%20VLOOKUP.png)
我正在用这个公式做一些中点计算
A1-MOD(A1+(Constants!$A$1/2),Constants!$A$1)
(其中列中的值为A
小数,中的值为Constants!$A$1
精确整数)
然后在辅助工作表上匹配输入的值,精确到十分位。
VLOOKUP(B1,Data!$A:$B,2,FALSE)
但VLOOKUP
对我的值的处理却不一致。其结果是(为了便于说明,参数的两列中的值是相同的table_array
):
根据关于 Excel 数值精度的问题,它最多支持 15 位数字,因此我提高了小数精度,只是为了看看它在计算什么:
将我的值包装在“Band”列中ROUND(..., 2)
使其按预期工作,但是为什么呢?
PS请注意,我使用的是Excel 2013。
答案1
要意识到并记住的一点是,这EXCEL
并不是在隐藏任何东西。如果隐藏了任何东西,那就是USER
在隐藏。
可以始终格式化以显示 15 位小数(这样可以完全显示任何值,即使小数点左边有位,也意味着末尾会出现一些(否则不必要的)0)。因此,您可以始终显示“12.5000000000000000”,而不是漂亮的“12.5”,并且知道没有隐藏任何内容。
然而,这有点“引出了一个问题”,而且没有人愿意这样做,然后“手动检查”数据集的每个部分。
您可以做的一件事是使用“显示精度”来强制值仅是您格式化显示的值。因此,如果 Excel 计算出的值是“0.1000000000000001”,并且您将格式设置为两位小数,则无论如何,单元格中的最终结果都将是“0.10”。没有什么可以滥用它,因为它就是那样。
但是,进一步使用该值意味着可能出现进一步的不精确,从而影响公式输出,因为虽然输出将只有两位小数(如果格式化的话),但内部计算结果不会。所以:
=2187.53 / 12.96 * 25.25
第一个运算结果不是 168.79,然后除以 25.25,而是(在公式内部)得到 168.790895061728,然后乘以 25.25。前者结果为 4261.97,而后者结果为 4261.95。因此,计算的输入相同,但结果不同。这意味着您的结果可能会显示差异,这取决于您如何设置计算……这不是一个好主意!
此外,这是发生在小数点后两位(本例中为六位精度)的事情,而不是发生在最后 15 位精度的事情!
另一种方法是进行计算填充列,然后将VLOOKUP()
其转换为文本,并格式化为您想要的小数位(例如两位),这样您现在在查找中就有了一个字符串。但将其包装起来以VALUE()
将其转换回查找的数字。(查找非常关心数字与字符串。)
为什么这样做有效?这是一样的吗?不。该列当前有计算出的值,但有些包含不精确值。查找无法匹配它们,因为它们实际上不同。但不精确仅在一次或多次计算后发生。将其转换为文本并在发生不精确之前将其截断会消除不精确。然后将其转换回数字,您将得到一个完全精确的全新数字,而不知道其实际不精确的历史记录。
如果你使用命名范围对其采取相应操作,并使用相对引用,那么它在这里很有用(或者,现在,用来LET()
在公式中进行清理,可以这么说,所以它非常容易),它甚至可以在查找中看起来很好,不会导致别人很难理解。
(很快,您就可以LAMBDA()
根据这个想法做出自己的想法了。)
当然,所有常用的舍入、底面、顶面、模数、截断等方法都可以使用,但您似乎希望避免使用所有这些方法,而不仅仅是ROUND()
。 不能说我怪你!(在某种程度上,这只是一种花哨的截断,我相信上面的后一种方法或TRUNCATE()
完全相同,但确保这一点的更好方法是使用这种方法,而不是TRUNCATE()
……!
要理解如何解决这个问题,关键是要记住不精确性永远不会来自简单值本身。它总是只来自对该数字的算术运算。(甚至不总是这样……将一个数字除以 3,然后乘以 5,结果可能完全正确,但将其除以 5,然后乘以 3,结果可能不正确。或者无论哪种方式都可能是好的,或者无论哪种方式都是错误的。天哪。)因此,获取该值,将其转换为缩短的字符串,然后再转换为值,不精确性就完全消失了。
答案2
该视频对二进制存储和浮点数进行了很好的解释。https://www.youtube.com/watch?v=PZRI1IfStY0&t=1s
此后,如果您需要精确的数字,请接受会出现浮点不准确的情况并对其进行四舍五入。