在另一個帖子,給出了一個公式,旨在幫助搜尋和插入條目(如果與條目關聯的日期小於另一張工作表上的日期)。公式如下:
=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。