Excel里的单元格颜色填充是一项最基础的功能，可以填充某种颜色对单元格进行强调，或者利用特定颜色来标识数据结构，如标题行啊，汇总行等等。这样可以得到比较美观的表格。

但是不少人由于缺乏系统学习，对这些基础的功能上手就来，感觉Excel的使用简直so easy!

所以经常利用这些基础的功能来面对一些本不是这些功能场景的问题。

比如，下面这个典型的对账单问题。

小张是某公司财务勤杂工，负责整理各项报销申请。一项最主要的工作就是核对每个报销单的申请金额与实际报销金额是否一致。

一般来说可能存在3种情况

1是实际报销金额可能会略高于申请金额，不超过一定比例也是认可的；

2是实际报销金额小于申请金额；

3则是最常见的申请金额与报销金额一致。

简化后的数据如下，保留了较少的信息。

现在老板过来跟小张说，“小张啊，3种情况给我标一下，我要看一看”。

这不复杂嘛，标出来3种情况。说干就干！

30分钟后

“老板，标好了，红色表示高于申请金额的，黄色表示低于申请金额的，没有颜色的就是相等的。180多条数据，我用了16分钟做好，又花了10分钟检查，剩余4分钟我还抽样了，保证没问题！”

老板看了一眼，又说，“把那个实际高于申请的单号总数给我看看，哦还有高出的金额”。

又过了15分钟

为什么是15分钟呢，因为数的实在眼睛疼，而且数了好几次，数量居然对不上，这很尴尬了。

所以小张在网站上啊，或者Excel学习交流群里，求助

“各位老师，请问，如何根据颜色来求汇总啊”

然后有“大神”给出各种解答方法，什么颜色筛选啊，什么VBA啊。但是萌新一看，我还是算了，我再仔细按一遍计算器吧。

又过了半小时

“老板，实际报销高于申请的一共有75条，实际报销金额合计是224421.86元”

“哦，那再看下低于申请的，和与申请金额一致的有多少条。”

“好的”

“还有，我们规定实际报销超出申请金额5%，最多只能多报销5%的，你给我再把这个分一下，看看哪些单号超出5%了”

“额，好的”

“另外，把我按照1-7号，8-14号，这样按照周次汇总一下，也是单号数和金额数。”

“恩。额。。啊。。。行。。。的吧”

你猜这次应该是几个小时后老板能看到需要的结果。

我猜是过了一个加班夜的第二天。

那么，是老板的问题太多了么？

并不是，这些需求，在现有的数据里再正常不过了，如果加上一些其他字段，比如申请部门，申请人，申请事由啊，那岂不是更麻烦了。

那么，问题的关键点在哪里呢？

其实就是，一开始选择标颜色就是一条极其错误的道路。标颜色这种操作给人的就是直观的视觉感受，而这种视觉的感受，要想进一步做统计，则基本也只能靠视觉，也就是靠眼睛来看了。

所以，解决这个问题的关键是什么？

首先可以确定的是，这种统计类的问题，肯定不能是视觉的展现，那就要用到统计形式的展现。而统计，在Excel的表里，最基础的就是对字段，也就是列的计数或者求和。

所以，如何得到老板需求里能用到的列，才是解决问题的关键。

其实实现起来并不难，就是一些辅助列的事情。

而这些辅助列，利用最基础的公式就可以完成。

1、 分类别

按照“实际大于申请”、“实际小于申请”、“实际等于申请”来分成三类，这样后续统计就很方便。这里用到了IF的嵌套，非常基础的公式。

二、金额是否超5%

这里也是用到IF，写法很多，学过IF的都会写。这样就可以利用是否来统计实际报销金额超出申请5%的数量了。

三、确认报销金额

确认的报销即按照超出5%作为上限，其它为实际报销金额来确认，同样还是简单的IF函数。

四、确认超出金额

确认超出金额，也就是确认报销金额减去申请的。这里讲报销金额小于申请的不做处理，可以统计整体的报销与申请是否基本一致。

好了，做好了辅助列，我们就可以利用透视表来统计数据了。统计数据就用透视表，这句看起来非常简单的话，操作起来不少人会忘在脑后。

1、 统计三类的数据和金额

怎么拖透视表我就不说了，只需要点几下即可实现。

2、 统计实际大于申请的里超过5%的数量

分类拖到筛选，然后就是这么简单。

3、 确认后的报销金额

可以看出来实际报销金额比确认的仅高几百，说明没有超比例特别高的。

4、 按照周次统计

这里只需要将日期拖到行，然后按照7天进行分组即可。

日期分组到周的操作是这样的。

5、 总的确认超出金额

为了查看5月总的确认后的报销金额与申请金额是否平衡，可以把确认超出金额拖到值，然后分类拖到行可以查看分类的情况。

这里可以看到超出的金额是略高于实际小于申请的，这样5月份总的报销金额将会比申请的高出771.23元，考虑到5月份总的报销金额将近50万，所以这个数字几乎无影响。

以上只是针对当前较少的字段建立的一些辅助列，进行的一些基础的统计。如果碰到实际情况，可能需要建立的辅助列，或者进行的汇总会相应复杂一些。

然而，最后需要特别强调的是，我们在这篇文章里介绍的并不只是这样一个案例，而是以下几个重要的解决问题的思路。

  • 一是，不要利用一些基础的操作来解决复杂的问题，比如最常见的就是利用筛选来统计，利用颜色来标记与统计，利用奇奇怪怪的方法来实现数据匹配等等；
  • 二是，统计就用数据透视表，学透视表是干嘛用，就是为了统计数据，学透视表是干嘛用，就是为了统计数据，学透视表是干嘛用，就是为了统计数据。
  • 三是，辅助列方法一定要灵活使用，配合透视表做统计的时候，如果没有需要统计的字段，那这个时候使用辅助列是最好了，另外还有其他的一些情况需要我们灵活使用。

Excel用得不好真的会加很多班！

Excel用得不好真的会加很多班！

Excel用得不好真的会加很多班！