Excel跨表提取，微软查询KO所有函数

很多伙伴跨表提取数据的第一反应是vlookup之类的函数，或者是index+small+if公式。其实如果提取多列数据，一个被放在角落很久的微软查询才是王道！它不仅操作简单，容易解决“一对多”，而且它生成的结果表可以与数据源形成动态链接。当数据源发生变化时，结果会动态更新！

今天给大家分享一个很少使用却很神奇的功能——Microsoft Query，帮助大家解决两个表“一对多”的数据提取问题，或者解决一个表与另一个表匹配生成特定数据的问题。

如下图所示，同一工作簿中有两个工作表。“部门人员信息表”列出了每个部门的员工姓名和对应的主管，“省级销售数据表”列出了每个员工负责的多个省份和对应省份的三个月销售数据。现在需要根据名称列将两个表汇总成一个表。

原始表格

要求的结果

如何使用Microsoft Query？步骤01启用Microsoft Query并加载数据。

(1)新建一个工作簿，在“数据”选项卡下的“获取外部数据”组中的“从其他来源”下拉菜单中单击“从MicrosoftQuery”。

在“选择数据源”窗口中，单击“数据库”选项下的“Excel文件”,并选中下面的“使用”。

在“选择工作簿”窗口右侧的目录中找到数据源的位置，在左侧的数据库名称中找到文件，然后单击“确定”。

(2)有时系统会提示如下窗口:“数据源不包含可见表”。这个不用担心。单击确定。

进入下方左侧的查询向导窗口，点击下方的选项按钮，打开右侧的表选项窗口，勾选系统表，点击确定。

这样，数据源中的工作表将出现在查询向导窗口中。这是因为Excel调用自己的工作表“系统表”，勾选后可以在查询窗口看到。

接下来，选择两个工作表，单击中间的“>”按钮，将左边的“可用表和列”添加到右边的“查询结果中的列”中，然后单击Next。

此时，将弹出另一个窗口，指示查询向导无法继续，因为该表无法链接到您的查询。您必须在MicrosoftQuery中的表之间拖动字段，并手动链接它们。“别管这个，点击确定。

步骤02根据所需项目匹配数据。

此时，我们进入Microsoft Query窗口，顶部有一个类似EXCEL的菜单栏，中间有一个表格区，显示我们添加的两个表格和对应的字段。下面的数据区域是合并两个表的结果。

这时候数据区的结果是混乱的，因为我们没有给两个表添加关系。这两个表通过名称列一一对应。

(1)用鼠标选中左边部门人员信息表中的姓名，拖动到右边省级销售数据表中的姓名上，然后松开鼠标。这时，在两个表的“名称”字段之间出现了一条两端带有小节点的连接线。下面的数据区会立即更新。

(2)由于有两列同名，我们选择其中一列，点击菜单栏中“记录”下的“删除列”。

步骤03将结果数据返回到Excel工作表。

最后要做的是将结果返回到EXCEL。

(1)点击菜单栏左侧的按钮“SQL”将数据返回到Excel中。

(2)导入数据窗口出现在EXCEL中，我们选择显示为“表格”并放置在现有的工作表中。

返回的结果如下:

在这三个简单的步骤中，我们完成了所需的数据匹配，并生成了一个新的数据表。

额外的快乐:我们发现Microsoft Query生成的数据是一个超级表，你还可以直接创建数据透视表或数据透视图。

同时，该表与数据源动态链接。例如，我们修改原始数据，然后单击保存关闭。

在返回的结果上右键单击刷新。

所以数据会同步。

需要注意的是，使用这种方法，一定要保证数据来源的规范性。要求工作表不能包含与数据源无关的数据，表格的第一行是列标题。如果要实现动态链接，工作簿和工作表的名称和位置是不能修改的。

最近怎么样？你学会了吗？是不是比PQ简单，比函数简单？

相关学习推荐:excel教程

这几篇文章你可能也喜欢：

• Excel函数学习财务对账的几个函数(分享)(如何编写财务对账系统)
• Excel案例分享:用正负数字制作图表(正负数字自动识别)(如何制作负图表)
• 实用Excel技巧分享:快捷方式示例【Ctrl+E】
• Excel函数学习的选择函数vs IF函数
• 【整理分享】10组神奇实用的Excel快捷键，大神也在用！（excel快捷键及excel常用技巧整理）

本文由主机参考刊发，转载请注明：Excel跨表提取，微软查询KO所有函数 https://zhujicankao.com/110587.html