「表格设计课」第4期 财务收款跟进，就该这么慢

表格设计课 第4期封面.jpg

首先，我对这个栏目做个介绍。

• 什么是「表格设计课」。我们在做Excel时遇到的问题，多半是表格没有设计好，那些各种复杂的公式，都是在弥补表格设计上的错误。「表格设计课」就是教你设计好表格，从源头上避免问题发生。
• 「表格设计课」的思想：好的表格是设计出来的。
• 每期一个表格问题。原始文稿，来自于「读者投稿」或者「网络」，投稿邮箱是laxiaodeng2017@163.com，如果你也有问题，可以给我投稿。
• 每周三更新

以下是本期内容

---

问题描述

今天要讲的，是关于账务追踪，表格结构设计的问题，如果你是财务人员，一定不要错过。

小樱同学，是个刚入职1年的职场新人，也是一个财务的新手，公司业务量非常的大，每天都有处理不完的账务追踪数据，下面是她向我求助的表格，大家猜猜，她的问题是什么？

-----思考时间-----

-----思考时间-----

-----思考时间-----

图片太宽，没看出来吗？我再来几张局部的特写。

1- 合同信息

2- 发票开票信息

3- 收款记录

4- 月度统计信息

以上这些数据，从A列开始，一直往右排列，排到了AR列，共计44列，天呐！

这样你的思路应该清晰了一些吧，小樱同学反馈的问题是：

1. 数据列太多，各个数据列查看起来很麻烦。
2. 开票、收款等记录，横向排布，统计麻烦。每次老板让汇总数据的时候，比如按月统计收款金额，和待还款的合同有哪些？每次都要一个个筛选，筛到晚上11点。

做财务的你，是不是也遇到过类似的问题，是不是也有这样的老板？

不过拉登师傅告诉你：小樱同学反馈的问题，只是表面的现象，根本的原因是表格结构设计错误。错误的把数据表，用汇总表的结构，设计出来。只满足了阅读的需求，没有考虑到数据统计的过程，这也是大部分人，设计表格的一个通病！

问题分析

接下来，我们来仔细看一看，这个表格的问题，到底是什么？

1. 数据层次混作一谈。
2. 关联字段被拆分，失去了对应关系。

没明白吧，没有关系，下面是详细的说明。

1. 数据层次混作一谈。

我们先思考一下，这个财务表格的目的是什么？是针对厂商的货款进行追踪，大致的流程是这样的。

针对第3个环节，财务在追账的过程中，有可能需要跟进很多次，才能完成把所有的款项都追回来。相应的形成多笔的开票、收款记录。

在这个过程中，有两个数据信息流：合同和收款。

合同数据

每个合同代表一个订单，我们可以根据合同的编号，建立一个清单，记录所有厂商的订单信息，这些信息包括：订单日期、合同金额、商品名称、商品数量等信息。

这一点在原表格中，是没有问题的。

收款数据

收款对应着合同中的金额，但是针对金额较大的合同，厂商可能无法一次付清，这样同一个合同，可能会有多笔的收款记录。

同样的，我们可以对这些多笔收款记录，建立一个清单，记录收款的信息，这些信息包括：收款日期、收款金额、收款对应的发票、收款方式等等。

它的结构，与合同清单应该是一样的，一行数据代表一次收款记录，收款信息对应第1行的字段。

但是问题表格中，在设计的时候，犯了两个严重的错误。

1. 每1笔的收款记录，本应该随着行纵向延展的，被设计成了横向的列数据。导致无法针对收款记录进行筛选。

1. 一个合同对应多笔收款记录，这种1对多的多级数据，被设计到了同1行中。数据的统计方向发生交叉，合同是纵向延展，而收款记录是横向延展，给数据统计造成了障碍。

上面这两个问题，总结成一点，就是：用阅读的思维，把统计数据设计成了，一个汇总表格。

2. 关联字段被拆分，失去对应关系

既然错误是把数据，设计成了阅读型的汇总表格，那么正确的，统计型的数据表格，应该是什么样子的呢？

你要记住一点，面向数据统计的表格设计，都是纵向的行数据。这类表格通常是一个一维的数据表，它有两个永远都不会变的特征：

1. 第1行，永远都是标题（也叫字段）。
2. 从第2行开始，下面的每一行数据（注意是行，是自上而下的，不是列），代表一条记录。
   1. 每1条记录里，都完整了保存了每个字段的信息。
   2. 每1列，是这个字段（比如说金额）包含的所有收款信息的金额。

如果你了解过ACCESS，SQL等数据库知识，你应该对这类表格结构也不陌生。

在这个方面，问题表格又犯了一个错误，相同字段的数据（比如金额），因为收款记录的横向设计，被分割到不同的列当中。连基本的筛选都无法实现。

解决方案

要解决这么多的问题，最最关键的，就是要梳理清楚数据信息的类别。然后我们按照下面的步骤，一步步修改表格。

1. 分析数据的层级
2. 根据数据层级，建立数据统计型表格
3. 根据数据表格，建立透视表，输出阅读型数据

1. 分析数据层级

正如前面我们所分析的，这个账务追踪表格的数据，就分为两类：合同记录和收款记录。

因为无论是合同还是收款记录，都会有多笔的记录，同时合同数据，是收款记录的上一级，一个合同可以对应多笔收款记录。所以我们把分别为合同、收款记录，建立单独的表格。

2. 建立数据统计型表格

所谓的数据统计型表格，就是简单的一维表格（你可以翻看前面的文章，回忆一下一维表格的特点）。我们把所有对应的信息，都横向的放在数据标题中，设计出下面的两个表格。

收款明细

合同汇总

这样数据全部设计成了纵向的延展，就可以轻松的使用筛选、统计公式，或者透视表来统计数据了。

3. 输出阅读型数据

回过头来，再看问题的表格，大概可以猜测出，老板想要的信息了。

1. 输出合同汇总表格，可以快速筛选出，待还款的合同，以及对应的单位。
2. 输出按发票统计的，收款状况。
3. 根据时段，输出统计每个月、或者每年的收款状况。

针对上面的这个3个需求，现在只需要使用sumifs函数，以及透视表技巧，就可以快速的的统计出出来了。

不啰嗦了，咱们直接看输出后的结果。

改善输出

接下来，就是阅读型数据的输出结果。

1. 合同汇总表

【合同汇总表】中，使用SUMIFS函数，以【收款明细】表为数据源，动态汇总每个合同的收款状态，如果收款100%，就会自动标记为绿色。

2. 统计各合同应收、预收状态

使用透视表，以【收款明细】为数据源，只要折叠或展开“单位名称”字段，就可以轻松的，按单位、或者按合同统计，当前应收款，或预收款的状态。

图中，红色表示预收款，黑色表示待收款额度。

3. 按时段统计收款状况

同样的，基于【收款明细】表创建数据透视表，使用“创建组”功能，可以轻松的是实现，按月、按年统计收款金额。

今天的案例呢，逻辑上有点复杂。但处理这类问题的中心思想很简单。降低数据的维度，减少数据方向交叉，尽量使用一维表。

好了以上就是今天的全部内容，晚安！

案例下载

案例下载包含两个部分

1- 问题表格

你可以在公众号（拉小登），回复【0510】获取本节练习文件，然后自己动手，尝试着去修改表格。

2- 答案表格

转发文章，截图发到我个人微信，可以获取答案表格。

footer

我是拉小登，如果你喜欢我的文章，请转发或者打赏，有你的支持，我才能继续写出更多，更好的教程，咱们明天见。