Excel透视表格三个坑,让我抛弃了她

每月下旬，财务群里都会跳出一条：「郑哥，帮对一下车的数据。」

对的是四张表：销售明细、回款明细、车辆采购、车辆费用。它们来自不同的人、不同的月份，行标题还对不齐。我要把它们并成一张总表，算出每台车（我们按单车个别计价）的收入、成本、费用、毛利，再给老板拼一个看板：月度销量、销售额、毛利，还有每个业务员卖了多少。

这件事，我每个月都要干一次。

一、透视表，在我这场景顶不住

最开始我用透视表。后来发现自己踩了三个实打实的坑：

1. 数据源一更新，它不会自己动。得手动去点「刷新」，不点就还是上个月的旧数。一个月忘点一次，全组跟着错。

2. 想在结果上再算点东西，只能「插入计算字段」。不能直接在单元格里写公式，想加个毛利率都得绕一道。

3. 多张表、行标题还不统一的时候，它汇总不了。这是最要命的——我那四张表的车架号顺序根本对不上。

透视表不是不能用，是它管不了那种「会一直长、还长得不齐」的表。我手头那四张，恰好就是这种。

二、我先用手边的函数，拼了一套

我不想每个月重做一遍，就去翻函数列表。最后拼出来一套组合：通过灵活使用FILTER + UNIQUE +SUMIFS+VSTACK等函数。不靠透视表，让汇总表自己「长」出来。

这不是什么教程，是我被四张表逼出来的解法。下面是我当时怎么一步步搭的。

先想清楚要什么

场景是汽车销售，单车个别计价。要汇总的，是已经销售或已经回款的车的收入成本；没卖没回款的，先不体现。

要算的衍生列：车辆毛利、毛利率、是否收齐销售款。看板要的：月度销量、销售额、毛利，还有业务员销售情况。

表头怎么来

汇总表每一行是一台车，行标题得是「车架号+品牌 +车型」，而且不重复、按品牌排好。我的做法：

先用 VSTACK() 把三张表（销售、回款、费用）里的「品牌 + 车架号」合并在一起；

这时候发现表头位置有车辆是重复的，这时需要用使用 UNIQUE() 对数据去重；

同时替换表头字段。三步下来，汇总表的「骨架」就有了——一行一台车。

数据怎么填进去

1. 销售金额。

业务员，销售日期和金额不挨着，我需要先用 FILTER() 按车架号取对应对应的数据。空白格会蹦个 #CALC! 报错，给 FILTER() 加第三个参数、设个错误返回值为空白，就干净了。

再用 CHOOSECOLS() 把需要的列按要求提取并排列出来，

这里需要注意一点就是日期的数据格式需要设置一下 不然会显示成数值。

2. 回款和费用。

因为回款存在一台车的客户分多次回款情况，不能单纯使用刚才的方法获取数据，这是回款人需要用 UNIQUE()+FILTER() 取，

这里是没有使用UNIQUE()去重前的数据，这笔销售客户回款了2次，添UNIQUE()函数后就可以消除重复显示。

下来公式后发现还是有错误提示，有一台车它是两个人回款

这时，就需要使用TEXTJOIN（）函数吧两个人名合并在一起

金额用 SUMIFS() 求和；费用同理。

3. 车辆成本。

没有重复项的，直接 VLOOKUP() 取；要是有重复（比如暂估入库、退库又重新入库、多批次入库），就上 SUM()+FILTER()。遇到这种来回折腾的，公式比查表稳。

4. 成本合计、毛利和毛利率。

直接写加加减减公式，可以用数组公式一次拉一列；未销售的车辆，用 IF() 替换掉，别让它拖累整列。

三、它能用，但没那么轻

这套组合拳打下来，好处是真的实在：多表合并、数据加工都能干；FILTER 再配上 UNIQUE、TEXTJOIN、SUMIFS、COUNTA，基本把透视表常用的功能都覆盖了。

但我得说句实话，坑也在：

一、表一多，好几个 FILTER 套在一起，算起来经常卡、慢；

二、FILTER 不支持 # 数组引用（ 套加MAP() 函数可以实现，我还没来得及试）。

所以它是「能用」的解法，不是「轻松」的解法。每月四十分钟，是省下来了，但我知道它还能更轻。

—— · ——

最后

透视表不是不能用，是它管不了那种「会一直长、还长得不齐」的表。

我先用手边会的函数，把第一版跑通了——能用，但数据重算起来耗时特别长，数据多时表格打开速度会特别的慢。

后来我发现了专门为统计而生的 GROUPBY()函数，下次我用他优化一下这个数据汇总表和这个较量一番。