Excel中IF函数应用及动态数值区间计算优化需求

原始问题与公式 #

原始手动公式（注：原公式中E()应为Excel的AND()函数，分号为区域设置下的参数分隔符）：

=SUM(IF(E(H39<=38;H39>=20);1;0);IF(E(H39<=38;H39>=21);1;0);IF(E(H39<=38;H39>=22);1;0);IF(E(H39<=38;H39>=23);1;);IF(E(H39<=38;H39>=24);1;);IF(E(H39<=38;H39>=25);1;);IF(E(H39<=38;H39>=26);1;);IF(E(H39<=38;H39>=27);1;);IF(E(H39<=38;H39>=28);1;);IF(E(H39<=38;H39>=29);1;);IF(E(H39<=38;H39>=30);1;);IF(E(H39<=38;H39>=31);1;);IF(E(H39<=38;H39>=32);1;);IF(E(H39<=38;H39>=33);1;);IF(E(H39<=38;H39>=37);1;);;IF(E(H39<=39;H39>=38);1;))*5,38

需求核心：

• 统计H39数值命中指定子区间的次数，每命中一个区间加1，最终总和乘以5.38
• 子区间范围需要频繁调整，手动修改公式效率极低，需实现灵活可变的自动计算

优化方案 #

方案1：用独立区域存储区间条件 #

1. 定义可变区间：在工作表中预留一个区域（如K1:K16），每个单元格写入一个区间判断条件，例如：

   • K1: =AND(H39>=20,H39<=38)
   • K2: =AND(H39>=21,H39<=38)
   • ...
   • K15: =AND(H39>=37,H39<=38)
   • K16: =AND(H39>=38,H39<=39)
     后续调整区间时，直接修改K列的条件即可。

2. 主计算公式：用SUMPRODUCT统计命中次数并计算结果：

   =SUMPRODUCT(--(K1:K16))*5.38
   

   （旧版Excel可改用COUNTIF数组公式，需按Ctrl+Shift+Enter输入：=COUNTIF(K1:K16,TRUE)*5.38）

方案2：用上下限列简化区间维护 #

如果子区间都是「≥下限且≤上限」的形式，可拆分上下限到独立列：

1. 存储上下限：在J列存区间下限，L列存区间上限，例如：

   J	L
   20	38
   21	38
   ...	...
   37	38
   38	39

2. 主计算公式：直接通过上下限列计算命中次数：

   =SUMPRODUCT(--(H39>=J1:J16),--(H39<=L1:L16))*5.38
   

   调整区间时只需修改J、L列的数值，无需编辑公式逻辑。

公式说明 #

• --(条件)：将布尔值TRUE/FALSE转换为1/0，实现计数逻辑
• SUMPRODUCT：同时判断两个条件（≥下限、≤上限），仅当两个条件都满足时计数1，最终求和得到总命中次数
• 区间列表可随时增减行数，只需同步调整公式中的单元格范围（如J1:J18）即可

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