Excel动态范围下计算最大连续盈亏及优化文件卡顿求助

针对动态数据范围下辅助列导致文件卡顿的问题，以下是无需辅助列的高效解决方案，分版本适配：

Excel 365/2021 动态数组方案（推荐） #

这类版本支持动态数组与LAMBDA函数，单个单元格即可完成计算，无需下拉公式，性能大幅优于辅助列。

假设盈亏数据在A:A（A1为表头，数据从A2开始），通过FILTER获取动态非空数据范围：

计算最大连续盈利总和 #

=MAX(SCAN(0,FILTER(A:A,A:A<>""),LAMBDA(a,b,IF(b>0,a+b,0))))

• 逻辑：用SCAN逐行累计连续盈利，遇到亏损时重置累计值为0，最终取累计值的最大值。

计算最大连续亏损总和（负数值代表亏损总额） #

=MIN(SCAN(0,FILTER(A:A,A:A<>""),LAMBDA(a,b,IF(b<0,a+b,0))))

若需返回亏损的绝对值，套上ABS()：

=ABS(MIN(SCAN(0,FILTER(A:A,A:A<>""),LAMBDA(a,b,IF(b<0,a+b,0)))))

旧版Excel（2019及更早）数组公式方案 #

若不支持动态数组，使用数组公式（输入后按Ctrl+Shift+Enter确认生效），注意限制数据范围为实际动态区域：

最大连续盈利总和 #

=MAX(MMULT(N(ROW(A2:INDEX(A:A,COUNTA(A:A)))>=TRANSPOSE(ROW(A2:INDEX(A:A,COUNTA(A:A)))))*(A2:INDEX(A:A,COUNTA(A:A))>0),A2:INDEX(A:A,COUNTA(A:A))))

最大连续亏损总和 #

=MIN(MMULT(N(ROW(A2:INDEX(A:A,COUNTA(A:A)))>=TRANSPOSE(ROW(A2:INDEX(A:A,COUNTA(A:A)))))*(A2:INDEX(A:A,COUNTA(A:A))<0),A2:INDEX(A:A,COUNTA(A:A))))

超大数据量的终极优化 #

如果数据量超万行，公式计算仍有压力，推荐用Power Query处理：

1. 将数据加载到Power Query编辑器
2. 添加自定义列标记盈利/亏损状态
3. 用"分组依据"功能按连续状态分组求和
4. 提取最大/最小累计值后加载回Excel
   Power Query为后台批量计算，不会导致实时卡顿。

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