☰

根据日期范围搜索和插入数据

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 上的至少一个日期更近，这会导致语句始终为真，因为每个 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 列。您要比较的日期在单元格 E2 中。

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

对于大表中的每个单元格，它都会执行一个 if 语句。如果单元格小于 E2，则返回其日期（Excel 将其存储为数字）；如果单元格大于 E2，则返回 0。通过取所有这些 if 语句的最大值，它会返回小于 E2 的最大日期。

相关内容