优惠思维 篇十二:这个excel函数是万能函数?让我们来聊一聊它的用法
有人说sumproduct函数是万能函数,我一个朋友有也和我说sumproduct函数简直太好用了。那么它好用在哪?本次就来说下它的用法。
一、基本用法
Sumproduct函数的语法结构:=sumproduct(数组或区域),首先要了解它的最基本用法,合计和乘积后求和。
合计:和sum函数简单用法一样,当然我们平时不会舍近求远,用sumproduct函数求和,而不用sum函数。
乘积后求和:这个有一定的使用度,简单的说就是两个数组对应位置的数值分别乘积后再求和,使用函数公式:sumproduct(数组,数组)。要记住:这里的两个数组不仅包括列形式的数组,还包括行形式的数组,只要两个数组有相同单元格数量即可。
二、和sum函数相似用法
我们在sum函数使用的那篇中已经提到sum的多种用法,包括单条件求和和多条件求和,忘记的可以去回顾下sum函数使用的内容。Sumproduct函数同样具有这些功能,大家可以看下有什么区别。
1.单条件求和
目标:需要张三的销售总额,按照计算关系,求得的值为销售数量*销售价格。
使用函数:=SUMPRODUCT((A$2:A$10=G3)*D$2:D$10*E$2:E$10)
说明:这个公式和sum函数的使用非常像,只不过我们在sum函数中求得是总和,这里求得是乘积和,这也是他们使用的主要区别,另外sum函数需要同时按“ctrl+shift+enter”键,这里直接回车即可。这里的“$”号可以去掉,因为求牛七的销售总额,所以加入“$”号。
2.多条件求和
目标:需要一组、A产品的销售总额。
使用函数:=SUMPRODUCT((B$2:B$10=G7)*(C$2:C$10=H7)*D$2:D$10*E$2:E$10)
说明:这里的(B$2:B$10=G7)*(C$2:C$10=H7)起到了确定条件的作用,D$2:D$10*E$2:E$10为计算组,这个计算和sum也很相似,不同的地方和单条件求和一样。
3.多条件计数
目标:计算从事一组、A产品销售的数量(人数)
使用函数:=SUMPRODUCT((B$2:B$10=G3)*(C$2:C$10=H3)*1)
说明:这个在sum函数没法直接实现,只能增加一列均为1的辅助列来计算;sumproduct函数则可以直接实现。掌握了多条件计数,那么单条件计数就轻而易举了。
三、排名
目标:对销售情况进行排序。我们知道可以使用rank函数对一组数进行排序,排名顺序是等于比它多(少)的数的个数,也就是会形成“1,2,2,4,5”的排名模式;那么我们如何实现“1,2,2,3,4”的排名模式?
使用函数:=SUMPRODUCT((B$2:B$10>B2)/COUNTIF($B$2:$B$10,$B$2:$B$10))+1
说明:如何理解呢?我们可以拆解这个函数,可以先拆成sumproduct(1/COUNTIF($B$2:$B$10,$B$2:$B$10),其中的countif($B$2:$B$10,$B$2:$B$10)表示这个数列中,某个数出现的次数。然后用sumproduct(1/某个数出现的次数),将这组数列相加(本文的第一个用法“sumproduct(数组或区域)”),得出的结果就是不重复销售业绩的个数。
剩下的就比较好理解,公式的整体意思就是:比张三销售业绩多,且不重复的销售业绩个数,再+1;这个+1因为也比较好理解。可以把(B$2:B$10>B2)变更为(B$2:B$10<B2),实现降序排列。
四、组内排序
目标:有时候,我们并不需要对公司所有人进行排序,只需要进行组内排序。
使用函数:=SUMPRODUCT(($B$2:$B$10=B2)*(C$2:C$10>C2))+1
说明:通过($B$2:$B$10=B2)明确所在组别内个数,然后通过(C$2:C$10>C2)得出比自身多的人的个数,再+1得出排名;可以通过(C$2:C$10<C2)实现降序排列。这个组内排序和rank排序效果是一样的。
Sumproduct函数确实是一个实用性很强的函数,但是它的普及性没有到达if函数、vlookup函数、sum函数一样,一个重要原因在于他的函数有时过于复杂。这里顺便普及一个知识点:奥卡姆剃刀定律,即如无必要,勿增实体,也就是要追求事情的简单化,拒绝复杂化,抓住本质,才最有效。
写于2月14日情人节,这里祝相爱的人都能白头偕老,祝单身的小伙伴们能够转角遇见爱。
作者声明本文无利益相关,欢迎值友理性交流,和谐讨论~
M1Nt
校验提示文案
M1Nt
校验提示文案