excel学习库

excel表格_excel函数公式大全_execl从入门到精通

excel日期区间如何返回对应数据?

收到粉丝提问:如图excel根据日期满足条件前两列条件会返回第三列数值,判断条件是不是在B列和C列中返回E列呢?

涉及到条件判断的,第一时间都会想到用IF函数来解决。

我们来捋捋思路,起始日期与截止日期是数据区间,确实可以通过IF函数嵌套公式来实现目标。我们来看一下,公式是怎么写的。

假设非要使用IF函数,公式是这样的:

=IF(AND(G2<DATE(2024,1,11),G2>=DATE(2023,12,30)),3588,IF(AND(G2<DATE(2024,1,21),G2>=DATE(2024,1,11)),3555,IF(AND(G2<DATE(2024,1,30),G2>=DATE(2024,1,21)),3573,IF(AND(G2<DATE(2024,2,11),G2>=DATE(2024,1,30)),3552,IF(AND(G2<DATE(2024,2,21),G2>=DATE(2024,2,11)),3552,IF(AND(G2<DATE(2024,2,28),G2>=DATE(2024,2,21)),3549,IF(AND(G2<DATE(2024,3,11),G2>=DATE(2024,2,28)),3485,IF(AND(G2<DATE(2024,3,21),G2>=DATE(2024,3,11)),3343,IF(AND(G2<DATE(2024,3,30),G2>=DATE(2024,3,21)),3317,IF(AND(G2<DATE(2024,4,11),G2>=DATE(2024,3,30)),3258,""))))))))))

这个函数公式很繁杂,数据量大时并不适用。还容易出现错误,不建议使用这个冗长的函数公式。我们可以使用下面的方法来返回价格。

方法一:INDEX结合MATCH

可以在H2单元格输入下面的公式:

=IFERROR(INDEX($E$2:$E$11,MATCH(1,INDEX(($G2>=$B$2:$B$11)*($G2<=$C$2:$C$11),0),0)),"无价格")

该公式通过MATCH函数查找G2出现日期区间的位置,借助INDEX函数返回$E$2:$E$11中的值(即价格),利用IFERROR返回没找到时的值。

当在 G2 中的日期落在 $B$2:$B$11 范围内的起始日期与 $C$2:$C$11 范围内的截止日期之间时,从价格列($E$2:$E$11)中找到对应的价格并返回。

如果没有找到相应的价格(即 G2 中的日期不在任何给定日期区间内),公式将返回 无价格

方法二:VLOOKUP函数

可以在H2单元格输入下面的公式:

=VLOOKUP(G2,IF({1,0},$B$2:$B$11,$E$2:$E$11),2,1)

该公式通过IF函数创建一个虚拟的数组,其中第一列是起始日期 $B$2:$B$11,第二列是对应的价格 $E$2:$E$11

再使用 VLOOKUP 函数在虚拟数组的第一列中查找 G2 的值。

使用了 1(近似匹配),VLOOKUP 会返回与 G2 最接近的日期对应的价格,从而得到日期区间对应的价格,来达到想要的效果。

方法三:LOOKUP函数

可以在H2单元格输入下面的公式:

=LOOKUP(G2,$B$1:$B$11,$E$1:$E$11)

LOOKUP函数会在 $B$1:$B$11(日期) 范围内查找 G2(指定日期) 的值。如果找到了相等的值,它就直接返回 $E$1:$E$11(价格) 相应位置的值。

如果在 $B$1:$B$11(日期) 范围内没有找到 G2 的值,它会返回 $B$1:$B$11 (日期)中小于 G2 的最大值对应的 $E$1:$E$11 (价格)中的值,也就是最后一个小于 G2 的值所对应的结果。

需要注意的是,使用该函数时,要确保查找数组(即$B$1:$B$11)是升序排序的哦。

方法四:XLOOKUP函数

可以在H2单元格输入下面的公式:

=XLOOKUP(G2,$B$1:$B$11,$E$1:$E$11,"未找到",-1)

XLOOKUP函数会在 $B$1:$B$11 (日期)范围内查找 G2(指定日期) 的值。

如果找到了完全匹配的值,它就返回 $E$1:$E$11(价格) 中相应位置的值。

如果没有找到完全匹配的值,-1 模式会返回小于或等于 G2 的最大值对应的 $E$1:$E$11 中的值。

如果 G2 小于 $B$1:$B$11 中的最小值,或者没有找到匹配的值,则返回 "未找到"。从而得到日期区间对应的价格,来达到想要的效果。

使用以上分享的函数公式我们可以在 Excel 中根据任意给定的日期查找并返回对应的价格。

根据自己的需求和习惯,选择适合自己的方法若还有疑问,欢迎给小兔留言哦~

想了解更多精彩内容,快来关注

发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

«    2024年12月    »
1
2345678
9101112131415
16171819202122
23242526272829
3031
控制面板
您好,欢迎到访网站!
  查看权限
网站分类
搜索
最新留言
    文章归档
      友情链接