我使用的是 Microsoft Excel for Macintosh 2011，并且有一个电子表格，记录了活动开始和结束所花费的时间，开始和结束时间之间存在一些时间差，这些时间差通过两种不同的路径转换为总小时数。在一种路径中，整个时间差列被一次性求和。在另一种路径中，相邻行的一系列列子集被求和到不同的列中（所有子集都对应一天），该列本身被求和，理论上得到的总和与第一个总和相同。
我正在减去这两个和，如果结果非零，我会使用条件格式将其涂成红色，以提醒用户电子表格中存在错误，并且求和的两种路径/方法不一致。
我的公式一直运行良好，但是，随着我的电子表格增长到大约 1,500 行，我现在看到了舍入误差，其中两个不同的路径对时间求和会得到略有不同的值，这些值相差数小时，0.000000000000909494701773并且单元格因非零而以红色突出显示。
如何避免在计算相同总和时这两条路径之间发生舍入漂移？我发现我可以通过将总和中的一个条目增加 1 秒来暂时解决这个问题，然后差值会再次返回到零，单元格会再次变为绿色。
这是 Excel 的一个已知问题吗？我可以让零比较条件格式允许比较中出现更多偏差吗？我可以强制两条路径计算总和以使其一致，而无需在其中一个时间上添加一秒吗？
答案1
据我所知，您无法避免舍入差异，但是，您可以调整条件格式以接受一些偏差。选择“使用公式确定要格式化的单元格”，然后尝试以下操作：
=IF(ABS(A1)<0.1,TRUE,FALSE)
答案2
实验表明，值 0.50001736 可以加长 1，Excel 仍会将其计算为 1.01 秒，但当您在末尾添加 2 时，它就变成了 2.02 秒。因此，基本上，这意味着 0.500017361 比 Excel 区分 1 秒和 2 秒所需的位数多一位。这是九位小数，因此对最终结果进行四舍五入会去掉多余的内容。
但是，根据 MS，Excel 使用 27 位十进制精度加上一位符号。然而，输入值还是公式结果却有所不同：公式的精度比手动输入更高。呵呵。无论如何，它将存储并显示 15 位精度。27 位意味着错误（除非从单个值累积）将始终是这 15 位中的最后一个。但对于您来说，这将是 1,500 个条目，因此大约是第 11 位或第 12 位。
因此，9 到 11 位数字对您来说很重要。如果您评估方法的未来发展，使其将来能够包含 10^5 个值，而不是今天的 10^3 个值，那么高端数字将减少到 9-10 位数字。
因此，请将每个计算路径的最终结果的舍入设置为小数点后 9 位或 10 位。顺便说一句，以某种方式对每个值进行包装并ROUND()没有多大意义，因为如果它是 3，并且它们的二进制数学使其贡献稍微大一点，那么它将在舍入之后发生，所以无论如何它都会发生。因此，只需将每个路径的最终结果与舍入一起包装即可。
然后将一个结果与另一个结果进行比较，应该可以完整表达小时-分钟-秒，但仍在错误影响结果之前截断这些值。
不过，关于这个话题，还有几点需要注意。首先，似乎可以达到百分之一秒的精度。但这似乎只是幻想，因为不可能精确到一秒，而 1.01 秒似乎是实际中最小的测量单位。其次，重要的是所用值的精确配对，因此顺序也很重要。可以将这 1,500 个值相加得到一个结果，然后将每个值乘以二，将它们全部相加（顺序相同），然后将结果除以二，得到不同的结果。因此，像四舍五入这样的技术是唯一的希望。
如果您使用 VBA 并且始终可以访问互联网，则可以编写一个宏，该宏会转到一个可以为您执行更高精度数学运算的网站，加载与 Excel 中相同的分组中的值集，让网站进行算术运算，然后将结果返回给 Excel。 （如果网站没有提供更有效的方法，VBA 可以将数据输入到任何网站，就像您键入数据一样。）结果可能是完全正确的 15 位数值，这是 Excel 将存储的最大数值，但是由使用 200 位精度的引擎完成的。 或者使用巧妙地评估值的底层引擎，从而主动避免二进制标准带来的此类错误。 一旦完成，它将非常透明并且可能比 Excel 更快，尽管对于 1,500 或 15,000 个值来说这几乎无关紧要。 现在甚至是 100,000 个。
我想你应该明白了吧？四舍五入到 9-10 位数字根本不会损害你的准确性，而且会消除产生的错误，这样它们就不会再影响你了。