EXCEL的sumproduct函数做条件统计

转载 2013年12月05日 15:39:40

EXCEL的sumproduct函数做条件统计

 

基本用法

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"))

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

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

和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内置函数

其它

数组公式

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

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

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

加权平均值

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

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

公式可包含数据

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的矩阵。

Excel函数sumproduct应用案例-多条件求和

作者:iamlaosong 越来越觉得sumproduct这个函数有用,过去用sum组函数,改起来复制起来都麻烦,sumif在条件多的时候也觉得不方便。现在改用sumproduct函数,就简单多了。查...
  • iamlaosong
  • iamlaosong
  • 2014年10月24日 16:58
  • 6228

sumproduct多条件求和经典问题(乘号和逗号)剖析

iamlaosong文 SUMPRODUCT函数的含义为在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。按我个人解释就是两个以上的数组乘积之和。例如,公式为:=SUMPRODUCT(A2:...
  • iamlaosong
  • iamlaosong
  • 2017年01月11日 09:33
  • 1034

JAVA实现EXCEL公式专题(七)——统计函数

统计函数主要实现的是较为复杂的统计函数如countif、sumif、frequency,也是,直接上代码/** * 项目名称: * 文件说明: * 主要特点:文件说明:EXCEL函数类型:...
  • u011680348
  • u011680348
  • 2015年08月15日 11:34
  • 2770

Excel中Sumproduct函数的使用方法

你还在为Excel中Sumproduct函数的使用方法而苦恼吗,今天小编教你Excel中Sumproduct函数的使用方法,让你告别Excel中Sumproduct函数的使用方法的烦恼。 经验主要从四...
  • sinat_34586770
  • sinat_34586770
  • 2016年11月28日 17:36
  • 538

EXCEL去重统计(条件去重复计数)

1、单列去重统计公式为:=sumproduct(1/countif(选定列范围,选定列范围)),其原理是在某一列的范围中,每个数据出现的次数有多少,然后用1去除。简单点说,假设A列数组中出了3次A,每...
  • daihongliu
  • daihongliu
  • 2015年09月01日 16:57
  • 17291

SUMPRODUCT函数使用方法及示例

在Excel里,除了VLOOKUP,另一个必学的应该是SUMPRODUCT函数了,她称得上是函数中的“万金油”!   首先,名字虽然长一点,但也因此齐集了SUM()和PRODUCT()的名字及继承了部...
  • u013465749
  • u013465749
  • 2014年01月14日 14:20
  • 3693

Excel查找函数--Vlookup(单条件匹配)

一、Vlookup介绍 Vlookup就是竖直查找,即列查找。 通俗的讲,根据查找值参数,在查找范围的第一列搜索查找值,找到该值后,则返回值为:以第一列为准,往后推数查找列数值的这一列所对应的值。 ...
  • zhuhengv
  • zhuhengv
  • 2015年12月25日 16:33
  • 2837

sumproduct函数的使用方法及实例

查过SUMPRODUCT()函数的使用方法,其解释为在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。按我个人解释就是两个以上的数组乘积之和。 其语法格式为SUMPRODUCT(array...
  • zjt921586518
  • zjt921586518
  • 2015年09月02日 17:56
  • 753

POI使用公式的问题,POI对excel函数的支持

一、问题 最近在使用POI操作excel文件时,会报如下错误: org.apache.poi.ss.formula.eval.NotImplementedException: Error evalua...
  • wengengeng
  • wengengeng
  • 2016年09月25日 22:15
  • 3929

通过IF({1,0}和VLOOKUP函数实现Excel的双条件多条件查找

在Excel中,通过VLOOKUP函数可以查找到数据并返回数据。不仅能跨表查找,同时,更能跨工作薄查找。   但是,VLOOKUP函数一般情况下,只能实现单条件查找。   如果想通过VLO...
  • wozengcong
  • wozengcong
  • 2015年07月07日 23:00
  • 3284
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:EXCEL的sumproduct函数做条件统计
举报原因:
原因补充:

(最多只允许输入30个字)