如何计算Excel中排除隐藏列的空白

Question

我试图计算一行中的所有空白单元格，同时忽略隐藏列，但找不到任何返回正确答案的公式。SUBTOTAL函数只适用于隐藏行，但我不能将数据更改为隐藏行而不是列。

例如，我想计算B2:BA2中的空白单元格，但需要忽略该范围之间隐藏列中的任何空白单元格。

感谢您的帮助！

Answer 1

您可以尝试以下VBA函数：

Function CntBlnk(Rng As Range)
Dim Cell As Range
Application.Volatile
For Each Cell In Rng
If Cell.EntireColumn.Hidden = False And Len(Trim(Cell)) = 0 Then
CntBlnk = CntBlnk + 1
End If
Next Cell
End Function

然后调用所需单元格中的函数CntBlnk。

Answer 2

VBA解决方案可能是这里的最佳选择。可以单独使用工作表公式进行设置，即：

=SUMPRODUCT(N(CELL("width",OFFSET(B2,,COLUMN(B2:BA2)-MIN(COLUMN(B2:BA2))))>0),N(B2:BA2=""))

或者，Office 365:

=SUMPRODUCT(N(CELL("width",OFFSET(B2,,SEQUENCE(,COLUMNS(B2:BA2),0)))<>0),N(B2:BA2=""))

尽管它有三个缺点：

1. 易挥发
2. 尽管存在上述波动性，但所通过范围内列宽的变化不会触发该公式的重新计算；用户将需要执行手动重新计算
3. 列宽小于0.5的列将被视为隐藏列

Answer 3

如果你有Excel 365并且对使用Lambda持开放态度，你也可以尝试：

=LAMBDA(range,index,IF(index>COLUMNS(range),0,ISBLANK(INDEX(range,index))*(@CELL("width",INDEX(range,index))>0)+CountVisBlanks(range,index+1)))

其中Lambda在名称管理器中被命名为CountVisBlanks。

与使用Cell的另一个答案一样，它也存在这样一个问题，即在强制重新计算图纸之前，Cell不会更新。

调用为：

=CountVisBlanks(b2:ba2,1)