转自:夏雪 excel教程
编按:
说到跨表提取数据,很多小伙伴第一反应就是函数如VLOOKUP,或者什么INDEX+SMALL+IF万金油公式。其实,如果提取的是多列数据,有一个被很多人丢在旮旯里许久许久的Microsoft Query才是王者!它不但操作简易,轻易解决“一对多”,而且它生成的结果表可以与数据源形成动态链接,数据源变化了,结果也会动态更新!
今天给大家分享一个很少人用但有奇效功能的Microsoft Query,来帮助大家解决两个表格“一对多”的数据提取,或者说解决用一个表去匹配另一个表生成特定数据的做法。
如下图所示,同一个工作簿里有两个工作表,“部门人员信息表”列出了各部门的员工姓名和对应的主管,“省份销售数据表”列出了每个员工负责的多个省份以及对应省份的三个月销售数据。现在要求把两个表根据姓名这列数据汇总到一个表里。
![db663c85a3b670da6e82b41b80ff68de.png](https://img-blog.csdnimg.cn/img_convert/db663c85a3b670da6e82b41b80ff68de.png)
![dcbe0b479c8eb89b02bb7026739d5a7f.png](https://img-blog.csdnimg.cn/img_convert/dcbe0b479c8eb89b02bb7026739d5a7f.png)
函数我们就不用了。在之前的《我折腾到半夜,同事用这个Excel技巧,30秒跨表核对数据交给领导!》中,Power Query就打败了函数实现多表匹配。这次Microsoft Query操作更简单,甩函数几条街~~~~~~
操作步骤
STEP 01 启用Microsoft Query并加载数据
(1)新建一个工作簿,点击【数据】选项卡下【获取外部数据】组里“自其他来源”下拉菜单的“来自Microsoft Query”。
![71fa4f2717ccb59e276b85ecb00af0c6.png](https://img-blog.csdnimg.cn/img_convert/71fa4f2717ccb59e276b85ecb00af0c6.png)
在【选择数据源】窗口“数据库”选项下点击“Excel Files”,勾选下方的“使用[查询向导]创建/编辑查询”,点击确定。
![fe83a72a91b1b464ba260f45134bb191.png](https://img-blog.csdnimg.cn/img_convert/fe83a72a91b1b464ba260f45134bb191.png)
在【选择工作簿】窗口右侧目录里找到数据源所在的位置,在左侧数据库名找到文件,点击确定。
![51eb7a34e73c4c810f92bd1fd26972cb.png](https://img-blog.csdnimg.cn/img_convert/51eb7a34e73c4c810f92bd1fd26972cb.png)
(2)有时系统会提示如下窗口:“数据源中没有包含可见的表格”,这个不用管,点击确定。
![8e23b57d984c22fde684314377cd19fc.png](https://img-blog.csdnimg.cn/img_convert/8e23b57d984c22fde684314377cd19fc.png)
进入下方左侧的【查询向导】窗口,点击下面的“选项”按钮,打开右侧【表选项】窗口,勾选“系统表”点击确定。
![e926eaae6957d26932e66845913ff904.png](https://img-blog.csdnimg.cn/img_convert/e926eaae6957d26932e66845913ff904.png)
这样【查询向导】窗口就会出现数据源里的工作表了。这是由于Excel把自己的工作表叫做“系统表”,勾选了之后在查询窗口就能看到了。
![8be4f1a67b0824dd53bfc83b4e8a82de.png](https://img-blog.csdnimg.cn/img_convert/8be4f1a67b0824dd53bfc83b4e8a82de.png)
接下来选中两个工作表分别点击中间的“>”按钮把左侧的“可用的表和列”添加到右侧的“查询结果中的列”,点击下一步。
![afc5789a27012bb29f8154ef085c34b3.png](https://img-blog.csdnimg.cn/img_convert/afc5789a27012bb29f8154ef085c34b3.png)
这时又会弹出一个窗口,提示“‘查询向导’无法继续,因为该表格无法链接到您的查询中。您必须在Microsoft Query中的表格之间拖动字段,人工链接。”这个也不用管,点击确定。
![d1be00dd15807ceb4362d0fd7a0b686d.png](https://img-blog.csdnimg.cn/img_convert/d1be00dd15807ceb4362d0fd7a0b686d.png)
STEP 02 按需要项匹配数据
此时我们就进入Microsoft Query窗口,上方是类似EXCEL的菜单栏,中间是表区域,显示了当前我们添加的两个表以及对应的字段。下方的数据区域就是融合了两个表的结果。
![16628012e78ecb9466257c97b58d2dfd.png](https://img-blog.csdnimg.cn/img_convert/16628012e78ecb9466257c97b58d2dfd.png)
这时候数据区域的结果是杂乱无章的,原因是我们没有给两个表添加关系。两个表里是通过姓名列来一一对应的。
(1)用鼠标选中左边“部门人员信息表”中的“姓名”,将其拖曳到右表“省份销售数据表”中的“姓名”上面,然后松开鼠标。这时在两个表的“姓名”字段之间出现了一条两端带有细小节点的联接线。下方数据区域就立即更新了。
![556e8ec09912f52f6e1ec3a7be6b7052.png](https://img-blog.csdnimg.cn/img_convert/556e8ec09912f52f6e1ec3a7be6b7052.png)
(2)由于有两列相同的姓名,我们选中其中一列,点击菜单栏【记录】下方的“删除列”。
![694a0652a721c2faa9ed3965bae223e8.png](https://img-blog.csdnimg.cn/img_convert/694a0652a721c2faa9ed3965bae223e8.png)
STEP 03 把结果数据返回到Excel工作表
最后要做的就是把结果返回到EXCEL。
(1)点击菜单栏“SQL”左侧的按钮,将数据返回到Excel。
![77a5df120b9880b2099402b91e17e712.png](https://img-blog.csdnimg.cn/img_convert/77a5df120b9880b2099402b91e17e712.png)
(2)在EXCEL中出现【导入数据】窗口,我们选择显示为“表”,位置放置在现有工作表。
![a972ea0f891e92050c0a1fbe66b7bf07.png](https://img-blog.csdnimg.cn/img_convert/a972ea0f891e92050c0a1fbe66b7bf07.png)
返回结果如下:
![55a479d1b33f9ac96076fcc414ae3d32.png](https://img-blog.csdnimg.cn/img_convert/55a479d1b33f9ac96076fcc414ae3d32.png)
到此简单的3步我们完成了需要的数据匹配,生成了新的数据表。
优势
我们发现Microsoft Query生成的数据就是一张超级表,也可以直接创建数据透视表或者数据透视图。
同时,这张表是和数据源动态链接的。比如我们修改一下原数据,点击保存关闭。
![8dac49595b12e655c07a512b5a28654f.png](https://img-blog.csdnimg.cn/img_convert/8dac49595b12e655c07a512b5a28654f.png)
在返回结果上右键点击刷新。
![c5d44b89bd0c66d0618f4a8edf074b6c.png](https://img-blog.csdnimg.cn/img_convert/c5d44b89bd0c66d0618f4a8edf074b6c.png)
这样数据就同步过来了。
![d642b88a2a6cc35b852513d6b9ed4436.png](https://img-blog.csdnimg.cn/img_convert/d642b88a2a6cc35b852513d6b9ed4436.png)
需要注意的是,使用这种方法,必须要保证数据源的规范性。要求工作表不能存在与数据源无关的数据,并且表格第一行为列标题。如果要实现动态链接,那么工作簿和工作表的名字和位置不能修改。
怎么样,大家学会了吗?是否比PQ简单,比函数简单?