如何使用 Excel 公式查找列表中最後一次出現的項目

如何使用 Excel 公式查找列表中最後一次出現的項目

在本教程中,您將學習如何使用 Excel 公式查找列表中最後一次出現的項目。

最近,我正在製定會議議程。

我在 Excel 中有一個列表,其中有一個人員列表以及他們擔任“會議主席”的日期。

由於列表中有重複(這意味著一個人曾多次擔任會議主席),我還需要知道一個人最後一次擔任“會議主席”是什麼時候。

這是因為我必須確保不會再次分配最近擔任主席的人。

所以我決定使用一些 Excel 函數魔法來完成這項工作。

下面是最終結果,我可以從下拉列表中選擇一個名稱,它為我提供了該名稱在列表中最後一次出現的日期。

在 Excel 公式中查找項目的最後一次出現 - 演示

如果您對 Excel 函數有很好的了解,您就會知道沒有一個 Excel 函數可以做到這一點。在這裡我們讓奇蹟發生。

在本教程中,我將向您展示執行此操作的三種方法。

查找最後一次出現 – 使用 MAX 函數

這是將返回列表中最後一個值的 Excel 公式:

=INDEX($B$2:$B$14,SUMPRODUCT(MAX(行($A$2:$A$14)*($D$3=$A$2:$A$14))-1))

下面是這個公式的工作原理:

  • MAX 函數用於查找最後匹配名稱的行號。例如,如果名稱是 Glen,它將返回 11,因為它位於第 11 行中。由於我們的列表從第二行開始,因此減去了 1。因此,Glen 最後出現的位置在我們的列表中是 10。
  • SUMPRODUCT 用於確保您不必使用 Control + Shift + Enter,因為 SUMPRODUCT 可以處理數組公式。
  • INDEX 函數現在用於查找最後一個匹配名稱的日期。
相關問題  如何過濾包含重複文本字符串(單詞)的單元格

查找最後一次出現 – 使用 LOOKUP 功能

這是完成相同工作的另一個公式:

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

查找列表中的最後一次出現 - 查找公式

下面是這個公式的工作原理:

  • 查找值為 2(你會明白為什麼.. 繼續閱讀)
  • 查找範圍是 1/($A$2:$A$14=$D$3) – 當它找到匹配的名稱時返回 1,否則返回錯誤。所以你最終得到一個數組。例如,查找值為 Glen,數組將為 {#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!}。
  • 第三個參數 ([result_vector]) 是它給出結果的範圍,在這種情況下是日期。

這個公式起作用的原因是 LOOKUP 函數使用了近似匹配技術。這意味著如果它可以找到精確匹配的值,它將返回,但如果它不能,它將掃描整個數組直到最後並返回小於查找值的下一個最大值。

在這種情況下,查找值為 2,在我們的數組中,我們只會得到 1 或錯誤。所以它掃描整個數組並返回最後一個 1 的位置——這是名稱的最後一個匹配值。

查找最後一次出現 – 使用自定義函數 (VBA)

讓我也向您展示另一種方法。

我們可以使用 VBA 創建自定義函數(也稱為用戶定義函數)。

創建自定義函數的好處是易於使用。您不必每次都擔心創建複雜的公式,因為大部分工作都發生在 VBA 後端。

我創建了一個簡單的公式(很像 VLOOKUP 公式)。

要創建自定義函數,您需要在 VB 編輯器中有 VBA 代碼。稍後我會給你代碼和將它放入 VB 編輯器的步驟,但讓我首先向你展示它是如何工作的:

相關問題  如何使用 Excel PROPER 函數

這是將為您提供結果的公式:

=LastItemLookup($D$3,$A$2:$B$14,2)

查找列表中的最後一次出現 - 自定義函數演示

該公式採用三個參數:

  • 查找值(這將是單元格 D3 中的名稱)
  • 查找範圍(這將是具有名稱和日期的範圍 – A2:B14)
  • 列號(這是我們想要結果的列)

創建公式並將代碼放入 VB 編輯器後,您可以像使用任何其他常規 Excel 工作表函數一樣使用它。

這是公式的代碼:

Function LastItemLookup(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
Dim i As Long
For i = LookupRange.Columns(1).Cells.Count To 1 Step -1
If Lookupvalue = LookupRange.Cells(i, 1) Then
LastItemLookup = LookupRange.Cells(i, ColumnNumber)
Exit Function
End If
Next i
End Function

以下是將此代碼放入 VB 編輯器的步驟:

  1. 轉到開發人員選項卡。查找列表中某個項目的最後匹配項
  2. 單擊 Visual Basic 選項。這將在後端打開 VB 編輯器。查找列表中某個項目的最後匹配項 - Visual Basic
  3. 在 VB 編輯器的 Project Explorer 窗格中,右鍵單擊要在其中插入代碼的工作簿的任何對象。如果您沒有看到 Project Explorer,請轉到 View 選項卡並單擊 Project Explorer。
  4. 轉到插入並單擊模塊。這將為您的工作簿插入一個模塊對象。插入模塊以查找最後一個匹配值
  5. 將代碼複製並粘貼到模塊窗口中。
相關問題  購買二手顯示器前的檢查清單

現在該公式將在工作簿的所有工作表中可用。

請注意,您需要將工作簿保存為 .XLSM 格式,因為其中包含宏。此外,如果您希望此公式在您使用的所有工作簿中可用,您可以將其保存到個人宏工​​作簿或從中創建加載項。

哦,您好 👋很高興認識你。

訂閱我們的電子報,定期發送很棒的科技內到您的郵

Post Comment