做表累到两眼昏黑？那是你不知道这招，学会了老板想不夸都不行

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

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

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三合一办公应用课程！

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

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

• Excel表格入库表自动录入入库时间(excel表格制作教程入门)
• Excel表格快速批量隐藏/显示不连续的工作表(excel表格快速排序)
• Excel表格利用切片器快速筛选数据/实时更新柱状图(excel表格利息计算公式)
• Excel表格在筛选状态下实时更新序号(excel表格在电脑上怎么下载)
• Excel表格不复制隐藏数据(excel表格不能自动求和)
• Excel表格快速汇总多个工作表的数据到同一个工作表(excel表格快速查找)
• Excel表格三种方法实现多条件查找(excel表格三位数变成两位数)
• Excel表格制作可以自动更新的删除重复项(excel表格制作教程入门视频免费)
• Excel表格利用Vlookup、Match、Countif函数实现一对多查找(excel表格利息计算公式)
• Excel表格快速提取单元格信息中的数字、字母、文字(excel表格快速填充内容)