如何用EXCEL FORMULA实现满足正负记录差条件的按日余额求和？

针对你描述的需求——按日累计余额，一旦当日正记录数比负记录数多2条，就停止后续记录的求和，我分两种场景给你解决方案：

方案一：适用于Excel 365/2021及以上版本（动态数组支持） #

这个版本可以用LET、BYROW、SCAN这些函数一次性自动计算所有日期的结果，不需要手动下拉或辅助列。

假设你的数据结构是：

• A列：Day（日期标识，如"01","02"）
• B列：Situation（"POSITIVE"或"NEGATIVE"）
• C列：Result（余额数值）

直接在空白单元格输入以下公式：

=LET(
  days,UNIQUE(A2:A100),
  output,BYROW(days,LAMBDA(d,
    LET(
      sits,FILTER(B2:B100,A2:A100=d),
      results,FILTER(C2:C100,A2:A100=d),
      track,SCAN({"pn":0,"stop":FALSE},sits,
        LAMBDA(acc,s,
          IF(acc["stop"],
            acc,
            LET(
              new_pn,acc["pn"] + IF(s="POSITIVE",1,-1),
              {"pn",new_pn,"stop",new_pn>=2}
            )
          )
        )
      ),
      keep,--(INDEX(track,,2)<2),
      SUM(results*keep)
    )
  )),
  HSTACK(days,output)
)

公式逻辑拆解 #

1. 提取唯一日期：UNIQUE(A2:A100)先把所有不重复的日期挑出来，避免重复计算。
2. 逐日期处理：BYROW(days,LAMBDA(d,...))对每个日期单独处理，相当于逐个“过一遍”每天的记录。
3. 筛选当日数据：sits和results分别筛选出当前日期对应的正负标记和余额数值。
4. 跟踪停止状态：SCAN是核心——它会逐行跟踪两个状态：
   • pn：到当前记录为止，正记录数减去负记录数的差值
   • stop：是否已经触发停止条件（差值≥2）
     只要还没停止，就更新差值；一旦差值≥2，就标记为停止，后续记录不再更新状态。
5. 标记可计入记录：keep把跟踪到的差值转换成1或0——差值<2的记录计入（1），否则不计入（0）。
6. 求和结果：SUM(results*keep)把每个余额乘以对应的标记值，求和后就是当日的最终结果。
7. 合并显示：HSTACK把日期和结果合并成一个整齐的表格，方便查看。

方案二：适用于旧版Excel（无动态数组/LAMBDA支持） #

如果你的Excel版本比较旧，用辅助列+SUMIFS就能解决：

步骤1：添加辅助列标记可计入记录 #

在D列（辅助列）的D2单元格输入以下公式，然后下拉填充到所有行：

=IF(
  $A2<>$A1,
  TRUE,
  (SUM(--($A$2:$A1=$A2)*--($B$2:$B1="POSITIVE")) - SUM(--($A$2:$A1=$A2)*--($B$2:$B1="NEGATIVE"))) <2
)

这个公式的逻辑：

• 如果是当天的第一条记录，直接标记为TRUE（计入）
• 否则，计算到上一条记录为止的正/负记录数差值，只要差值<2，就标记为TRUE（计入），否则标记为FALSE（不计入）

步骤2：计算每日求和 #

在空白区域（比如F列）输入所有唯一日期，然后在G2单元格输入：

=SUMIFS($C:$C,$A:$A,F2,$D:$D,TRUE)

下拉填充后，就能得到每个日期的最终求和结果。

两种方案都能完美匹配你给出的示例数据，比如：

• Day01求和结果为3
• Day02求和结果为5
• Day03求和结果为26

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