本函数无需对关键字进行排序操作,就能实现在某个区域查找某个关键字,并根据关键字对应的值自动填充到对应的单元格里面。
典型的案例是:学生成绩册和考勤册不一致的情况(比如学号不对应,退学后学号空缺情况),要根据考勤表学生成绩输入到另一张表中。比如有一张表如下:
表1
另一张表也是这些学生名单,但是顺序乱了,甚至有学号缺失的情况,见下表
表2
按Alt + F11打开 Visual Basic 编辑器 (在 Mac 上, 按FN + Alt + F11), 然后单击 "插入>模块"。 "新模块" 窗口将显示在 Visual Basic 编辑器的右侧。将以下代码复制并粘贴到新模块中 :
Function AutoLookup(ParamArray Rng1())
‘本函数实现了在选定区域查找某个关键字,并根据该关键字对应的值自动填充到对应的单元格里面
'3个range 第一个是当前选择的cell,第二个是关键字查找的范围
'第三个是关键字对应数值,是一个cell
currRow = Rng1(0).Row '学号 第一个行号,
refRowBegin = Rng1(1).Row '学号 第一个行号,
refRowEnd = refRowBegin + Rng1(1).Count - 1 '学号 结束行号,
refColumn = Rng1(1).Column '学号 第一个列号,
refValueColumn = Rng1(2).Column '成绩列
Dim vv As Integer
Set d1 = Rng1(0)
For j = refRowBegin To refRowEnd
Set d2 = Rng1(1).Parent.Cells(j, refColumn)
If StrComp(d1, d2, 1) = 0 Then
AutoLookup = Rng1(1).Parent.Cells(j, refValueColumn).Value
Exit For
End If
Next j
End Function
VBA代码环境如下所示:
在表2中的单元格D2中输入公式:=AutoLookup(A2,Sheet1!A$2:A$6,Sheet1!C2) ,然后用excel自动填充功能便实现要求。
自动填充效果如下: