如何用简单的IF函数与数据透视表搞定复杂的报销金额核对问题
作者：郅龙来源：部落窝教育发布时间：2021-09-26 14:31:29点击：5151
编按：
Hello Everyone~我们经常说精通Excel可以帮助我们提高工作效率，减少加班！但精通Excel不是一朝一夕的事情，当我们Excel水平是只会简单的IF函数和数据透视表的情况下，就完全不能解决工作中复杂的问题了吗？当然不是！用不好Excel也许不是你技术不行而是思维被局限了！今天我们就来学习一下如何用简单的IF函数与数据透视表搞定复杂的报销金额核对问题。
小刘是某公司财务勤杂工，主要负责整理各项报销申请，每天的工作就是核对每个报销单的申请金额与实际报销金额是否一致。简化后的数据如图所示。
核对报销常见的就三种情况：
①是实际报销金额可能会略高于申请金额，不超过一定比例也是认可的；
②是实际报销金额小于申请金额；
③则是最常见的申请金额与报销金额一致。
8月的报销核对完后，经理吩咐小刘，把8月份的数据按照三种情况标一下，他要看看。8月份一共两百多张单子，小刘用了半小时就搞定了，然后找经理汇报。
“经理，标好了，红色是实际报销金额高于申请金额的，黄色是实际报销金额低于申请金额的，没有颜色的就是相等的。200多条数据，我用了16分钟做好，又花了10分钟检查，剩余4分钟我还抽样了，保证没问题！”做好的表格大概就是这样子。
经理打开表格扫了几眼，冲小刘说：把实际高于计划的单数以及高出的金额算出来。小刘回去就开始折腾，看了几分钟实在是头大，因为数的眼睛都疼了，也没统计出来，结果老是对不上。于是就开始百度，也无结果，实在没办法了就在微信群里发了求助信息：“各位大神，老师们，请问如何通过颜色来汇总啊？”
大神们倒是很热情，抢着回复：VBA啊、宏表函数啊、颜色筛选啊，辅助列啊。
听的小刘一脸懵逼，没办法，还是老老实实按计算器吧。又30分钟过去了，结果终于正确了，赶紧回复经理：一共91单，金额是12995。
接着经理又陆续提出这样几个要求：
低于计划的和等于计划的各是多少单？
公司规定实际报销不能超过计划金额5%的，如果超了最多按5%报销，看下有多少单超标了，超标的金额一共是多少？
最好再按周统计一下，每一周的单数和金额？
……
场景是不是很熟悉？那么问题来了，你觉得小刘多久才能完成这些工作呢？我猜小刘晚上加班是肯定了，至于多久，不好说，也许是第二天下午了吧。
问题出在哪里？是经理的要求太麻烦吗，还是小刘的技术太菜了？都不是！就拿这个简化后的数据来说，经理的要求都是很常规的，如果加上报销人、报销部门、报销事由等等信息，可以统计的就更多了。
是小刘的水平太菜了？是也不是，因为小刘一开始就选择一种注定是低效率的方法：标颜色。标颜色这种操作给人的就是直观的视觉感受，但要想进一步做统计，基本也只能靠视觉，也就是靠眼睛来看了。与之类似的低效率操作还有合并单元格。
那么，什么方法才是高效率的呢？首先要明确一点，涉及到数据统计的问题，就得用适合数据统计的形式呈现。在Excel的表里，数据统计最基础的就是对字段，也就是列。具体的统计方式就是计数或者求和。所以，如何在数据源里得到统计所需要用到的列，才是解决问题的关键。
其实实现起来并不难，就是一些辅助列的事情。按照小刘所掌握的函数，构造辅助列是足够了，就是一个IF的事情。之后的数据统计靠数据透视表就能完成，这也是小刘会做的。
对于仅仅会用几个基本函数（例如IF）和拖来拽去的数据透视表的小刘来说，怎样才是正确的操作方式？
首先要做的就是基础数据的完善，通常可以结合自己的业务经验、公司的管理要求、领导的习惯这几个方面入手。结合自己的业务经验来说，首先是对单据做基本的分类标注：
①实际大于计划：实际报销金额可能会略高于申请金额，不超过一定比例也是认可的；
②实际小于计划：是实际报销金额小于申请金额；
③实际等于计划：则是最常见的申请金额与报销金额一致。
这个用IF函数很容易就能做到，公式为：=IF(D2>C2,"实际大于计划",IF(D2=C2,"实际等于计划","实际小于计划"))
接下来，按照公司规定：实际报销不能超过计划金额5%的，如果超了最多按5%报销。所以继续用IF函数来判断，是否超标。公式为：=IF(D2/C2>=1.05,"是","否")
判断完是否超标以后，就应该想到还需要算出审批金额，也就是超了最多按5%报销，没超就按实际金额报销。还是用IF来实现，公式为：=IF(F2="否",D2,C2*1.05)
到这里已经添加了三个辅助列，分别是按照自己的业务经验和公式的相关规定设计的，那么领导的习惯呢？对于超标的单据，超过了多少金额是不是应该考虑加上，万一领导问起来就不用返工了。要算出超标的金额，连函数都不需要，直接用实际金额-审批金额就行。
好了，暂时想到可以完善的数据都添加了，就等着看领导有什么要求吧，可以冲杯咖啡休息一下了……
领导：小刘，统计一下实际大于计划、实际等于计划和实际小于计划的各有多少单，多少金额。
小刘：好的，马上就有结果。
动手点几下鼠标就搞定了，真爽，继续喝咖啡。
五分钟以后
领导：小刘，统计一下实际大于计划里超过5%的有多少单。
小刘：好的，马上就有结果。
动手点几下鼠标就搞定了，真爽，继续喝咖啡。
又过了五分钟
领导：小刘，统计一下符合标准的报销金额，还有实际报销一共超了多少。
小刘：好的，马上就有结果。
动手点几下鼠标就搞定了，真爽，继续喝咖啡。
又过了五分钟
领导：小刘，按周统计一下审核后的报销金额。
小刘：好的，马上就有结果。
动手点几下鼠标就搞定了，真爽，继续喝咖啡。
领导：小刘，最近效率很高啊，继续加油，我会把你的表现告诉老板的。
小刘：谢谢领导的肯定，我会继续努力的。
小刘心里美美的继续喝咖啡……
像小刘这样的表哥表姐们还有很多，甚至会的比小刘更多，但是却没有小刘这样的效果，为什么？
三个建议给大家：
第一，不要利用一些基础的操作来解决复杂的问题。比如最常见的就是利用筛选来统计，利用颜色来标记与统计，利用奇奇怪怪的方法来实现数据匹配等等；
第二，统计就用数据透视表。学透视表是干嘛用？就是为了统计数据！
第三，灵活使用辅助列方法。配合透视表做统计的时候，如果没有需要统计的字段，那这个时候使用辅助列是最好了，另外还有其他的一些情况需要我们灵活使用。
Excel用得不好真的会加很多班！很多时候真的不是你的技术不行……
本文配套的练习课件请加入QQ群：902294808下载。
做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！
扫下方二维码关注公众号，可随时随地学习Excel：
相关推荐：
版权申明：
本文作者郅龙；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。