用EXCEL的sumproduct函数做条件统计

作者:

此篇为学习笔记。

1. 基本用法

sumproduct 的正常用法是

= sumproduct(array1, array2, array3, ...)

它可以支持最高 30 个参数。但必须要注意,这里每个参数都必须为数值型,连逻辑型都不行。所以任何以下式子得到的结果都是 0 :

= sumproduct(A1:A100, B1:B100<>"a")        ' 错误用法结果总是返回0

解决方法之一是通过显性或隐性的转换将逻辑值转为数值型:

= sumproduct(A1:A100, N(B1:B100<>"a"))      ' 显性转换 
= sumproduct(A1:A100, --(B1:B100<>"a"))     ' 隐性转换速度更快推荐

另一个方法是使用连乘:

= sumproduct((A1:A100)*(B1:B100<>"a"))

来个复杂的:

= sumproduct((A1:A100)*(B1:B100<>"a")*((C1+C100)-(E1:E100)*(F1:F100)))

使用连乘和加减的技术原理可参考 Excel 区域计算的原理 。它比上面的多参数形式运算速度要稍微慢一些,但也有以下两个好处:

  • 连乘的表达式不需要转换逻辑表达式,写法更为简单。
  • 连乘可突破最多 30 个参数式子的限制。

2. 和 sumifs 的区别

office 2007 引入了 sumifs ,可进行多条件求和,可以部分实现 sumproduct 的功能。但 sumproduct 有一个功能, sumifs 无法做到。比如:

= sumproduct((A1:A100)*((B1:B100="a")+(B1:B100="b")))

即 sumproduct 可以实现嵌套的条件求和(包括 and 和 or ),而 sumifs 只能实现并列条件求和(即只能是 and )。其实 sumproduct 的适用范围要宽的多,比如以下条件求和, sumifs 都无法实现:

= sumproduct((A1:A100)*((C1:C100+D1:D100)>0))  
                      ' sumproduct可对不同区域进行预算

= sumproduct((A1:A100)*(C1:C100<D1:D100))      
                      ' sumproduct可对不同区域进行比较

= sumproduct((A1:A100)*(LOG(C1:C100,2)))          
                      ' sumproduct可使用excel内置函数

3. 其它

3.1. 数组公式

sumproduct 的所有功能都可以用公式组实现。比如将 sumproduct 换成 sum ,然后按 CTRL+SHIFT+ENTER 确认,可得到一模一样的结果。

= sumproduct((A1:A100)*((B1:B100="a")+(B1:B100="b")))

{= sum((A1:A100)*((B1:B100="a")+(B1:B100="b")))}

事实上, sumproduct 本质上就是先对参数进行数组运算,然后在 sum 起来。

但直接写数组公式难以被理解,输入速度慢(无法拖动等等),并且一般情况下数组公式比普通公式效率要低,所以能用普通函数实现的功能,建议直接使用普通函数。

3.2. 加权平均值

value 和 weight 分别为值和权重,那么可以通过以下方法

= sumproduct(value*weight) / sum(weight)

3.3. 公式可包含数据

Excel 支持直接输入数据,所以我们可以使用下面这样的表达式:

= sumproduct((A1:A4)*{1;2;3;4})

注意下面公式得到的结果是一样的,但运算效率要低很多。从 Excel 区域计算的原理 知道,下面的式子中 sumproduct 的参数被展开成一个 4×4 的方块,所以运算速度要慢一个级别(线性 vs 平方):

= sumproduct((A1:A4)*{1,2,3,4})

这里区别在于";"号表示换行,而","号表示下一列,所以 {1;2;3;4}是一个列向量,而 {1, 2, 3, 4}是一个行向量。 我们也可以将其结合, {1,2,3,4;5,6,7,8;9,10,11,12;}是一个 3×4 的矩阵。

Q. E. D.

类似文章:
编程 » Excel
在编辑 Excel 文件时经常遇到的一个问题是,我这边用得好好地,换台机器就变了个样,或者根本用不了。下面是我在日常工作中总结的一些避免这些情况的小技巧。
编程 » Excel
Excel 中可设置每个单元格显示数字和文本的格式:
编程 » Excel
最近看到一个比较有趣的问题, Excel 中以下表达式代表什么含义:
编程 » Excel
香港这边没有 WIND ,部门的彭博也不在我的机器上,为了取个股票价格数据都得跑来跑去。一怒之下,我写了一个 Excel 的函数,用来获取行情数据。
编程 » VBA, Excel
如果不提供错误处理方法, VBA在出错时会停留在出错之处。Excel/VBA 提供 On Error关键词来处理程序运行过程中的错误,具体有下面两种用法:
编程 » Excel
最近看到一个比较有趣的问题, Excel 中以下表达式代表什么含义:
最佳约会策略 里,我们提到,如果有 100 个女孩可以顺序挑选,那么最好的方法是先看前 37 个,然后在剩下的女孩里选择当时最好的那个女孩,这样有接近 40%的概率挑选到最好的那个女孩。同时, 不可能有更好的策略