Excel Power Query数字舍入的隐藏参数及跨工具兼容问题咨询

问题背景 #

在Excel中输入0.925，通过工具栏「减少小数位数」或公式=Round(0.925,2)都会得到0.93，但在Power Query中，使用Number.Round([Amount],2)处理值为0.925的字段时，结果却是0.92。

差异原因 #

1. 浮点数存储特性：0.925在二进制浮点数中无法精确存储，实际存储值略小于0.925。
2. 舍入模式不同：
   • Excel的ROUND函数默认将0.925视为精确值，按四舍五入规则得到0.93。
   • Power Query的Number.Round默认使用银行家舍入（四舍六入五成双），叠加浮点数的微小误差后，最终得到0.92。

解决方法 #

Power BI环境（支持RoundingMode枚举） #

使用Number.Round的第三个参数指定舍入模式，强制实现类似Excel的四舍五入效果：

• RoundingMode.AwayFromZero：远离零舍入，对正数等价于四舍五入
• RoundingMode.Up：向上舍入

对应的M代码示例：

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1NzU01bM0MlWKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table ["RAW Number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RAW Number", type number}}),
    #"默认Round结果" = Table.AddColumn(#"Changed Type", "默认Round", each Number.Round([RAW Number],2)),
    #"指定模式舍入" = Table.AddColumn(#"默认Round结果", "模拟Excel舍入", each Number.Round([RAW Number], 2, RoundingMode.AwayFromZero))
in
    #"指定模式舍入"

Excel环境（不支持RoundingMode枚举） #

由于Excel版Power Query无法识别RoundingMode，可以通过数值修正的方式模拟Excel的舍入逻辑：

each Number.Round([RAW Number] * 100 + 0.0001, 0) / 100

原理：给放大后的数值加一个极小值，抵消浮点数存储的误差，确保0.925被正确舍入为0.93。

内容的提问来源于stack exchange，提问作者Maxcot