最近遇到了一个关于Excel公式的相关问题,数据如下图所示:
要求如下:
H2输入一个日期,要求I2返回跟【交易时间】最接近的【余额】,并且【交易时间】不能大于输入的日期(【交易时间】<=输入日期)。
比如 输入日期:2023/11/21,应该返回 D6,输入2023/11/13 ,应该返回 D4。
公式思路如下:
1、插入辅助列,格式设为【常规】,计算 【输入日期】-【交易时间】。
2、获取辅助列非负数的最小值,这里使用MINIFS。
在J2单元格输入以下公式:=MINIFS(B:B,B:B,">=0")
3、获取最小值所在的行,获取【余额】所在列(因为【余额】不一定在E列,可能在其他列,但是名称唯一不变)
这里使用match函数。
获取最小值所在的行:MATCH(MINIFS(B2:B7,B2:B7,">=0"),B2:B7,0),返回的是5
获取【余额】所在列:MATCH("余额",B1:G1,0),返回的是4
4、最终使用offset函数获取匹配的【余额】
OFFSET(A1,MATCH(MINIFS(B2:B7,B2:B7,">=0"),B2:B7,0),MATCH("余额",B1:G1,0))
备注:
如果余额固定在E列,可直接使用index+match函数:
INDEX(E2:E7,MATCH(MINIFS(B2:B7,B2:B7,">=0"),B2:B7,0))