excel跨表核对数据 实现教程及技巧
发布时间:2022-12-14 10:56:17
在工作中,我们经常要进行表与表之间的快速核对和匹配,查找函数是小伙伴们的第一选择,常用的有VLOOKUP,LOOKUP还有经典的INDEX+SMALL+IF组合等等。不过这些函数都有很多限制,VLOOKUP只能支持单条件查找,LOOKUP只能找到匹配的第一列,而INDEX+SMALL+IF组合又太难掌握。现在不用担心啦,今天给大家介绍使用Power Query来一次性实现各种要求的多表查找和匹配。
之前给大家介绍过Power Query,EXCEL2016可以直接使用,EXCEL2010和2013必须安装插件才能使用。在EXCEL2016里,Power Query所有使用功能都镶嵌在“数据”选项卡下【获取和转换】组。
案例如图,工作簿里有两个工作表,分别是销售组和销售额。现在要根据大区和小组把“销售额”这个表里的订单数和订单金额匹配到“销售组”这个表里。
这就是典型的多条件查询,查找符合多个条件的数据并返回多列数据。实际工作中跨表核对数据非常常用!由于两个表里的大区和小组都不能作为查找的唯一值,所以需要根据两项进行查找匹配,并且要把订单数和订单金额两列匹配过来。使用函数实现的话就太烧脑了,如何操作呢?步骤如下:
1.点击数据选项卡下,新建查询—从文件—从工作簿。
2.在“导入数据”窗口找到该工作簿点击导入。
3.在“导航器”窗口单击“选择多项”,然后选择两个工作表,点击“编辑”(或者点击“转换数据”)。
进入Power Query编辑器之后,在左侧查询窗口能看到导入的两个工作表查询。
4.由于导入的表格将column作为新标题,为了方便以后的操作,我们先把两个查询的第一行作为标题。点击两个查询,分别点击开始选项卡下的“将第一行用作标题”。
完成如下:
5.接下来进行两个表格的合并查询。选择要填写内容的表“销售组”,点击开始选项卡下,“合并查询”下拉菜单的“将查询合并为新查询”。
6.在“合并窗口”,第一个表是要填写匹配内容的表“销售组”,第二个在下拉窗口选择包含匹配信息的表“销售额”。首先把两个表的“大区”这一列选中,这两列就变成绿色。这就代表着两个表通过“大区”这列进行匹配数据。
然后按住Ctrl键,再次选中两个表的“小组”这一列。这时候,两个表列标签出现了“1”和“2”。其中1列匹配1列,2列匹配2列。点击确定。
注意:下方的联接种类有六种,我们选用第一种“左外部”,即第一个表里的值是不重复值,根据选中的列来把第一个表的所有行联接第二个表里的匹配行。也就是我们常用的VLOOKUP的功能。这里合并查询默认选择第一种。大家有兴趣的话,后续可以介绍其他五种联接种类。
7.查询窗口就会生成一个新查询“Merge1”,在新查询表里就把“销售额”表里的信息匹配出来了。点击销售额这列Table旁的空白进行预览,下方的预览窗格能看到根据相同的大区和小组匹配的销售额表的所有内容。
利用这种方法我们可以在合并窗口自由选择匹配的列数,两列三列甚至更多列都能满足。这样就解决了多条件查找的问题;并且根据匹配的列可以把匹配表所有内容都查找出来。
8.现在把需要导入表格的内容展开到表里。点击“销售额”这列右侧的展开按钮,在下方展开窗格里,选择要展开的列“订单数”和“订单金额”,不要勾选“使用原始列名作为前缀”。
完成如下:
9.最后把这个查询上载到表格里。选择新查询表,点击开始选项卡下的“关闭并上载”。
这样就会把三个查询表都上载到工作簿里,生成三个新工作表。右侧会出现“工作簿查询”窗口,点击新查询,工作簿就会自动跳转到对应的查询工作表。
完成如下:
好了,有关Power Query的合并查询就介绍完了。这种查询方式让两个表格根据多个匹配列进行表与表之间的连接匹配,对于在日常工作中进行复杂的多表查询很有帮助!
excel跨表核对数据 实现教程及技巧的下载地址:
![](https://www.aspxhome.com/images/zang.png)
![](https://www.aspxhome.com/images/jiucuo.png)
猜你喜欢
- 小爱同学发布win10UWP版相信很多人都已经知道了,很多用户都想要安装,但是发现无法安装,其实安装小爱同学对于系统的版本是有一定的要求的,
- 如何在EXCEL表中自动计算员工入职年限?相信很多朋友都不是很清楚吧,其实方法很简单的,下面小编就为大家介绍一下,不会的朋友可以参考本文,希
- Excel表格的每一个单元格都有一个默认的名称,其命名规则是列标加横标,例如A1表示第一列、第一行的单元格。如果要将某单元格重新命名,可以运
- 当我们准备为自己的电脑安装操作系统时,对于目前市面上许多下载网站还有软件平台,许多小伙伴一定不知道该如何选择吧~这样的问题小编觉得我们可以在
- excel2016怎么创建水平项目符号列表?excel2016表格中想要制作创意的水平项目符号列表,该怎么制作呢?下面我们就来看看详细的教程
- Excel是一个常用的软件,下面我来教大家在Excel里如何做到隔列求和,赶紧看看吧大家都知道,Excel是一个常用的软件,下面我来教大家在
- 很多时候,我们需要在一个excel表中求出某一类的个数总和,当这一类是价格或是年龄之类的时候,我们有可能也需要求出这类项的总和。今天,小编就
- 1、先说如何用word打开wps文档大家都知道金山wps文档的扩展名是.wps,那么有一个办法很简单,直接改.wps为word扩展名.doc
- 在使用Excel表格时,很多时候会需要将单元格设置为文本格式。今天来演示下Excel 如何设置文本格式的操作。打开一个Excel文档,以设置
- 标准差是统计学中常用的一个数据,用来统计数据和平均值之间的偏差程度。想要做出数据图表,你可以列出数据,并通过Microsoft Excel轻
- Excel2016表格中怎么固定表头?Excel2016中想要让表头固定,然后数据可以动,这样方便查看,下面我们就来看看详细的教程,需要的朋
- 组策略这个系统功能大家应该不会陌生,在我们更改系统设置的时候经常会使用到他,但是对于Win10家庭版用户而言,系统中默认没有这个功能,那么要
- 有的office文档明明下载到电脑中,但用关键词就是搜不到,这是什么原因呢?原因:可能你的文档名含有空格 方法:去除文档名中多余的空格减少关
- Office 365版本出厂可以免费试用30天,首次打开会提醒激活、购买、试用,如果需要购买可以直接联系微软即可Office 365是一套更
- 有时候我们使用电脑的时候,电脑突然提示“Windows无法访问指定设备、路径或文件”,遇到这种情况我们都打不开文件,这个问题要怎么解决呢?是
- 凭借自适应高刷的优秀体验,以及对续航的优化,iPhone 13 Pro 系列机型吸引了大量用户入手。因此,在今年推出的 iPhone 14
- 随着win10的发布,新的性能可以带来轻松的操作流程,但也有网友对此表示满意。为了让他们的电脑更加个性化,一些朋友想改变win10系统的主题
- 超级实用的Excel图表技巧解析【例】如下图所示的excel图表中,从下至上分三种蓝色作为背景,分别显示差(<50)、中(<10
- Office序列的办公软件,比如Word、Excel、Powerpoint,都存在宏安全性的问题!Office序列的办公软件,比如Word、
- win10系统是一款性能游戏的计算机系统,因其出色的性能而被越来越多的用户选择!各种个性化设置也深受用户喜爱,但最近很多朋友反映,他们设置的