如何利用Excel的Power Query快速转换数据
时间:2022-10-23 08:46:12
在管理学生成绩的实践当中,成绩原始数据往往按学生条目记录(图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)。
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Word2007自定义设置页眉页脚的页码样式的方法步骤
![](https://img.aspxhome.com/file/2023/3/29913_0s.jpg)
遇到生僻字 Word帮你学它的读法
![](https://img.aspxhome.com/file/2023/8/21888_0s.jpg)
Word中如何添加剪切线
![](https://img.aspxhome.com/file/2023/2/17872_0s.png)
word 对齐页面和对齐页边距有什么区别?
![](https://img.aspxhome.com/file/2023/5/35525_0s.png)
Word遇到问题需要关闭。我们对此引起的不便表示抱歉。
![](https://img.aspxhome.com/file/2023/3/24543_0s.jpg)
word怎么设置大号字体?
![](https://img.aspxhome.com/file/2023/2/26102_0s.jpg)
在Word2010中使用Outlook联系人作为收件人
win10以太网未识别怎么办?win10以太网未识别如何配置
![](https://img.aspxhome.com/file/2023/4/47574_0s.png)
mathtype6.9简体中文版不能打汉字解决方法
![](https://img.aspxhome.com/file/2023/7/29017_0s.png)
PDF文档如何提取图片?抠出PDF中的好图5个方法分享
![](https://img.aspxhome.com/file/2023/1/15341_0s.jpg)
如何修复win10版本2004提示错误0x800F0955?
![](https://img.aspxhome.com/file/2023/1/46971_0s.jpg)
如何设置WORD文字的着重号
Word怎么去除回车箭头?
![](https://img.aspxhome.com/file/2023/5/23825_0s.jpg)
用word快速方便地制作会议台签
![](https://img.aspxhome.com/file/2023/4/25914_0s.jpg)
Word 2007技巧:在"大小"对话框裁剪图片
![](https://img.aspxhome.com/file/2023/7/21637_0s.jpg)
word怎么关闭超链接
![](https://img.aspxhome.com/file/2023/7/34587_0s.gif)
Word中2003版本使用纹理作为页面背景的操作方法
Win10Defender怎么关闭?具体要怎么操作?
![](https://img.aspxhome.com/file/2023/4/47164_0s.png)
Word2016文章怎么取消自动编号?
![](https://img.aspxhome.com/file/2023/5/24185_0s.jpg)
Office 2021预览版发布,无需订阅即可使用
![](https://img.aspxhome.com/file/2023/1/16721_0s.jpg)