Excel技术问询：用标准函数查询累计金额为负的日期数组及首尾日期

1. 获取累计金额为负的日期列表 #

假设日期存于A2:A100，每日金额存于B2:B100，以下分版本给出公式：

Excel 365/2021（动态数组） #

直接生成完整日期数组：

=FILTER(A2:A100, SCAN(0, B2:B100, LAMBDA(a,b,a+b)) < 0)

• 逻辑：SCAN函数逐行计算累计金额，FILTER筛选出累计值小于0的对应日期。

旧版Excel（无动态数组） #

使用数组公式（输入后按Ctrl+Shift+Enter确认），下拉填充至出现错误值：

=INDEX(A2:A100, SMALL(IF(SCAN(0, B2:B100, LAMBDA(a,b,a+b)) < 0, ROW(A2:A100)-ROW(A2)+1), ROWS($1:1)))

• 逻辑：IF标记所有负累计的行位置，SMALL按顺序提取行号，INDEX返回对应日期。

2. 首次出现负累计的日期 #

=INDEX(A2:A100, MATCH(TRUE, SCAN(0, B2:B100, LAMBDA(a,b,a+b)) < 0, 0))

• 逻辑：MATCH定位第一个累计值小于0的位置，INDEX返回对应日期。旧版Excel需按Ctrl+Shift+Enter输入。

3. 末次出现负累计的日期 #

通用版本（兼容所有Excel） #

数组公式（按Ctrl+Shift+Enter确认）：

=INDEX(A2:A100, MAX(IF(SCAN(0, B2:B100, LAMBDA(a,b,a+b)) < 0, ROW(A2:A100)-ROW(A2)+1)))

• 逻辑：IF提取所有负累计的行号，MAX取最后一个位置，INDEX返回对应日期。

Excel 365简化版 #

=XLOOKUP(TRUE, SCAN(0, B2:B100, LAMBDA(a,b,a+b)) < 0, A2:A100,,0,-1)

• 逻辑：XLOOKUP的-1参数指定从后往前查找，直接返回最后一个符合条件的日期。

内容的提问来源于stack exchange，提问作者Dmitry Leushkin