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所示。
