Excel中统计AdjustedMark列非零且非隐藏行的数量问题求助

问题解决方法 #

你之前的两个公式出错原因如下：

• 第一个公式中，TMarks[AdjustedMark]/(TMarks[AdjustedMark]<>0) 会在AdjustedMark为0时产生#DIV/0!错误，虽然AGGREGATE的参数7会忽略错误，但COUNTA（参数3）统计的是非空值，这种数组运算逻辑会导致公式返回#VALUE!。
• 第二个公式用AGGREGATE(9,5)（SUM+忽略隐藏行），但(TMarks[AdjustedMark]>0)*1作为数组参数，在非动态数组版本的Excel中需要按Ctrl+Shift+Enter确认输入，且这种写法无法正确结合隐藏行判断逻辑，导致错误。

推荐两种可行的公式：

方法1：使用AGGREGATE结合IF和NA

=AGGREGATE(3, 7, IF(TMarks[AdjustedMark]>0, TMarks[AdjustedMark], NA()))

• 参数3代表COUNTA（统计非空单元格），参数7代表忽略隐藏行和错误值；
• IF函数将AdjustedMark大于0的单元格保留原值，否则返回NA()，AGGREGATE会自动忽略这些NA值和隐藏行，最终统计符合条件的可见行数；
• 注意：Excel 2019及之前版本需按Ctrl+Shift+Enter作为数组公式输入；Excel 365/2021支持动态数组，直接回车即可。

方法2：使用SUMPRODUCT结合SUBTOTAL

=SUMPRODUCT(SUBTOTAL(103, OFFSET(TMarks[AdjustedMark], ROW(TMarks[AdjustedMark])-MIN(ROW(TMarks[AdjustedMark])), 0, 1))*(TMarks[AdjustedMark]>0))

• SUBTOTAL(103)对每个单元格单独判断是否可见（103代表忽略隐藏行的COUNTA），OFFSET用于逐个定位单元格；
• 乘以(TMarks[AdjustedMark]>0)筛选出大于0的行，SUMPRODUCT求和得到最终数量；
• 此公式无需数组输入，所有Excel版本通用。

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