AI 助理
备案控制台
开发者社区 云计算 文章 正文

基于Excel2013的PowerQuery入门

2018-06-21 2295
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 2018年6月19日笔记所有要进行操作的文件下载链接: https://pan.baidu.com/s/10VtUZw8G-Ly-r4VypntjiA 密码: y5qu下载成功后，整个文件夹如下图所示。

2018年6月19日笔记

所有要进行操作的文件下载链接: https://pan.baidu.com/s/10VtUZw8G-Ly-r4VypntjiA 密码: y5qu
下载成功后，整个文件夹如下图所示。

img_919cff018c829a5abc25b489611fe719.png
文件夹图示.png

0.Power Query与其他PowerBI系列组件的关系

获取数据——>分析数据——>呈现数据
PowerQuery获取和整理——>PowerPivot建模和分析——>PowerView交互式报表||PowerMap地图可视化——>PowerBI在线版可视化仪表板发布和分享


img_444732308c9af9a4b14c519396171e55.png
关系图.png

简而言之：获取——>分析——>呈现——>发布

1.数据导入Power Query并进行追加查询

新建一个空的excel文件，在导航栏的POWER QUERY中选择从文件——>从Excel

img_ba244c5f53ef8fc26659aa86efb9714e.png
导入按钮图示.png

img_182235120a300a79d868b1dfed5332ed.png
导入一店数据1.png

img_8b6842e8f66eb476c99cc34c8a07a6be.png
导入一店数据2.png

点击 加载， 一店.xlsx这个文件的数据会被导入到工作薄查询中。
img_44c30ce7c0e06a04a36061d499f1dff9.png
工作簿查询.png

用相同的方法加载下载文件中的 二店.xlsx文件，结果图示如下。
img_fc19185441d8d0911e07e41af9ba5669.png
image.png

双击上图中的 一店，出现下图所示界面。选择 删除最前面几行。
img_546ba62da1dd0f9be1b709a84b78240c.png
删除前三行1.png

img_5fd7ee60b61a6b5f568340b507e853c6.png
删除前三行2.png

img_86a47bc09aaa6bbd865323735a5de0cb.png
打开左边查询栏1.png

点击上图所示的按钮，出现下图所示界面。
img_a7342adce254011b0676eef928f7812d.png
打开左边查询栏2.png

img_42b7ba8d15c602cd4e733419bd87e543.png
追加查询1.png

img_8e24bc15d1216be686a386de5da3cb62.png
追加查询2.png

img_75da3892da23f38a7687c4b56070c9f7.png
关闭并上载1.png

点击上面的按钮，回到Excel界面， 出现下图所示的 工作簿查询。
img_3f78010c05cad10e8f3158889fa1f049.png
关闭并上载2.png

从上图可以看出一店从 6377行增加到 10739行。

2.数据的行列管理及筛选

删除行

打开下载文件中的02-数据的行列管理及筛选.xlsx，出现如下图所示。

img_d8abd9b306ffd5461ac0384bc8911ce3.png
删除空行1.png

如下图所示，选定要加载的区域，即A3:C25区域
img_f0c1ff7e4b647d1beaa201e93a78d8dc.png
删除空行2.png

img_c7129c2cede663e8a7d80de9307aa2fd.png
删除空行3.png

点击上图箭头所示处，出现下图所示。
img_55d696ca2cb7470b3344709b2fe7497f.png
删除空行4.png

img_a8eae4b6d2ab57b439c1f292b78c49d0.png
删除空行5.png

img_1d4df14929460abd85f73469e72ac0cd.png
成功删除空行.png

删除间隔行

img_bf32ff6fe8eb5b4c3e13642b3f4a85de.png
删除间隔行1.png

设置数值如下图所示，然后点击 确定。
img_03ad25671cea1654bd119b940d496c5e.png
删除间隔行2.png

img_e2fec2d922a0f5d6f9bc247d5bc2b35a.png
成功删除间隔行.png

关闭并上载

img_64a20c8f61a311ebe9e72c5bd81ceb85.png
关闭并上载1.png

设置数值如下图所示，点击下图中的 加载。
img_a3b44a7599d341074c7f6f3cc37999c2.png
关闭并上载2.png

img_989ee4a365c1dd85d64c4503d4dfdd1e.png
成功关闭并上载.png

填充行

如下图所示，打开第二个表

img_c45f4c39a99abaabcc3aa58adf43291b.png
加载到查询编辑器1.png

加载到PowerQuery中如下图所示
img_2111c9b16b091c41da670e46d1fb177c.png
加载到查询编辑器2.png

img_cbfc5540f7d4c0d3eb6a4bf4e3fd43b8.png
将第一行作为标题.png

img_b5e1d36469d72c2340fb7b320ea1aa20.png
成功将第一行作为标题.png

img_03ad25671cea1654bd119b940d496c5e.png
删除间隔行1.png

img_a6d9088778b13f0e3af5c87a7298c87d.png
删除间隔行2.png

img_e7767fb78f30cf24dd0582fbb31d3a17.png
成功删除最后一行.png

img_dc33aed1ba2175880e0bba23bca039c1.png
填充按钮位置.png

img_8ed5f87ae18bd378089ca4ea675b8303.png
成功填充.png

选择导航栏中的 开始中的 关闭并加载至，出现下图所示，填入现有工作表的你想填入的位置。
img_748276724973090af0769d1cde1ca02c.png
加载设置.png

img_d1255f0fdac609c730aea5b3f1b323ef.png
成功填充并将成果加载至原有表.png

3.数据格式的转换

打开下载文件中的03-数据格式的转换.xlsx，如下图所示。

img_ab23a94701584607580fe55f207d0014.png
打开文件图示.png

img_21d85f785e736c667741d54f483e39f4.png
加载数据至PowerQuery中.png

img_a8662defd3a45d9d7f24a84e8eb2c21a.png
修整1.png

img_047effa03bbbd3a33bf02e27e788f211.png
成功修整.png

img_d45f324b76169c1a708920cc08e062c3.png
拆分列1.png

img_547f3c61aabb4ab41fd9e73ee4fb0f08.png
拆分列2.png

img_b61566bee3a5305c9792184986e1e77d.png
成功拆分列.png

img_eb0779dafbfc10d073b6f7a918d9a965.png
修整1.png

img_18d906096366c92396a5ac925f14f93f.png
成功修整.png

合并的期间选择多列的时候，要先选择 姓名.1那一列，再选择 姓名.2
img_d63ac9c344fbed7a689faf94b8a3abd0.png
合并列1.png

img_a0147c3c481d97eb4ec7fd442f5200c3.png
合并列2.png

img_8de20fd1c127840799faaaa588004fda.png
成功合并.png

img_6e86b427ae4d5299527bebc0000b3ac5.png
拆分列1.png

img_59d20f8f54fe8aec6d2d151d1d4f40fb.png
拆分列2.png

img_0bd86a54749729e9d843c15bf8e5fa91.png
成功拆分.png

img_3b5e03e3a7fc079cb5307a66a1b985a9.png
修整.png

img_eb736ff081f70a389b407eb76b5eb5bc.png
成功修整.png

img_a93c65ed21f732d6bdf2006db7053404.png
合并列1.png

img_1237d0c5166f73239daa0f47f9c2a88d.png
合并列2.png

img_ce62d3f7577a87281eda54bcb342f3a3.png
成功合并.png

img_b5d0fcb95888439413cda74e0f4fc71e.png
首字母大写按钮位置.png

img_586b965061e4d17508cd8a07732356a3.png
成功设置首字母大写.png

img_20d7857367f1bdd088eca6b77d922528.png
转成文本1.png

img_5dacdfed9d645b66dd84853d7dfaae5e.png
替换1.png

img_f2741e744192b3d588ee80a4be67b2be.png
替换2.png

img_fef50b5b00f00cbdba569831853ea747.png
成功替换.png

可以将下图与 结果表进行对照
img_4acae8f5065d0ae9476ec87cf7dc8d1e.png
成功加载.png

4.数据的拆分合并提取

打开下载文件中的04-数据的拆分合并提取.xlsx，如下图所示。

img_2c497692e3f90407d8eab20a71b453c8.png
打开文件图示.png

img_fc6aedf091fe28c90e37ae989c199efd.png
加载至查询编辑器.png

img_edcfcb32b1b5bd528213e556d009fe2f.png
修改数据类型为文本.png

img_f9c7eae26b88d27c04d1ac13d5accea6.png
成功修改数据类型.png

img_c499b7f5d14c9fe299dc35212a858023.png
添加重复列.png

img_f5889812fdcd790780214b58d259754c.png
按字符数拆分列1.png

img_9a2b96c0468a7219b59861507c6a892b.png
按字符数拆分列2.png

img_fed5c9cd24489c638f120ca58a455249.png
列重命名.png

img_497838a784f813892612570bf89010bf.png
成功列重命名.png

img_102a1055816be68be0806fd14e2dae86.png
提取1.png

img_d440cd631aefc3187118e177bf8d8c15.png
提取2.png

选定新产生的一列转换数据类型为整数
img_d61697606460ad0d3cbe21cf06296f7e.png
转化1.png

img_a2d49f61cbd9af05e7910e6b35c75853.png
转化2.png

img_a2d49f61cbd9af05e7910e6b35c75853.png
转化3.png

img_03df54e8e3ffff90ba4fac2078a5fd08.png
转换4.png

img_0dac68a9650ec65ee2bbb089d083a868.png
转换5.png

img_637a156034c8001c66d52998ef770992.png
转换6.png

img_f919ebf5d562165cbf9914b33c20e3fb.png
转换7.png

img_81daed2fa92a69bdace67ed693cab979.png
成功转换.png

其他的各项步骤原理相同，省略。

5.删除重复项

在下载文件中打开05-删除重复项.xlsx，如下图所示。

img_bc01fe16ca075a248a953102fce8834f.png
删除重复项1.png

img_4bfcdf48272912381a0d2f3dd16cc9fb.png
加载数据到PowerQuery中.png

客户首次购买分析

选定下单日期这一列，进行升序排序。

img_9b27764c4f0aff820192bc8da5348cb8.png
下单日期升序排序.png

选定 客户名称这一列，进行删除重复项
img_c5f9d009097ab36f6d150f40584d46b2.png
对客户名称删除重复项.png

img_c29adcfd0bf6a8b4941b65ea1e3ca506.png
首次购买分析结果.png

客户最大订单分析

选定金额这一列，进行降序排序

img_652d4baacd8365f125bcc7f432e90404.png
金额降序排序.png

选定 客户名称这一列，进行删除重复项
img_f433ab84a60c2e947d8e3ee36bb55d0b.png
image.png

img_12fa1eace254c4e95071954eb0a64024.png
客户最大订单分析结果.png

多次购买客户分析

选定客户名称这一列，进行保留重复项

img_e9d2d67edd24089d3462c499600b221b.png
保留重复项按钮位置.png

只有1次购买记录的客户会被删除，多次购买记录的客户会被保留。
例如一个客户有3次购买记录， 保留重复项后该客户被保留3次购买记录。
img_ca41bba3c7e53e72df3f3e5d62e86678.png
保留结果.png

6.删除错误

打开下载文件中的06-删除错误.xlsx，如下图所示。

img_6738e18f6b92f028dd72254d3a834d42.png
打开文件图示.png

img_ce2cebc291065a17da86921f8be7142d.png
加载数据至查询编辑器中.png

选定 日期这一列，将数据类型改为 整数。
img_ec5984c501f6f5f9d5b3f50e25a0ad5e.png
image.png

img_1c41b67d1427a379a066e3bd71ea2428.png
删除错误行.png

img_cb4f299ec9143652fcc52343dca6540a.png
成功删除.png

选择导航栏 开始中的 关闭并上载至，并按照下图所示设置。
img_a09ae52d70ccb76b760729636105c7d1.png
关闭并上载至原有表.png

img_e5c4dc56af73bbf93ae054d7809191a8.png
成功删除错误行.png

7.转置和反转

打开下载文件中的07-转置和反转.xlsx，如下图所示。

img_9c1ff8ad472c06efdbe9cc44861bd0e1.png
打开文件图示.png

img_c77fe87568a822198e9c7c54f4857e10.png
加载数据到PowerQuery中.png

img_9d6efc191a594323b3c562cd1079a7e7.png
成功加载结果.png

img_549f61c38d9bd64b3f429cf07e6a5c65.png
将标题作为第一行.png

img_dfa60df12b2c2bfc5856fb0be9632c2a.png
转置按钮位置.png

img_49c3da1a5c8d8d504caeceb30af6279a.png
转置后结果.png

img_53f3bedf0231e5974165fde4c2a68df7.png
将第一行作为标题.png

img_6c1718a3bf645e0e9842b22024ba54ee.png
取消自动更改类型.png

img_bead3f143e1d9f8eda7ddcd14d1672bf.png
关闭并上载至原有表格.png

img_2a5e6505814a3d6d2cbd3ec0c89cb60d.png
上载设置.png

img_adbe7d9c7b80f221e88ce32d2104c744.png
转置结果.png

如果上载位置有偏差，自己可以移动表格位置调整至上图所示效果

8.透视和逆透视

打开下载文件中的08-透视和逆透视.xlsx，如下图所示

img_ce94920942cd4d00be8ffe7699ee4506.png
打开文件图示.png

不要选中第一列，选中后面的列，然后点击下图所示的 逆透视列。
img_46a4193783cdd688eb47100a8094980f.png
逆透视1.png

img_aff708fa1e20448b8e5e791f5558c6cc.png
成功逆透视结果.png

选择 关闭并上载至，在窗口中设置值如下图所示。
img_25b834a40d47f4ff01f63790514e7635.png
上载1.png

img_7f597908b2812e74e8a9b98a9dc34b34.png
上载2.png

出现上图所示 2月， 3月的错误，要设置___单元格格式。
img_579e311ae6befea6ba5dc4646e6fcf6c.png
上载3.png

img_455125097ee527b240afc2ed5dd7d7b2.png
成功上载结果.png

透视——不要聚合

选择不要聚合这个表，加载至PowerQuery中第一步操作如下。

img_b9fc04bcc5e9a4cb966d184424f977db.png
透视1.png

值列为 是否完成销售额，点开 高级选项，聚合值函数选择 不要聚合，最后点击 确定。
img_1eaa8a04e77c72389e5d0768e7b9d089.png
透视2.png

img_12dc492293f3f713c7ffdf55ded430ec.png
成功透视结果.png

img_c7ec5c1d2f70f2c4f87dd294fc7dd134.png
加载至原有表.png

img_f97894fc28806a245f882d6ade8c9c42.png
成功加载至原有表结果.png

9.分组依据

打开下载文件中的09-分组依据.xlsx，如下图所示。

img_34f89cffb3ce1b24c859e7dc4ac5a539.png
打开文件图示.png

img_f52aa797a385edd01fff23c3ef3a536e.png
分组依据1.png

img_ad92a9ebd8899fa40c376b277ac7dc58.png
分组依据2.png

img_29936944d3bc5a8c2bc538fee9639f20.png
成功分组结果.png

10.添加列

打开下载文件中的10-添加列.xlsx，如下图所示。

img_7baecb7e0874c7aae76081381dfca9e7.png
打开文件图示.png

img_fb929f6dcf23953dfdc3e214223c891e.png
进行分组操作.png

img_736e0a21e0d8aaecd7ede5071a40e0cd.png
逆序排序.png

img_ccfb4d67e80311c09c3d9844a253a9ef.png
添加索引列.png

img_1a287d073b1497aeb0fea0e8a05f29e4.png
自定义列按钮位置.png

img_ad28ed14a76cbe8a0780f7fd458ea66a.png
自定义列设置.png

img_6a278eb290f720dd780f23afa0249786.png
成功添加自定义列.png

关键词：
excel入门
潇洒坤
目录
相关文章
疯狂的猿
|
6月前
|
Java Apache
EasyPOI操作Excel从零入门
我们不造轮子，只是轮子的搬运工。（其实最好是造轮子，造比别人好的轮子）开发中经常会遇到excel的处理，导入导出解析等等，java中比较流行的用poi，但是每次都要写大段工具类来搞定这事儿，此处推荐一个别人造好的轮子，EasyPOI封装了Apache的POI技术，实现了基本的Excel导入、导出从此不再为Excel操作头疼~
疯狂的猿
424 2
EasyPOI操作Excel从零入门
周周的奇妙编程
|
SQL 关系型数据库 数据库
RDS入门——Excel文件转存到RDS数据库实践
本实验将帮助您快速掌握RDS产品的实例开通，熟悉RDS产品的常用功能与基础操作，完成云上数据库搭建。
周周的奇妙编程
9955 1
虫无涯
|
14天前
|
Java 测试技术 持续交付
【入门思路】基于Python+Unittest+Appium+Excel+BeautifulReport的App/移动端UI自动化测试框架搭建思路
本文重点讲解如何搭建App自动化测试框架的思路，而非完整源码。主要内容包括实现目的、框架设计、环境依赖和框架的主要组成部分。适用于初学者，旨在帮助其快速掌握App自动化测试的基本技能。文中详细介绍了从需求分析到技术栈选择，再到具体模块的封装与实现，包括登录、截图、日志、测试报告和邮件服务等。同时提供了运行效果的展示，便于理解和实践。
虫无涯
53 4
【入门思路】基于Python+Unittest+Appium+Excel+BeautifulReport的App/移动端UI自动化测试框架搭建思路
若峰er
|
6月前
|
存储 程序员 开发工具
【Excel VBA 从入门到出门】二、Excel VBA 简单使用
【Excel VBA 从入门到出门】二、Excel VBA 简单使用
若峰er
134 2
若峰er
|
6月前
|
存储 小程序 开发工具
【Excel VBA 从入门到出门】一、Excel VBA 是个啥？
【Excel VBA 从入门到出门】一、Excel VBA 是个啥？
若峰er
156 2
童小纯
|
6月前
|
Python
Python办公自动化【Excel数据拆分-xlrd、Excel读取数据-openpyxl、Excel写入数据-openpyxl】(二)-全面详解（学习总结---从入门到深化）
Python办公自动化【Excel数据拆分-xlrd、Excel读取数据-openpyxl、Excel写入数据-openpyxl】(二)-全面详解（学习总结---从入门到深化）
童小纯
91 0
红目香薰
|
6月前
|
存储 XML 移动开发
大一保姆级Excel一篇入门全——满足基本办公需求(wps版本——安装方便快捷高效)
大一保姆级Excel一篇入门全——满足基本办公需求(wps版本——安装方便快捷高效)
红目香薰
128 0
童小纯
|
6月前
|
Python
Python办公自动化【Excel查询重复数据、Excel提取身份证号信息、Python操作Excel模块库文档、Python操作Word基础 】(四)-全面详解（学习总结---从入门到深化）
Python办公自动化【Excel查询重复数据、Excel提取身份证号信息、Python操作Excel模块库文档、Python操作Word基础 】(四)-全面详解（学习总结---从入门到深化）
童小纯
168 0
童小纯
|
6月前
|
Python
Python办公自动化【Excel写入数据-xlwt、Excel读取数据-xlrd、Excel更新数据-xlutils、Excel设置样式】(一)-全面详解（学习总结---从入门到深化）
Python办公自动化【Excel写入数据-xlwt、Excel读取数据-xlrd、Excel更新数据-xlutils、Excel设置样式】(一)-全面详解（学习总结---从入门到深化）
童小纯
104 0
赵广陆
POI入门操作Excel单元格
POI入门操作Excel单元格
赵广陆
150 0

热门文章

最新文章

  • 1
    RDS入门——Excel文件转存到RDS数据库实践
  • 2
    C#对word、excel、pdf等格式文件的操作总结
  • 3
    【UiPath2022+C#】UiPath Excel和数据表实战
  • 4
    如何使用免费控件将Word表格中的数据导入到Excel中
  • 5
    Java Poi 创建与读取Excel
  • 6
    将Excel数据导入MySQL（图解）
  • 7
    [SilverLight]DataGrid实现批量输入(like Excel)（补充）
  • 8
    excel日期格式转换为文本格式
  • 9
    poi导出Excel 单元格样式丢失问题
  • 10
    Excel自动筛选、单元格选择不同的值显示不同的颜色、第一行不动
  • 1
    `openpyxl`是一个用于读写Excel 2010 xlsx/xlsm/xltx/xltm文件的Python库。它不需要Microsoft Excel，也不需要.NET或COM组件。
    96
  • 2
    Python代码示例，读取excel表格，将行数据转为列数据。（10）
    171
  • 3
    2024年新一代WebOffice内嵌网页组件，Web网页在线编辑Word/Excel/PPT
    253
  • 4
    技术心得：前端点击按钮，导入excel文件，上传到后台，excel接收和更新数据
    53
  • 5
    这份Excel+Python飞速搞定数据分析手册，简直可以让Excel飞起来
    78
  • 6
    Python对Excel两列数据进行运算【从基础到高级的全面指南】
    262
  • 7
    【Python】已解决：（pandas read_excel 读取Excel报错）ImportError: Pandas requires version ‘2.0.1’ or newer of ‘x
    253
  • 8
    【Python】已解决：（Python xlwt写入Excel样式报错）ValueError: More than 4094 XFs (styles)
    63
  • 9
    【Python】已解决：（Python xlwt写入Excel报错）AttributeError: ‘function’ object has no attribute ‘font’
    112
  • 10
    【Python】已解决：（Python写入Excel表格报错）‘NoneType’ object has no attribute ‘write’
    261

    • 相关电子书

    更多
  • 87-司超——Swoo...1506528287.pdf
  • 5-使用TensorFl...1506522894.pdf
  • 202-MobileGrow...1506518927.pdf
    • 下一篇
    无影云桌面