如何利用Excel的PowerQuery快速转换数据Excel利用PowerQuery快速转换数据的方法

excel

在管理学生成绩的实践当中，成绩原始数据往往按学生条目记录。若需要转换为以学号、姓名、学科、成绩、等第进行分列的表式效果，数据量大，逐一复制、粘贴操作显然相当繁琐。利用excel的Microsoft Power Query加载项可实现快速转换(excel 2016已经包含Microsoft Power Query组件，可以在数据选项卡直接调用，其他版本需在微软官方下载安装)。

1. 数据转换为表

打开数据表，切换到数据选项卡，在获取和转换功能组单击从表格按钮，在随后弹出的对话框根据提示选择数据源，excel会自动将选定区域转换为表，并打开查询编辑器界面。

微软

2. 提取学科数据

在表1-查询编辑器窗口选择学号、姓名两列的列标，切换到转换选项卡，在任意列功能组依次选择逆透视列逆透视其他列，执行后可以将当前选定列转换为属性/值对，并与每行中的剩余值相结合。

可以看到属性列包含了语文、数学、英语等多门学科的名称，我们首先需要将这些学科的名称从属性列提取出来。选择属性列的列表，右击选择拆分列按分隔符，此时会打开按分隔符拆分列对话框，在选择或输入分隔符列表框选择自定义，输入|进行分隔，这里不需要更改其他选项。可以看到原来的属性列已经被分隔为属性1、属性2两列，属性1是各门学科的名称，属性2则是成绩、等第。

由于属性2列仍然混合了成绩、等第这两个内容，因此还需要将这些内容区分开来。选择属性2列，切换到转换选项卡，在任意列功能组单击透视列按钮，这一操作是为了使用当前选中列中的名称创建新列，此时会弹出透视列对话框，在值列下拉列表框选择值，点击高级选项前面的按钮，在下拉列表框选择不要聚合。

完成上述设置之后，原有的属性2列已经被转换为成绩、等第两个新的列。如果觉得默认的属性1不太合适，可以右击重命名为学科。关闭查询编辑器，此时会提示是否保留更改，确认之后会返回excel主界面，此时就可看到最终效果。接下来可以根据对各个项目进行适当的筛选，感兴趣的朋友可以一试。

扩展阅读：利用Power Query快速分离混合文本

如图所示的DATA列都是字母、数字的混合文本。现在要求依次提取纯文本、纯数值、不重复数值。手工提取显然是相当麻烦，而且也容易出错，利用Power Query内置的函数可以轻松实现。

在源数据选择DATA列的数据区域，切换到数据选项卡，在获取和转换功能组单击从表格按钮，此时会打开查询编辑器，切换到添加列选项卡，单击常规功能组的添加自定义列按钮，此时会弹出添加自定义列对话框，首先将默认的列名Custom修改为纯文本，接下来在下面的自定义列公式窗格输入= Text.Trim(Text.Remove([DATA],{0..9}), )，检查无误之后单击确定按钮，很快就可以在新添加的自定义列显示从DATA列提取出来的纯文本内容，按照同样的方法继续添加纯数值、不重复数值两个自定义列，公式分别为=Text.Remove(Text.Trim(Text.Remove([DATA],{A..z}), ), )和=Text.Combine(List.Distinct(Text.ToList([纯数值])))，请注意后一个公式的纯数值必须与上一个自定义列的名称保持一致，否则会提示Error。

关闭查询编辑器，在提示是否保留更改时，请选择保留，返回excel主界面之后，就可以看到分离效果。