只知道用SUM函数求和,那就十分OUT了!
点击上方
蓝色
文字 关注我们吧!
送人玫瑰,手有余香,请将文章分享给更多朋友
动手操作是熟练掌握EXCEL的最快捷途径!
大家都知道,SUM函数是求和函数,每一个使用EXCEL的人都会使用它。
但同时,SUM函数又是一个令人着迷的函数,它有着太多的不为人知的东西...
累计求和
在此技巧中,关键是将区和区域的起始单元格位置固定,即可完成累计求和了。
多工作表同一位置求和
在单元格B2中输入“=SUM(Sheet2:Sheet4!B2)”并向下拖曳即可。
多工作表同一位置求和
TIPs:其实输入“=SUM('*'!B2)”也是可以获取正确答案的。不过请注意:用此方法是求和工作簿中所有工作表相同位置的数据和。
条件计数
谁说SUM函数只能用来求和的?SUM函数也可以用来计数!
在单元格A10中输入“=SUM(--($B$2:$B$6>4000))”,CTRL+SHIFT+ENTER回车即可。
条件计数
TIPs:SUM函数也可以多条件计数。如此例。
条件求和
什么?条件求和不是SUMIF函数的职责吗?不,请看SUM函数是如何做的!在旁边看哭了SUMIF函数和SUMPRODUCT函数!
在单元格A10中输入“=SUM(($B$2:$B$6>4000)*(B2:B6))”,CTRL+SHIFT+ENTER回车即可。
条件求和
TIPs:同样地,SUM函数可以多条件求和。
可以为SUM函数提供求和条件的函数还包括了LARGE函数,SMALL函数等等。
SUM函数和SUMIF函数组合
SUM函数和SUMIF函数组合可以产生什么样的效果呢?
在单元格A14中输入“=SUM(SUMIF($A$2:$A$11,{"一月","二月","三月"},$B$2:$B$11))”并CTRL+SHIFT+ENTER回车即可
思路:
利用SUMIF函数首先求出一到三月的数量
SUM函数求出总数量
合并单元格求和
如下例,需要在C列的合并单元格中输入公式求相对应月份的总计。
选中单元格区域C2:C13,然后输入“=SUM(B2:B13)-SUM(C3:C13)”,CTRL+ENTER回车即可。
思路:
SUM(B2:B13)部分,是正常的单元格,所求为所有数据的总和
SUM(C3:C13),由于是合并单元格,二月份的汇总会记录在单元格C4,其余的同理:三月份在单元格C7;四月份在单元格C8;五月份在单元格C11
SUM(B2:B13)-SUM(C3:C13)实际上就是减掉了单元格C3以下部分的值,正好是一月份的合计
其余单元格的原理类似
此技巧需要先选中所有单元格区域后再书写公式。为屏蔽错误,此公式还可以将单元格B13和单元格C13采用绝对引用。
数组应用-中国式排名
在中国式排名中,无论有几个并列,之后的排名仍然顺位排列。即无论有多少个第二名并列,之后的排名仍然是第三名。
在单元格C2中输入“=SUM(--IF($A$2:$A$11>=A2,MATCH($A$2:$A$11,$A$2:$A$11,0)=ROW($2:$11)-1))”,CTRL+SHIFT+ENTER回车并向下拖曳即可。
思路:
MATCH函数返回{1;2;3;3;5;5;7;8;9;9};ROW($2:$11)-1返回{1;2;3;4;5;6;7;8;9;10};MATCH=ROW对比的结果是{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE}
$A$2:$A$11>=A2返回的结果是{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE},它表明在$A$2:$A$11中有10个数据大于等于A2中的数据
利用IF函数逻辑值判断为真时,返回上面MATCH=ROW相比的结果{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE}
利用“--”运算得到{1;1;1;0;1;0;1;1;1;0}
利用SUM函数求和得到“7”,表示A2单元格中的数值排名第七名
其它单元格分析类似
当有重复值时,由于MATCH函数总是返回查找到的第一个值,因此重复值的排名总是和第一个出现的重复值相同。因此实现了中国式排名
有关于MATCH函数,详细的内容请参看帖子总结篇-MATCH函数使用终极帖。
文章推荐理由:
SUM函数是如此地简单、重要。但它仍有许多功能强大,但却并不为大多数人所熟悉!
-END-
长按下方二维码关注EXCEL应用之家
面对EXCEL操作问题时不再迷茫无助
推荐阅读:
听说正在评比万能函数,SUBTOTAL函数800里加急正在赶来
戳原文,更有料!免费模板文档!