当前所在位置: 首页 > 儿童教育 > 正文

听说有人靠这一个函数打下了Excel半壁江山?

2023-01-26 餐饮美食网 【 字体:

今天给大家分享一个很常用也很实用的函数:SUMPRODUCT。众所周知,条件求和与计数是表格使用者最经常碰到的两个问题,而该函数不但集合了条件求和与计数两大功能于一身;还可用于复杂场景下的排名处理,甚至听说有人靠它一个函数就打下了Excel半壁江山……于是便不可不学了。

基础语法

先来看基础语法,SUMPRODUCT的官方语法说明是在给定的几组数组(array)中,将数组间对应的元素相乘,并返回乘积之和。

语法格式如下:=SUMPRODUCT(array1,array2,array3, …)

——SUM是求和的意思,PRODUCT是相乘的意思,参数之间相乘之后再求和,你看,SUMPRODUCT确实人如其名了。

看我手,歪、图、斯瑞……总结起来,SUMPRODUCT函数具有以下三个特点:

1> 它本身默认执行数组运算。

2> 它会将参数中非数值型的数组元素作为0处理。

3> 参数必须有相同的尺寸,否则返回错误值。

特点解析

看完了SUMPRODUCT的简历,想必很多朋友是雾里看花,仅仅对它有个模糊的认知,它的这些特点是啥意思?它到底能够胜任什么样子的工作?其实并不了然。

打个响指,我举几个例子。

如上图所示的数据表,C列是商品单价,D列是销售数量,现在需要在C9单元格计算销售总额。

C9输入以下公式,即可得出结果11620.60=SUMPRODUCT(C3:C7, D3:D7)

这便是一个简单的SUMPRODUCT函数了。

它的运算过程是:C3:C7和D3:D7两个区域数组内的元素分别相乘,也就是C3*D3,C4*D4,C5*D5……直至C7*D7

等于先将每个商品的销售金额计算出来,最后汇总求和。

由于SUMPRODUCT函数第一个特点,本身是支持数组间运算的,所以虽然该公式执行了多项运算,但并不需要按数组三键<Ctrl+Shift+Enter>结束公式输入。

有的朋友说啦,公式也可以写成这样:=SUMPRODUCT(C3:C7*D3:D7)

或者使用以下数组公式,也是可以的。=SUM(C3:C7*D3:D7)

那么这三个公式之间有什么区别呢?首先,大部分情况下,SUMPRODUCT函数都不需要数组三键结束公式输入即可执行数组运算,而SUM函数是需要的。

其次,就要说到SUMPRODUCT函数另一个非常重要的特点了。

……

我们将上面的表稍做改动,将“钢笔”的销售数量更改为:暂未统计。同样需要在C9单元格计算销售总额。

这时候,如果使用公式:=SUMPRODUCT(C3:C7*D3:D7)

或者数组公式:=SUM(C3:C7*D3:D7)

都将返回错误值#VALUE!返回错误值的原因在于D4单元格“暂未统计”为文本值,文本值是无法直接参与数学运算的,于是C4*D4返回错误值#VALUE!,进而造成整个公式的结果返回错误值。

而使用以下公式就没有这方面的困扰,会直接返回正确结果:=SUMPRODUCT(C3:C7,D3:D7)这便是SUMPRODUCT函数的第二个特点:将非数值型的数组元素作为0处理。

以该示例来说,D4单元格的值“暂未统计”为文本,并非数值,SUMPRODUCT将其主动视为零,于是C4*D4,结果亦为零,其余数组元素照常计算,得出11385.60的结果。

需要特别说明的是,SUMPRODUCT将非数值型的数组元素作为0处理,所谓的非数值型数组元素,包含逻辑值、文本,但并不包含错误值,如果数组元素中包含错误值,该公式亦返回错误值,比如该示例的第一条公式。……

说完了SUMPRODUCT函数的两个特点,我们就再来聊聊它的第三个特点:数组参数必须有相同的尺寸,否则返回错误值。我们依然用上述图片的例题为例,继续计算商品的销售总额。

如果我们在C9输入公式:=SUMPRODUCT(C3:C7,D3:D6)

结果会是怎么样的呢?错误值:#VALUE!

为什么?细心的你肯定已经注意到了,两个区域数组,C3:C7明显显比D3:D6多了一个元素,C3和D3结对子,C4和D4结对子……那么C7和谁结对子呢?女人们都嫁了,结果剩下一个光棍,这日子没法过了!一个萝卜一个坑,只有萝卜没有坑,这不是要萝卜死吗?——于是SUMPRODUCT就不高兴了,它给你一个错误值#VALUE!,明确告诉你,和谐时代幸福岁月,日子不能这么过。

这就是SUMPRODUCT函数的第三个特点:数组参数必须有相同的尺寸,否则返回错误值。

下面是一道练习题,你看看,能用SUMPRODUCT函数做出来吗?

案例拓展

假设下面这张图,是某个公司工资发放的部分记录表(数据纯属虚拟,如有雷同,那是穿越)。A列是工资发放的时间,B列是员工所属的部门,C列是员工姓名,D列是相关员工领取的工资金额。

——那么,问题和广告都来了:

1、员工西门庆领取了几次工资?这是一个单条件计数的问题,通常我们使用COUNTIF函数,但如果使用SUMPRODUCT函数,一般写成这样:=SUMPRODUCT((C2:C13="西门庆")*1)

先判断C2:C13的值是否等于”西门庆”,相等则返回TRUE,不等则返回FALSE,由此建立一个有逻辑值构成的内存数组。

上文已经说过,SUMPRODUCT有一个特性,它会将非数值型的数组元素作为0处理,逻辑值自然是属于非数值型的数组元素,为了避免SUMPRODUCT函数把逻辑值视为0,造成统计错误,我们使用*1的方式,把逻辑值转化为数值,TRUE转化为1,FALSE转化为0,最后统计求和。

2、员工西门庆领取了多少工资?这是一个单条件求和的问题,通常我们使用SUMIF函数,如果使用SUMPRODUCT函数,我们可以写成这样:=SUMPRODUCT((C2:C13="西门庆")*D2:D13)

依然首先判断C2:C13的值是否等于”西门庆”,得到逻辑值FALSE或TRUE,再和D2:D13的值对应相乘。TRUE乘以数值,得到数值本身。FALSE乘以数值返回0。最后统计求和得出结果。看完了上面两个问题,有些朋友可能会在心里想,貌似SUMPRODUCT能干的事,SUMIF和COUNTIF也能做到,而且做的更好,那么还要SUMPRODUCT干啥嘞?

乡亲们呐,话不能这么说,SUMPRODUCT可是上得厅堂下得厨房,对工作环境不挑不拣,它对参数类型没有啥特别要求,COUNTIF和SUMIF就不同了,他俩要求个别参数,必须是区域(Range型),不支持数组,比如下面这两个问题,COUNTIF和SUMIF就要绕了。

3、二月份外交部发放了几次工资?总额是多少?第1个问题,二月份外交部发放了几次工资?这是一个多条件计数的问题。第一个条件,发放工资的时间必须是二月份;第二个条件,发放工资的部门必须是外交部。如果使用多条件计数函数COUNTIFS,判断发放工资的时间是否属于六月份,会简单问题复杂化。而使用SUMPRODUCT函数,咱们可以把公式写成这样:=SUMPRODUCT((MONTH(A2:A13)=2)*(B2:B13="外交部"))……

第2个问题,统计二月份外交部发放了多少工资?这是一个常见的多条件求和问题。如果使用SUMIFS函数,判断发放工资的时间是否属于六月份,也会简单问题复杂化。SUMPRODUCT跃然而至:=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="外交部"),D2:D13)

或者:=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="外交部")*D2:D13)

打个响指,关于这两个形式的SUMPRODUCT函数的区别,咱们上文已有详细说明——你还记得吗?上面这个公式可以说是SUMPRODUCT多条件求和的典型用法啦,可以归纳为:=SUMPRODUCT((条件一)*(条件二)……,求和区域)

4、二月份外交部和步兵部合计发放了多少工资?解决了上面的问题,相信大家已经晓得如何计算二月份外交部发放多少工资了,那么二月份外交部和步兵部合计发了多少工资,又当怎么计算呢我们经常见有些性格朴素的表亲们把公式写成这样:=SUMPRODUCT((MONTH(A2:A13)=2)*(B2:B13="外交部")*D2:D13)+SUMPRODUCT((MONTH(A2:A13)=2)*(B2:B13="步兵部")*D2:D13)

这些表亲们估计心想,不就是计算两个部门吗?甭说两个,二十个咱也能算,一个加一个,一直加到二十个,世上无难事,只怕有心人嘛,一砖加一砖,长城就建成了,一泡加一泡,长江就奔流了……呃……公式写的那么长,先不谈计算速度,首先它累手啊,万一写错了,又要修改,那也是麻烦他妈哭麻烦——麻烦死了。其实我们可以写成这样:=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13={"外交部","步兵部"})*D2:D13)

5、排名应用认识了SUMPRODUCT函数在条件计数和求和方面的用法,最后,咱们再来看一个它在排名上的使用方法。

如上图所示,某个月某个公司某些人领了某些工资,然后呢,他们想看看自己的工资,在部门内的排名情况,比如说步兵部的鲁智深都是老员工了,非常想知道自个工资在各自部门排几号。

当然啦,不排不知道,一排就傻掉。

SUMPRODUCT是这么解决这个问题的,D2输入公式向下复制:=SUMPRODUCT(($A$2:$A$9=A2)*(C2<$C$2:$C$9))+1

(思考,为什么公式的最后+1,而不是直接写成如下:)=SUMPRODUCT(($A$2:$A$9=A2)*(C2<=$C$2:$C$9))

结束语唠唠叨叨说了这么多,眼睛都说酸麻了,是到了该结束的时候啦。最后,请思考两个小问题:

第1个问题:下面SUMPRODUCT函数有几个参数?=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="财务部")*D2:D13)

下面这个SUMPRODUCT函数又有几个参数?=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="财务部"),D2:D13)

第二个问题:SUMPRODUCT为什么有时候比SUMIF/COUNTIF计算速度慢?

阅读全文
相关推荐

如何自制蛋挞

如何自制蛋挞
1、倒入低筋面、黄油和水,揉成面团状,藏一小时后取出,抹上黄油卷起,包上保鲜膜冷藏半小时。2、取出,将面团切成一厘米的面团,放入模具内,用力按压,中间稍薄,外缘要比模具高,将蛋挞液倒入蛋挞皮中,放入烤箱里烤30分钟即可。

肉火烧面怎么和面

肉火烧面怎么和面
1、首先用温水把酵母融开,加入面粉,用手把面粉揉成非常软的面团,可以放一点熬好的猪油在面团里面。2、然后放在温暖的地方,盖上保鲜膜发酵至2倍大,发好的面团用手插入不回缩,就说明面团发酵好了。

奶茶几分糖好喝

奶茶几分糖好喝
1、不同的人对奶茶的口感要求是不一样的,一般来说,奶茶有三分糖、五分糖、七分糖三种不同的口味。2、女生一般都喜欢喝甜一点的,而男生则喜欢喝不那么甜的,七分糖的奶茶最好喝。

过桥米线是哪里的?

过桥米线是哪里的?
过桥米线是来自云南省滇南地区的一种有名特色小吃。过桥米线最早是在清朝就已经出现,距离现在至少有一百多年的历史啦!起源于建水县东城外锁龙桥西侧的鸡市街头处,有建水的特产草芽、地椒作配料,风味独特而远近闻名。深受广大美食爱好者的喜欢,不少人为此专门长途跋涉,只为尝

面霜和乳液的区别

面霜和乳液的区别
面霜和乳液的区别,相较而言乳液的水分含量要比面霜高,乳液质地要轻薄一些,乳液主要作用是保湿,滋润可以隔离外界干燥的气候,面霜既可保湿,又可美白,还能抗衰老,乳液的吸收快一些,而面霜吸收比较慢一些,因为液体的吸收速度都比较快。

怎么摘隐形眼镜

怎么摘隐形眼镜
在摘隐形眼镜之前,先用洗手液将手清洗干净,以免将细菌带入眼睛内,对着镜子,用右手中指轻拉眼睛下眼睑,左手中指轻拉眼睛上眼睑,让黑色眼球暴露在空气中,用右手食指和拇指轻触镜片的两边缘部分,向中间推使镜片拱起,再用两手指轻轻捏出镜片即可。

粉饼和散粉的区别

粉饼和散粉的区别
粉饼是呈压缩固体状态,多呈圆形或者方形,散粉则是细腻的粉末状,粉饼遮瑕力会比较强一些,可以湿用做粉底,或者用来补妆,而散粉则是定妆的效果,粉饼通常用在底妆的第一步,而散粉通常用在底妆最后一步。

冷烫和热烫的区别

冷烫和热烫的区别
冷烫和热烫的区别:冷烫对头发的要求是要在保温状态下才能给卷有个好的效果,而热烫是在干和湿的情况下都行,热烫烫出来的头发比较自然明显而有弹力,冷烫的头发风干后基本看不出来,并且发质会有点干。

高品质香水如何鉴别?用三步就可以解决

高品质香水如何鉴别?用三步就可以解决
1、看色泽以天然香料调制而成的高级香水,都有它本来的颜色,且大都是琥珀色或褐色,看起来很像宝石,比如,从茉莉、玫瑰或水仙等天然鲜花中所萃取的精油都呈黄色、褐白或绿褐色;此外,香水中所添加的魅惑香气物性香料也是褐色,苔类中的橡树苔是绿色,从树根或树根脂类中萃取的

如何去除黑眼圈

如何去除黑眼圈
去除黑眼圈首先可以用热毛巾敷眼,然后再用冷毛巾敷,十分钟就能让黑眼圈淡化的不那么严重,其次将去壳后的鸡蛋用无菌纱布包裹住,敷于眼部轻轻转动,可以急救去除黑眼圈,另外抹完眼霜后,用双手顺时针按摩,可以促进血液循环消除黑眼圈。
本文Tag