35个求和公式汇总,史上最全整理,建议收藏!(文末有大神私藏)
关键字:sum;excel教程;求和
栏目:汇总篇
全文1720字,预计6分钟读完
今天来给大家归纳整理了Excel里的求和公式,涉及sum、sumifs、sumproduct等多个函数,35个公式,囊括多种工作场景,建议大家先收藏,慢慢看!
一、SUM函数
一些特殊用法:
1、黄色单元格求和
如图所示,要求黄色单元格的和,按快捷键Ctrl+G,选择空值,然后按快捷键Alt+=求和。
2、有小计的单元格求和
=SUM(B2:B8)/2
3、多表相同位置(B2)求和
在汇总表中输入公式:=sum("*"!B2), 将公式向右拖拽,点击【不带格式填充】。
PS:如果是 N个不相邻的表在 同一单元格中(B2)求和,直接用sum就不可行了,需要再嵌套一个函数。
=SUM(SUM(INDIRECT({1,2,3}&"!B2")))
4、合并单元格求和
=SUM(C2:C21)-SUM(E3:E22)
注:合并单元格,输入公式时不能拖动,需要选取E2:E22,然后在E3输入公式,然后将光标放在公式结尾处,按Ctrl+Enter结束。
注意:这两个公式需要先选择合并单元格区域,再输入公式,最后按快捷键“ Ctrl+Enter”完成输入。
5、交叉区域求和
只需要给SUM函数加一个空格,就可以轻松搞定。
二、SUMIF函数
数据源如图所示:
1、计算小米的销量
=SUMIF(C2:C9,"小米",D2:D9)
2、除小米之外的销量
=SUMIF(C2:C9,"<>小米",D2:D9)
3、计算大于300的销量
=SUMIF(D2:D9,">300")
4、计算低于平均值的销量
=SUMIF(D2:D9,"<"&AVERAGE(D2:D9))
5、以“红”开头的产品求和
=SUMIF(C2:C9,"红*",D2:D9)
注:*是通配符,表示任意多个字符
6、以红开头,2个字符长度产品求和
=SUMIF(C2:C9,"红?",D2:D9)
注:?是通配符,表示任意单个字符
7、以“米”结尾的产品求和
=SUMIF(C2:C9,"*米",D2:D9)
8、计算名称为3个字的销量总和
=SUMIF(C2:C9,"???",D2:D9)
9、计算当前日期的销量总和
=SUMIF(B2:B9,TODAY,D2:D9)
10、计算小米和大米的销量总和
=SUM(SUMIF(C2:C9,{"小米","大米"},D2:D9))
一些特殊用法:
1、忽略错误值的销售额
=SUMIF(D2:D9,"<9e307")
注:”9e307”表示一个非常大的数字,小于这个数字相当于对所有数值求和。
2、错位求和
如图所示,我们要求各商品1-3月的销量之和。
三、SUMIFS函数
SUMIFS(数字区域,条件区域1,条件1,条件区域2,条件2....)
1、多条件求和
=SUMIFS(C:C,B:B,F2,A:A,E2)
2、加绝对引用的多条件求和
3、SUMIFS函数数组公式求和
计算一班语文和数学的总分数。
4、隔列求和
如下图所示,每个日期下面,有一列购买订单、一列购买金额;
四、SUMPRODUCT函数
SUMPRODUCT函数是一个数学函数,用于在给定的几组数据中,将数组间对应的元素相乘,并返回乘积之和。
下面,我们用一个简单的例子来说明函数的基本功能:
一些巧用:
1、带单位的求和
如果单位统一的话,可以用下面这个公式:
=SUMPRODUCT(--SUBSTITUTE(C2:C21,"元",""))
延伸一下:
情况1:如果单位不一致,但单位的长度一致。
如图所示,我们需要将赠品数量的份数提出来。(不管是盒还是包,都为一份赠品)
在D15输入公式:
=SUMPRODUCT(LEFT(D2:D14,LEN(D2:D14)-1)*1)
2、按月求和
比如,我们需要统计3月的销量,在F2输入公式:
=SUMPRODUCT((MONTH(A2:A11)=3)*D2:D11)
五、其他公式
1、筛选排名进行求和
我们只需要选择一个名次,就能对前几名的销量进行自动求和。
公式很简单=SUMIF(D:D,"<="&H2,F:F)
2、可见求和
9表示包含隐藏值求和,109忽略隐藏值求和。
六、烧脑题
最后,再给大家推荐个大神写的求和公式,不保证一定能看懂哟!
题目很简单,小学生级别。
如图所示,我们需要算出这些文字中的百分比。在单元格C2中输入公式:
=SUM(IFERROR(--MID(TEXT(RIGHT(TRIM(MID(SUBSTITUTE( "ss"&B2, "%", "%"&REPT( "",99)),99*COLUMN(A:D)-98,99)),ROW( $2: $6)),),2,99),0))输完后,按三键(CTRL+SHIFT+ENTER),并向下拖曳即可。
如果您对Excel函数还不是很熟悉,扫码关注Excel教程视频号,有各种函数用法小案例,可以学起来。
六姑娘碎碎念:好啦,以上就是今天给大家归纳整理的 35个求和公式,涉及多个工作场景。原创整理不易,希望大家 点赞、在看、评论三连哟~~~
免责声明:本文为转载,非本网原创内容,不代表本网观点。其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。