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

SUM函数的进阶用法:快速对交叉区域、应收款项、小计行求和!

 

作者:小花来源:部落窝教育发布时间:2020-06-02 16:08:52点击:5257

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

编按:

哈喽,大家好!平时我们用SUM函数一般都是处理一些简单的求和问题,今天我们要给大家分享几招SUM函数的进阶用法:快速对交叉区域、应收款、小计行自动求和。保证让你眼前一亮,赶紧来看看吧!

 

Excel函数家族树大根深,枝繁叶茂,但若要按使用频率高低排个序,那唯一能和IF函数一争高下的,恐怕只有SUM了。我们会在很多不经意的地方用到它,比如自动求和,各种总计小计等等。SUM函数的基本用法非常简单,=SUM(求和范围),几乎人人都会用!但你知道吗,SUM函数的小宇宙也有大爆发的高光时刻。不信?一起来瞧瞧,SUM的这些牛皮用法,你会吗?

 

 

案例一:交叉区域求和

 

 

什么是交叉区域,就是两个不同单元格区域的重叠的部分(交集)。例如下图所示,单元格区域A5:G8是华南区不同产品的销售情况,单元格区域D2:E2B系列产品在不同区域的销售情况,这是两个不同的单元格区域,他们的交叉区域是D5:E8单元格区域。当我们要对华南区域B系列产品销售额进行求和时,就是要对A5:G8D2:E2单元格区域的交叉区域求和。

 

 

这种对交叉区域求和的公式应该怎么写?很简单,只要给SUM函数一个空格,它就能轻松帮你搞定。

 

=SUM(D2:E11 A5:G8)

 

 

公式说明:

 

空格是Excel引用符之一,和冒号(表示连续区域引用)、逗号(表示并列区域)引用不同,空格表示两个单元格区域的交集。所以SUM(D2:E11 A5:G8)就表示对A5:G8D2:E2单元格区域的交叉区域求和。

 

 

案例二:应收款管理

 

 

财务的表哥表姐们,对应收款项的管理怎能不熟悉?销售收款政策产生了分月应收明细,而财务实际收款通常无法直接与之一一对应。但为了对应收款的账龄进行分析,我们又需要区分每个月应收款的实际收取情况,形成未收明细和逾期账龄明细。具体案例如下图,应收客户账款总额200万,对应各月份到期账款如下,截止目前已收款85(收款金额动态变化),目前需计算得出各期应收款催收情况。

 

图片

 

一边是应收款,一边是实收款,通往未收账款明细的道路在哪里?人工计算填写?IF多重嵌套?别迷茫,SUM披荆斩棘,踏马而来,快快把它收入你的技能包吧!

 

=MIN($F$1-SUM($A5:A5),B4)

 

 

公式说明:

 

该公式十分简短,其精髓不在函数本身,而在于对应收未收款计算逻辑的理解。

 

1.假定先欠先还,以3月为例,3月份款项收回的前提是已收款总额大于往期月份应收款的总额,换句话说,已收款总额优先用于填补往期应收款,有剩余可偿还金额方能用于填补本期应收款,这个可偿还金额就是$F$1-SUM($A5:C5)。这里将SUM求和范围的初始单元格锁定,结束单元格定为当前单元格左侧的一个单元格,就表示已收取往期应收款的总额。

 

2.另一方面,偿还本期账款的金额不能大于本期应收款,于是我们使用MIN函数在可归还额度和应收账款之间取得最小值,确保不会出现超额收取的情况。

 

3.MIN函数的使用也确保了每一期已收款额都不会大于可偿还额,因为$F$1-SUM($A5:A5)的最小值为0,递推,已收账款(MIN函数返回值)一定不小于0,所以不会出现已收账款为负数的情况。

 

综上,应收账款管理问题的完美解决方案就是MIN+SUM的函数组合,这是一个逻辑的结晶,无关公式难易,建议多看几遍,定有助于举一反三!

 

 

案例三:批量自动求和Alt+=

 

 

在实操中,SUM函数最常应用的场景就是对行列的小计求和,就像下图这样,我们需要根据销售情况按行合计,形成每个人的销售总额,同列总计形成各产品的销售总额,如何快速设置下图红色方框内的单元格求和公式呢?

 

 

字门门人相信,没有什么求和是一个加法解决不了的,如果有,就用N个。

 

刀帮帮众坚持,只要写好一个公式,拖动填充能围绕地球两圈。

 

不管你是字门还是刀帮,小花接下来祭出的这个大杀器,一定会让你放下屠刀,立刻出家

 

选中求和区域和结果区域,即C2:G12,接着按Alt+=,行列求和一秒搞定!

 

注意:结果区域必须为空值,否则将影响自动求和技巧的使用。

 

 

上述案例过于基础,无法充分体现Alt+=的神奇力量,为此,我们需要加点难度,即分区域分产品求和,如图。我们需要对红色区域进行求和,Alt+=是否也能一键搞定?

 

 

答案显而易见,能!但需要Ctrl+G定位技巧的配合,Alt+=才能发挥作用,我们来看具体操作。

 

操作步骤:

 

1.选择C1:G15单元格区域,按Ctrl+G,弹出【定位】对话框,点击【定位条件】按钮,弹出【定位条件】对话框,选择【空值】,点击【确定】,即可快速选中上图中的红色区域;

 

2.再按Alt+=,即可一键求和,真的再便捷不过了,NICE

 

 

The End

 

SUM函数的高级用法,我们就先分享这三招,希望对小花瓣们的工作能有所帮助。你还知道哪些与SUM函数相关的独门绝技,欢迎留言与我们分享,下篇我们继续深挖SUM函数,敬请期待!

 

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

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

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

IMG_256

相关推荐:

合并单元格求和《双十一到底要花多少钱?一张Excel表格,让你看得明明白白!》

求和函数大汇总《求和,我是认真的(Excel函数教程)》

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

自动求和小白最爱的自动求和,恰是连专家也坑的老虎