本文总共1256个字,阅读需5分钟,全文加载时间:2.191s,本站办公入门专栏收录该内容! 字体大小:

文章导读:评论回复关键词【福利】,获取丰富办公资源,助你高效办公早下班! 不懂表格设计,做出来的表格,经常会让自己陷入死循环。 01 问题描述 学员雅儿是个财务,她就被前同事设计的追账表格,气得够呛。 一……各位看官请向下阅读:

评论回复关键词【福利】,获取丰富办公资源,助你高效办公早下班!

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

01

问题描述

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

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

数据都快到 Z 列了!

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

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

领导让统计当月追款金额汇总的时候,是用计算器算呢?

还是一行一行敲 SUMIF 函数呢?

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

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

02

解决方法

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

❶ 追账记录添加标记❷ 合同记录数据拆分❸ 追账记录结构转换❹ 合同记录合并查询

追账记录添加标记。

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

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

合同记录数据拆分

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

添加自定义名称

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

♦ 拆分数据表 ♦

首先将数据加载的 PQ 中。

数据加载到 PQ 中之后,复制成两个。

修改新表的名称为:「记录」。

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

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

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

❸ 追账记录结构转换

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

转置

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

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

将第 1 行提升为标题。

逆透视

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

透视列

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

这样,我们就完成了表格结构的转换。

接下来,就是把这些追账记录,和前面的「账单列表」合并到一起,把合同信息也添加进来。

合同记录合并查询

合并查询

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

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

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

展开数据

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

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

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

03

总结

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

❶ 转置功能

❷ 数据透视表

❸ 逆透视

❹ 分组依据

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

好了,今天就是这样!

评论回复关键词【福利】,获取丰富办公资源,助你高效办公早下班!

想要从零系统学习Excel,查看本站上方“视频教程”获取最新秋叶Office三合一办公应用课程!

以上内容由优质教程资源合作伙伴 “鲸鱼办公” 整理编辑,如果对您有帮助欢迎转发分享!

你可能对这些文章感兴趣:

发表评论

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