修法布施得聪明智慧，多分享让生活更美好。上次分享《Excel如何使用内插法查表（纵2横1）》，介绍了表格纵2横1查表处理方法，如果是纵1横1查表方法（经常用到这种），将怎样处理？实例：Excel表格纵横向两值A,B值，查找表格中相应值，如果A、B值在表格纵横向值去区间内，得出的值需线性内插值。假设当A值等于1.8 ，B值等于1.2，需得出线性内插值。下面通过这实例分享处理方法。工具/原料moreMicrosoft Office Excel 2007Excel VBA处理方法/步骤1/6分步阅读

首先打开Microsoft Office Excel 2007，新建文档并保存文件名《Excel怎样查找表格纵横向两值A、B值相应值 .xlsm》（演示文件，下面代码复制到能运行宏的工作簿都可以）如图。

2/6

然后按下快捷键ALT+F11打开VBE（宏）编辑界面，然后点菜单栏【插入】下拉中列表中点【模块（M）】如图。

3/6

然后插入了一个模块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/6

以上操作动态过程如下：

5/6

回到工作表窗口，输入公式如下：

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/6

如果觉得这篇经验帮到了您，请点击下方的 “投票点赞 或者“收藏”支持我！还有疑问的话可以点击下方的 “我有疑问”，谢谢啦！

注意事项

动态图片要双击成单独大图才能播放全过程。

纵横值查表插入法查表自定义函数查表EXCEL纵横查表