☰

如何讓 VLOOKUP 回傳*最後一個*匹配項？

tag-icon • tag-icon microsoft-excel libreoffice-calc openoffice-calc vlookup lookup
如何讓 VLOOKUP 回傳*最後一個*匹配項？

我習慣使用 VLOOKUP 但這次我遇到了挑戰。我不想要第一個匹配值，但是最後的。如何？ （我正在使用 LibreOffice Calc，但 MS Excel 解決方案應該同樣有用。）

原因是我有兩個包含數千行的文字列，假設一個是交易收款人清單（亞馬遜、Ebay、雇主、雜貨店等），另一個是支出類別清單（工資、稅收、家庭、租金等） 。有些交易每次的支出類別都不相同，我想取得最近使用的一項。請注意，該清單不會按任何一列排序（實際上按日期排序），我不想更改排序順序。

我所擁有的（不包括錯誤處理）是通常的“首次匹配”公式：

=VLOOKUP( 
[payee field] , [payee+category range] , [index of category column] , 
0 )

我見過解決方案像這樣，但我收到#DIV/0!錯誤：

=LOOKUP(2 , 1/( [payee range] = [search value] ) , [category range] )

解可以是任何公式，不一定是VLOOKUP。我也可以交換收款人/類別欄位。請不要更改排序列。

選擇以下解決方案的獎勵積分最頻繁的價值而不是最後！

答案1

您可以使用陣列公式從最後一個符合記錄中取得資料。

=INDEX(IF($A$1:$A$20="c",$B$1:$B$20),MAX(IF($A$1:$A$20="c",ROW($A$1:$A$20))))

Ctrl使用+ Shift+輸入公式Enter。

這與a 的INDEX/結構類似，但使用條件代替。MATCHVLOOKUPMAXMATCH

ROW(...)請注意，這假設您的表格從第 1 行開始。

答案2

（在這裡回答沒有針對排序資料的單獨問題。）

如果數據是排序後，您可以VLOOKUP與range_lookup參數一起使用TRUE（或省略，因為它是預設值），Excel 的官方描述為「搜尋近似匹配」。

換句話說，對於排序資料：

  • 將最後一個參數設定為FALSE返回第一的值，以及
  • 將最後一個參數設定為TRUE返回最後的價值。

這在很大程度上沒有記錄且晦澀難懂，但可以追溯到 VisiCalc (1979)，並且今天至少在 Microsoft Excel、LibreOffice Calc 和 Google Sheets 中仍然存在。這最終是由於LOOKUPVisiCalc 中的初始實現（以及由此而來的VLOOKUP和HLOOKUP），當時沒有第四個參數。該值是透過以下方式找到的二分查找，使用包含左邊界和排他右邊界（一種常見且優雅的實現），這會導致此行為。

從技術上講，這意味著從候選區間 開始搜索[0, n)，其中n是數組的長度，循環不變條件是A[imin] <= key && key < A[imax]（左邊界 <= 目標，右邊界從結束後開始，是> 目標；要驗證，要么檢查之前端點的值，要么檢查之後的結果），然後連續二等分並選擇保留此不變式的一側：通過排除一側將，直到達到具有1 項的區間，[k, k+1)並且然後算法返回k。這不必是完全匹配（！）：它只是下面最接近的匹配。如果出現重複匹配，則會傳回最後的匹配，因為它要求下一個值是更大比鍵（或數組的末尾）。如果您需要重複的內容一些行為，這是合理且易於實施的。

此行為在這篇舊的 Microsoft 知識庫文章（已新增重點）中明確說明：「XL：如何傳回陣列中的第一個或最後一個符合項目」（Q214069）：

您可以使用 LOOKUP() 函數在已排序資料的陣列中搜尋值，並傳回另一個陣列中該位置包含的對應值。如果查找值在陣列中重複，它傳回最後遇到的匹配項。對於 VLOOKUP()、HLOOKUP() 和 LOOKUP() 函數來說，這種行為是正確的。

一些電子表格的官方文件如下；兩者都沒有說明「最後匹配」行為，但 Google Sheets 文件中暗示了這一點：

  • 微軟Excel

    真的假設表中的第一列按數字或字母順序排序，然後將搜尋最接近的值。

  • 谷歌表格:

    如果is_sorted是TRUE或省略，最接近的匹配（小於或等於到搜尋鍵）返回

答案3

如果搜尋陣列中的值是連續的（即您正在尋找最大值，例如最新日期），您甚至不需要使用 INDIRECT 函數。嘗試這個簡單的程式碼：

=MAX(IF($A$1:$A$20="c",$B$1:$B$20,)

再次使用 CTRL + SHIFT + ENTER 輸入公式

答案4

我嘗試了最常見的值。不確定它是否可以在 libreOffice 中工作，但似乎可以在 excel 中工作

=INDEX($B$2:$B$9,MATCH(MAX(--($A$2:$A$9=D2)*COUNTIFS($B$2:$B$9,$B$2:$B$9,$A$2 ) :$A$9,D2)),--($A$2:$A$9=D2)*COUNTIFS($B$2:$B$9,$B$2:$B$9,$A$2:$A$9, D2 ),0))

A 欄是收款人，B 欄位是類別，D2 是您要篩選的收款人。我不確定為什麼它在上面的函數中添加額外的換行符。

我查找最後一個單元格的函數如下：

=間接("B" & MAX(--($A$2:$A$9=D2)*ROW($A$2:$A$9)))

間接允許我指定要傳回的列並直接查找行（因此我不需要減去標題行數。

這兩個函數都需要使用輸入Ctrl+Shift+回車

相關內容