平平无奇SUM函数,10种基础用法 3种特殊用法,总有一个你还不会!
哈喽,大家好。
欢迎来到excel教程函数课堂,今天介绍SUM函数。
SUM函数很神奇,作为Excel里最基础的函数,几乎所有的人都会用,但这个函数又强大得令人难以想象,很多人掌握的知识仅仅只是冰山一角。
今天,我们就由浅到深地撕开SUM函数神秘的面纱,解决平常你认为它绝对做不到的问题。
一、SUM函数语法和基础用法
先来看看基础语法:
SUM(number1,[number2],...)
参数最多可添加255个,每个参数都接受常数,单元格,单元格区域;参数之间用英文逗号分隔。
每个参数也可以是计算表达式,或者结果是数字的公式。(太多的人不知道这点。)
如果是多个参数混合求和,可以先选择某个参数区域,然后按住ctrl键不放,再选取其他区域。
基础用法示意:
二、 SUM函数经典用法
1.带有小计的求和
直接在C18单元格输入公式=SUM(C2:C17)/2
2.求累计之和
在D2单元格输入公式=SUM(C$2:C2),然后下拉即可。
3.合并单元格的求和
三、用SUM函数条件求和
例如,下图所示,当我们需要求大于10000的业绩之和,以及业绩大于6000的女销售业绩总和。
公式语法:
=SUM((条件1)*(条件2)*…*(求和区域))
提示:数组公式,不是Excel 365、Excel 2019及以上版本,需要按Ctrl Shift Enter结束。
再譬如按月份求和,输入公式 =SUM((MONTH(A2:A19)=D2)*B2:B19)
SUM条件求和的优势:
当数据从系统导入,默认为文本格式,使用SUMIF进行条件求和会出错,而SUM的数组公式会自动忽略文本,省略清理数据这一步,更加方便高效。
四、用SUM函数条件计数
如下图所示,当我们需要统计业绩大于10000的(女性)人数,依然可以用到SUM的数组模式。
公式语法:
=SUM((条件1)*(条件2)*…*1)
五、用SUM函数实现多种动态求和
1.求排名前N的业绩总和
如下图所示,当我们需要求排名前三的业绩总和,可以先借助ROW函数生成一个{1,2,3}的序列,再用LARGE函数取前三名的值,最后用SUM函数来求和,数组公式记住三键完成输入。
如果需要动态设置排名,可以再借用INDIRECT函数,用“"1:"&E7”形式,构造了一个动态的引用,作为ROW函数的参数。
2.可以任意删除或插入行的动态求和
输入公式:=SUM(C2:INDEX(C:C,ROW()-1))
使用ROW()-1确定总计上的行号,再嵌套INDEX函数,获取上一个单元格中的内容,当起始单元格与结束单元格都确定时,即可进行求和了。
当插入新的行或者删除行时,Excel依然能够自动汇总。
关于INDEX函数的用法,可以戳此文查看:INDEX:函数中的精确制导导弹,最强大的瘸子
3. 根据月份自动汇总1-N月份之和
选取1月,显示1月份的值
选取5月,汇总1-5月之和
选取12月,汇总1-12月之和
输入公式=SUM(OFFSET($B2,,,,MATCH($N$1,$B$1:$M$1,0)))
这里我们使用offest函数来生成动态区域,即根据N1的月份来生成由1月到N月的区域。假如N1等于6月,那就是求B列到G列的和。
offset(单元格,行偏移数,列偏移数,总行数,总列数)
列数如何得来?我们可以用MATCH函数来查找= MATCH($N$1,$B$1:$M$1,0)
关于MATCH函数的用法,大家可以戳以下链接查看:
MATCH:函数哲学家,找巨人做伴。新出道必学!
六、SUM函数带单位求和
用SUBSTITUTE函数将“元”替换为空,再用双负符号将文本格式转换成数值,同样是数组公式,记得按Ctrl Shift Enter三键结束。
最后,针对这个函数的注意事项,再来唠叨几句。
如果参数为引用单元格、区域,只有其中的数字将被计算。引用中的空白单元格、逻辑值、文本将被忽略;
如果是文本类型数字,可以使用两个负号强制将文本型数字转化为数字(注意双负号用于纯文本会出错);
如果参数中有错误值或为不能转换成数字的文本,将会导致错误,可以使用IFERROR函数来忽略错误值。
下面用一张示意图来说明:
没有求不了和的数据,只有写不出公式的人。大胆假设,小心求证,假以时日,你一定会将SUM使用得炉火纯青。
用分列太笨,复制太慢!这才是No.1的Excel分行神技
VIP学员的问题,左边的员工用逗号隔开并写在一个单元格,现在需要计算人数、平均工作量,最后按员工拆分成右边的效果。跟卢子来看看怎么解决。1.人数=LEN(E2)-LEN(SUBSTITUTE(E2,",",))1LEN(E2)就是计算总字符数。SUBSTITUTE(E2,",",)就是将逗号替换掉。站长网2023-07-28 14:35:060000微信转账时出现这行字,记得不要输入密码,看完记得告诉家人
站长网2023-07-28 14:05:150000天呐!不愧是精通电脑的高手,原来早就把键盘上的功能全搞清楚了,并
天呐!不愧是精通电脑的高手,原来早就把键盘上的功能全搞清楚了,并且还详细写了出来,让人一看就懂,不得不说即使是电脑小白,也能看清楚明白。建议大家保存下来,尤其是办公室白领、学生或者是经常需要使用的人,尽早把这些快捷键和按键功能背书,不但可以快速提高工作效率,还能彻底解决日常遇见的问题,助你成为电脑高手,瞬间让你在同事与同学当中脱颖而出。站长网2023-07-30 13:22:020000【知识技术】使用ps制作3d立体高光小人?
使用Photoshop制作3D立体高光小人需要借助其3D渲染功能。以下是一些基本的步骤:打开Photoshop并创建一个新的文档。在菜单栏中选择“3D”>“从文件加载3D模型”,选择一个小人模型文件。在3D编辑器中选择“场景”选项卡,然后选择“材质”选项卡。在材质属性中调整高光的强度和反射属性,以获得所需的高光效果。站长网2023-07-27 11:02:010000财务报表分析中的财务报表审计(二)
各位学友们,大家好!上一篇介绍了证券财报公告财务报表中财务报表审计的基本情况,这一篇介绍资产负债表、利润表、现金流量表、所有者权益变动表表内科目勾稽,专业的企业财务人员是不会在财务报表的表内出错的,但是普通投资人员可以通过财务报表表内勾稽的准确,分析出企业财务人员对财务工作的态度。资产负债表T字表格式,左边增加记借,右边增加记贷。站长网2023-07-28 14:50:180000