如何在Google Sheets公式中动态定义区域最后一列的水平结束单元格

核心思路 #

先自动识别数据集的最后一列（无需手动指定BZ、K这类固定列标），再基于该动态范围统计目标内容。

场景1：统计整列无数据的列数（从第2行开始全为空的列） #

适用于需要知道数据集中完全无内容的列的数量。

新版Excel（支持动态数组函数：BYCOL、LAMBDA） #

使用以下公式：

=SUM(--(BYCOL(B:INDEX(XFD:XFD,MAX(COLUMN(2:2)*(2:2<>""))),LAMBDA(col,COUNTA(col2:col)=0))))

公式解析：

• MAX(COLUMN(2:2)*(2:2<>""))：自动获取第2行最后一个非空单元格的列号，以此确定数据集的最后一列
• B:INDEX(XFD:XFD,...)：构建从B列到最后一列的完整列范围
• BYCOL(..., LAMBDA(col, COUNTA(col2:col)=0))：遍历每一列，判断该列从第2行开始是否无任何非空内容
• SUM(--(...))：将布尔结果转换为数字后求和，得到空列总数

场景2：统计动态范围内的空单元格总数（替代你的SUM(COUNTIF(B2:K,"<1"))） #

如果实际需要统计范围内所有空单元格的数量（而非整列空），用简洁公式：

=COUNTBLANK(B2:INDEX(XFD:XFD,MAX(COLUMN(2:2)*(2:2<>""))))

如果确实需要统计小于1的单元格（含空、0），则用：

=SUM(COUNTIF(B2:INDEX(XFD:XFD,MAX(COLUMN(2:2)*(2:2<>""))),"<1"))

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

使用数组公式（输入时需按Ctrl+Shift+Enter确认）统计空列数量：

=SUM(--(MMULT(--(B2:INDEX(XFD1048576,MAX(IF(2:2<>"",COLUMN(2:2),0)))<>""),ROW(INDIRECT("1:"&ROWS(B2:B1048576))))=0))

动态水平范围通用技巧 #

任何需要动态水平范围的公式，都可以用INDEX(XFD:XFD,MAX(COLUMN(行号:行号)*(行号:行号<>"")))替代固定列标。比如你之前拖拽的=COUNTA(B2:B)，要让范围自动延伸到最后一列，可改为：

=COUNTA(B2:INDEX(XFD:XFD,MAX(COLUMN(2:2)*(2:2<>""))))

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