如何利用Excel的Power Query快捷转换数据-英雄云拓展知识分享

　　在管理学生成绩的实践当中，成绩原始数据常常按学生条目记录(图1)。若需要转换为以学号、姓名、学科、成绩、等第进行分列的表式效果，数据量大，逐一复制、粘贴操作明显相当繁琐。利用Excel的Microsoft Power Query加载项可实现快捷转换(Excel 2016已包括Microsoft Power Query组件，可以在“数据”选项卡直接调用，其他版本需在微软官方http://dwz.cn/2J93Jh 复制网址 下载安装)。

　　1. 数据转换为表

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

　　2. 提取学科数据

　　在“表1-查询编辑器”窗口选择学号、姓名两列的列标，切换到“转换”选项卡，在“任意列”功能组顺次选择“逆透视列→逆透视其他列”，履行后可以将当前选定列转换为“属性/值”对，并与每行中的剩余值相结合，显示效果如图2所示(图2)。

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

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

　　完成上述设置以后，原本的“属性2”列已被转换为成绩、等第两个新的列。如果觉得默许的“属性1”不太适合，可以右击重命名为“学科”。关闭查询编辑器，此时会提示是不是保存更改(图5)，确认以后会返回Excel主界面，此时便可看到终究效果(图6)。接下来可以针对对各个项目进行适当的挑选，感兴趣的朋友可以一试。

　　扩大浏览：利用Power Query快捷分离混合文本

　　如图所示的“DATA”列都是字母、数字的混合文本(图7)。现在要求顺次提取纯文本、纯数值、不重复数值。手工提取明显是相当麻烦，而且也容易出错，利用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主界面以后，就能够看到分离效果(图8)。