你只会用SUM函数求和？大神都玩出新花样了!

回复[目录]学习113篇Excel教程

全套Excel视频教程，微信扫码观看
对于经常使用Excel办公的职员来说，SUM是一个简单的不能再简单的函数了，公司几乎所有的人都知道用这个函数可以求和。但是有一个文员小姐姐一连用SUM完成了三个并非求和的任务，就被经理看中了，直接将其提拔为自己的助理。到底发生了什么事呢？还得从头说起……
某日经理召集部门内的表哥表姐们做一次内部选拔，打算物色一名助理，为此准备了三个任务让大家逐一完成，小姐姐也在候选人当中。

任务1：使用SUM函数批量制作标签

这里说的标签其实是一种直接在Excel里录入后打印出来使用的小标签，如图所示：

需要按照B列的数字，做出对应数量的标签，要求很简单，不怕麻烦的话可以复制粘贴，当然如果是复制粘贴的话，大家都会，小姐姐当然不会这样做，请看：

在后面一列使用公式=SUM(B$2:B2)-ROW(A1)下拉，一直拉到出现0时停止，接下来就是一连串的操作：
①对C列按升序排序
②选中A列的数据区域，按快捷键F5定位，选择定位到空值。
③定位到空值后，直接输入“=A3”，按Ctrl+Enter完成填充。
旁边还在复制粘贴的同事瞬间被雷到……

公式很简单，就一个SUM函数和一个ROW函数，操作也很简单，排序定位加上批量填充的操作，但是谁让你就想不到呢？
想问公式的原理？简单的数学问题，实在想不通的话就把公式记下吧，我们赶快看第二个任务是什么。

任务2：快速按部门编写序号

在这个表格中需要对A列进行编号，规则是部门发生变化时序号才递增。
接到这个任务之后，大家又开始各自琢磨，有人开始尝试各种公式，有人开始琢磨用操作技巧完成，小姐姐直接用SUM秒杀：

公式够简单吧：=SUM(A1,B1B2)，利用了SUM忽略文本和可对逻辑值计算的特性，第二招出手，惊叹声一片，经理也无法保持淡定，直接发出了第三个任务。

任务3：计算阶梯返利额

按照公司的规定，要按照各经销商的年销售额进行返利，具体返利规则为：年回款200万以内返点5%，超过200到350万的部分返7%，超过350万到500万的部分返10%，超过500万到700万的部分返13%，超过700万的部分返17%。一共分为五个阶梯，举个简单的例子：

以经销商A来说，销售额是225.02万元，返还金额就是200*0.05+（225.02-200）*0.07，换个思路，还可以这样算：225.02*0.05+（225.02-200）*（0.07-0.05）

这还只是涉及到两级的算法，如果是五个级别都考虑的话……
在弄明白了计算方法以后，大家又开始埋头苦干。有一级一级往上叠加的，有开始嵌套if的，不管是什么方法，四级以后都有了眩晕感。此时经理又说了句，明年考虑把返利等级从五级调整到八级，以便计算时更加细化，一时间众人皆倒……
小姐姐不紧不慢的提交了自己写的公式，充满了套路的一个公式：
=SUM(TEXT((B2-{0,2,3.5,5,7}*100)*{5,2,3,3,4},"0.00;!0")%)

对于这个公式，经理也有点发懵，考虑到大家看到这个公式后的不同反应，对公式的要点进行解析：
1.B2-{0,2,3.5,5,7}*100，用客户年销售额分别减去0万，200万，350万，500万，700万；
2.TEXT((B3-{0,2,3.5,5,7}*100)*{5,2,3,3,4},"0.00;!0")%，是将第1步相减结果分别乘以5，2，3，3，4（这是相邻两个级别之间提成比例从差值），用TEXT将结果为负数的直接转化为0，再缩小100倍（%的作用）。
常量数组{5,2,3,3,4}的由来：200万内返5个点，超过200万到350万的部分返7个点，比200万内的返点多2个点，后来以此类推。TEXT函数第二参数"0.00;!0"，意指正数保留两位小数，负数直接转化为0。
3.SUM(TEXT((B3-{0,2,3.5,5,7}*10^6)*{5,2,3,3,4},"0.00;!0")%)，将第2步计算的各段返点金额加总，得到累计返点金额。
好吧，肯定还是有一大波人无法领会其中的奥妙，但不管怎么样，小姐姐是毫无悬念的脱颖而出了。
通过今天分享的这个故事，可以得到一个结论，Excel用的溜真的有钱途哦！

小结

1.案例一其实还有很多其他的解法，比如使用复杂的数组公式，还有使用REPT函数结合换行符后再用Word去完成本文提到的SUM解法，相对比较玄妙，思路过于奇巧，有用到这种问题的话可以直接套路搬走。
2.案例二也并不复杂，其实就是对部门进行不重复计数的公式，常见的是
=SUMPRODUCT(1/COUNTIF($B$2:B2,$B$2:B2))这个公式，本例中是对部门进行了排序，才能取巧的。
3.案例三就非常有用了，虽然公式比较难，好处是扩展性强，在遇到计算各种阶梯价格的时候对公式中的两个常量数组进行调整就可以直接套用。
Excel真的是博大精深，妙趣无穷。
扫一扫添加老师微信

扫一扫，在线咨询Excel课程

Excel教程相关推荐
SUM家族中的超级英雄，猜猜是谁？【Excel函数教程】SUM函数的取代函数SUMPRODUCT Sum,Lookup,Countif三个函数，仅知道还是能流利写出漂亮的嵌套公式
想要跟随滴答老师全面系统学习Excel，不妨关注《一周Excel直通车》视频课或者《Excel极速贯通班》。

《一周Excel直通车》视频课
包含Excel技巧、函数公式、
数据透视表、图表。
一次购买，永久学习。
[iframe]https://v.qq.com/iframe/preview.html?width=500&height=375&auto=0&vid=b05674i0j3h[/iframe]
最实用接地气的Excel视频课
《一周Excel直通车》
风趣易懂，快速高效，带您7天学会Excel
38 节视频大课
（已更新完毕，可永久学习）
理论+实操一应俱全

主讲老师：滴答

Excel技术大神，资深培训师；
课程粉丝100万+；
开发有《Excel小白脱白系列课》
《Excel极速贯通班》。

原价299元
限时特价 99 元，随时涨价
少喝两杯咖啡，少吃两袋零食
就能习得受用一生的Excel职场技能！

长按下面二维码立即购买学习

购课后，加客服微信：blwjymx3领取练习课件

让工作提速百倍的「Excel极速贯通班」
↓ 点击阅读原文，可直接购买。