Excel 365中XLookup精确匹配VBA生成ID返回#N/A问题求解

• 核心触发点是二进制浮点数精度误差：十进制的0.1转换为二进制是无限循环小数，无法被精确存储，你在VBA中反复累加0.1时会产生微小的计算误差，写入单元格后，界面显示的是四舍五入后的数值，但单元格实际存储的是带误差的近似值，和你输入的查找值存在极微小的差异，因此精确匹配判定为不命中。
• 手动进入单元格编辑模式再退出时，Excel会自动将界面显示的字面量重新写入单元格存储，覆盖了原来带误差的数值，因此精确匹配可以正常返回结果。
• 你尝试转文本格式查找失败，是因为ID列的单元格实际存储类型为数值，和文本类型的查找值类型不匹配，自然无法命中。
• 匹配模式设为-1时能返回结果，本质是该模式下允许微小的数值偏差，把带误差的近似值判定为和查找值匹配。

• 方案1：修改VBA写入逻辑，从根源避免浮点误差
  将原有的浮点累加逻辑替换为整数运算后再换算为小数，彻底消除0.1累加带来的精度问题，修改后代码参考：

Dim myID As Variant
Dim myCell As Variant
Dim myRange As Range
Dim idIntPart As Long
Dim idDecPart As Integer

Set myRange = Range(someNamedRange)

myID = GenerateNextID
' 拆分初始ID的整数和小数部分
idIntPart = Int(myID)
idDecPart = Round((myID - idIntPart) * 10, 0)

For Each myCell In myRange
    myCell.Value = idIntPart + idDecPart / 10
    idDecPart = idDecPart + 1
    ' 小数位满10进位
    If idDecPart = 10 Then
        idIntPart = idIntPart + 1
        idDecPart = 0
    End If
Next myCell

• 方案2：批量修正已有数据的存储误差
  如果已经生成了大量ID不想重写，可以选中ID所在整列，点击顶部菜单栏「数据」→「分列」→直接点击「完成」即可，该操作会批量将所有单元格的显示值重新写入存储，等同于手动给每个单元格进编辑模式再退出，操作完成后精确匹配即可正常命中。
• 方案3：公式层面适配，无需修改源数据
  如果不想修改VBA和源数据，可以在XLookup的查找值和查找区域外层套ROUND函数统一保留1位小数，强制对齐精度，公式示例如下：
  =XLOOKUP(ROUND(你的查找值,1), ROUND(你的ID列查找区域,1), 需要返回的列区域, , 0)

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