(7)合并单元格求和
如下图所示,要求在D列对C列的类别求和:
=SUM(C2:C$10)-SUM(D3:D$10)
公式输入方法:先选取D2:D10,在编辑栏中输入上述公式,再按ctrl+enter完成批量输入。
2、【Sumif】单条件求和
(1)案例——求各业态的销售额
如下图,已知各商铺的销售额,要对各业态的销售额求和,公式为:
=SUMIF(C$2:C$8,F2,D$2:D$8)
案例延伸:
——同是上图,假设商铺号01-01中的前面两位数是楼层号,已知各商铺号,要对1层的商铺销售额进行求和,公式为:
=SUMIF(B2:B8,"01*",E2:E8)
——同是上图,计算商铺名称为三个字的销售额之和,公式为:
=SUMIF(B2:B8,"???",E2:E8)s
注:
”*”和”?”都属于通配符:
*可以代表任何文字或字符(任意个数)
?仅代表单个文字或字符
(2)案例——隔列求和
如下图,已知各商铺的每月实际和目标销售额,要隔列求和,计算第一季度的合计公式为:
=SUMIF($C$2:$H$2,I$2,$C3:$H3)
(注:隔列的标题必须完全一致)
3、【Sumifs】多条件求和
案例——求多种条件下(如各楼层、各业态等)的销售额之和
如下图,已知各商铺的销售额,要对各楼层各业态的销售额求和,公式为:
=SUMIFS(E$2:E$8,A$2:A$8,G2,D$2:D$8,H2)
案例延伸:(如上图)
——统计”除快时尚以外”的销售额之和,公式为:
=SUMIF(D2:D8,"<>快时尚",E2:E8)
——也可以去掉行号,写成整列引用,但必须前后一致:
=SUMIF(D:D,"<>快时尚",E:E)
——统计”销售额大于100万”的销售额之和,公式为:
=SUMIF(E2:E8,">1000000",E2:E8)
——sumifs可以用于无限个条件,语法为:
SUMIFS(统计区域,第一条件区域,条件,[第二条件区域,第二条件....])
Sumif与Sumifs易错点解析:
①sumifs与sumif语法格式几乎是相反的:Sumif的统计区域在最后,Sumifs的统计区域在最前面;
②要搞清楚绝对引用和相对引用,导致下拉公式时,需要固定的数据区域发生了变化;
③原始表格的条件区域表格要规范(不能有时是“服饰”,有时是“普通服饰“,必须严格一致)。
4、【Sumproduct】乘积
案例——求各业态的销售额
如下图,已知各商铺的面积和租金单价,要对总租金求和,公式为:
=SUMPRODUCT(D2:D8,E2:E8)
案例延伸:
sumproduct函数也可以用于多条件求和,和多条件计数,但是数据量非常大时运行速度较慢,所以不推荐使用。
运行速度最快请用:
多条件求和——sumifs,
多条件计数——countifs.
三、统计公式
1、【Count/Counta/Countblank】简单统计
如下图所示,三个函数的不同功能和结果:
2、【COUNTIF】单条件的统计
案例——统计符合单一条件的商铺数量
如下图所示,用countifs做相关统计:
计算纯保底的公式是:
(注:G3单元格=”纯保底”)
如上图,延伸计算:
销售额大于100万的商铺数:
=COUNTIF(D$2:D$19,">1000000")
销售额小于等于75万的商铺数
=COUNTIF(D$2:D$19,"<=7500000")
销售额大于75万且小于100万的商铺数
=COUNTIF(D$2:D$19,"<1000000")-COUNTIF(D$2:D$19,"<=7500000")