Excel多值公式异常求助：编辑后结果与预期不符

问题回顾 #

需求：计算I12:AM12区域中带$BE$10（即"P"）标记且数值≥指定阈值的单元格，将每个符合条件的数值减去阈值后求和，预期结果为6.0。

原公式：

=SUM(IF(IF(ISNUMBER(FIND($BE$10,I12:AM12)),VALUE(LEFT(I12:AM12,FIND($BE$10,I12:AM12)-1)),0)>=8,IF(ISNUMBER(FIND($BE$10,I12:AM12)),VALUE(LEFT(I12:AM12,FIND($BE$10,I12:AM12)-1)),0),0))-(BE12*8)

异常现象：编辑或回车后结果变为-24.0，修改阈值为7.5后结果仍异常，改回8也无法恢复预期值。

原公式错误原因 #

1. 重复逻辑与硬编码：两次重复编写提取数值的逻辑，增加出错概率；同时硬编码阈值8，未使用单元格引用，修改阈值时需多处调整。
2. 错误的差值计算逻辑：最后一步-(BE12*8)依赖BE12作为符合条件的单元格数量，但BE12的取值可能不准确（比如未正确统计符合条件的单元格数），直接导致求和结果偏差。
3. 数组公式兼容性问题：旧版Excel中该公式需按Ctrl+Shift+Enter作为数组公式输入，若仅按回车，会导致计算范围错误，结果异常。

解决方案 #

方案1：Excel 365/2021（动态数组支持） #

使用LET函数简化逻辑，避免重复计算，提升可读性：

=LET(
    目标区域, I12:AM12,
    含标记, ISNUMBER(FIND($BE$10, 目标区域)),
    提取数值, IF(含标记, VALUE(LEFT(目标区域, FIND($BE$10, 目标区域)-1)), 0),
    阈值, $BE$11, // 将阈值存入BE11单元格，方便修改
    符合条件, 提取数值 >= 阈值,
    SUM(IF(符合条件, 提取数值 - 阈值, 0))
)

逻辑说明：

• 定义变量简化重复操作
• 先筛选含指定标记的单元格，提取数值
• 判断数值是否≥阈值，对符合条件的数值直接计算（数值-阈值）后求和

方案2：旧版Excel（需数组公式输入） #

合并嵌套IF，直接计算符合条件的差值，无需额外依赖外部计数单元格：

=SUM(IF(ISNUMBER(FIND($BE$10,I12:AM12)),IF(VALUE(LEFT(I12:AM12,FIND($BE$10,I12:AM12)-1))>=$BE$11,VALUE(LEFT(I12:AM12,FIND($BE$10,I12:AM12)-1))-$BE$11,0),0))

注意：输入完成后需按Ctrl+Shift+Enter确认数组公式，公式会自动添加{}包裹。

验证示例 #

假设I12:AM12中符合条件的单元格为10P、10P、10P，阈值设为8，则：
(10-8)+(10-8)+(10-8) = 2+2+2 = 6.0，与预期结果一致。

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