求Excel公式：统计指定单元格上方至下一个非空单元格间的空白单元格数

针对需求：统计某列中值为1的单元格上方，从该单元格的上一行开始向上遍历，直到遇到第一个非空单元格为止的空白单元格数量，以下是不同Excel版本的可用公式：

Excel 365/2021（支持动态数组） #

假设目标值1位于A列，以单元格A10为例，使用以下公式：

=XLOOKUP(TRUE,INDEX(A$1:A9<>"",,),ROW(A$1:A9),0,-1)-ROW(A9)

公式说明： #

• INDEX(A$1:A9<>"",,) 生成A1至A9区域的非空判断数组
• XLOOKUP(...,ROW(A$1:A9),0,-1) 从下往上查找第一个非空单元格的行号，未找到则返回0
• 用找到的行号减去A9的行号，得到两者之间的空白单元格数量

若要批量处理A列所有值为1的单元格，可使用：

=BYROW(A:A,LAMBDA(x,IF(x=1,XLOOKUP(TRUE,INDEX(A$1:OFFSET(A,,,-ROW(A))<>"",,),ROW(A$1:OFFSET(A,,,-ROW(A))),0,-1)-ROW(A)+1,"")))

旧版Excel（不支持动态数组） #

同样以A10单元格的值为1为例，使用数组公式（输入后按Ctrl+Shift+Enter确认）：

=MAX(IF(A$1:A9<>"",ROW(A$1:A9),0))-ROW(A9)

公式说明： #

• IF(A$1:A9<>"",ROW(A$1:A9),0) 将非空单元格的行号保留，空单元格对应值设为0
• MAX(...) 提取最靠近目标单元格的上一个非空单元格的行号
• 减去A9的行号，得到空白单元格数量

注意事项： #

• 公式中的区域A$1:A9需根据值为1的单元格位置调整，例如1在A5时，区域改为A$1:A4
• 若值为1的单元格上方无任何非空单元格，公式将返回0

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