Power Query应用：汇总多个不规整的Excel表（比如简历、调查问卷、评分表、财务报表等）

2023-07-24 12:28| 来源: 网络整理| 查看: 265

一、应用背景

日常常见各种类型的多维表格，比如各类简历、调查问卷、登记表、工资单、财务报表等。

如何将这些不规整的多维表格，汇总生成如下格式的一维表格，以便后续进行各种分析呢？

下面介绍使用Power Query的方法，只需要半小时既可快速完成任务。

二、实现方法

1、分析原始表格内容

列标题：姓名、应聘岗位、参加工作时间、上一份工作离职时间、第一学历/学位、……

列标题所在位置（行，列）：（3，1）、（3，4）、（4，1）、（4，4）、（5，1）

列值：张三、产品经理、2015/7/1、2016/8/10、本科/学士、……

列值所在位置（行，列）：（3，2）、（3，5）、（4，2）、（4，5）、（5，2）

2、单个表格的实现

在Excel中导入数据到Power Query

将数据转换成list的列表

根据原始表格分析的结果，构建列标题的list

根据原始表格分析的结果，构建列值得list

生成结果表格

M语言代码如下：

let 源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content], 降级的标题 = Table.DemoteHeaders(源), 转为列表 = Table.ToColumns(降级的标题), 标题 = List.Transform(List.Zip({{0,3,0,3,0,2,4,0,2,4,0,3,0,3,0,1,2,4,5},{2,2,3,3,4,4,4,5,5,5,6,6,7,7,9,9,9,9,9}}),each 转为列表{_{0}}{_{1}}), 数据 = List.Transform(List.Zip({{1,3,1,3,1,3,5,1,3,5,1,4,1,4,0,1,2,4,5},{2,2,3,3,4,4,4,5,5,5,6,6,7,7,10,10,10,10,10}}),each {转为列表{_{0}}{_{1}}}), 结果 = Table.FromColumns(数据,标题) in 结果

3、多个表格汇总

对单个表格的实现，封装成函数

遍历文件夹下每个文件，调用封装好的函数，提取每个文件的内容并进行合并

M语言代码如下：

let fx=(bin)=> let 表= Excel.Workbook(bin){0}[Data], 转为列表 = Table.ToColumns(表), 标题 = List.Transform(List.Zip({{0,3,0,3,0,2,4,0,2,4,0,3,0,3,0,1,2,4,5},{2,2,3,3,4,4,4,5,5,5,6,6,7,7,9,9,9,9,9}}),each 转为列表{_{0}}{_{1}}), 数据 = List.Transform(List.Zip({{1,3,1,3,1,3,5,1,3,5,1,4,1,4,0,1,2,4,5},{2,2,3,3,4,4,4,5,5,5,6,6,7,7,10,10,10,10,10}}),each {转为列表{_{0}}{_{1}}}), 结果 = Table.FromColumns(数据,标题) in 结果, 源 = Folder.Files("C:\Users\admin\Desktop\源数据"), 合并 = Table.Combine(List.Transform(源[Content],fx)) in 合并

生成结果表格

【本文地址】

公司简介

联系我们