Excel:按日期查找下一个最高/最低值

Excel:按日期查找下一个最高/最低值

示例数据

在此按日期(降序)列出的示例数据中,我希望能够按日期查找该值最后高于或低于当前值的时间。

如果当前月份是 12 月且值为 5,则上次较低值是在 11 月(4),上次较高值是在 9 月(8)。

到目前为止,我使用 MATCH/INDEX/etc 找到的解决方案将返回下一个较高的值作为 August (6),因为它是下一个最接近 5 的较高值。我想按日期顺序搜索列表。

例如,这可以表示“本月的值是自九月以来的最高值”。

答案1

如果有动态数组公式过滤器:

=@FILTER(A3:A7,B3:B7>=B2)

=@FILTER(A3:A7,B3:B7<=B2)

在此处输入图片描述


如果不是,那么我们使用INDEX / AGGREGATE:

=INDEX(A:A,AGGREGATE(15,7,ROW(A3:A7)/(B3:B7>=B2),1))

=INDEX(A:A,AGGREGATE(15,7,ROW(A3:A7)/(B3:B7<=B2),1))

在此处输入图片描述

答案2

这个数组(CSE)公式几乎解决了这个问题:

在此处输入图片描述

  • 单元格 S16 中的公式:

    {=INDEX($P$16:$P$21,MAX(IF(($Q$16:$Q$21<=$Q16),$Q$16:$Q$21,0),0))}
    
  • 在单元格 T16 中:

    {=INDEX($P$16:$P$21,MIN(IF(($Q$16:$Q$21>=$Q16),$Q$16:$Q$21,0),0))}
    

注意:

  • 完成配方Ctrl+Shift+Enter

:编辑:

我想推荐这两步解决方案来找到最接近的最高/最低值。

在此处输入图片描述

  • 单元格 S20 中的数组(CSE)公式:

    {=INDEX($P$16:$P$21,MATCH(MIN(IF($Q$16:$Q$21>$Q16,$Q$16:$Q$21)),$Q$16:$Q$21,0))}
    
  • 单元格 T20 中的数组(CSE)公式:

     {=INDEX($P$16:$P$21,MATCH(MAX(IF($Q$16:$Q$21<$Q16,$Q$16:$Q$21)),$Q$16:$Q$21,0))}
    

注意:

  • 在 S20 和 T20 中完成公式Ctrl+Shift+Enter

  • 您可以根据需要调整公式中的单元格引用。

相关内容