Excel干货——详解如何利用SUMPRODUCT计数求和,你真的都知道么?
大家好,我是婶婶,希望接下来的分享能够对大家有些许帮助,也希望大家多多支持鼓励,收藏、分享、评论多多益善啦,如果对胃口记得关注哦!
连续更新了这么多天,终于讲到统计函数sum家族四兄弟中的sumproduct函数,欲知之前三兄弟sum、sumif以及sumifs的前世今生,请关注婶婶之前更的文章,今天我们将要详细介绍------如何利用SUMPRODUCT计数求和!
一)函数名词解释
SUMPRODUCT函数:返回相应的数组或区域乘积的和
SUMPRODUCT(array1,array2,array3, ...)
Array1,array2,array3, ... 为 2 到 30 个数组,其相应元素需要进行相乘并求和。
sumproduct函数通常有2个应用:1)计数;2)求和。
其基本计算公式如下:
计数=sumproduct((条件1)*(条件2)*(条件3)...*(条件n));
求和=sumproduct((条件1)*(条件2)*(条件3)*...*求和区域)。
显然,求和是在计数的基础上“加”上一个求和区域。
二)案例详解
一、使用 SUMPRODUCT 函数进行多条件计数
对于sumproduct函数的计数问题,我们将其分为4类,每一类都会有一个案例进行分析;当然万变不离其宗,所有的计算公式都是:计数=sumproduct((条件1)*(条件2)*(条件3)...*(条件n)),具体如下:
1)例 1:条件间关系为 and
如图,我们要统计类别为“b”,且长=12的木板的种类,依据我们在《婶操作-Excel教程——从0到1,动图详解统计函数SUM家族四兄弟,赶紧收藏了》这篇文章中的介绍,我们不难得出以下公式:
公式=SUMPRODUCT((B3:B11="b")*(C3:C11=12)), 具体操作如下:
2)例 2:条件间关系为 or,且各条件互斥
还是这个例子,现在要统计类别为"b",且长为12或9 的木板的种类,对需求分析如下:
显然,大条件间为且的关系,大条件内部的分条件之间为或的关系,但是分条件之间相互排斥,或者说没有重叠部分,则同理可输入公式=SUMPRODUCT((B3:B11="b")*((C3:C11=12)+(C3:C11=9))),具体操作如下:
3)例 3:条件间关系为 or,且各条件相容
还是这个例子,我们要统计长>10 或 宽>10 的木板的种类,显然,这两个条件之间关系为或,且满足两个条件的部分可能相互重叠,倘若我们依照之前的的公式进行计算的话,会有以下结果:
公式=SUMPRODUCT((C3:C11>10)+(D3:D11>10))=10 ,显然,木板6、木板7和木板9我们计算了2次;
则真实答案应该为10-3=7;也就是说我们把同时满足两个条件的木板计算了2次,则需要减去一次同时满足条件的木板;
故正确公式应该为:=SUMPRODUCT(((C3:C11>10)+(D3:D11>10))-(C3:C11>10)*(D3:D11>10)),具体操作如下:
【总结】
1、若条件间关系为 or,且各条件相容,条件数为2时,公式如下:
=SUMPRODUCT((条件1)+(条件2)-(条件1*条件2)) ;
2、若条件间关系为 or,且各条件相容,条件数为3时,公式如下:
=SUMPRODUCT((条件1)+(条件2)+(条件3)-(条件1*条件2)-(条件1*条件3)-(条件2*条件3)+(条件1*条件2*条件3))
有朋友可能不理解为什么要加上(条件1*条件2*条件3),请看下图:
我门暂且做一下替代:条件1*条件2=A;条件2*条件3=B,条件1*条件3=C,条件1*条件2*条件3=D;
我们现在要求的就是3个圆圈的“占地面积”,显然简单的相加更定多算了,但是如果最后不把D加上,肯定少算了,以为3个圆圈有3个D,但是A/B/C各有一个D,若不加上,就没有了D,得到的结果也就不是3个圆圈的“占地面积”了!
4)例 4:复杂条件的拆分
还是这个例子,我们现在要统计长和宽的最大值大于12,且数量大于3的木板种类;
经过分析,我们不难得出,其其实可以拆分为两个条件:
1、长大于等于宽,且数量大于3;
2、长小于宽,且数量大于3;(条件关系和,即:and) 则我们不难得出公式
=SUMPRODUCT(((C3:C11>=D3:D11)*(C3:C11>12)+(C3:C11<D3:D11)*(D3:D11>12))*(E3:E11>3)),操作如下:
二、使用 SUMPRODUCT 函数进行多条件求和
求和=sumproduct((条件1)*(条件2)*(条件3)*...*求和区域)。
还是这个例子,我们对于例1,现在统计类别为b,且长度为12的木板数量,根据求和公式,我们不难得出:
公式=SUMPRODUCT((B3:B11="b")*(C3:C11=12)*(E3:E11)),具体操作如下:
例2同理:公式=SUMPRODUCT((B3:B11="b")*((C3:C11=12)+(C3:C11=9))*(E3:E11))
怎么样,大家理解了么,如果有问题,可以在评论里交流或者私信我哦!
请先 后发表评论~