EXCEL高手装备高级函数SUMPRODUCT攻克统计难题

时间:2016-10-21   作者:snow   来源:互联网

相信大多数小伙伴们刚入门学习EXCEL的时候,最先接触到的条件统计函数就是SUMIF、SUMIFS、COUNTIF、COUNTIFS这几个函数吧(如果你入门的时候没学过,那么今天可以学习一下)。这些入门级函数,虽然简单,容易操作,但遇到特殊问题时(例如数组),就无能为力了。要想真正成为一名EXCEL高手,首先要从你的函数装备开始升级。今天表妹就给小伙伴们,介绍一个高级函数装备——SUMPRODUCT函数,有他出场,那些入门级条件统计函数不能攻克的难题统统帮你搞定!

函数介绍:

SUMPRODUCT(array1,array2,array3, ...)

参数说明:将数组(array)间对应的元素相乘,并返回乘积之和。(SUM是求和的意思,PRODUCT是相乘的意思,所以函数就是相乘之后再求和。学好英语很重要!)

我们先看他亮个相:

公式=SUMPRODUCT(B2:B11,C2:C11)

实际上就是=B2*C2+B3*C3+...+B11*C11,分别用单价与个数相乘,然后再计和。

侠之大者,往往不拘小节。SUMPRODUCT也具有这条品格。

就算的数据中有“无价之宝”这种非计算类的数据,SUMPRODUCT也会自动将其视为0,继续执行其他数据计算(非常任性有木有?)

-----------------------------------------

下面介绍一下SUMPRODUCT面对数据条件统计时的基础技能

1.条件求和基本公式

初级函数:

SUMIF(条件查找区域,条件,求和区域)

SUMIFS(求和区域,条件查找区域1,条件1,条件查找区域2,条件2...)

高级函数:

SUMPRODUCT((条件查找区域1=条件1)*(条件查找区域2=条件2)*...*(条件查找区域n=条件n)*(求和区域))

2.条件计数基本公式

初级函数:

COUNTIF(计数区域,计数条件)

COUNTIFS(计数区域1,条件1,计数区域2,条件2...)

高级函数:

SUMPRODUCT((计数区域1=条件1)*(计数区域2=条件2)*...*(计数区域n=条件n))

有了基础技能,我们来看看高级函数的风采:

1.统计不重复项个数

公式=SUMPRODUCT((1/COUNTIF(A2:A11,A2:A11))*1)

公式含义是:COUNTIF依次返回一组数值,分别是A2、A3...A11各自的个数,即{2,2,1,1,1,1,2,1,2,1},被1除后,变为{1/2,1/2,1,1,1,1,1/2,1,1/2,1}再求和后得出不重复项8。

2.联合多列判断

公式=SUMPRODUCT((B2:B11<C2:C11)*1)

公式含义是:分别比较B2与C2,B3与C3...B11与C11之间的大小,如果小于,就返回1,如果不小于就返回0,最后计和。

3.隔列求和

公式=SUMPRODUCT((MOD(COLUMN(B2:G2)+1,3)=0)*(B2:G2))

公式含义是:COLUMN(B2:G2)返回{2,3,4,5,6,7},加1后变为{3,4,5,6,7,8}。用MOD函数除以3取余数后得到{0,1,2,0,1,2},等于0的只有第1列和第4列,对应B列和E列,即1月和4月。

以上这三种情况,初级函数是无法做到的,感觉到高级函数的威力了吧?

但是也要说明一下,SUMPRODUCT由于是数组计算函数,所以计算效率上没有普通函数高(技能冷却时间较长),所以一般的小问题,不建议使用高级函数(总不能天天用大炮打蚊子呀)。

有了今天的高级函数装备,小伙伴们是不是距离EXCEL高手又进了一步呢?相信在表妹的陪伴下,小伙伴们变成EXCEL高手的那一天一定会更快到来的!

tag : SUMPRODUCT
发表评论