16、Power Query-批量生成工资条的妙用

本节所用知识点:Power Query-表头和首行互转的妙用。

实用案例:公司员工工资条的批量制作。

原始数据如下图所示:


然而我们人事部打印的要求需要一行标题,一行明细,一行空格,以此类推,如下图所示:


其实用Excel的普通功能也可以实现,但今天我们就用一个新方法来实现,具体思路:

比如某公司有1000个员工,那我们需要准备1000个表头,1000条明细记录已经存在,1000个空行,最后追加到一起,进行排序。

下面直奔主题,选中数据源,“Power Query”——“从表”,进入到PQ界面。

改名为“工资条”,复制一份出来做“标题”。


下面我们首先处理“标题”部分。比如我们的示例数据有10条记录,那我们必须创建10个标题行。

难点在于如何将数据源内容全部转换为标题行。

首先我们需要把它里头的内容都清空,这里用到的功能是“转换”——“提取”——“范围”(必须全选)。


这里的关键在于范围的起始索引,我们设置为199的目的是什么?

目的是从每一单元格的199位开始提取,因为每一单元格都不满199个,所以无论提取多少个字符都是为空,这样一来,空行就得到了。


接下来一步则是查找替换值,查找空值,替换成为真空,即null,“转换”——“替换值”(必须全选)。


效果如下:


接下来我们就可以把上面的表头复制到下面的内容中来,“转换”——“将第一行用作标题”——“使用表头作为首行”。


效果如下:


这样一来第一行已经有内容了,下面的怎么办呢?使用“转换”——“填充”——“向下”(必须全选)。


效果如下:


可以发现,已经全部填充为标题行了,但是我们发现多了一行表头,这个时候我们可以用到反转的功能,将第一行内容变回为表头行。

“转换”——“将第一行用作标题”——“使第一行用作标题”,这样一来的好处是,之后可以和明细行对的上。



下面要做10个空行,这个相当简单了,直接复制“标题”即可。


然后右边“应用的步骤”删除到空行就可以了。


好了,基础数据已经准备完毕,这个时候还有一个问题需要考虑,即排序!

我们先给“标题”表添加索引列,“添加列”——“添加索引列”——“自定义”。

由工资表的布局,我们可以确定它的其实索引是1,增量是3,即第二个员工是从第四行开始打印。


效果如下:


然后给“工资条”也添加索引列,起始索引为2,增量同样是3。


同样的给空行设置索引列。

最后万事俱备,追加三个表。“开始”——“追加查询”——“三个或更多表”,把三个表添加进去。


效果如下:


下面对索引列进行排序就可以了,“开始”——“升序”。


效果如下:


这个时候发现第一行数据和表头的一样一样的,同样用上面的方法一步操作,“将第一行用作标题”。


删除索引列,关闭并上载出来。


最后我们用老方法美化一下它,好给员工打印。


在“视图”里面将它的网格线也去掉。


然后选中这么几列,“开始”——“条件格式”——“新建规则”。


即有内容的时候,我们给它设置边框,条件=A1<>""。




有没有发现很销魂,最后我们在数据源里随意添加记录。


刷新,动态更新记录。


同样,删除也是动态的,一劳永逸!


相关内容推荐