Google Sheets中如何批量计算区域内数值与指定值的正差值之和？

问题回顾 #

你需要批量判断一组数值是否大于指定阈值（示例为10），将超出部分累加求和，之前手动逐个嵌套IF的方式效率低下，尝试的数组公式未正常生效。

解决方法 #

方法1：修正数组公式用法 #

你的数组公式逻辑本身正确，失效是因为Excel版本差异导致的确认方式不同：

• Excel 365/2021及以后版本：直接输入公式后回车即可生效

  =SUM(IF(A1:A3>=10,A1:A3-10,0))
  

• Excel 2019及更早版本：输入公式后需按 Ctrl+Shift+Enter 组合键确认（系统会自动在公式两端添加大括号{}，不要手动输入）

方法2：用SUMPRODUCT实现（无需特殊确认，通用所有版本） #

SUMPRODUCT可直接处理数组运算，不需要额外按键，是更省心的批量方案：

=SUMPRODUCT((A1:A3>=10)*(A1:A3-10))

原理：(A1:A3>=10)会生成由TRUE/FALSE组成的数组，乘以差值时，TRUE自动转为1、FALSE转为0，自动过滤不满足条件的数值后求和。

方法3：引用单元格作为阈值（更灵活） #

如果阈值存放在指定单元格（比如B1），直接替换公式中的固定值即可，两种方法都适用：

• 数组公式版：

  =SUM(IF(A1:A3>=B1,A1:A3-B1,0))
  

• SUMPRODUCT版：

  =SUMPRODUCT((A1:A3>=B1)*(A1:A3-B1))
  

扩展说明 #

如果需要处理更大范围（比如A1:A100），只需修改公式中的单元格区域即可，完全实现批量处理，无需逐个调整。

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