Excel筛选状态下统计非空及日期条件的SUBTOTAL公式问题

问题核心 #

你之前的公式失效原因：

• 用COUNTIF的版本无法识别筛选隐藏行，会统计所有行数据，导致结果不符合筛选后的场景。
• 尝试SUBTOTAL(109, 数组运算)的写法不成立，因为SUBTOTAL本身不支持直接嵌套数组运算，无法正确统计筛选后的符合条件单元格。

正确公式方案 #

方案1：兼容所有Excel版本（SUMPRODUCT+SUBTOTAL） #

通过SUBTOTAL判断每行是否可见，结合条件统计符合要求的可见行数量：

=SUMPRODUCT((SUBTOTAL(103, OFFSET(A3:A76, ROW(A3:A76)-ROW(A3), 0, 1))=1)*(E3:E76>"")*(E3:E76>TODAY()-365))/SUBTOTAL(103, A3:A76)

• 拆解说明：
  • SUBTOTAL(103, OFFSET(...))=1：标记当前行是否为筛选可见行（返回1代表可见）
  • (E3:E76>"")：排除E列空白单元格
  • (E3:E76>TODAY()-365)：筛选出E列日期在近一年以内的单元格
  • 分子是满足所有条件的可见行总数，分母是筛选后的总可见行数（A列非空行）

方案2：适用于Excel 365/2021（动态数组写法） #

利用FILTER提取可见行数据，再统计符合条件的数量：

=LET(
    visible_E, FILTER(E3:E76, SUBTOTAL(103, OFFSET(A3:A76, ROW(A3:A76)-ROW(A3), 0, 1))=1),
    valid_count, COUNTA(visible_E)-COUNTIF(visible_E, "<="&TODAY()-365)-COUNTBLANK(visible_E),
    valid_count/COUNTA(visible_E)
)

• 拆解说明：
  • visible_E：提取筛选后可见的E列数据
  • valid_count：计算可见行中既非空白、日期也未超过一年的数量
  • 最终返回符合条件的数量占总可见行的比例

方案3：旧版Excel数组公式（需按Ctrl+Shift+Enter输入） #

=(SUBTOTAL(103,A3:A76)-SUM(IF(SUBTOTAL(103,OFFSET(A3:A76,ROW(A3:A76)-ROW(A3),0,1))=1,(E3:E76<=TODAY()-365)+(E3:E76=""),0)))/SUBTOTAL(103,A3:A76)

• 注意：输入完成后不要直接回车，需按下Ctrl+Shift+Enter触发数组运算，Excel会自动为公式添加大括号{}

优化提示 #

如果需要精确计算「自然年跨度」（避免闰年366天的误差），可以将TODAY()-365替换为EOMONTH(TODAY(),-12)，对应条件改为E3:E76>EOMONTH(TODAY(),-12)。

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