财务:一个合同追款4次,再追下去就到Z列了

作者: ladeng6666 分类: Excel 发布时间: 2019-08-14 11:04 阅读: 17,812

今日目标

学习PQ表格转换

不懂表格设计,做出来的表格,经常会让自己陷入死循环。

1- 问题描述

学员雅儿是个财务,她就被前同事设计的追账表格,气得够呛。

一个合同可能需要反复几次才能把款项追回来,每次有新的追款记录,就往右边填。数据都快到Z列了。

我打包票,在座的各位财务,有83.4%的人都是这么做表的。

不是说你做的不对,但是这样做有什么坏处呢?

领导让统计当月追款金额汇总的时候,是用计算器算呢?还是一行一行的敲SUMIF函数呢?

好在雅儿同学,跟我学了表格的设计,知道规范的数据统计,应该是下面这样的:

这样只需要透视表,拖一拖鼠标,就可以搞定月度统计,季度统计也顺带着出来了。

2- 原因分析

如果克制不了对成就感的欲望,可以直接看「解决方法」。

下面我来分析一下原因。

What 这是什么问题

财务追账问题,本质上是多记录跟进的问题。

类似的问题还有:

1- 客户到访记录跟进

2- 客户关系维护跟进

3- 产品维修记录跟进

所有涉及到多次记录的问题,都属于这个类型。

Why 为什么统计复杂

多记录跟进,普通人都会用「所见即所」的思维,在一行数据中,不断的向右追加跟进记录。

这是导致数据统计复杂的根本原因:不懂数据管理,没有数据思维,按纸质表单记录数据

所谓的数据思维,大致包含几个特点:

1- 表格是一维数据表

2- 第1行是字段名称

3- 新增的数据,永远都是从上往下行方向追加

4- 定性转定量,数据中尽量将「是、否」「有、无」等定性的词语,转成「0、1」这样定量的数据。

雅儿同学需求的结果,就是一个典型的一维数据表。这个是大部分人缺乏的思维。

How 怎么做

了解了数据思维之后,大部分的数据在统计之前,都需要先转成一维的数据表。

常见的转换方法包括,但不限于:

1- 手动复制粘贴

2- 借助插件,比如方方格子、易永宝、万能输入表

3- 使用Power query(Excel自带的功能)

4- 自己编写VBA代码

这节课,我们重点看一下,如何使用Power query把这个追账数据转换成一维数据表。

3- 解决方法

追账表转一维表的过程,比较的繁琐,大致可以分成下面几步:

1- 追账记录添加标记

2- 合同记录数据拆分

3- 追账记录结构转换

4- 合同记录合并查询

1-追账记录添加标记

为了让PQ更好的识别,每次追账都是第几次,手动添加一个「追账次数」的标题。

同时原有的标题,也要统一起来,方便后面使用「透视列」功能,对数据结构转换。

2- 合同记录数据拆分

接下来,把修改后的数据,添加到Power Query中,下面的操作很繁琐,一定要一步步跟着操作。

1- 添加自定义名称

选择所有的数据,添加自定义名称:账单列表。

2- 拆分数据表

首先将数据加载的PQ中。

数据加载到PQ中之后,复制成两个。修改新表的名称为:记录。

第1个表,「账单列表」删除记录。

第2个表,「记录」只保留记录。

非常重要的一点,两个表都要保留序号,稍后要做「合并查询」使用。

3- 追账记录结构转换

接下来,要对「记录」中的多条追账记录进行转换了,操作同样很复杂,一步步跟着做。

1- 转置

使用转置功能,对行列数据进行转换。

转置之后,使用「填充」功能,把追账次数,填充的所有行。

将第1行提升为标题。

2- 逆透视

选择所有的记录列,对数据进行逆透视

3- 透视列

根据「序号」列,对数据进行透视,透视的列为「值」这一列,计算方式选择「不聚合」

这样,我们就完成了表格结构的转换。接下来,就是把这些追账记录,和前面的「账单列表」合并到一起,把合同信息也添加进来。

4- 合同记录合并查询

1- 合并查询

使用「合并查询」功能,把两个表格记录都合并起来。

仔细看一下动图,这个合并的过程,其实和VLOOKUP是类似的,匹配的依据,就是第1个表中的「序号」,和第2个表中的「属性」。

因为序号是相同的,所以相同的数据就被匹配过来了。

2- 展开数据

数据合并过来后,默认是table类型的数据。

点击右上角的展开按钮,就可以得到合并后的数据了。

最后,点击关闭并上载,得到转换后的数据。

4- 总结

Excel可以实现数据结构转换的功能不多,总结一下,大致就下面几个:

1- 转置功能

2- 数据透视表

3- 逆透视

4- 分组依据

其中后面3中,都是Power query中才能用到的。

5-聊五毛钱

我猜,看图文肯定让你看到有点懵,最好的学习方式,还是身边有个高手,能手把手的带着你练。

刚好,我开了一个Excel的学习班,下周一开班。

课程的讲解的主要内容,就是以表格设计为主线的,各种技能的综合运用。

1- 21天学习班

2- 7次视频直播

3- 7次答疑直播

4- 7次作业实战

通过直播的形式,把我搬到了你的身边。课程具体的大纲如下:

现在报名学习,还有以下福利:

1- 原价999元,报名领400元优惠券。

2- 送30个精选技巧视频

3- 30个精选知识卡片

4- 140套表格实用模板

点击「阅读原文」就可以报名,

4-0元优惠券,扫码下面二维码,加我助理「秋小E」领取。

扫码暗号:妹夫在吗?

 

好了,今天就是这样,我去吹空调去了。

我是拉小登,一个会设计表格的Excel老师

 

 

= = 推荐文章 = =

销售:产品信息分列,可愁死我了

财务:多工作表财务数据,快速核对

HR:能不能不要改表头,说了800遍了

销售:一长串运单遍号,如何拆分成一列

项目,如何统计标记黄色的项目数量

HR:按权重计算绩效一个公式搞定

HR:如何快速统计缺勤天数

销售:不能不知的数据拆分和提取神器

教师:这个排班表让我想辞职

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!

发表评论

电子邮件地址不会被公开。 必填项已用*标注