Power Query零基础入门（Excel2021专业加强版）

已于 2022-11-19 16:49:57 修改
阅读量3.1w 收藏 171
点赞数 31
分类专栏： 数据分析 文章标签： 数据分析
于 2022-09-14 15:53:43 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_52421831/article/details/126853895
版权

一、导入数据及切换存储位置

1.导入数据

数据>获取数据>启动Power Query编辑器>主页>新建源

例如：导入CSV文件

数据>获取数据>启动Power Query编辑器>主页>新建源>文件>文本/CSV>选择文件>导入>确定>主页>关闭并上载至>现有工作表 或者 新工作表>确定

2.回到Power Query编辑器

1）方法一：数据>获取数据>启动Power Query编辑器

2）方法二：数据>查询和链接>双击需要的链接

3.切换存储位置

加载到Power Query编辑器的不可以直接在进行关闭并上载至，即不能直接对获得的数据表的位置进行修改

1）解决方法一：数据>查询和链接>右键目标链接>加载到>仅创建链接>确定>在右键选择加载到>表>现有工作表 或 新工作表>确定

2）解决方法二：剪切数据表到需要的位置

二、行列管理及筛选

1.删除空白行

第三季度各区预计销售额汇总
区域产品分类预计销售额
东区服装88,888,888
东区辅助用品161,574
东区配件3,635,720
东区自行车22,059,206
区域产品分类预计销售额
南区服装281,120
南区辅助用品126,424
南区配件2,923,210
南区自行车17,408,316
区域产品分类预计销售额
西区服装452,260
西区辅助用品228,852
西区配件4,058,615
西区自行车25,954,963
区域产品分类预计销售额
北区服装556,511
北区辅助用品267,961
北区配件5,275,135
北区自行车31,938,793

1）将需要处理的表上传到Power Query中：数据>来自表格/区域

2）删除空白行：主页>删除行>删除最前面几行(可以选择删除前面的多少行)；删除空行

3）提升标题：主页>将第一行用作标题

4）筛选掉中间的标题：点击标题字段的筛选按钮>将需要去除的内容取消勾选

5）修改数据类型：点击标题字段左边的数据类型按钮>选择合适的数据类型

6）将数据上载到Excel中：主页>关闭并上载至>现有工作表 或 新工作表>确定

2.删除间隔行

区域产品分类预计销售额
东区服装372,960
辅助用品161,574
配件3,635,720
自行车22,059,206
东区 汇总26,229,460
南区服装281,120
辅助用品126,424
配件2,923,210
自行车17,408,316
南区 汇总20,739,071
西区服装452,260
辅助用品228,852
配件4,058,615
自行车25,954,963
西区 汇总30,694,691
北区服装556,511
辅助用品267,961
配件5,275,135
自行车31,938,793
北区 汇总38,038,400
总计115,701,621

1）将需要处理的表上传到Power Query中：数据>来自表格/区域

2）提升标题：主页>将第一行用作标题

3）删除间隔行：主页>删除行>删除间隔行>要删除的第一行(填写第一个要删除的行的行号，例如：5)>要删除的行数(填写需要删除几行，例如：1)>要保留的行数(例如：前面删除的数据在第5行，删除一行，则保留的行数为4)

4）过滤掉其他不需要的行：点击标题字段的筛选按钮>将需要过滤的内容取消勾选

5）填充控制：转换>填充>向下 或者 向上

6）将数据上载到Excel中：主页>关闭并上载至>现有工作表 或 新工作表>确定

三、数据清洗

陈二陈二jason chenjasonchen男1976
韩十四韩十四kevin hankevinhan男1975年
李四李四leo lileoli男1985
刘一刘一jerry liujerryliu男1971
钱十三钱十三bill qianbillQIAN男1979
乔十二乔十二jenny qiaojennyqiao女1974
孙七孙七lawrence sunlawrencesun男1996
王五王五vince wangvincewang男1980
吴九吴九joanna wujoannawu女1971
萧十一萧十一linda xiaolindaxiao女1973年
张三张三karen zhangkarenzhang女1977
赵六赵六carol zhaocarolzhao女1983
郑十郑十anderson zhengandersonzheng男1984
周八周八lily zhoulilyzhou女1984

1.清除空白字符

选中字段>转换>格式>修正>格式>清除

2.将多个连续的空白字符替换为一个

1）将该字段按照空白字符分为两个字段：选中字段>转换>拆分列>按分隔符>选择或输入分隔符：空格>拆分位置：最左侧的分隔符 或者 最右侧的分隔符>确定

2）清除空白字符：选中拆分得来的两个字段>转换>格式>修正>格式>清除

3）转换为首字母大写：选中字段>转换>格式>每个字词首字母大写

4）将处理好的两个字段拼接会一个字段：选中两个字段>转换>合并列>分隔符：自定义为空格(按需求选择)>新列明：为合并后的列起一个列名>确定

四、拆分&合并&提取

会员ID姓名身份证号兴趣爱好
CT-2192王天宇546756198510118573运动
CT-2193方文成546803197810073602饮茶\服饰
CT-2194钱顺卓175873198106214890书籍
CT-2195王崇江344564197807067016影视\饮食\饮食
CT-2196黎浩然347430199303047980书籍\影视\音乐\运动
CT-2197刘露露879169198010243578饮茶\饮食\影视\服饰
CT-2198陈祥通763426196808244909
CT-2199徐志晨763761199903097962饮茶\阅读\饮茶
CT-2200张哲宇870513198311181384旅游\音乐\饮茶
CT-2201王炫皓870748196712186594运动\旅游\饮茶
CT-2202王海德332833197210209467运动\影视
CT-2203谢丽秋728516198411232398影视\饮食
CT-2204王崇江854419199006022643饮茶\饮茶
CT-2205关天胜778963196610244612饮茶\服饰\服饰
CT-2206唐小姐778795197709246969饮茶\饮茶\运动\阅读
CT-2207钱顺卓763111196607043574旅游\旅游\影视
CT-2208刘长辉413330196811129566影视\饮食\影视
CT-2209李晓梅347801196907079482书籍\饮食\影视
CT-2210方文成347100197509164483运动\旅游
CT-2211王雅林625181198010028372
CT-2212谢丽秋362160198210192639影视\饮茶

1）将需要处理的表上传到Power Query中：数据>来自表格/区域

2）提取姓：选中姓名字段>添加列>提取>首字符>计数：1>确定>双击字段名称以修改

3）提取名：选中姓名字段>添加列>提取>范围>起始索引(起始下标为0)：1，字符数：10(按需求填写)>确定>双击字段名称以修改

4）提取出生年份：选中身份证号字段>添加列>提取>范围>起始位置：6，字符数：4>确定>双击字段名称以修改

5）提取首要兴趣：选中兴趣爱好字段>添加列>提取>分隔符之前的文本>分隔符：\（按需求填写）>确定>双击字段名称以修改

6）判断性别：选中身份证号字段>添加列>提取>范围>起始位置：16，字符数：1>确定>双击字段名称以修改>将数据类型转换为整型>添加列>信息>奇数 或者 偶数(返回逻辑值)>将数据类型转换为文本>右键>替换值>按需求填写，要替换的值 和 替换为>确定

7）添加尊称列：

（1）生成尊称：添加列>条件列>新列名：尊称，判断条件：列名为性别，如果为男则输出先生，否则输出女士>确定

（2）拼接尊称和姓：先选中姓，再选中尊称>添加列>合并列>分隔符不需要>新列名：尊称>确定>将原来的尊称删掉

8）将数据上载到Excel中：主页>关闭并上载至>现有工作表 或 新工作表>确定

会员ID姓名身份证号兴趣爱好姓名出生年份首要兴趣性别尊称
CT-2192王天宇546756198510118573运动王天宇1985运动男王先生
CT-2193方文成546803197810073602饮茶\服饰方文成1978饮茶女方女士
CT-2194钱顺卓175873198106214890书籍钱顺卓1981书籍男钱先生
CT-2195王崇江344564197807067016影视\饮食\饮食王崇江1978影视男王先生
CT-2196黎浩然347430199303047980书籍\影视\音乐\运动黎浩然1993书籍女黎女士
CT-2197刘露露879169198010243578饮茶\饮食\影视\服饰刘露露1980饮茶男刘先生
CT-2198陈祥通763426196808244909陈祥通1968女陈女士
CT-2199徐志晨763761199903097962饮茶\阅读\饮茶徐志晨1999饮茶女徐女士
CT-2200张哲宇870513198311181384旅游\音乐\饮茶张哲宇1983旅游女张女士
CT-2201王炫皓870748196712186594运动\旅游\饮茶王炫皓1967运动男王先生
CT-2202王海德332833197210209467运动\影视王海德1972运动女王女士
CT-2203谢丽秋728516198411232398影视\饮食谢丽秋1984影视男谢先生
CT-2204王崇江854419199006022643饮茶\饮茶王崇江1990饮茶女王女士
CT-2205关天胜778963196610244612饮茶\服饰\服饰关天胜1966饮茶男关先生
CT-2206唐小姐778795197709246969饮茶\饮茶\运动\阅读唐小姐1977饮茶女唐女士
CT-2207钱顺卓763111196607043574旅游\旅游\影视钱顺卓1966旅游男钱先生
CT-2208刘长辉413330196811129566影视\饮食\影视刘长辉1968影视女刘女士
CT-2209李晓梅347801196907079482书籍\饮食\影视李晓梅1969书籍女李女士
CT-2210方文成347100197509164483运动\旅游方文成1975运动女方女士
CT-2211王雅林625181198010028372王雅林1980男王先生
CT-2212谢丽秋362160198210192639影视\饮茶谢丽秋1982影视男谢先生

五、转置&反转行

1.转置

月份1月2月3月4月5月6月
金额12,06324,03823,76820,16821,03327,299

1）将数据加载到Power Query中：数据>来自表格/区域

2）转换>将第一行用作标题 下拉选项>将标题作为第一行>转置>将第一行用作标题>将月份转换为文本

3）将数据上载到Excel中：主页>关闭并上载至>现有工作表 或 新工作表>确定

月份金额
1月12063
2月24038
3月23768
4月20168
5月21033
6月27299

2.反转行

内容
对酒当歌
人生几何
譬如朝露
去日苦多
慨当以慷
忧思难忘
何以解忧
唯有杜康
青青子衿
悠悠我心
但为君故
沉吟至今

1）将数据加载到Power Query中：数据>来自表格/区域

2）将字段拆分为四列：转换>拆分列>按字符数>字符数：1，拆分：重复>确定

3）处理：转置>反转行>转置>选中所有字段>转换>合并列>确定

4）将数据上载到Excel中：主页>关闭并上载至>现有工作表 或 新工作表>确定

内容
歌当酒对
何几生人
露朝如譬
多苦日去
慷以当慨
忘难思忧
忧解以何
康杜有唯
衿子青青
心我悠悠
故君为但
今至吟沉

六、分组依据

订单编号客户ID客户名称客户省份销售代表ID下单日期产品ID产品名称数量单价金额
SO45814129491甘肃29491自行车销售有限公司甘肃2132016/2/29731帽子15.195.19
SO45814129491甘肃29491自行车销售有限公司甘肃2132016/2/29743山地自行车15204030599.9
SO45814129491甘肃29491自行车销售有限公司甘肃2132016/2/29745山地自行车车架4722.592890.36
SO45814129491甘肃29491自行车销售有限公司甘肃2132016/2/29752山地自行车袜子75.739.9
SO45815129549海南29549自行车销售有限公司海南2092016/2/29731帽子35.1915.57
SO45815129549海南29549自行车销售有限公司海南2092016/2/29743山地自行车15204030599.9
SO45815129549海南29549自行车销售有限公司海南2092016/2/29745山地自行车车架2714.71429.4
SO45815129549海南29549自行车销售有限公司海南2092016/2/29752山地自行车袜子65.734.2
SO45815129549海南29549自行车销售有限公司海南2092016/2/29758运动头盔620.19121.14
SO45815129549海南29549自行车销售有限公司海南2092016/2/29759长袖运动衫728.84201.88
SO45816111830广西11830自行车销售有限公司广西2092016/2/29709公路自行车13578.33578.27
SO45817114801浙江14801自行车销售有限公司浙江2042016/2/29709公路自行车13578.33578.27

1.分组依据

1）将数据加载到Power Query中：数据>来自表格/区域

2）主页 或者 转换>分组依据>高级>不够可以添加分组：客户省份；产品名称>聚合字段：列名1为销售数量，操作为求和，柱为数量；列名2为销售金额，操作为求和，柱为金额>确定

3）将数据上载到Excel中：主页>关闭并上载至>现有工作表 或 新工作表>确定

客户省份产品名称销售数量销售金额
上海公路自行车484738367.87
上海公路自行车前轮214158.84
上海公路自行车后轮13878.02
上海公路自行车外胎6158.16
上海公路自行车外胎内胎311.97
上海公路自行车水壶架217.98
上海公路自行车车把165.6
上海公路自行车车架11348418.34
上海冬用手套1212757.59
上海前叉6826.14
上海夏用手套61882.65
上海女士山地短裤3209.97

2.分组依据所有行

新增字段：订单总额，对应订单编号的金额合计

Power Query中的每个单元格可以放一个表

1）将数据加载到Power Query中：数据>来自表格/区域

2）主页 或者 转换>分组依据>高级>根据 订单编号 分组>订单总额，求和，金额；data，所有行>确定

3）点击data字段右边的展开按钮>将订单编号、使用原始列名作为前缀 都取消勾选>确定

4）将数据上载到Excel中：主页>关闭并上载至>现有工作表 或 新工作表>确定

注：每个单元格都可以存储一张表

订单编号客户ID客户名称客户省份销售代表ID下单日期产品ID产品名称数量单价金额订单总额
SO45814129491甘肃29491自行车销售有限公司甘肃2132016/2/29 0:00731帽子15.195.1933535.3
SO45814129491甘肃29491自行车销售有限公司甘肃2132016/2/29 0:00743山地自行车152039.9930599.8533535.3
SO45814129491甘肃29491自行车销售有限公司甘肃2132016/2/29 0:00745山地自行车车架4722.592890.3633535.3
SO45814129491甘肃29491自行车销售有限公司甘肃2132016/2/29 0:00752山地自行车袜子75.739.933535.3
SO45815129549海南29549自行车销售有限公司海南2092016/2/29 0:00731帽子35.1915.5732402.04
SO45815129549海南29549自行车销售有限公司海南2092016/2/29 0:00743山地自行车152039.9930599.8532402.04

3.非重复值计数

针对客户id进行计数

1）将数据加载到Power Query中：数据>来自表格/区域

2）主页>选择列>勾选客户省份，客户ID>确定>分组依据>基本>客户省份>客户数量，非重复行计数>确定

3）将数据上载到Excel中：主页>关闭并上载至>现有工作表 或 新工作表>确定

客户省份客户数量
甘肃65
海南83
广西75
浙江92
天津72
上海90
安徽80
辽宁80
黑龙江66
江苏88
西藏61

七、透视&逆透视

  • 透视: 将字段的各项值转换为新的字段, 项: 字段的各项非重复值

  • 逆透视: 将字段转换为项

1.透视——聚合

1）将数据加载到Power Query中：数据>来自表格/区域

2）选择要透视的列>转换>透视列>值列(按需求选择)>确定

3）将数据上载到Excel中：主页>关闭并上载至>现有工作表 或 新工作表>确定

2.透视——不要聚合

产品区域是否完成
自行车东区完成
自行车南区完成
自行车西区完成
自行车北区完成
配件东区未完成
配件南区完成
配件西区完成
配件北区完成
服装东区完成
服装南区完成
服装西区未完成
服装北区完成
辅助用品东区完成
辅助用品南区完成
辅助用品西区完成
辅助用品北区完成

1）将数据加载到Power Query中：数据>来自表格/区域

2）选中区域字段>转换>透视列>值列选择 是否完成>高级选项>聚合值函数选择 不要聚合>确定

3）将数据上载到Excel中：主页>关闭并上载至>现有工作表 或 新工作表>确定

产品东区南区西区北区
服装完成完成未完成完成
自行车完成完成完成完成
辅助用品完成完成完成完成
配件未完成完成完成完成

3.逆透视

1）将数据加载到Power Query中：数据>来自表格/区域

2）逆透视操作：

（1）需要逆透视的列数量较少：选中所有需要逆透视的列>转换>逆透视列>修改字段名称

（2）需要逆透视的列数量较多：选中所有不需要逆透视的列>转换>逆透视其他列>修改字段名称

3）将数据上载到Excel中：主页>关闭并上载至>现有工作表 或 新工作表>确定

八、分组依据——文本聚合

区域销售代表销售金额
北区孙七922141
北区王五729224
北区赵六951278
北区周八1095339
东区陈二800874
东区李四643085
东区刘一780376
东区张三624187
南区乔十二876410
南区吴九650423
南区萧十一467832
南区郑十369241
西区韩十四1198631
西区钱十三1449233

1）将数据加载到Power Query中：数据>来自表格/区域

2）分组依据：主页>分组依据>高级>区域>销售代表，求和，销售代表；销售金额，求和，销售金额>确定

3）修改公式：

（1）视图>勾选编辑栏

（2）选中销售代表字段>将 = Table.Group(更改的类型, {"区域"}, {{"销售代表", each List.Sum([销售代表]), type nullable text}, {"销售金额", each List.Sum([销售金额]), type nullable number}}) 改为 = Table.Group(更改的类型, {"区域"}, {{"销售代表", each Text.Combine(List.Distinct([销售代表]), "\"), type nullable text}, {"销售金额", each List.Sum([销售金额]), type nullable number}})

4）将数据上载到Excel中：主页>关闭并上载至>现有工作表 或 新工作表>确定

区域销售代表销售金额
北区孙七\王五\赵六\周八3697982
东区陈二\李四\刘一\张三2848522
南区乔十二\吴九\萧十一\郑十2363906
西区韩十四\钱十三2647864

九、日期和时间

开始时间结束时间持续时间日期时间时区
2018/5/25 1:262018/5/27 11:02451.67903112018/5/25 1:26 +09:00
2013/12/1 12:232013/12/22 21:59630.87270892013/12/1 12:23 +09:00
2018/1/10 4:362018/1/24 16:36583.80557022018/1/10 4:36 +09:00
2016/12/22 5:352017/1/8 17:35463.52489582016/12/22 5:35 +09:00
2012/1/26 7:012012/1/27 11:49941.76271132012/1/26 7:01 +09:00
2016/11/24 12:322016/12/15 17:20541.43162062016/11/24 12:32 +09:00
2016/9/7 1:282016/9/17 8:40346.39347282016/9/7 1:28 +09:00
2013/1/22 1:012013/1/26 10:37145.89469292013/1/22 1:01 +09:00
2016/9/3 1:062016/10/1 5:54247.98954692016/9/3 1:06 +09:00
2018/2/28 6:102018/3/29 18:10301.11376482018/2/28 6:10 +09:00
2017/9/5 9:032017/9/6 21:03520.70929392017/9/5 9:03 +09:00
2016/8/26 6:422016/9/20 16:18792.52332882016/8/26 6:42 +09:00

将数据加载到Power Query中：数据>来自表格/区域

转换和添加列中都有日期和时间，前者是在原字段中操作，后者是新增字段进行操作

1.年的处理

选中开始时间>添加列>日期>年限

返回值为当前时间减去该时间字段中的时间，中间隔了多少天和时分秒

2.日期

选中开始时间>添加列>日期>仅日期

返回值为所选字段中的年月日

3.年&月&季度&周&天

1）年

（1）选中开始时间>添加列>日期>年

返回值为所选字段中的年

（2）选中开始时间>添加列>日期>年>年份开始值

返回值为所选字段中的年的开始值，如2018/1/1 0：00：00

（3）选中开始时间>添加列>日期>年>年份结束值

返回值为所选字段中的年的下一年的开始值，如原始值为2018/5/25 1:26:33，处理后2019/1/1 0：00：00

2）月

（1）选中时间字段>添加列>日期>月

提取时间字段中的月份

（2）选中时间字段>添加列>日期>月>月份开始值

选中字段中月份的开始值

（3）选中时间字段>添加列>日期>月>月份结束值

选中字段中月份的下个月的开始值

（4）选中时间字段>添加列>日期>月>一个月的某些日

获取选中字段中月份总共的天数

（5）选中时间字段>添加列>日期>月>月份名称

返回值为选中字段中月份的英文名称

3）季度

（1）选中时间字段>添加列>日期>季度>一年的某一季度

返回选中字段中月份所在的季度值

（2）选中时间字段>添加列>日期>季度>季度开始值

返回选中字段中月份所在季度的开始月份的开始值

（3）选中时间字段>添加列>日期>季度>季度结束值

返回选中字段中月份所在季度的下个季度的开始月份的开始值

4）周

（1）选中时间字段>添加列>日期>周>一年的某一周

返回选中字段中时间为一年中的第几周

注意：默认以周天作为一周的开始，需将公式中的对应值改为 each Date.WeekOfYear([开始时间], Day.Monday)

（2）选中时间字段>添加列>日期>周>一个月的某一周

返回值为字段中时间是字段中月份的第几周

注意：默认周天为一周的开始，需将公式中的对应值改为 each Date.WeekOfMonth([时间字段], Day.Monday) 实现从周一开始

（3）选中时间字段>添加列>日期>周>星期开始值

返回值为字段中时间所在周的开始那一天的开始值

注意：默认周天为一周的开始，需将公式中的对应值改为 each Date.StartOfWeek([时间字段], Day.Monday) 实现从周一开始

（4）选中时间字段>添加列>日期>周>星期结束值

返回值为字段中时间所在周的下一周的开始那一天的开始值

注意：默认周天为一周的开始，需将公式中的对应值改为 each Date.EndOfWeek([时间字段], Day.Monday) 实现从周一开始

5）天

（1）选中时间字段>添加列>日期>天

返回日期字段中 天 的部分

（2）选中时间字段>添加列>日期>天>每周的某一日

返回星期的索引号

注意：默认周天开始，索引从零开始，需将公式中的对应值改为 each Date.DayOfWeek([时间字段], Day.Monday) 实现从周一开始

（3）选中时间字段>添加列>日期>天>一年的某一日

该日期是一年中的第几天

（4）选中时间字段>添加列>日期>天>一天开始值

返回值与前面年、季度、周的描述相似

（5）选中时间字段>添加列>日期>天>一天结束值

返回值与前面年、季度、周的描述相似

（6）选中时间字段>添加列>日期>天>星期几

返回当前日期为星期几，返回值为英文形式

4.减去天数

需要选中两个时间字段

选中结束时间，再选中开始时间>添加列>日期>减去天数

返回值为两个时间的天数差

5.合并日期和时间

用于日期和时间的合并

选中日期字段，再选中时间字段>添加列>日期>合并日期和时间

返回值为两个字段的合并字段，数据类型为日期格式

6.最早&最新

1）最早

选择两个时间字段>添加列>日期>最早

返回值为两个时间中过去时间更长的

2）最新

选择两个时间字段>添加列>日期>最新

返回值为两个时间中过去时间更短的

7.时间

1）仅时间

选中时间字段>添加列>时间>仅时间

返回日期时间中的时间部分，即时分秒

2）小时

（1）小时

选中时间字段>添加列>时间>小时

返回字段中的小时部分

（2）小时开头

选中时间字段>添加列>时间>小时>小时开头

返回时间字段所在小时的开始值

（3）小时结尾

选中时间字段>添加列>时间>小时>小时结尾

返回时间字段所在小时的下个小时的开始值

3）分钟

选中时间字段>添加列>时间>分钟

返回日期时间中时间部分的分钟值

4）秒

选中时间字段>添加列>时间>秒

返回日期时间中时间部分的秒值

8.本地时间

选择日期时间时区字段>添加列>时间>本地时间

将原来时间的时区改为当前所在时区的时区值，如：修改前2018/5/25 1:26:00 +09:00，修改后2018/5/25 0:26:00 +08:00

9.持续时间

持续时间字段的格式：天.时:分:秒

添加列>持续时间>......

操作与前面的日期时间操作近似

注：只有持续时间格式的时间字段才可使用

10.分析

日期
2016年3月5日
3月,2016年
2016年,3月
2016年，3月
2016年3月份
July 1st,2016
July 1,2016
2016,Jan
2016,july
2016,july 1st

选中字段>转换>日期>分析

 黄色的为无法分析的日期格式

注意：部分日期形式解析不出来

十、数学运算

数值
13
15
10
-19
-13
-20
10.812
20.899
-13.852
-16.288

1.加&减&乘

加：选中字段>添加列>标准>添加>值：填写要给选中的字段统一添加多少，如：5

减、乘 操作与 加 操作近似

2.除

1）除

与加、减、乘操作相似

2）除(整数)

选中字段>添加列>标准>除(整数)>值：填写要给选中的字段统一除以多少，如：3

返回值为两个数相除的整数部分

3）取模

选中字段>添加列>标准>取模>值：填写要给选中的字段统一除以多少，如：3

返回值为两个数相除的余数部分

3.百分比

1）第一个百分比

选中字段>添加列>标准>第一个百分比>值

返回 字段中的值 * 值 / 100，即先将输入的值转换为百分比形式再乘字段中的值

2）第二个百分比

选中字段>添加列>标准>第二个百分比>值

返回 字段中的值 / 值 * 100，即先将字段中的值除以输入的值再乘100

4.科学计数

添加列>科学计数

绝对值：取绝对值、幂：幂运算、平方根：求平方根、阶乘：求阶乘

特殊：

1）求幂：以自然常数 e 作为底数来求指定的次幂，即e的 字段中值 的次方

2）对数：例如10的2次方等于100，即2为以10为底100的对数

（1）常用对数：就是以10为底进行计算，即求以10为底 字段中值 的对数

（2）自然对数：以e作为底数

数值加法乘法减法除整除取模百分比（乘）百分比（除）向上舍入向下舍入舍入偶数奇数符号
131826104.333333333411.3130131313FALSETRUE1
152030125501.5150151515FALSETRUE1
10152073.333333333311100101010TRUEFALSE1
-19-14-38-22-6.333333333-6-1-1.9-190-19-19-19FALSETRUE-1
-13-8-26-16-4.333333333-4-1-1.3-130-13-13-13FALSETRUE-1
-20-15-40-23-6.666666667-6-2-2-200-20-20-20TRUEFALSE-1
10.81215.81221.6247.8123.60431.8121.0812108.12111010TRUEFALSE1
20.89925.89941.79817.8996.96633333362.8992.0899208.99212020TRUEFALSE1
-13.852-8.852-27.704-16.852-4.617333333-4-1.852-1.3852-138.52-13-14-14TRUEFALSE-1
-16.288-11.288-32.576-19.288-5.429333333-5-1.288-1.6288-162.88-16-17-17FALSETRUE-1

5.统计信息

数值1数值2数值3
693
3810
6108
3108
3510
1173
6108
441
459
465

针对一行数据进行运算，必须选中所有列才能激活功能

1）求和

选中所有列>添加列>统计信息>求和

对每行数据进行求和

2）对非重复值进行计数

选中所有列>添加列>统计信息>对非重复值进行计数

返回一行中不同值的个数

3）标准偏差：(((一组数据中的每个值跟这组数据的均值的差)的平方和)的平均数)的平方根

4）其他操作与求和近似

数值1数值2数值3加法最大值平均值计数
69318963
3810211073
6108241083
3108211073
3510181063
1173211173
6108241083
4419433
45918963
46515653

十一、查询

1.追加查询

union: 上下合并

注意：合并的两个表的字段名称必须保持一致，但是顺序不必一致

订单编号金额客户名称
SO491812,182甘肃17890自行车销售有限公司
SO491822,443浙江16830自行车销售有限公司
SO491832,443安徽16944自行车销售有限公司
SO491842,049广西14129自行车销售有限公司
SO491852,049辽宁14134自行车销售有限公司
SO491862,443上海23526自行车销售有限公司
订单编号客户名称客户省份金额
SO49187黑龙江23545自行车销售有限公司黑龙江2,182
SO49188江苏26815自行车销售有限公司江苏2,049
SO49189西藏15525自行车销售有限公司西藏783
SO49190河北25033自行车销售有限公司河北1,000
SO49191新疆19435自行车销售有限公司新疆1,000

（1）将两个表上再到PowerQuery中

（2）主页>追加查询>追加查询(在当前表内追加另一个表或多个表)；将查询追加为新查询(将想要连接的两个表或多个表合并到新的表中处理)>根据需求勾选 两个表 或者 三个或更多表>选择要追加的表>确定

（3）关闭并上载至

订单编号客户名称客户省份金额
SO49187黑龙江23545自行车销售有限公司黑龙江2181.5625
SO49188江苏26815自行车销售有限公司江苏2049.0982
SO49189西藏15525自行车销售有限公司西藏782.99
SO49190河北25033自行车销售有限公司河北1000.4375
SO49191新疆19435自行车销售有限公司新疆1000.4375
SO49181甘肃17890自行车销售有限公司2181.5625
SO49182浙江16830自行车销售有限公司2443.35
SO49183安徽16944自行车销售有限公司2443.35
SO49184广西14129自行车销售有限公司2049.0982
SO49185辽宁14134自行车销售有限公司2049.0982
SO49186上海23526自行车销售有限公司2443.35

2.合并查询

左右合并，相当于join

工号姓名性别年龄
10001洪强男25
10003余婵女27
10004贺立男20
10005万兰女27
10007龙锦男25
10008孟斯云女27
10009邱谦男29
10010袁康男30
工号电话邮箱
100011351514981073516412@qq.com
100021351566868469653798@qq.com
100031353410146131145049@qq.com
100051355035271278347321@qq.com
100061352368361919933678@qq.com
100071352568882239317816@qq.com
100091352805656664913626@qq.com
100101351686192328342132@qq.com

首先将两个表加载到Power Query

合并两个表：主页>合并查询>将查询合并到新的表>选择两个表>分别点击选中两个表的关联字段>选择需要的连接种类>确定

展开另一个表的信息：点击另一个表名称命名的字段右侧的展开按钮>根据需要勾选字段名称>确定

注意：连接种类类似于数据库

  • join: 左右合并,左连接,左连接,内连接,全连接,左反连接,右反连接等

  • 左反: 返回左表存在而右表不存在的数据

  • 右反: 返回右表存在而左表不存在的数据

  • 模糊匹配：将使用模糊匹配执行合并勾选>模糊匹配选项>相似性阈值：当相似度达到多少时就算匹配的

十二、合并多个工作表

1.从工作簿合并多个工作表

前提：表的位置、结构和字段名称是一样的

数据>获取数据>来自文件>从工作簿>找到数据的路径>点击导入>先选择一个表>转换数据>将右侧查询设置中应用的步骤只保留源其他操作步骤都删除>只保留Data字段，其他字段全部删除>点击字段名称右侧的展开按钮>取消勾选使用原始列名作为前缀>确定>转换>将第一行用作标题>点击字段的筛选按钮将重复的标题过滤掉>确定>关闭并上载至

2.从文件夹合并多个Excel

1）工作表名称一致(每个文件内的工作表名一致)

数据>获取数据>来自文件>从文件夹>找到文件夹路径>打开>转换数据>只保留字段名为Content的字段其他字段全部删除>点击字段名右侧的合并文件按钮>选择名称一致的那个表，比如Sheet1>确定>关闭并上载至

2）工作表名称不一致(每个文件内的工作表名不一致)

数据>获取数据>来自文件>从文件夹>找到文件夹路径>打开>转换数据>保留字段名为Content的字段其他字段全部删除>添加列>自定义列>给新列起名：data>自定义列公式：=Excel.Workbook(Content) > 确定>点击data字段右边的展开按钮>只勾选Data字段>确定>对该字段再进行展开>确定>将Content字段删除>转换>将第一行用作标题>将重复的表头过滤掉>关闭并上载至

3.从文件夹合并多个CSV

数据>获取数据>来自文件>从文件夹>找到文件夹路径>打开>转换数据>保留字段名为Content的字段其他字段全部删除>添加列>自定义列>给新列起名：data>自定义列公式：=Csv.Document([Content], [Delimiter="分隔符", Encoding=加载数据时的编码格式]) > 确定>将Content字段删除>对data该字段进行展开>确定>转换>将第一行用作标题>将重复的表头过滤掉>关闭并上载至

十三、管理参数

类似于Excel创建名称: 创建变量, 变量指向某些值

数据>获取数据>来自文件>从工作簿>选择一个工作簿>导入>随便读取其中的任何一个表>删除右侧查询设置中的 更改的类型>主页>管理参数>新建参数>参数名称自定义>建议的值：将工作簿中的所有工作表名称依次写入>指定默认值>当前值>确定>回到查询表>选中右边 查询设置 里的 导航 >用我们创建的参数名称替换当前的名称，如：= 源{[Item="地区信息",Kind="Sheet"]}[Data] 改为 = 源{[Item=工作表,Kind="Sheet"]}[Data]

注：工作表为创建的参数名称，当我们修改工作表的值时，查询表中的数据也会改变

十四、Excel链接MySQL

1.下载并安装连接器

1）Excel专业加强版：MySQL :: Download Connector/NET

2）Excel为家庭学生版： MySQL :: Download Connector/ODBC

2.链接数据库

1）写SQL语句：数据>获取数据>来自数据库>从MySQL数据库>服务器：localhost，如果是服务器的数据库就输入ip地址>数据库：输入要连接的数据库库名>高级>SQL语句：根据需求书写SQL语句>确定>转换数据

2）不写SQL语句：数据>获取数据>来自数据库>从MySQL数据库>服务器：localhost，如果是服务器的数据库就输入ip地址>数据库：输入要连接的数据库库名>确定>选择需要导入的表>转换数据

注：第一次连接数据库需要输入账号密码

Power Query 自学教程
weixin_44781801的博客
05-31 7万+
挖坑很久都没填，突然发现这篇文章阅读量很高，大半年过去了，虽然距离Power Query大神还有很远很远，但是通过学习和摸索，勉强算得上能够熟练操作80%+常用功能。 在使用Power Query半年后，我个人觉得PowerQuery有以下几个优点： 1.Power Query的数据处理逻辑相当于面向过程建模，将数据的处理流程固定下来，之后只需要更新源数据就能通过刷新获得经过处理的结果，能够将使...
Excel（进阶篇）：powerquery详解、PowerQuery的各种用法，逆透视表格、双行表头如何制作透视表、不规则数据如何制作数据透视表
最新发布
storyfull
03-21 2094
Excel（进阶篇）：powerquery详解、PowerQuery的各种用法，逆透视表格、双行表头如何制作透视表、不规则数据如何制作数据透视表
1、大道至简的数据处理工具-（Microsoft Power Query入门）
热门推荐
少年休闲海
12-23 8万+
大道至简的数据处理工具-Microsoft Power Query 告别复杂的excel函数，excel VBA编程，让一切回归简单与职能。 什么样的人群适合这样的一个工具： 1、出纳、会计、统计、仓管、数据分析等与数据打交道的的岗位。 2、不想学处理数据的函数、VBA编程等复杂的功能，但工作又有需要的人。 究竟什么是Microsoft Power Query A、获取数据： B、清
Excel Power Query（孙兴华）- 学习笔记
weixin_50765750的博客
02-21 1941
PowerQuery基础教程
EXCEL中的POWER QUERY功能简介
ABAP 技巧与实战
05-17 1万+
点击蓝字关注我们一前言如果你在工作中或项目中需要大量使用EXCEL处理数据, 那么请务必了解一下POWER QUERY. 因为它将极大的优化你的工作:解决一些EXCEL公式性能很差的情况...
【PowerQuery】基础
Mercy92的博客
06-09 1万+
https://editor.csdn.net/md?not_checkout=1&articleId=115160776 01 添加自定义列 1.调出power query 选择表格区域（或选中表格区域某个单元格），右击，[从表格/区域获取数据]，确认数据来源和是否包含标题 2.常用设置 主要标签-主页、转换、添加列 属性-名称-可以修改表名 应用的步骤-可以撤销步骤 3.添加列 从标签【添加列】-【自定义列】 设置-新列名 自定义公式=_(代表一整行，详见M函数) （【视图】-【编辑栏】查看公式
PowerQuery学习总结
qq_45838249的博客
06-15 1万+
Power Query工具的学习和使用
初学者，Power Query函数Day07
qq_46544455的博客
04-11 2082
SUMMARIZE()：函数可以实现对既定表格的分组，生成分组信息表(不重复多层分组信息)同时还可以基于分组信息进行汇总计算，它返回的是一张表;迭代函数：是在统计函数后加一个X，Sumx(表，表达式)，迭代是表的逐行扫描（迭代），其内容是计算表达式的值，扫描完成后再统计。最大值= Max(‘表’[字段名])最小值= Min('表'[字段名])记录数= Countrows('表’),“新列名”，[列表达式])数据筛选---保留---删除---去重---缺失值、错误值处理---文本处理---时间日期处理。
excel power Query
m0_56914070的博客
06-22 2376
一、power query Power query是微软从Excel 到 PowerBI 的中间产物，它既是PowerBi取数部分的内核，又能够在Excel中使用。 在excel中使用Power query处理数据的能力要高出用excel本身处理数据的性能数倍。power query的操作方法不仅有图形化界面，也有它自己独立的语言：M。power query是excel2016及之后的版本所拥有的功能 它的位置就在excel中数据一栏下的“获取和转换一栏” 它可获取数据的渠道非常多，不仅可以从普通的表
Power Query For Excel数据处理利器
kiku
11-07 8343
Power Query For Excel数据处理利器 告别复杂的excel函数、excel VBA编程，让一切回归简单与智能，让你的工作效率更高。查询增强版（Power Query）是一个Excel插件，是Power BI的一个组件。Power Query 在Excel中通过简化数据发现、访问和合作的操作，从而增强了商业智能自助服务体验。 Power Query是什么？ Power Query ...
数据分析POWER BI之power query
2301_79194110的博客
03-25 4126
ctrl+a全选--数据--获取数据--其他来源--来自表格/区域。转换----选中一列---ctrl---选中另一列---合并列。转换---格式---修整（修整后前面后面的空格没有了）破坏原数据（转换）----提取身份证号中的生日。保留原数据----提取身份证号中的生日t。转换--- 逆透视列（将二维转换为一维）6（前面去掉的位数）8（提取的位数）看着复杂可以：舍入----选则两位。转换---透视列（选中要透视的列）想把年替换为空，格式保持一致。转换---提取----范围。添加列---提取----范围。
Power Query基本功能介绍
03-29
主要介绍Power Query基本功能，对其中的每个功能进行了比较详细的解说。
Power Query学习素材——学生成绩表
02-15
Power Query学习素材——学生成绩表 讲解Power Query的合并查询功能所用。
power query
11-26
power query好用
Power_Query教程2
03-20
查询增强版（Power Query）是一个Excel插件，是Power BI的一个组件。 Power Query在Excel中通过简化数据发现、访问和合作的操作，从而增强了商业智能自助服务体验。 Power Query是一个新的插件，它为BI专业人士以及其他用户，提供了一个无缝的数据发现、数据转换体验,
Power Query for Power BI and Excel
08-19
This book is for people who spend a lot of time working with Excel building reports and dashboards. More specifically, this book is for people who work with Excel building reports and dashboards and who are bored with copying and pasting data into worksheets, bored with clicking the same sequence of buttons every month to clean and shape that data, and bored with fixing the problems associated with complex formulas, dirty data, and the errors that are inevitable when you have to follow the same procedures over and over and over again. The good news is that Power Query is here to free you from these dull, repetitive tasks and give you time to concentrate on what’s important: analyzing your data and gaining insights from it. Even better, Power Query is easy to use and lots of fun to learn (so long as you’re the kind of person who thinks that playing with data can be fun—no need to feel ashamed if you do) and, as a result, it will make you more efficient, more productive, and, hopefully, less bored.
从零开始构建Excel宏：自动化任务的快速入门
本文全面探讨了Excel宏在数据处理和自动化任务中的基础知识、应用价值、实用技巧和高级编程技术。首先介绍了宏的基础知识及其在不同领域中的应用价值，然后详细阐述了VBA编程语言的基础语法、对象模型、错误处理和...
零基础小白如何自学Microsoft Office
小宝哥Code的专栏
01-09 1520
Microsoft Office 是职场、学习和日常生活中最常用的办公套件。无论是写文档、做表格、还是设计PPT，Office 都是必备技能。以下是零基础学习 Office 的完整路线图。Office 套件包括多个应用程序，常用的有：每天投入 12 小时，坚持 46 周可以掌握基础功能。Word 是文档编辑的核心工具，适合用来处理报告、合同、论文等文字内容。基础知识：排版技巧：进阶功能：Excel 是数据处理和分析的利器，适合预算表、数据统计、图表制作等。基础知识：常用功能：公式与函数：进阶功能：Power
【M语言探秘】：掌握Power Query核心编程，编写高效数据查询脚本
参考资源链接：[Power Query教程：从入门到深度开发](https://wenku.csdn.net/doc/6412b75bbe7fbd1778d4a016?spm=1055.2635.3001.10343) # 1. Power Query简介与M语言概述 ## 1.1 Power Query的发展历程 Power ...
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值