同样是做表格，同事折腾半天也没理清的款项跟踪，我早就算完收工了！
问题描述
今天要讲的，是关于账务追踪，表格结构设计的问题，如果你是财务人员，一定不要错过。
小樱同学，是个刚入职 1 年的职场新人，也是一个财务的新手，公司业务量非常的大，每天都有处理不完的账务追踪数据，下面是她向我求助的表格，大家猜猜，她的问题是什么？
——-思考时间——
-——-思考时间——
-——-思考时间——-
图片太宽，没看出来吗？
我再来几张局部的特写。
以上这些数据，从 A 列开始，一直往右排列，排到了 AR 列，共计 44 列，天呐！
这样你的思路应该清晰了一些吧，小樱同学反馈的问题是：
❶ 数据列太多，各个数据列查看起来很麻烦。
❷ 开票、收款等记录，横向排布，统计麻烦。每次老板让汇总数据的时候，比如按月统计收款金额，和待还款的合同有哪些？每次都要一个个筛选，筛到晚上 11 点。
做财务的你，是不是也遇到过类似的问题，是不是也有这样的老板？
不过拉登师傅告诉你：小樱同学反馈的问题，只是表面的现象，根本的原因是表格结构设计错误。
错误地把数据表，用汇总表的结构，设计出来。
只满足了阅读的需求，没有考虑到数据统计的过程，这也是大部分人，设计表格的一个通病！
问题分析
接下来，我们来仔细看一看，这个表格的问题，到底是什么？
❶ 数据层次混作一谈。
❷ 关联字段被拆分，失去了对应关系。
没明白吧，没有关系，下面是详细的说明。
▋数据层次混作一谈
我们先思考一下，这个财务表格的目的是什么？
是针对厂商的货款进行追踪，大致的流程是这样的：
针对第 3 个环节，财务在追账的过程中，有可能需要跟进很多次，才能完成把所有的款项都追回来。
相应的形成多笔的开票、收款记录：
在这个过程中，有两个数据信息流：合同和收款。
合同数据
每个合同代表一个订单，我们可以根据合同的编号，建立一个清单，记录所有厂商的订单信息，这些信息包括：订单日期、合同金额、商品名称、商品数量等信息。
这一点在原表格中，是没有问题的。
收款数据
收款对应着合同中的金额，但是针对金额较大的合同，厂商可能无法一次付清，这样同一个合同，可能会有多笔的收款记录。
同样的，我们可以对这些多笔收款记录，建立一个清单，记录收款的信息，这些信息包括：收款日期、收款金额、收款对应的发票、收款方式等等。
它的结构，与合同清单应该是一样的，一行数据代表一次收款记录，收款信息对应第 1 行的字段。
但是问题表格中，在设计的时候，犯了两个严重的错误。
❶ 每 1 笔的收款记录，本应该随着行纵向延展的，被设计成了横向的列数据，导致无法针对收款记录进行筛选。
❷ 一个合同对应多笔收款记录，这种 1 对多的多级数据，被设计到了同 1 行中；数据的统计方向发生交叉，合同是纵向延展，而收款记录是横向延展，给数据统计造成了障碍。
上面这两个问题，总结成一点，就是：用阅读的思维，把统计数据设计成了，一个汇总表格。
▋关联字段被拆分，失去对应关系
既然错误是把数据，设计成了阅读型的汇总表格，那么正确的，统计型的数据表格，应该是什么样子的呢？
你要记住一点，面向数据统计的表格设计，都是纵向的行数据。
这类表格通常是一个一维的数据表，它有两个永远都不会变的特征：
❶ 第 1 行，永远都是标题（也叫字段）。
❷ 从第 2 行开始，下面的每一行数据（注意是行，是自上而下的，不是列），代表一条记录：
每 1 条记录里，都完整了保存了每个字段的信息。
每 1 列，是这个字段（比如说金额）包含的所有收款信息的金额。
如果你了解过 ACCESS，SQL 等数据库知识，你应该对这类表格结构也不陌生。
在这个方面，问题表格又犯了一个错误，相同字段的数据（比如金额），因为收款记录的横向设计，被分割到不同的列当中。连基本的筛选都无法实现。
解决方案
要解决这么多的问题，最最关键的，就是要梳理清楚数据信息的类别。
然后我们按照下面的步骤，一步步修改表格。
❶ 分析数据的层级
❷ 根据数据层级，建立数据统计型表格
❸ 根据数据表格，建立透视表，输出阅读型数据
▋分析数据层级
正如前面我们所分析的，这个账务追踪表格的数据，就分为两类：合同记录和收款记录。
因为无论是合同还是收款记录，都会有多笔的记录，同时合同数据，是收款记录的上一级，一个合同可以对应多笔收款记录。
所以我们把分别为合同、收款记录，建立单独的表格。
▋建立数据统计型表格
所谓的数据统计型表格，就是简单的一维表格（你可以翻看前面的文章，回忆一下一维表格的特点）。
我们把所有对应的信息，都横向的放在数据标题中，设计出下面的两个表格。
这样数据全部设计成了纵向的延展，就可以轻松的使用筛选、统计公式，或者透视表来统计数据了。
▋输出阅读型数据
回过头来，再看问题的表格，大概可以猜测出，老板想要的信息了。
❶ 输出合同汇总表格，可以快速筛选出，待还款的合同，以及对应的单位。
❷ 输出按发票统计的，收款状况。
❸ 根据时段，输出统计每个月、或者每年的收款状况。
针对上面的这个 3 个需求，现在只需要使用 sumifs 函数，以及透视表技巧，就可以快速的的统计出来了。
不啰嗦了，咱们直接看输出后的结果。
改善输出
接下来，就是阅读型数据的输出结果。
▋合同汇总表
【合同汇总表】中，使用 SUMIFS 函数，以【收款明细】表为数据源，动态汇总每个合同的收款状态，如果收款 100%，就会自动标记为绿色。
▋统计各合同应收、预收状态
使用透视表，以【收款明细】为数据源，只要折叠或展开「单位名称」字段，就可以轻松的，按单位、或者按合同统计，当前应收款，或预收款的状态。
图中，红色表示预收款，黑色表示待收款额度。
▋按时段统计收款状况
同样的，基于【收款明细】表创建数据透视表，使用「创建组」功能，可以轻松的是实现，按月、按年统计收款金额。
今天的案例呢，逻辑上有点复杂，但处理这类问题的中心思想很简单。
降低数据的维度，减少数据方向交叉，尽量使用一维表。
好了以上就是今天的全部内容，再会！
案例下载
▋问题表格
你可以在后台，回复【0510】获取本节练习文件，然后自己动手，尝试着去修改表格。
如果你还想了解更多的 Excel 知识，更轻松高效地解决这类问题， 我推荐你参加《3 天 Excel 集训营》！
为期 3 天的课程专为职场人准备，全部基于职场真实表格案例设计，还有很多超实用 Excel 技巧教学。
每天学习大概 30 分钟，你也有可能成为 Excel 高手！
3 天 Excel 集训营
提升效率的好帮手
数据可视化的助力
原价 99 元
现在仅需 1 元
保存下方图片扫码即可报名
👇👇👇
↑↑↑
现在报名免费领取
35 个常用函数说明
……
*广告
