Excel实战技巧55： 在包含重复值的列表中查找指定数据最后出现的数据

• 2019 年 10 月 8 日
• 筆記

文章详情：excelperfect

本文的题目比较拗口，用一个示例来说明，如下图1所示，是一个记录员工值班日期的表，在安排每天的值班时，需要查看员工最近一次值班的日期，以免值班时间隔得太近。例如，可以查到张无忌最近是2019年9月9日值班，因此下一天的值班就不会安排张无忌了。现在就是要求给出张无忌后，获得他最近值班的日期2019年9月9日，对于其他的员工也是这样。

图1

下面，我们分别使用公式和VBA来解决。

使用INDEX+SUMPRODUCT+MAX+ROW函数

公式如下：

=INDEX($B$2:$B$10,SUMPRODUCT(MAX(ROW($A$2:$A$10)*($D$2=$A$2:$A$10))-1))

公式先比较单元格D2中的值与单元格区域A2:A10中的值，如果相同返回TRUE，不相同则返回FALSE，得到一个由TRUE和FALSE组成的数组，然后与A2:A10所在的行号组成的数组相乘，得到一个由行号和0组成的数组，MAX函数获取这个数组的最大值，也就是与单元格D2中的值相同的数据在A2:A10中的最后一个位置，减去1是因为查找的是B2:B10中的值，是从第2行开始的，得到要查找的值在B2:B10中的位置，然后INDEX函数获取相应的值。之所以使用SUMPRODUCT函数，是因为该函数可以处理数组公式，而无须在公式输入完成后按Ctrl+Shift+Enter组合键。

结果如下图2所示。

图2

使用LOOKUP函数

公式如下：

=LOOKUP(2,1/($A$2:$A$10=$D$2),$B$2:$B$10)

公式中，比较A2:A10与D2中的值，相等返回TRUE，不相等返回FALSE，得到由TRUE和FALSE组成的数组，然后使用1除以这个数组，得到由1和错误值#DIV/0!组成的数组，由于这个数组中找不到2，LOOKUP函数在数组中一直查找，直至最后一个比2小的最大值，也就是数组中的最后一个1，返回B2:B10中对应的值，也就是要查找的数据在列表中最后的值。

结果如下图3所示。

图3

使用VBA自定义函数

在VBE中输入下面的代码：

Function LookupLastItem(LookupValue AsString, _

LookupRange As Range, _

ColNum As Integer)

Dim i As Long

With LookupRange

For i = .Columns(1).Cells.Count To 1 Step -1

If LookupValue = .Cells(i, 1) Then

LookupLastItem = .Cells(i, ColNum)

Exit Function

End If

Next i

End With

End Function

然后，在工作表中像Excel内置函数一样，使用公式：

=LookupLastItem($D$2,$A$2:$B$10,2)

结果如下图4所示。

图4

无论使用上述哪种方法，最终的结果如下图5所示。