Excel怎样查找表格纵横向两值A、B值相应值
1、首先打开Microsoft Office Excel 2007，新建文档并保存文件名《Excel怎样查找表格纵横向两值A、B值相应值 .xlsm》（演示文件，下面代码复制到能运行宏的工作簿都可以）如图。
2、然后按下快捷键ALT+F11打开VBE（宏）编辑界面，然后点菜单栏【插入】下拉中列表中点【模块（M）】如图。
3、然后插入了一个模块1，在代码框中复制如下代码：
Function YXCRCB(ByVal y As Range, ByVal x As Range, ByVal cb As Range)
'2020-10-5 22:15:22
Dim i As Long, r1 As Long, c1 As Long, k1 As Long, k2 As Long, n1 As Long, n2 As Long, z1, z2, x1, x2
Dim m1, m2, y1, y2, xy1, xy2
r1 = cb.Rows.Count
c1 = cb.Columns.Count
If y.Value < cb.Cells(2, 1).Value Or y.Value > cb.Cells(r1, 1).Value Or x < cb.Cells(1, 2).Value Or x > cb.Cells(1, c1).Value Then
YXCRCB = "查值超出表范围，请检查纵横值！"
Else
k1 = 0
For i = 1 To r1
If y.Value = cb.Cells(i, 1).Value Then
k1 = i
Exit For
End If
Next i
k2 = 0
For i = 2 To c1
If x.Value = cb.Cells(1, i).Value Then
k2 = i
Exit For
End If
Next i
If k1 > 0 And k2 > 0 Then
YXCRCB = cb.Cells(k1, k2).Value
ElseIf k1 = 0 And k2 > 0 Then
n1 = 0
For i = 2 To r1 - 1
If y.Value > cb.Cells(i, 1).Value And y.Value < cb.Cells(i + 1, 1).Value Then
n1 = i
Exit For
End If
Next i
y1 = cb.Cells(n1, 1).Value
y2 = cb.Cells(n1 + 1, 1).Value
m1 = cb.Cells(n1, k2).Value
m2 = cb.Cells(n1 + 1, k2).Value
YXCRCB = (y.Value - y1) * (m2 - m1) / (y2 - y1) + m1
ElseIf k1 > 0 And k2 = 0 Then
n2 = 0
For i = 2 To c1 - 1
If x.Value > cb.Cells(1, i).Value And x.Value < cb.Cells(1, i + 1).Value Then
n2 = i
Exit For
End If
Next i
x1 = cb.Cells(1, n2).Value
x2 = cb.Cells(1, n2 + 1).Value
z1 = cb.Cells(k1, n2).Value
z2 = cb.Cells(k1, n2 + 1).Value
YXCRCB = (x.Value - x1) * (z2 - z1) / (x2 - x1) + z1
Else
n1 = 0
For i = 2 To r1 - 1
If y.Value > cb.Cells(i, 1).Value And y.Value < cb.Cells(i + 1, 1).Value Then
n1 = i
Exit For
End If
Next i
n2 = 0
For i = 2 To c1 - 1
If x.Value > cb.Cells(1, i).Value And x.Value < cb.Cells(1, i + 1).Value Then
n2 = i
Exit For
End If
Next i
y1 = cb.Cells(n1, 1).Value
y2 = cb.Cells(n1 + 1, 1).Value
m1 = cb.Cells(n1, n2).Value
m2 = cb.Cells(n1 + 1, n2).Value
z1 = cb.Cells(n1, n2 + 1).Value
z2 = cb.Cells(n1 + 1, n2 + 1).Value
xy1 = (y.Value - y1) * (m2 - m1) / (y2 - y1) + m1
xy2 = (y.Value - y1) * (z2 - z1) / (y2 - y1) + z1
x1 = cb.Cells(1, n2).Value
x2 = cb.Cells(1, n2 + 1).Value
YXCRCB = (x.Value - x1) * (xy2 - xy1) / (x2 - x1) + xy1
End If
End If
End Function
4、以上操作动态过程如下：
5、回到工作表窗口，输入公式如下：
K4： =YXCRCB(I4,J4,$A$3:$G$15) 结果：3.06
K5： =YXCRCB(I5,J5,$A$3:$G$15) 结果：0.83
K6： =YXCRCB(I6,J6,$A$3:$G$15) 结果：0.86
K7： =YXCRCB(I7,J7,$A$3:$G$15) 结果：1.09
K8： =YXCRCB(I8,J8,$A$3:$G$15) 结果：0.82
K9： =YXCRCB(I9,J9,$A$3:$G$15) 结果：0.6196
K10： =YXCRCB(I10,J10,$A$3:$G$15) 结果：查值超出表范围，请检查纵横值！
K11： =YXCRCB(I11,J11,$A$3:$G$15) 结果：查值超出表范围，请检查纵横值！
K12： =YXCRCB(I12,J12,$A$3:$G$15) 结果：查值超出表范围，请检查纵横值！
K13： =YXCRCB(I13,J13,$A$3:$G$15) 结果：查值超出表范围，请检查纵横值！
6、如果觉得这篇经验帮到了您，请点击下方的 “投票点赞" 或者“收藏”支持我！还有疑问的话可以点击下方的 “我有疑问”，谢谢啦！