对于经常使用Excel的办公职员来说，SUM是一个简单的不能再简单的函数了，公司几乎所有的人都知道用这个函数可以求和，但是有一个文员小姐姐一连用SUM完成了三个并非求和的任务，就被经理看中了，直接提拔为自己的助理。到底发生了什么事呢？还得从头说起……
某日经理召集部门内的表哥表姐们做一次内部选拔，打算物色一名助理，为此准备了三个任务让大家逐一完成，小姐姐也在候选人当中。
任务1：使用SUM函数批量制作标签
这里说的标签其实是一种直接在Excel里录入后打印出来使用的小标签，如图所示：
需要按照B列的数字，做出对应数量的标签，要求很简单，不怕麻烦的话可以复制粘贴，当然如果是复制粘贴的话，大家都会，小姐姐当然不会这样做，请看：
在后面一列使用公式=SUM(B$2:B2)-ROW(A1)下拉，一直到出现0时停止，接下来就是一连串的操作：对C列排序，然后选择A列区域，F5定位后输入=A3，Ctrl 回车完成填充旁边还在复制粘贴的同事瞬间被雷到……
公式很简单，就一个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巧用SUM函数＊＊＊＊
原创：老菜鸟/部落窝教育（未经同意，请勿转载）
更多教程：部落窝教育（www.itblw.com）
微信公众号：exceljiaocheng
网友评论