二维码 购物车
部落窝在线教育欢迎您!

心疼!小姐姐加班到深夜,竟是为了这两项合计!

 

作者:老菜鸟来源:部落窝教育发布时间:2019-04-03 09:15:03点击:1484

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

昨晚回到家,我的小表姐就开始折腾她的表格,一直忙到凌晨都不见她休息,我凑过去看了看,发现她是为了得到某两项合计,在一项一项的对比数据。其实求这两项合计,根本不需要这么繁琐,只要用对了公式,分分钟钟就搞定了呀!

一年的销售数据整理完了,除了要看到每个人的销售合计之外,老板今年还要看到图中这两项合计:


销量最高的三个月合计是指汇总每人一年中,销量最高的三个月的数据。

超过平均值的销售合计是指汇总超过总平均值的月份销量。

两项合计需要一个一个去比较后再求和吗?若一个个比较,用时一个小时也不能算好。这可难住了小表姐。

其实这两项求和不难,下面就给大家分享这两种求和的套路。


1.汇总销量最高的三个月的数据


这个问题首先是找出销售额排名前三的数据,然后再将数据进行求和就行了。

我们都知道在EXCEL中,求最大值用MAX,求最小值用MIN,除此之外,还有两个非常实用的求最大、最小值的函数——LARGE函数和SMALL函数。

LARGE函数可以在指定的数据区域中,返回指定的第几大的值。

格式:LARGE(数据区域,第几大的值)

例如:=LARGE(B2:M2,3),会返回B2:M2区域中第三大的值。


除此以外,这个函数还可以找到数据区域中若干个最大值,例如找前三名的值,可以将第二个参数写成常量数组的格式:

=LARGE(B2:M2,{1,2,3})

为了大家看起来更直观,我们将前三名的值用颜色标注:


在上图O2单元格只能看到销量最高的1月的数据,是公式出问题了吗?

公式使用常量数组后,结果虽是一组数据,但在单元格中只能显示出这组数据中的第一个值。要想看到每个数据,可以在编辑栏选中公式后,按F9键:


可以看到前三名的值都出现了。

注意:使用F9之后不要按回车键,要按ESC键返回。

SMALL函数与LARGE函数的用法是完全一样的,我们也可以利用SMALL函数得到最小的几个值。

找到了最高的三个值,我们再使用SUM函数求和即可:=SUM(LARGE(B2:M2,{1,2,3}))


下面来看第二个问题,超过平均值的销售合计。


2.超过平均值的销售合计


这项合计涉及到平均值,就一定要用到求平均值的AVERAGE函数。使用AVERAGE(B2:M17) 可以得到每月的平均值:


现在问题就明朗了许多,其实就是条件求和。说到条件求和,当然少不了SUMIF函数。再来复习一下这个函数的基本用法吧!

格式:SUMIF(条件区域,条件,求和区域)

函数的格式很简单,但在这个例子中,对于SUM函数第二参数的写法是个难点,也许你会这样写公式:=SUMIF(B2:M2,>AVERAGE(B2:M17),B2:M2),得到的结果是这样的:


那这样写,=SUMIF(B2:M2,”>AVERAGE(B2:M17)”,B2:M2)


倒是不报错了,可结果不对啊,变成0了……

好吧,不卖关子了,正确的写法是这样的:=SUMIF(B2:M2,">"&AVERAGE(B2:M17),B2:M2)


在这个公式中,第二参数用到了比较运算符于号>,还有平均值函数AVERAGE(B2:M17)。在SUMIF中,运算符需要加引号,如果是与一个具体的数值比较的话,这样写是没有问题的:=SUMIF(B2:M2,">20424",B2:M2)。但当要比较的对象是一个函数时,那就不行了,因为函数是不能加引号的。此时需要用文本连接符&连接函数,因此第二参数的正确写法就是">"&AVERAGE(B2:M17)

同时SUMIF函数还有个特性,当求和区域与条件区域相同时,第三参数可以省略,这个公式最终可以写成,=SUMIF(B2:M2,">"&AVERAGE($B$2:$M$17))AVERAGE函数中的数据区域要绝对引用:


最后来总结一下今天的收获:

1)使用LARGESMALL函数可以返回指定数量的最大值或最小值。函数的第二参数使用常量数组时,常量数组的大括号需手动输入,而不能三键产生。

2)使用SUMIF函数时,如果第二参数同时出现运算符和函数,运算符要加引号,再使用&连接该函数。当条件区域和求和区域相同时,可以省略第三参数。

今天的教程就是这么多,咱们下期再见。

 

本文配套的练习课件请加入QQ群:264539405下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

DSUM条件求和《DSUM,最简单的条件求和函数!你知道不?

快速核对数据的方法再因为核对数据而加班,买块豆腐吧!难道12种方法不够你用?!

Excel跨表求和《Excel教程:Excel跨工作表求和的四种方法