☰

如果日期大於且在另一個日期的 n 天內，則對日期範圍建立索引

tag-icon • tag-icon microsoft-excel worksheet-function
如果日期大於且在另一個日期的 n 天內，則對日期範圍建立索引

在一個上一篇文章我問了一個問題，如何找到與特定實體關聯的最新日期，並在 Sheet2 中返回該值，只要它大於 Sheet2 上的關聯日期即可。然而，我意識到，真正的問題不是在滿足條件的情況下返回最近的日期，而是在大於參考日期且在 2 天內（或通常為 n 天）的情況下返回日期。

之前產生的公式如下：

=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.

有沒有辦法在 INDIRECT() 上新增另一個條件，以便它不僅詢問它是，< $B1而且詢問它到日期的距離是<2？

我嘗試了以下公式，但沒有成功：

=IF(AND(ABS(INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1))-$A2)<2,
INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1))>$A2,INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1))>$A2),INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1)),$A2)

可以找到樣本表這裡。

答案1

我必須回溯你的一連串問題來看看問題出在哪裡，但是（只做了很小的修改）@Hannu的公式是非常正確的。您在實現它時遇到的問題是您的電子表格佈局與 @jbmorch 在其中指定的內容不匹配回答你之前的問題。

@jbmorch 的答案中非常明確地指定了佈局，並且很重要，原因如下：

  • 除了偽公式中包含工作表名稱的部分之外，您沒有在問題中指定有關電子表格佈局的更多詳細資訊。因此，@jbmorch 和其他人必須推測並創建自己的佈局，以便組合出適當的公式。
  • 如果不按照答案中指定的方式對工作表中的列進行排序，則對工作表中的列的參考將不正確，除非您對其進行調整。
  • LargerSheet 上的排序順序很重要，因為 MATCH 函數的方式 - 如果您沒有按照答案中指定的方式對行進行排序，MATCH 將不會產生準確的結果。
  • @jbmorch 的答案還假設（因為沒有提供其他資訊）您的資料從第1 行開始。 。

需要注意的其他幾個可能的問題：

  • 您需要確保所有日期/時間條目實際上都格式化為日期和時間，否則 Excel 無法進行正確的比較。這可以在單元格屬性下的格式選項中進行檢查，也可以透過對單元格進行數學運算來檢查。 （例如：如果 A2 包含1/7/2003，且 B2 是=A2+2，則 B2 的值應解析為1/9/2003）。
  • 您的範例工作表在 Sheet1 中包含幾個條目，這些條目實際上符合您要尋找的從 LargerSheet 傳回值的條件。這使得故障排除有些麻煩。事實上，我找到的唯一匹配的是第 9 行。

也就是說，這就是您需要的公式。將其放在 Sheet1 的 C2 中並向下複製：

=IF(AND(INDIRECT("LargerSheet!$B"&MATCH($A2,LargerSheet!$A:$A,1))>B2,INDIRECT("LargerSheet!$B"&MATCH($A2,LargerSheet!$A:$A,1))<B2+2),INDIRECT("LargerSheet!$B"&MATCH($A2,LargerSheet!$A:$A,1)),$B2)

再次，請注意您的工作表的格式確切地如下，否則公式將無法在不調整的情況下運作。

  • 所有日期必須格式化為日期 - 而不是文字或數字。
  • 兩片必須A 欄包含名稱，B 欄包含日期，實際資料從第 2 行開始。
  • 較大的紙張必須按名稱（升序）和日期（升序）排序，並設定排序優先級以該順序。

另一件需要注意的事情是「2 天內」和「48 小時內」之間的區別 - 該公式使用後者。也就是說，如果某個時間位於5/6/2012 03:00:00Sheet1 上，而 LargerSheet 上的對應值是，5/8/2012 03:00:01則 IF 語句的計算結果將為 FALSE，並傳回 Sheet1 中的值，而不是 LargerSheet 中的值。如果您想要匹配“未來兩天內的任何時間”而不是“48 小時內的任何時間”，則需要對公式進行大量修改。

另外，由於 IF 語句使用獨佔大於 ( >) 而不是大於或等於，因此完全匹配的次數將導致其計算結果為 FALSE。如果您希望它對完全匹配評估 TRUE，請替換>為>=。

答案2

注意：我只是查看您的 IF() 並更改條件語句。
如果這對您不起作用 - 它至少可能給出如何編寫/更改它的想法。

=如果(
     AND(INDIRECT("LargerSheet!$B"& //以「$B」開頭並以「$B」結尾的儲存格
     MATCH($A1,LargerSheet!$A:$A,1) //名稱最後日期的行。
     )<$B1, //與 SmallerSheet 日期比較
     INDIRECT("LargerSheet!$B"& //以「$B」開頭並以「$B」結尾的儲存格
     MATCH($A1,LargerSheet!$A:$A,1) //名稱最後日期的行。
     )<($B1+2)), //與 SmallerSheet (date-2) 比較
     INDIRECT("LargerSheet!$B"& //"Then" 傳回 LargerSheet 日期，
     MATCH($A1,更大的表!$A:$A,1)
     ),$B1)

……重要的補充是在IF和第一個AND( condition1, condition2 )之間。IF(,

相關內容