如何用Excel公式统计含至少一个大于1数值的列数（支持多列）

数据表格 #

需求说明 #

需要统计表格中至少有一个单元格数值大于1的列数，要求公式可适配千列场景，本案例预期结果为2（列b和c）。现有公式=SUM(--(MAX(B2:B4)>1), --(MAX(C2:C4)>1), --(MAX(D2:D4)>1))仅适用于少量列，无法适配千列场景。

解决方案 #

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

使用BYCOL函数实现批量列处理，公式如下：

=SUM(--(BYCOL(B2:D4,LAMBDA(col,MAX(col)>1))))

• 逻辑说明：BYCOL遍历指定区域的每一列，通过LAMBDA判断该列最大值是否大于1，返回布尔值数组；--将布尔值转换为1/0，最后SUM求和得到符合条件的列数。
• 适配千列：只需将公式中的B2:D4替换为实际的数据区域（如B2:XFD4，覆盖Excel最大列范围）即可。

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

使用数组公式实现，输入完成后需按Ctrl+Shift+Enter确认：

=SUM(--(MMULT(TRANSPOSE(--(B2:D4>1)),ROW(B2:B4)^0)>0))

• 逻辑说明：
  1. --(B2:D4>1)：将区域内每个单元格是否大于1转换为1/0的数组；
  2. TRANSPOSE转置数组，将列转为行；
  3. ROW(B2:B4)^0生成全1的行数组，与转置后的数组做矩阵乘法MMULT，得到每列中大于1的单元格数量；
  4. >0判断该列是否存在符合条件的单元格，转换为布尔值后用--转成1/0，最后SUM求和得到列数。
• 适配千列：同样只需替换B2:D4为实际的大范围数据区域即可。

内容的提问来源于stack exchange，提问作者Dorkhan C.