VBA中Index-Match函数无法识别表格特定数值的问题求助

问题现象 #

我用VBA写了数值插值代码，通过Index-Match提取表格里的计算因子，大部分情况运行正常，但遇到以下场景会触发Error 1004：

• RoundUp得到的psi_up为±0.3、±0.8时
• RoundUp得到的alpha_up为-0.3、-0.8时
  这些数值明明存在于表格中，但对应的psi_down、alpha_down为上述值时无异常。

相关代码 #

Dim alpha_0 As Double
Dim psi As Double

Dim psi_down As Double
Dim psi_up As Double

Dim alpha_down As Double
Dim alpha_up As Double

Dim xp01 As Double
Dim xp02 As Double
Dim xp1 As Double
Dim xp03 As Double
Dim xp04 As Double
Dim xp2 As Double

Dim C1_0 As Double


    If MyA = 0 And MyB = 0 Then
    psi = 0
    Else
    psi = MyA / MyB
    End If
               

    If My0 >= Abs(MyB) Then
    alpha_0 = MyB / My0
    Else
    alpha_0 = My0 / MyB
    End If
       

    'Parameter to Interpolate 
    
    alpha_down = WorksheetFunction.RoundDown(alpha_0, 1)
    psi_down = WorksheetFunction.RoundDown(psi, 1)
                                
    alpha_up = WorksheetFunction.RoundUp(alpha_0, 1)
    psi_up = WorksheetFunction.RoundUp(psi, 1)
    
                            
    If psi_up = 1 Then
    psi_down = 0.9
    End If

    If psi = 1 Then
    psi_down = 0.9
    End If
                            
    If psi_down = psi_up Then
    psi_up = psi_down + 0.1
    End If
                            
    If alpha_0 = -1 Then
    alpha_down = -0.9
    End If

    If alpha_down = alpha_up Then
    alpha_up = alpha_down - 0.1
    End If
    
    If alpha_0 = -0 Then
    alpha_0 = 0
    End If
                                            
                                            
' My0 = 0



    If My0 = 0 Then
                                            
    xp01 = _
    WorksheetFunction.Index(Sheets("C1,0 & C2,0").Range("I61:T71"), _
    Application.WorksheetFunction.Match(alpha_0, Sheets("C1,0 & C2,0").Range("I61:I71"), 0), _
    Application.WorksheetFunction.Match(psi_down, Sheets("C1,0 & C2,0").Range("I50:T50"), 0))
                                
    xp02 = _
    WorksheetFunction.Index(Sheets("C1,0 & C2,0").Range("I61:T71"), _
    Application.WorksheetFunction.Match(alpha_0, Sheets("C1,0 & C2,0").Range("I61:I71"), 0), _
    Application.WorksheetFunction.Match(psi_up, Sheets("C1,0 & C2,0").Range("I50:T50"), 0))
                                            
                                
    C1_0 = xp01 + (xp02 - xp01) * ((psi - psi_down) / (psi_up - psi_down))
    
    
    
                                                                                     

'If My0 >= abs(MyB)
                                            
    ElseIf My0 >= Abs(MyB) Then
                                                                 
                                
    xp01 = _
    WorksheetFunction.Index(Sheets("C1,0 & C2,0").Range("I50:T61"), _
    Application.WorksheetFunction.Match(alpha_down, Sheets("C1,0 & C2,0").Range("I50:I61"), 0), _
    Application.WorksheetFunction.Match(psi_down, Sheets("C1,0 & C2,0").Range("I50:T50"), 0))
                               
    xp02 = _
    WorksheetFunction.Index(Sheets("C1,0 & C2,0").Range("I50:T61"), _
    Application.WorksheetFunction.Match(alpha_down, Sheets("C1,0 & C2,0").Range("I50:I61"), 0), _
    Application.WorksheetFunction.Match(psi_up, Sheets("C1,0 & C2,0").Range("I50:T50"), 0))
    
                                            
                                
    xp1 = xp01 + (xp02 - xp01) * ((psi - psi_down) / (psi_up - psi_down))
                                            
                                            
    xp03 = _
    WorksheetFunction.Index(Sheets("C1,0 & C2,0").Range("I50:T61"), _
    Application.WorksheetFunction.Match(alpha_up, Sheets("C1,0 & C2,0").Range("I50:I61"), 0), _
    Application.WorksheetFunction.Match(psi_down, Sheets("C1,0 & C2,0").Range("I50:T50"), 0))
                                            
                               
    xp04 = _
    WorksheetFunction.Index(Sheets("C1,0 & C2,0").Range("I50:T61"), _
    Application.WorksheetFunction.Match(alpha_up, Sheets("C1,0 & C2,0").Range("I50:I61"), 0), _
    Application.WorksheetFunction.Match(psi_up, Sheets("C1,0 & C2,0").Range("I50:T50"), 0))
                                            
                                            
    xp2 = xp03 + (xp04 - xp03) * ((psi - psi_down) / (psi_up - psi_down))
                                            
                                            
                                            
    C1_0 = xp1 + (xp2 - xp1) * ((Abs(alpha_0) - Abs(alpha_down)) / ((Abs(alpha_up) - Abs(alpha_down))))
                                
                                
                                
                                            
' abs(MyB) > My0
                                                                                  
                                            
    ElseIf My0 < Abs(MyB) Then

                                            
    xp01 = _
    WorksheetFunction.Index(Sheets("C1,0 & C2,0").Range("I61:T71"), _
    Application.WorksheetFunction.Match(alpha_down, Sheets("C1,0 & C2,0").Range("I61:I71"), 0), _
    Application.WorksheetFunction.Match(psi_down, Sheets("C1,0 & C2,0").Range("I50:T50"), 0))
                                
    xp</think_never_used_51bce0c785ca2f68081bfa7d91973934>02 = _
    WorksheetFunction.Index(Sheets("C1,0 & C2,0").Range("I61:T71"), _
    Application.WorksheetFunction.Match(alpha_down, Sheets("C1,0 & C2,0").Range("I61:I71"), 0), _
    Application.WorksheetFunction.Match(psi_up, Sheets("C1,0 & C2,0").Range("I50:T50"), 0))
                                            
                                
    xp1 = xp01 + (xp02 - xp01) * ((psi - psi_down) / (psi_up - psi_down))
                                            
                                           
    xp03 = _
    WorksheetFunction.Index(Sheets("C1,0 & C2,0").Range("I61:T71"), _
    Application.WorksheetFunction.Match(alpha_up, Sheets("C1,0 & C2,0").Range("I61:I71"), 0), _
    Application.WorksheetFunction.Match(psi_down, Sheets("C1,0 & C2,0").Range("I50:T50"), 0))
                                            
    xp04 = _
    WorksheetFunction.Index(Sheets("C1,0 & C2,0").Range("I61:T71"), _
    Application.WorksheetFunction.Match(alpha_up, Sheets("C1,0 & C2,0").Range("I61:I71"), 0), _
    Application.WorksheetFunction.Match(psi_up, Sheets("C1,0 & C2,0").Range("I50:T50"), 0))
                                            
                                            
    xp2 = xp03 + (xp04 - xp03) * ((psi - psi_down) / (psi_up - psi_down))
                                            
                                            
    C1_0 = xp1 + (xp2 - xp1) * ((Abs(alpha_0) - Abs(alpha_down)) / ((Abs(alpha_up) - Abs(alpha_down))))
                        
    Else
               

End If

表格说明 #

工作表C1,0 & C2,0中包含两个数据矩阵：

• 区域I50:T61：纵向为alpha值（步长0.1，范围-1到1），横向为psi值（步长0.1，范围-1到1），对应一组计算因子
• 区域I61:T71：结构与上述一致，对应另一组计算因子

问题原因 #

核心是浮点数精度误差：VBA中使用RoundUp计算得到的0.3、0.8等数值，实际存储的是类似0.300000000000001或0.799999999999999的近似值；而表格中的数值是精确的0.3、0.8。Match函数默认精确匹配（参数0）时，会因微小的精度差判定为“找不到匹配项”，触发1004错误。

而RoundDown得到的数值没有这个问题，是因为这类数值的二进制浮点数表示更接近精确值，误差未达到影响匹配的程度。

解决方案 #

针对RoundUp后的数值，增加一步精确到1位小数的修正，消除浮点数误差，以下两种方法任选其一：

方法1：用Round二次处理 #

将计算alpha_up和psi_up的代码修改为：

alpha_up = WorksheetFunction.Round(WorksheetFunction.RoundUp(alpha_0, 1), 1)
psi_up = WorksheetFunction.Round(WorksheetFunction.RoundUp(psi, 1), 1)

方法2：用Format强制格式化 #

alpha_up = CDbl(Format(WorksheetFunction.RoundUp(alpha_0, 1), "0.0"))
psi_up = CDbl(Format(WorksheetFunction.RoundUp(psi, 1), "0.0"))

备选方案：使用近似匹配 #

如果表格数据是按步长0.1排序的（符合你的场景），可以将Match函数的第三个参数设为1（升序匹配），允许近似匹配：

' 示例：修改psi_up的匹配语句
Application.WorksheetFunction.Match(psi_up, Sheets("C1,0 & C2,0").Range("I50:T50"), 1)

但注意：近似匹配要求查找区域必须升序排列，否则结果会出错。

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