如何计算连接数>10000的当前连续次数并排除空白单元格？

问题描述 #

我有一个每日填写的「连接数」列，需要计算当前连续大于10000的次数，且这个数值能在每日输入新连接数后自动更新。举例：

• 当前最后5条数据12000、13000、15000、13000、13250都超过10000，连续次数为5；
• 若次日输入3200，连续次数需重置为0。

尝试过的无效方案 #

1. 创建辅助列「CONNECTIONS TO HIDE」，公式：

=IFS(
   [@[Number of connections]]=""; "";
   [@[Number of connections]]>10000; "Win";
   [@[Number of connections]]<10000; "Loss"
)

2. 统计连续次数的单元格公式：

=COUNTA(Table8[CONNECTIONS TO HIDE]) -
 MATCH(2;INDEX(1/(Table8[CONNECTIONS TO HIDE]="Loss");0))

3. 简化后的公式：

=COUNTA(Table8[Number of connections]) -
 MATCH(2;INDEX(1/(Table8[Number of connections]<10000);0))

核心问题：上述公式无法排除列中待填充的空白单元格，仅选中已输入数值的范围时公式可正常运行。

解决方案 #

方案1（Excel 365/2021及以上版本，逻辑更简洁） #

在空白单元格输入以下公式，可自动识别最后一行数据，并计算连续超10000的次数：

=LET(
    last_data_row, MATCH(9.99999999999999E+307, Table8[Number of connections]),
    last_below_threshold, XLOOKUP(TRUE, INDEX(Table8[Number of connections],1):INDEX(Table8[Number of connections],last_data_row)<=10000, ROW(INDEX(Table8[Number of connections],1)):ROW(INDEX(Table8[Number of connections],last_data_row)), 0, 0, -1),
    IF(INDEX(Table8[Number of connections],last_data_row)>10000, last_data_row - last_below_threshold, 0)
)

逻辑说明：

• last_data_row：定位到列中最后一个有数值的行；
• last_below_threshold：从最后一行往前找第一个≤10000的行号，找不到则返回0；
• 最后判断最后一行是否超10000，是则用最后行号减去找到的行号，否则返回0。

方案2（兼容旧版Excel） #

如果你的Excel版本不支持LET函数，使用以下公式：

=IF(INDEX(Table8[Number of connections],MATCH(9.99999999999999E+307,Table8[Number of connections]))>10000,
MATCH(9.99999999999999E+307,Table8[Number of connections])-
LOOKUP(2,1/(INDEX(Table8[Number of connections],1):INDEX(Table8[Number of connections],MATCH(9.99999999999999E+307,Table8[Number of connections]))<=10000),
ROW(INDEX(Table8[Number of connections],1)):ROW(INDEX(Table8[Number of connections],MATCH(9.99999999999999E+307,Table8[Number of connections])))),
0)

逻辑说明：

• 先定位最后一个数据行，再在该范围内用LOOKUP反向查找第一个≤10000的行，最后计算行号差得到连续次数；若最后一行≤10000则返回0。

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