☰

如果日期大于且在另一个日期的 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 和其他人必须推测并创建自己的布局，以便组合出合适的公式。
  • 如果不按照答案中指定的方式对工作表中的列进行排序，则除非您对其进行调整，否则对工作表中列的引用将不正确。
  • 由于 MATCH 的功能方式，LargerSheet 上的排序顺序很重要 - 如果您没有按照答案中指定的方式对行进行排序，MATCH 将不会产生准确的结果。
  • @jbmorch 的回答也假定（因为没有提供其他信息）您的数据从第 1 行开始。因此，他们的答案是为这样的电子表格编写的，如果不根据实际布局进行调整，就会产生错误的结果。

还要注意其他一些可能的问题：

  • 您需要确保所有日期/时间条目都实际格式化为日期和时间，否则 Excel 无法进行正确的比较。这可以在单元格属性下的格式选项中进行检查，也可以通过对单元格进行数学运算来检查。（例如：如果 A2 包含1/7/2003，而 B2 是=A2+2，则 B2 的值应解析为1/9/2003）。
  • 您的示例工作表在 Sheet1 中包含的条目很少，但实际上这些条目符合您要从 LargerSheet 返回值的条件。这让故障排除变得有点麻烦。事实上，我找到的唯一匹配项是第 9 行。（Sheet1 中该行的 DAILY, JIM 日期实际上与 LargerSheet 上他的最新日期相差不到两天。）

也就是说，这是您需要的公式。将其放在 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”开头，以
     MATCH($A1,LargerSheet!$A:$A,1) //名称的最后日期行。
     )<$B1, //与 SmallerSheet 日期进行比较
     INDIRECT("LargerSheet!$B"& //单元格以“$B”开头，以
     MATCH($A1,LargerSheet!$A:$A,1) //名称的最后日期行。
     )($B1+2)), //与 SmallerSheet (date-2) 进行比较
     INDIRECT("LargerSheet!$B"& //"然后"返回 LargerSheet 日期，
     匹配（$A1，LargerSheet！$A：$A，1）
     ),$B1)

... 重要的补充是在IF之间和第一个AND( condition1, condition2 )之间。IF(,

相关内容