求Excel数组中两个1之间0的间隔数统计公式

需求说明：定位每行AE:BC列数组中的第一个1，统计所有两个1之间的0的数量之和；末尾无后续1的连续0串不计入统计，公式需支持拖拽批量计算。

方法1：Excel 365/2021 动态数组方案 #

在目标单元格（如BD2）输入以下公式，下拉即可批量应用：

=SUM(IFERROR(DROP(FILTER(SEQUENCE(COLUMNS(AE2:BC2)),AE2:BC2=1),1)-DROP(FILTER(SEQUENCE(COLUMNS(AE2:BC2)),AE2:BC2=1),-1)-1,0))

公式逻辑： #

1. FILTER(SEQUENCE(COLUMNS(AE2:BC2)),AE2:BC2=1)：提取该行所有1所在的列位置序号
2. DROP(...,1) 移除第一个1的位置，DROP(..., -1) 移除最后一个1的位置，让两个数组错位对齐
3. 错位相减后减1，得到每两个相邻1之间的0的数量；IFERROR处理仅含单个1的行（返回0）
4. SUM求和得到该行总间隔数

如果需要一次性处理整列数据，可使用BYROW批量生成结果：

=BYROW(AE2:BC100,LAMBDA(row_data,SUM(IFERROR(DROP(FILTER(SEQUENCE(COLUMNS(row_data)),row_data=1),1)-DROP(FILTER(SEQUENCE(COLUMNS(row_data)),row_data=1),-1)-1,0))))

方法2：兼容Excel 2013及以上版本（非动态数组） #

在BD2输入以下公式，按Ctrl+Shift+Enter（数组公式确认）后下拉：

=SUM(LEN(FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN("",TRUE,AE2:BC2),"1","</s><s>")&"</s></t>","//s[position()>1 and position()<last()]")))

公式逻辑： #

1. TEXTJOIN("",TRUE,AE2:BC2)：将该行AE到BC的单元格内容拼接为一串文本（如100101）
2. SUBSTITUTE(..., "1", "</s><s>")：用XML节点分隔符替换所有1，得到类似"</s><s>00</s><s>0</s><s>"的文本
3. 包裹XML标签后，FILTERXML提取所有<s>节点，通过//s[position()>1 and position()<last()]筛选掉开头的空节点和末尾无后续1的0串节点
4. LEN(...)获取每个有效间隔的0的长度，SUM求和得到总间隔数

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