Loading [MathJax]/jax/output/CommonHTML/config.js
首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >Power Query 系列 (09) - 合并查询,全面超越 VLookup 函数

Power Query 系列 (09) - 合并查询,全面超越 VLookup 函数

原创
作者头像
StoneWM
修改于 2021-03-25 01:45:47
修改于 2021-03-25 01:45:47
4.7K0
举报
文章被收录于专栏:Stone的专栏Stone的专栏

VLookup 函数据说在 Excel 函数的使用频率排名前三,但这个函数也有不少局限性,Microsoft 推出了一个新的函数 XLookup 可以用于取代 VLookup。但 XLookup 目前还没有正式发布。处理多表连接,如果有多条件,之前我比较多的是用 MS Access 的查询,但现在有了 Power Query (PQ),在 Excel 中也能愉快的玩耍了。多条件连接准备放在下一篇。PQ 的查询表合并,实现将两个表横向合并,无论是界面友好性方面,还是功能上,都比 VLookup 强大太多。

举一个例子:现在有一个销售记录,需要从 Material Master 中获取物料的成本价和销售价:

这是 Excel VLookup 的典型场景,我们来看看 PQ 怎么实现。

从当前工作簿加载数据到 PQ

本篇介绍从 当前工作表导入数据到 PQ 的方法。在 Material Master 工作表中,选中数据区域,像下面这样:

然后同时按下 Ctrl + T,Excel 弹出“创建表” 对话框,将 A1:D4 这个区域 (Range) 变成一个 Table。Excel 的 Table 是和 Range 相对应的一个概念,在开发用 Excel Object 对象模型中,这个对象被称为 ListObject

点击数据区域任意单元格,Excel 会多出一个选项卡:【表设计】,切换到【表设计】,将表的名称改为 MaterialMasters。

用同样的方法根据 Sales Journals 工作表创建一个名为 SalesJournals 的表 (ListObject):

接下来,将 MaterialMasters 表和 SalesJournals 表中的数据加载到 PQ。选中 MaterialNasters 表中任意一个单元格,切换到【数据】选项卡:选择【获取数据】- 【自其他源】- 【自表格/区域】

表中的数据就被导入到 PQ,这是一从当前工作簿 (Current Workbook) 中加载数据到 PQ 最方便的方法。下面给出操作的动图:

由于后面不需要对 MaterialMaster 查询进行输出显示,所以在【关闭并上载】时,选择【关闭并上载至】:

PQ 将界面切回到 Excel 工作表,弹出【导入数据】对话框:

选择【仅创建连接】,点击确定按钮,“仅创建连接”选项控制 MaterialMasters 查询表的数据不被加载到工作表中。下面是动图:

合并查询

用同样的方法将表 SalesJournal 数据加载到 PQ,这样我们现在有了两个查询。选中 SalesJournals 查询,切换到【主页】,点击【合并查询】:

弹出【合并】对话框,选中 SalesJournal 表的 MaterialNo 字段,然后在中间第二个表中选择 MasterMaters 查询表,也选中第二个表MaterialNo 字段,作为两个表的关联条件。

在连接种类中,选择【左外部】,然后点击确定按钮。PQ 在 SalesJournal 查询表中新建了一个字段:

MaterialMaster 列是一个结构化列,结构化列的每个单元格包含结构化信息,比如 MaterialMaster 字段包含物料的多个信息。结构化列是 PQ 很重要特征,后面会专门写一篇结构化列的博客来介绍其用法。选中 MasterialMaster 字段任意一个单元格,显示区下面部分相应显示对应物料主数据的信息:

下面是合并查询的动图:

点击 MaterialMaster 列的【展开】图标,将 MaterialMaster 列进行展开 (expand):

只选择我们需要的 Cost 和 Price 两个字段:

点击确定按钮,获取 Price 和 Cost 的连接:

新建一列,计算每一行的利润:

完成后点击【关闭并上载】,数据加载至 Excel 工作表。

连接类型

合并查询中。两个表的连接类型有下面几种,相当于数据库中两个表的连接类型。熟悉数据库 SQL 语句的人一看就知道怎么回事。

各种连接的图解说明如下:

示例数据:

github

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
Power Query 系列 (10) - 合并查询多字段关联技巧
本篇介绍的合并查询的时候,需要多个字段关联作为连接条件的操作技巧。Excel Vlookup 函数可以进行多列作为条件的关联,但已经需要高级操作技巧,在 Power Query (PQ) 中实现则非常简单,方法基本上和上篇一样。
StoneWM
2021/03/25
2.5K0
Power Query 系列 (18) - 参数化查询
参数化查询增加了查询的灵活性。Power Query 可以设置和管理参数,同一工作簿下所有查询都可以使用。
StoneWM
2021/03/25
2.7K0
正点原子DS100拆解全过程-硬件工程师必备
之前一篇只针对正点原子DS100手持示波器的使用介绍文章。可作为一个电子工程师,光使用不是我们的风格哈,我们还要拆开看看电路。
良知犹存
2021/02/05
2.2K0
正点原子DS100拆解全过程-硬件工程师必备
Power Query 系列 (03) - 从数据库导入数据
Excel 支持部分数据库数据导入和基于 ODBC 的数据库导入,Power Query (以下简称 PQ) 扩大了直连数据库的范围,并且使用起来更加直观。本篇介绍 MS Access 和 MySQL 数据导入,其他数据库的使用方式类似。也会介绍 从 ODBC 数据源导入数据的方法。
StoneWM
2019/09/11
2.6K0
Power Query 系列 (01) - Power Query 介绍
Power Query 是微软提供的工具,Excel 2013 版作为插件加载使用,从 Office 2016 版开始,Power Query 的功能集成到 Excel 中,可以直接使用。微软推出 Power BI Desktop 后,一系列的工具,比如 Power Query, Power Pivot, Power View 等,都集成在其中。Power Query 定位查询,中文一般翻译为超级查询,主要作用是连接不同种类的数据源,进行数据的转换。下图来自微软官方对 Power Query 的介绍,可以帮助理解。Power Query 主要实现连接和转换功能。
StoneWM
2019/08/30
6.2K0
Power Query 系列 (15) - Table 和 List 转换应用案例
本文通过一个数据处理的案例,说明 table 和 list 相互转换在 Power Query (PQ) 数据处理中的作用。假设有下面的销售数据,需要按月份进行汇总,输出为右边的格式:
StoneWM
2021/03/25
2.6K0
Power Query 系列 (12) - Power Query 结构化列应用案例
本篇数据处理来自一个实际处理数据的简化。下图中,假设左边是一个直观的 BOM 结构展示,数据在 Excel 中存储格式如中间部分所示:第一列为物料编码的级别,第二列为物料编码。数据处理任务:需要在 Excel 中增加一列输出物料编码上一级的物料编码(目标为黄色部分)。
StoneWM
2021/03/25
1.5K0
Power Query 系列 (13) - 自定义函数
以工资类所得应交个税为例,最新的个税起征点为 5000 并按下表的级次进行缴税(假设没有其它扣除项)。
StoneWM
2021/03/25
2K0
Power Query 系列 (17) - BOM数据展开应用案例(2)
在本系列的第 14 篇,我介绍了类似 BOM 的数据应该怎么存储,以及在 Power Query 中如何展开。那篇文章使用创建辅助查询的方法,从而简化了在查询编辑器的操作,小白都是可以理解和掌握的。但如果我们来回顾一下处理的过程,有很多重复的操作,比如合并和展开第 2 级数据,合并和展开第 3 级数据等等。有编程概念的人立即想到,应该用循环来解决啊。在编程语言中,循环是一种基础的控制结构,是基础的东西,但在 PQ 中却没有直接的循环语句,需要通过函数的辅助来实现。上篇介绍了 List.Generate 函数之后,我们可以对 BOM 数据的展开进行优化了。
StoneWM
2021/03/25
9140
Power Query 系列 (08) - 行转列案例
行转列是一种常见的数据处理操作,所以对如何在 SQL 语句中、如何在 pandas 中实现这种行转列做过一些总结。请参考我之前写的博文:
StoneWM
2021/03/25
1.4K0
Power Query 系列 (06) - M 语言结构化数据类型
本篇介绍 Power Query M 语言的三种结构化类型(或称为容器类型):List、Record 和 Table,它们是 Power Query 数据处理的核心。了解本篇三种容器类型后,应该能大体看懂查询编辑器和高级编辑器中的代码逻辑。
StoneWM
2019/12/13
1.9K0
Power Query 系列 (04) - 从 Web 导入数据
因为网络页面具有很大不确定性,比如数据变化,或者页面过几天就不见了,所以无法保证我所选取的页面在您查看的时候一定还在。但基本方法类似,您也可以找到一个类似的页面来操作。
StoneWM
2019/09/17
2K0
Power Query 系列 (05) - M 语言介绍
M 语言或者叫 M 查询语言是 Power Query (简称为 PQ) 幕后的英雄。据说 Power Query Editor 可视化操作可以实现 PQ 80% 的功能,所以从操作层面来说,大部分人不用学习 M 语言,但学习和掌握 M 语言无疑是在数据处理的时候如虎添翼,而且,有一些 M 的基础也能加深对 PQ 操作步骤的理解,进而有可能对这些应用的步骤做一些优化和改善。总的来说,M 是一种值得学习和掌握的技能,Excel 和 Power BI 都可以用。
StoneWM
2019/09/18
2.6K0
还记得面试时被算法支配的恐惧吗?
大多数程序员心里会想"总结的真精辟",当面试到算法时,各种“跪”、“再跪”、“还是跪”......,多少人因为算法而拿不到心仪的offer,算法毁一生啊。
老孟Flutter
2020/03/02
4570
Power Query 系列 (20) - 如何在外部使用Power Query提供的服务
Power Query 作为桌面端数据清理和转换的工具,能极大解放生产力,将繁琐的数据处理工作从重复的劳动中解放出来。那么,Power Query 能否对外提供计算服务呢?或者说 Power Query 有没有对外提供的编程接口? 根据我的探索,似乎没有,但在网络上找到下面的两种 walkaround 方式,都比较小众。所以如果真的需要数据处理、数据分析服务的话,不如选择其他的方案,比如 pandas 等等,拥有更大的自由度。
StoneWM
2021/03/25
2.9K0
小程序群发短信,借助云开发5行代码实现短信群发功能
但是官方文档里已经说明,我们群发短信的时候需要用到的是一组手机号,也就是说需要用数组来存放数据。但是我们这里是一个字符串。那么我们就要分割字符串成数组了。
编程小石头
2021/01/11
1.3K0
小程序群发短信,借助云开发5行代码实现短信群发功能
1分钟实现小程序发短信功能,借助云开发10行代码实现短信验证码登录小程序
不仅仅可以很方便的使用短信功能,还送了咱们1000条免费短信。不用白不用嘛。这1000条短信足够咱们把小程序短信功能,和小程序短信验证码功能都学会了。
编程小石头
2021/01/09
4.6K1
1分钟实现小程序发短信功能,借助云开发10行代码实现短信验证码登录小程序
Power Query 系列 (14) - BOM数据展开应用案例
层次化数据是一种比较常见的数据关系,比如 BOM、公司的组织架构、族谱等等。本文讲解应该如何对层次化数据进行存储和加工输出。设计的场景如下:
StoneWM
2021/03/25
1.1K0
【玩转腾讯云】Python 操作腾讯对象存储(COS)详细教程
django项目中,使用editormd时需要上传本地图片,使用到了腾讯对象存储,通过后台可以将图片上传到COS,由此记录一下。 <font color="red">想了解django中如何引入markdown编辑器可以参考此篇文章 --> django 中引入markdown编辑器</font> 1. 腾讯对象存储 1.1 开通服务 腾讯COS 开通后会赠送免费额度 1.2 后台 [watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9i
ruochen
2021/04/11
20.2K0
【玩转腾讯云】Python 操作腾讯对象存储(COS)详细教程
Power Query 系列 (11) - 函数基础
函数是 Power Query (PQ) 解决问题的核心。之前的博客文章我多层刻意在完成查询的步骤之后显示高级编辑器中 M 语言代码,想必大家已经对 M 的函数有了初步印象,尽管不一定很关注。本篇介绍 M 函数比较重要的知识点。
StoneWM
2021/03/25
1.4K0
推荐阅读
相关推荐
Power Query 系列 (10) - 合并查询多字段关联技巧
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档