Excel公式需求：统计当前连续达标天数及历史最长Streak

嘿，这个需求我刚好帮人解决过好几次，给你两个实用的公式，完美适配你的场景——不管以后插多少新列都能自动更新，分情况给你说明：

一、计算当前连续达标天数（从最后一天往前数的连续A/B天数） #

如果你的Excel是365/2021版本（支持动态数组和LAMBDA函数），用这个公式最简洁：

=LET(
    dataRow, S7:CK7,
    revData, INDEX(dataRow,1,COLUMNS(dataRow)):INDEX(dataRow,1,1),
    firstBreak, XLOOKUP(TRUE, NOT(revData={"A","B"}), SEQUENCE(COLUMNS(dataRow),,COLUMNS(dataRow),-1), 0),
    COLUMNS(dataRow) - firstBreak
)

公式解释：

1. dataRow 定义当前行的所有评级数据范围
2. revData 把数据从最后一列倒转到第一列，方便从后往前找第一个非A/B的位置
3. firstBreak 找到第一个不是A/B的单元格对应的列序号（从后往前数），如果全是A/B就返回0
4. 最后用总列数减去这个序号，得到连续达标天数

如果你用的是旧版Excel（没有365函数），可以用这个数组公式（输入后按Ctrl+Shift+Enter确认）：

=COLUMNS(S7:CK7)-MATCH(TRUE,INDEX(NOT((S7:CK7="A")+(S7:CK7="B")),,COLUMNS(S7:CK7)):INDEX(NOT((S7:CK7="A")+(S7:CK7="B")),,1),0)+1

二、计算历史最长连续达标Streak #

同样优先推荐365版本的动态数组公式，逻辑清晰且高效：

=LET(
    dataRow, S7:CK7,
    flag, IF((dataRow="A")+(dataRow="B"),1,0),
    streaks, SCAN(0, flag, LAMBDA(prev,curr, IF(curr=1,prev+1,0))),
    MAX(streaks)
)

公式解释：

1. flag 把所有A/B转换成1，其他评级转换成0
2. streaks 用SCAN函数累计连续的1，遇到0就重置计数
3. 最后取累计结果的最大值，就是最长的连续达标天数

旧版Excel的数组公式（同样按Ctrl+Shift+Enter输入）：

=MAX(FREQUENCY(IF((S7:CK7="A")+(S7:CK7="B"),COLUMN(S7:CK7)),IF(NOT((S7:CK7="A")+(S7:CK7="B")),COLUMN(S7:CK7))))

关键优化：让公式自动适应新插入的列 #

上面的公式用了固定范围S7:CK7，如果你以后插入新列，默认不会自动扩展范围。最省心的办法是把你的数据转换成Excel表：

1. 选中你的数据区域（包括表头）
2. 按Ctrl+T，勾选「我的表有标题」，确认转换
3. 把公式里的S7:CK7替换成表的行引用，比如Table1[@[S]:[CK]]（具体表名和列名根据你的实际情况调整）

这样以后不管在右侧插入多少新列，公式都会自动识别并包含新的列数据，完全不用手动修改公式！

备注：内容来源于stack exchange，提问作者Uncle Bajubjubs