☰

根據日期範圍搜尋和插入數據

tag-icon • tag-icon microsoft-excel worksheet-function
根據日期範圍搜尋和插入數據

在另一個帖子，給出了一個公式，旨在幫助搜尋和插入條目（如果與條目關聯的日期小於另一張工作表上的日期）。公式如下：

=IF(

    INDEX([LargerSheet!*Range with Dates*],

       MATCH(

         IF([SmallerSheet!*First Date in Range*]<[LargerSheet!*First Date in Range],
         [SmallerSheet!*Range with Dates]),

       [SmallerSheet!*Range with Dates*],0)

    )>0,1,"")

然而，這並沒有完全達到預期的效果。我想知道是否可能只是我的問題略有不同。本質上我想做的是，對於 SmallerSheet 中的給定條目，檢查與 LargerSheet 中的多個物件關聯的日期，並僅當最近日期 < SmallerSheet 日期時才返回 LargerSheet 中的最新日期。我認為上面的公式現在所做的是檢查 SmallerSheet 日期是否比 LargerSheet 上的至少一個日期更新，這會導致該語句始終為 true，因為每個 SmallerSheet 條目在LargerSheet 的歷史可以追溯到很久以前。這是一個容易解決的問題嗎？

答案1

假設您可以對 LargerSheet 中的資料進行排序，您可以像這樣解決您的問題：

首先在 LargerSheet 上進行自訂排序；首先按名稱 (AZ) 排序，然後按日期（從舊到新）排序。現在，所有相同名稱的條目都被分組在一起，每組中的最後一個條目是該名稱的最近日期。

--A-- --B-- Alice 2003-08-20 Alice 2005-01-01 Alice 2006-05-16 Bob 2001-08-19 Bob 2003-01-01 Bob 2004-05-15 Charlie 2004-08-19 : :

然後，在 SmallerSheet 中每個名稱旁邊的欄位中，使用以下公式（假設與 LargerSheet 中一樣，名稱位於 A 列，日期位於 B 列）。去掉空白和註解。

=IF( INDIRECT("LargerSheet!$B"& //Cell starting with "$B" and ending with MATCH($A1,LargerSheet!$A:$A,1) //row of the last date for the name. )<$B1, //Compare with SmallerSheet date INDIRECT("LargerSheet!$B"& //"Then" return LargerSheet date, MATCH($A1,LargerSheet!$A:$A,1) ),$B1) //"Else" return SmallerSheet date.

如果 LargerSheet 中的最新日期小於 SmallerSheet 中的日期（對於每個名稱），則應傳回 LargerSheet 中的最新日期，否則傳回 SmallerSheet 中的日期。

如果需要在日期選擇上新增更多條件，請嘗試使用「AND」和「OR」邏輯功能，或變更測試本身。例如，公式的以下修改將傳回 LargerSheet 中的最新日期，只要日期在 SmallerSheet 中的日期的 -2 天內，並且只要 LargerSheet 的 C 列中的值大於零。正如您所看到的，該公式開始看起來很笨拙，因此應注意匹配括號並檢查語法。使用多列將公式分成幾個階段可能會有所幫助。

=IF(AND(INDIRECT("LargerSheet!$B"&MATCH($A1,LargerSheet!$A:$A,1))>=($B1-2),INDIRECT("LargerSheet!$B"&MATCH($A1,LargerSheet!$A:$A,1))<$B1,INDIRECT("LargerSheet!$C"&MATCH($A1,LargerSheet!$A:$A,1))>0),INDIRECT("LargerSheet!$B"&MATCH($A1,LargerSheet!$A:$A,1)),$B1)

最後，您可以用另一種方​​式解決此問題：在 LargerSheet 中新增一列，該列在 SmallerSheet 中尋找名稱的單一條目，進行測試，並根據測試傳回 TRUE/FALSE 值。

答案2

我將為您提供一個與您列出的公式不同的公式，因為 MATCH 會傳回第一個函數。

該公式是數組公式，因此使用以下命令輸入CTRL+SHIFT+ENTER。

這假設您的大表位於 A 列。

=MAX(IF($A$1:$A$33<E2,$A$1:$A$33,0))

對於大表中的每個儲存格，它執行一個 if 語句。如果儲存格小於 E2，則傳回其日期（Excel 將其儲存為數字）；如果儲存格大於 E2，則傳回 0。

相關內容