Google Sheets条件格式高效公式优化及COUNTIF统计问题咨询

一、优化条件格式公式（解决卡顿） #

原公式性能差的核心原因：

• 大量使用INDIRECT（易失性函数，会触发频繁重计算）
• 重复调用REGEXEXTRACT，每行每列都要执行正则解析
• 多层嵌套IF逻辑，计算复杂度高

步骤1：在DATA表新增辅助列预处理公差规则 #

假设DATA表中：

• C列是公差规则（如±0.5、-0.3+0.4）
• D列是基准值

新增两列并下拉填充：

• E列（上限值）：

=IF(REGEXMATCH(C2,"±"), D2+VALUE(REGEXEXTRACT(C2,"±(.*)")), IF(REGEXMATCH(C2,"-(.*)\+(.*)"), D2+VALUE(REGEXEXTRACT(C2,"\+(.*)")), D2))

• F列（下限值）：

=IF(REGEXMATCH(C2,"±"), D2-VALUE(REGEXEXTRACT(C2,"±(.*)")), IF(REGEXMATCH(C2,"-(.*)\+(.*)"), D2-VALUE(REGEXEXTRACT(C2,"-(.*)")), D2))

这一步把公差解析逻辑只执行一次，避免后续重复计算。

步骤2：替换MAIN表的条件格式公式 #

选中MAIN表的2:22行所有数据列，打开条件格式规则，替换为：

=AND(NOT(ISBLANK(A2)), OR(A2=4, A2="✘", A2>INDEX(Data!E:E, ROW()), A2<INDEX(Data!F:F, ROW())))

说明：

• 用INDEX替代INDIRECT，非易失性函数大幅降低重计算频率
• 直接引用预处理好的E/F列，避免重复正则解析
• 逻辑简化为AND+OR，计算效率更高

二、解决列内符合条件的统计问题 #

原COUNTIF失效是因为ROW()在COUNTIF中会引用统计行的行号，而非目标单元格的行号。改用以下方案实现统计：

单列统计（如J24统计J2:J22） #

在J24输入：

=SUMPRODUCT(
  --NOT(ISBLANK(J2:J22)),
  --((J2:J22=4)+(J2:J22="✘")+(J2:J22>INDEX(Data!E:E, ROW(J2:J22)))+(J2:J22<INDEX(Data!F:F, ROW(J2:J22)))>0)
)

解释：

• --把布尔值转为1/0，方便SUMPRODUCT计算
• +代表OR逻辑，只要满足任一条件就计数
• INDEX(Data!E:E, ROW(J2:J22))自动匹配每行对应的DATA表上下限

批量统计所有列（一次性生成24行所有统计值） #

在MAIN表A24单元格输入（根据实际列数调整A1:Z1的范围）：

=ARRAYFORMULA(IF(A1:Z1="", "", BYCOL(A2:Z22, LAMBDA(col, SUMPRODUCT(--NOT(ISBLANK(col)), --((col=4)+(col="✘")+(col>INDEX(Data!E:E, ROW(col)))+(col<INDEX(Data!F:F, ROW(col)))>0))))))

注：BYCOL是Google Sheets较新的函数，支持按列批量处理逻辑。

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