只知道用SUM函数求和,那就十分OUT了!

点击上方

蓝色

文字  关注我们吧!

送人玫瑰,手有余香,请将文章分享给更多朋友

动手操作是熟练掌握EXCEL的最快捷途径!

大家都知道,SUM函数是求和函数,每一个使用EXCEL的人都会使用它。

但同时,SUM函数又是一个令人着迷的函数,它有着太多的不为人知的东西...

01

累计求和

在此技巧中,关键是将区和区域的起始单元格位置固定,即可完成累计求和了。

02

多工作表同一位置求和

在单元格B2中输入“=SUM(Sheet2:Sheet4!B2)”并向下拖曳即可。

SUM

多工作表同一位置求和

TIPs:其实输入“=SUM('*'!B2)”也是可以获取正确答案的。不过请注意:用此方法是求和工作簿中所有工作表相同位置的数据和

03

条件计数

谁说SUM函数只能用来求和的?SUM函数也可以用来计数!

在单元格A10中输入“=SUM(--($B$2:$B$6>4000))”,CTRL+SHIFT+ENTER回车即可。

SUM

条件计数

TIPs:SUM函数也可以多条件计数。如此例。

04

条件求和

什么?条件求和不是SUMIF函数的职责吗?不,请看SUM函数是如何做的!在旁边看哭了SUMIF函数和SUMPRODUCT函数!

在单元格A10中输入“=SUM(($B$2:$B$6>4000)*(B2:B6))”,CTRL+SHIFT+ENTER回车即可。

SUM

条件求和

TIPs:同样地,SUM函数可以多条件求和。

可以为SUM函数提供求和条件的函数还包括了LARGE函数,SMALL函数等等。

04

SUM函数和SUMIF函数组合

SUM函数和SUMIF函数组合可以产生什么样的效果呢?

在单元格A14中输入“=SUM(SUMIF($A$2:$A$11,{"一月","二月","三月"},$B$2:$B$11))”并CTRL+SHIFT+ENTER回车即可

思路:

  • 利用SUMIF函数首先求出一到三月的数量

  • SUM函数求出总数量

05

合并单元格求和

如下例,需要在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采用绝对引用。

06

数组应用-中国式排名

在中国式排名中,无论有几个并列,之后的排名仍然顺位排列。即无论有多少个第二名并列,之后的排名仍然是第三名。

在单元格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里加急正在赶来

一对多查询?这里的方法是比较全的,值得你收藏!

涨知识了,筛选功能原来可以这样用!

你知道每个月你家交多少电费吗?

高级筛选?公式表示不服!

戳原文,更有料!免费模板文档!

(0)

相关推荐