Excel之PowerQuery 处理横向课程表

2022-09-29 11:37 作者:孙兴华zz 0人读过 | 我要投稿

网友提问：

网友通过邮件把表格原始样式和想做好的样式发给我了，评论区可以下载

原始：

想要的样子：

因为列特别多，所以没办法完全截图给大家，需要练习的小伙伴可以评论区下载Excel工作簿练习。

先说声抱歉，您的问题我解决不了，原因是PowerQuery有一个规则，他的列名（字段名）不能重复。当您星期一显示1、2、3、4、5...（节课），星期二就不能再显示1、2、3、4、5...

我可以做一个和您一样的表，但是表头做不了。我做成下面的样子。绿表是我的，蓝表是您的要求，我只能把您的表头放在第一行上才能实现您的效果。

本文章的内容应用不广泛，只写文章不录课了。

下面说一下操作方法：

1、导入数据到PowerQuery

这一步，切记，要从原始数据第一列第一个单元格开始按住鼠标拖动到最后一列最后一个单元格松手，然后再点【数据】~【来自工作表】，因为原表有合并单元格。

2、第一列的标题可以修改一下，因为原始单元格是如下图所示，不改也行但是写代码麻烦

我们双击表头第1个，把它改成教师，这样就会自动新生成一个步骤，如下图

3、跳过第一行，因为第一行数据没有出现在目标表上

添加步骤，代码如下

4、降标题，操作顺序【主页】~【将标题作为第一行】

会自动生成两个步骤，如下图

5、对【更改的类型1】这张表转置，用按钮或代码都行

6、将转置表Column1这列所有是"列"开头的都替换为null

7、使用Table.Group第五参数分组（原理详见PowerQuery107集合集课程）

8、获取分组这张表中的三个部分

红色框为首行，蓝色框为末行，黄色框为中间

接下来分三步：

9、给中间这张表中的每个Table插入星期X

因为网友要求在星期X后面增加一列，写上大课间

于是我在每个Table第0行插入一条记录，记录您可以用公式生成，但是我觉得此案例没必要

所以直接写的记录，其中星期X引用每张Table中【Column1】列的第0号下标的值

10、选中数据列，【主页】~【删除列】~【删除其它列】

11、展开数据列，依次点击如下图

它会自动生成一个步骤，并生成代码如下

= Table.ExpandTableColumn(删除的其他列, "数据", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"})

但是这个代码只是中间表修改后的代码，我们还要连接上首行和末行的表，所以代码这样写

= 首行 & Table.ExpandTableColumn(删除的其他列, "数据", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}) & 末行

12、将展开的表再转置回来

13、提升标题

【主页】~【将第一行用作标题】，会自动生成如下两个步骤

14、再次提升标题

【主页】~【将第一行用作标题】，会自动再生成如下两个步骤

15、修改每个人的Table

按Column1列进行分组，使用Table.Group第5参数，公式中x代表数据列的每张表，

将每张表按列转成列表，遍历每个列，将下标1连接下标2的数据，

但是最后一列是数值，会报错，所以我们用try函数，当出现错误时，我们只取这个列的第0号下标的数据

16、删除其它列

选中数据列，【主页】~【删除列】~【删除其它列】

17、展开数据，步骤如下图

结果：

18、现在数据都没问题了，缺个表头那行

其实我们要的就是转置表1的第二行数据做表头，修改一下第一个和最后一个单元格内容即可

总有网友发消息问我，步骤中的表还要继续用行不行，那有什么不行的？咱们不是经常这么做吗？

思考步骤：

跳过转置表第1行：Table.Skip(转置表1,1)

取上面结果表的第1行：Table.FirstN(Table.Skip(转置表1,1),1)

把上面结果按列转列表：Table.ToColumns(Table.FirstN(Table.Skip(转置表1,1),1))

取上面结果的列表中从第2个到倒数第2个元素：List.Range( Table.ToColumns(Table.FirstN(Table.Skip(转置表1,1),1)) , 1 , List.Count(Table.ToColumns(Table.FirstN(Table.Skip(转置表1,1),1)))-2 )

给这个列表最前面加上教师，最后面加上课时合计：Table.FromColumns( {{"教师"}} & List.Range( Table.ToColumns(Table.FirstN(Table.Skip(转置表1,1),1)) , 1 , List.Count(Table.ToColumns(Table.FirstN(Table.Skip(转置表1,1),1)))-2 ) & {{"课时合计"}} )

现在生成了表头这张表