多条件统计,顾名思义就是统计满足多个条件的对象的个数、合计值等,它比单一条件的统计要复杂,却在现实场景中使用得更多,你是不是有同感?
开始
笔者就从自己常使用的函数中遴选了三个函数,因为在我涉及到统计方面的工作中使用到它们三个的场景能占98%以上,如果在Excel函数库中你还有其它要推荐,请留言给我。
它们是Countifs、Sumifs和Sumproduct,今天讲讲常用的一些技巧,并对Sumproduct进行深度解读。
01
=Countifs($B$2:$B$17,"="&G2,$A$2:$A$17,"="&F2)
说明:统计10月份每天各险种类别有销量的产品数量
通用公式:=Countifs(判断对象1,条件表达式1, [判断对象2,条件表达式2],…)
参数:判断对象1,条件表达式1是必须要有的,如果只有一个条件和Countif(判断对象1,条件表达式1)同等效果。各条件之间是“与“的关系。
知识点:
1.各条件分别判断,各参数之间用逗号隔开
2.判断区域如果是固定的,单元格要用绝对引用,避免公示拖拽时引用区域发生变化。
02
=Sumifs($D$2:$D$17,$B$2:$B$17,"="&G2,$A$2:$A$17,"="&F2)
说明:统计10月份每天各险种类别的销售额
通用公式:=Sumifs(统计对象,判断对象1,条件表达式1, [判断对象2,条件表达式2],…)
该函数的参数顺序是和正常的逻辑相反的,不知道微软工程师在设计这个函数的时候是基于什么考虑。
与Countifs和Countif的包含被包含关系不同,Sumifs与Sumif只是在概念上有此关系,但实际写法在逻辑上却相反,Sumif的参数表达式为Sumif(判断对象,条件表达式,统计对象),大家使用的时候要注意。
知识点:至少要有两个统计条件,否则不会自动地算出一个条件下的统计结果。
03
=Sumproduct(($B$2:$B$17=G3)*($A$2:$A$17=F3))或
=Sumproduct(($B$2:$B$17=G2)*($A$2:$A$17=F2)*$D$2:$D$17)
说明:统计10月份每天各险种类别有销量的产品数量和各险种类别的销售总额
官方公式:=Sumproduct(array1,[array2],[array3],...)
Office对此函数的官方解释是返回对应的区域或数组的乘积之和。所以参数中的数组必须是相同的维数,即数组元素的个数必须一样。
我们常用的变体公式:=Sumproduct(条件1*条件2*条件3*……*统计区域)
通过下面的例子我解释一下这个函数的统计原理:
=Sumproduct((B2:B17=G2)*(A2:A17=F2)*D2:D17)
参数1:B2-B7的单元格的值分别与G2进行对比,相等的话值为1,不等的话值为0。
参数2:A2-A7的单元格的值分别与F2进行对比,相等的话值为1,不等的话值为0。
所以参数1*参数2中的值是1或者0,这就回归到了条件表达式的本质上来了。
Sumproduct(参数1*参数2*参数3)就相当于单元格D2-D17中与参数1*参数2中为1的元素对应的数值的和。
从录屏的结果可以看到第一个公式得到的结果和Countifs是一样的,第二个公式的结果和Sumifs是一样,也就是说Sumproduct可以代替Countifs和Sumifs,并且使用起来可能更方便和容易理解
知识点:
1.功能更强大,可代替Countifs和Sumifs,看个人使用习惯了。
04
=Sumproduct((A3:A18=G2)*(C3:E18))
说明:相对于基础用法,本例进行了扩展,将参数对象扩展成多维矩阵,即满足一定条件下的多维区域求和。
知识点:理解了该函数的本质,就可以在很多用法上进行扩展,大家可以结合实际工作灵活运用。
联系客服