无需VBA/宏高效统计特定行数据：Excel公式优化求助

原公式性能瓶颈在于易失性函数INDIRECT和整列无差别遍历，以下是无需VBA、支持数据动态扩展的优化方案：

方案1：Excel 365/2021 专属（最简洁高效） #

利用PIVOTBY动态数组函数自动生成统计表，无需手动填充公式：
在Sheet0的空白起始单元格（如B2）输入：

=PIVOTBY(Sheet1!$B:$B, Sheet1!$C:$Z, COUNTA,,0,0)

• 参数解释：
  • Sheet1!$B:$B：分组依据（Height列）
  • Sheet1!$C:$Z：需统计的答案列范围（可根据实际数据调整列区间）
  • COUNTA：统计非空答案的数量
  • 末尾两个0：指定不生成汇总行/列，直接输出按Height和答案分类的统计结果

若需固定统计Yes/No/Possibly三类答案，可添加列筛选：

=PIVOTBY(Sheet1!$B:$B, CHOOSECOLS(Sheet1!$C:$Z, MATCH({"Yes","No","Possibly"}, Sheet1!$C$1:$Z$1,0)), COUNTA,,0,0)

方案2：兼容所有Excel版本 #

步骤1：将Sheet1数据转为结构化表格 #

选中Sheet1数据源区域，按Ctrl+T，勾选「我的表格有标题」，将数据转为Excel表格（默认命名为Table1）。结构化引用仅计算有数据的行，彻底避免整列遍历的资源浪费。

步骤2：用COUNTIFS替代SUMPRODUCT+INDIRECT #

在Sheet0的目标统计单元格（如F3，对应Medium+Yes）输入：

=COUNTIFS(Table1[Height], $B3, Table1[$B3], F$2)

完成后横向、纵向批量填充公式即可。

若无法使用结构化表格，用INDEX+MATCH替代易失性的INDIRECT，优化SUMPRODUCT：

=SUMPRODUCT((Sheet1!$B$2:$B$1000=$B3)*(INDEX(Sheet1!$C$2:$Z$1000,0,MATCH($B3,Sheet1!$C$1:$Z$1,0))=F$2))

• 注意：将公式中的$B$2:$B$1000替换为实际数据行范围，若需自动适配数据行数，优先推荐结构化表格方案。

核心优化逻辑 #

• 替换INDIRECT：改用非易失性的INDEX+MATCH或结构化引用，避免每次计算强制刷新全表
• 缩小计算范围：放弃整列引用，仅计算有数据的行，大幅降低CPU负载
• 优先用COUNTIFS：Excel原生优化的统计函数，多条件统计效率远高于SUMPRODUCT

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