好文档 - 专业文书写作范文服务资料分享网站

Excel函数之——SUMPRODUCT函数太强大了

分享 时间: 加入收藏 我要投稿 点赞

在excel中,对于sumproduct函数的说明是返回相应的数组或者区域乘积的和。看上去就是一组数据和另一组数据先分别相乘然后求和嘛,其实sumprodct函数的用法不只是看上去这么简单,它兼有sum函数、product函数、sumif函数、sumifs函数、countif函数、countis等函数的功能。本文就详细给大家介绍一下sumproduct函数的用法吧。

一、基本用法。对于sumproduct函数,公式参数特别简单,即=SUMPRODUCT(数组1,数组2,数组3, ……),每个数组之间用逗号隔开,表示数组之间先相乘再求和。

如下图所示,在E2单元格中输入函数=SUMPRODUCT(C2:C21,D2:D21),计算过程为188*5+232*7+292*4+……224*2=23308,直接就求出来总销售额,而不用求出每个地区每个产品的销售额再求和。

在F2中输入函数=SUMPRODUCT(F9:F28),因为只有一组数据,所以返回的结果就是对这组数据求和,相当于sum函数。

在G2单元格输入函数=SUMPRODUCT(E4,F4),则表示E4单元格的数值乘以F4单元格的数值。相当于product函数。

所以我们可以看出来,这个函数只要有逗号,那么就是逗号隔开的区域相乘,且逗号两边区域的单元格个数必须相同。

t017707fe4802805b61

二、条件求和。

在下图中的E2单元格中输入公式=SUMPRODUCT((B2:B21=B2)*C2:C21),就会算出A01产品的销量合计(图中绿色部分),这个公式中仍然只有一组参数,B2:B21=B2是在B列内容中判断条件是否是A01,返回的结果是TRUE;FALSE;FALSE……,(B2:B21=B2)*C2:C21表示逻辑值与销售数量相乘,返回{188;0;0;0;283;0;0;0;327;0;0;0;288;0;0;0;211;0;0;0},可以看到FALSE与数值相乘返回的是0,最后的sumproduct函数仅表示求和,因为只有一个参数。

在F2单元格中输入公式=SUMPRODUCT((B2:B21=B2)*(C2:C21>200)*C2:C21),就会算出A01产品中销量大于200的合计数,对于这种多条件求和,其实原理和单条件求和一样,条件之间用乘号隔开即可。

在G2单元格中输入公式=SUMPRODUCT((B2:B21=B2)*C2:C21,D2:D21),算出的是A01产品的销售额。首先看到有一个逗号了,说明这里的sumproduct函数的参数就有两个,然后(B2:B21=B2)*C2:C21表示A01产品的数量(不是A01返回的数量是0),加上逗号后面D2:D21,表示的单价先相乘再求和,最后就算出A01产品的销售额了。

看完这三个公式,A01产品中销量大于200的销售额的公式也应该明白了,即SUMPRODUCT((B2:B21=B2)*(C2:C21>200)*C2:C21,D2:D21),和你想的一样吗?

t01848d2671ae5f6431

三、条件计数。

在下图中E2单元格输入公式=SUMPRODUCT((B2:B21=B2)*1)后,可以统计出A01产品的数量。(B2:B21=B2)表示在产品型号中条件是A01,计算结果是返回的是TRUE;FALSE;FALSE……FALSE,但是此处为什么要在后面乘以1呢?因为sumproduct是对数值计算,而(B2:B21=B2)返回的结果是文本。而true*1=1,false*1=0,所以(B2:B21=B2)*1计算结果是1;0;0;0;1;0;0;0;1;0;0;0;1;0;0;0;1;0;0;0,进行求和就会算出5。

在F2单元格中输入公式=SUMPRODUCT((B2:B21=B2)*1+(B2:B21=B3)*1),会统计出A01和B02产品的合计个数,因为求合计个数是或的关系,两个条件满足一个即可,所以两个条件之间用加号连接。

在G2单元格中输入公式=SUMPRODUCT((C2:C21>200)*1*(C2:C21<400)*1),会计算出销售数量大于200且小于400的个数,此处表示且的关系,两个条件都要满足,所以条件之间用乘号连接。

t0136480bf1c3da8656

四、模糊条件求和。

如下图所示,如果想求出北方地区(东北、华北、西北)A01产品的销售额,那么在E2单元格中输入公式=SUMPRODUCT(--(ISNUMBER(FIND("北",A2:A21)))*(--(B2:B21=B2))*C2:C21,D2:D21)即可。FIND("北",A2:A21)表示查找“北”在单元格中的位置,如果能找到,返回字符的位置,找不到返回#VALUE!。ISNUMBER(FIND("北",A2:A21))表示如果find函数结果是数值,isnumber返回true,否则返回false。而isnumber函数前加--表示减负数(作用和上面乘以1相同),最终把true返回到1,false返回0。--(B2:B21=B2)判断产品是否为A01,如果是返回1,否则返回0。此时就相当于多条件求和,逗号前面条件和数量相乘,逗号后面为单价,最终符合条件的值相乘并求和。

t016ba6d7676954294d

五、中国式排名。

excel中的排名函数rank函数返回的是西方国家惯用的排名方式,而我们中国式排名如果有并列名次,不会占用下面的名次,下面是sumproduct函数与rank函数排名结果对比。

利用sumprodunct排名时,在E2单元格输入公式=SUMPRODUCT(($C$2:$C$21>C2)/COUNTIF($C$2:$C$21,$C$2:$C$21))+1即可。这个函数比较难理解。对于E2单元格,COUNTIF($C$2:$C$21,$C$2:$C$21)函数表示条件计数,如果有重复值,则返回重复的个数,此处返回的结果是1;1;2;1;1;2;1;1;1;1;1;1;1;1;2;1;2;1;1;1,而用1/COUNTIF($C$2:$C$21,$C$2:$C$21)表示相同的数字只统计一次(因为每个重复的数字都被平均了)。返回结果为1;1;0.5;1;1;0.5;1;1;1;1;1;1;1;1;0.5;1;0.5;1;1;1,然后($C$2:$C$21>C2)/COUNTIF($C$2:$C$21,$C$2:$C$21)的返回结果为0;1;0.5;1;1;0.5;1;1;1;1;0;0;1;0;0.5;0;0.5;0;0;1,其中$C$2:$C$21>C2采用的是相对引用,所以向下填充会返回不同的结果。直接决定了计算的相对名次。然后再用sumproduct函数对上面返回结果求和,最后+1对结果修正。

t01df716f5f923a18a5

这就是sumproduct函数的常见用法,如果本文的用法理解并熟练运用,那么有一些其他的用法自然就会了,比如隔列求和,组内排序等。最后提醒的是,注意函数中逗号,乘号,加号的运用,函数中的参数应为数值格式,且各个参数区域中的单元格个数相同。

221381
领取福利

微信扫码领取福利

微信扫码分享