Excel表格中数据比对和查找的几种技巧 - Office办公助手
最专业的Office办公软件学习和原创Office教程网站！
Excel表格中数据比对和查找的几种技巧
发布时间：09-11　来源：Office办公助手
经常被人问到怎么对两份Excel数据进行比对，提问的往往都很笼统；在工作中，有时候会需要对两份内容相近的数据记录清单进行比对，需求不同，比对的的目标和要求也会有所不同。下面Office办公助手()的小编根据几个常见的应用环境介绍一下Excel表格中数据比对和查找的技巧。
应用案例一：比对取出两表的交集(相同部分)
Sheet1中包含了一份数据清单A，sheet2中包含了一份数据清单B，要取得两份清单共有的数据记录(交集)，也就是要找到两份清单中的相同部分。
方法1：高级筛选
高级筛选是处理重复数据的利器。
选中第一份数据清单所在的数据区域，在功能区上依次单击【数据】&&【高级】(2003版本中菜单操作为【数据】&&【筛选】&&【高级筛选】)，出现【高级筛选】对话框。
在对话框中，筛选【方式】可以根据需求选取，例如这里选择&将筛选结果复制到其他位置&；【列表区域】就是之前所选中的第一份数据清单A所在的单元格区域；【条件区域】则选取另外那份清单B所在的单元格区域。如下图所示：
点击【确定】按钮后，就可以直接得到两份清单的交集部分，效果如下图。其中两个清单中虽然都有【西瓜】和【菠萝】，但是由于数量不一致，所以没有作为相同记录被提取出来。
这个操作的原理，就是利用了高级筛选功能对于匹配指定条件的记录进行筛选的功能，把两张表中的任意一张作为条件区域，在另外一张表中就能筛选出与之相匹配的记录，忽略掉其他不相关的记录。
需要注意的是，使用高级筛选的时候务必注意两个清单的标题行要保持一致(高级筛选中作为条件区域的前提)，并且在选取【列表区域】和【条件区域】的时候都要把标题行的范围包含在其中。
方法2：公式法
使用公式进行比对的方法有很多，如果是单列数据对比比较常用的函数是，如果是多列数据记录对比，比较胜任。
在其中一张清单的旁边输入公式：
=SUMPRODUCT((A2&B2=Sheet2!A$2:A$13&Sheet2!B$2:B$13)*1)
并向下复制填充。其中的Sheet2!A$1:A$13和Sheet2!B$2:B$13是另一张清单中的两列数据区域，需要根据实际情况修改。公式结果等于1的记录就是两个清单的交集部分，如下图所示：
应用案例二：取出两表的差异记录
要在某一张表里取出与另一张表的差异记录，就是未在另外那张清单里面出现的部分，其原理和操作都和上面第一种场景的差不多，所不同的只是筛选后所选取的集合正好互补。
方法1：高级筛选
先将两个清单的标题行更改使之保持一致，然后选中第一份数据清单所在的数据区域，在功能区上依次单击【数据】&&【高级】，出现【高级筛选】对话框。在对话框中，筛选方式选择&在原有区域显示筛选结果&；【列表区域】和【条件区域】的选取和前面场景1完全相同，如下图所示：
点击【确定】完成筛选，将筛选出来的记录全部选中按【Del】键删除(或做标记)，然后点击【清除】按钮(2003版本中为【全部显示】按钮)就可以恢复筛选前的状态得到最终的结果，如下图所示：
方法2：公式法
使用公式的话，方法和场景1完全相同，只是最后需要提取的是公式结果等于0的记录。
应用案例三：取出关键字相同但数据有差异的记录
前面的两份清单中，【西瓜】和【菠萝】的货品名称虽然一致，但在两张表上的数量却不相同，在一些数据核对的场景下，就需要把这样的记录提取出来。
方法1：高级筛选
高级筛选当中可以使用特殊的公式，使得高级筛选的功能更加强大。
第一张清单所在的sheet里面，把D1单元格留空，在D2单元格内输入公式：
=VLOOKUP(A2，Sheet2!$A$2:$B$13，2，0)&&B2
然后在功能区上依次单击【数据】&&【高级】，出现【高级筛选】对话框。在对话框中，筛选方式选择&在原有区域显示筛选结果&；【列表区域】选取第一张清单中的完整数据区域，【条件区域】则选取刚刚特别设计过的D1:D2单元格区域，如下图所示：
点击【确定】按钮以后，就可以得到筛选结果，就是第一张中货品名称与第二张表相同但数量却不一致的记录清单，如下图所示：
同样的，照此方法在第二张清单当中操作，也可以在第二张清单中找到其中与第一张清单数据有差异的记录。
这个方法是利用了高级筛选中可以通过自定义公式来添加筛选条件的功能，有关高级筛选中使用公式作为条件区域的用法，可参考本站发布的；另外一篇教程：
Excel中数据库函数和高级筛选条件区域设置方法详解
方法2：公式法
使用公式还是可以利用前面用到的SUMPRODUCT函数，在其中一张清单的旁边输入公式：
=SUMPRODUCT((A2=Sheet2!A$2:A$13)*(B2&&Sheet2!B$2:B$13))
并向下复制填充。公式中的包含了两个条件，第一个条件是A列数据相同，第二个条件是B列数据不相同。公式结果等于1的记录就是两个清单中数据有差异的记录，如下图所示。这个例子中也可以使用更为人熟知的来进行匹配查询，但是VLOOKUP只适合单列数据的匹配，如果目标清单中包含了更多字段数据的差异对比，还是的扩展性更强一些。
上一篇： 下一篇：
专题教程推荐
(window.slotbydup=window.slotbydup || []).push({
id: '3577897',
container: s,
size: '300,450',
display: 'inlay-fix'
热门资源下载查看: 1037|回复: 5
多表格多更如何查找匹配数据项
阅读权限10
在线时间 小时
如何查找匹配多列数据项，有多个匹配条件项的数据
如附件，通过表格数据源中的数据，在“计算公式”sheet中要得出&数据源&sheet中相应的价格数据，要用什么 公式 组合？
17:21 上传
点击文件名下载附件
100.74 KB, 下载次数: 36
阅读权限70
在线时间 小时
=SUMIFS(数据源!C:C,数据源!A:A,B2,数据源!B:B,A2)
阅读权限10
在线时间 小时
=SUMIFS(数据源!C:C,数据源!A:A,B2,数据源!B:B,A2)
谢谢您的回复 ，可能我描述不是很清楚；
我是想要匹配既满足A列表格，且满足B列表格数据的C列的数据，是查找结果，不是求和，谢谢！
阅读权限20
在线时间 小时
C2单元格输入“=VLOOKUP(B2&A2,IF({1,0},数据源!$A$1:$A$3000&数据源!$B$1:$B$3000,数据源!$C$1:$C$)”三键输入
阅读权限90
在线时间 小时
MAX((数据源!$B$2:$B$2102=A2)*(数据源!$A$2:$A$2102=B2)*(数据源!$C$2:$C$2102))
阅读权限10
在线时间 小时
本帖最后由
10:46 编辑
=LOOKUP(1,0/((A2=数据源!B:B)*(计算公式!B2=数据源!A:A)),数据源!C:C)
10:46 上传
点击文件名下载附件
175.97 KB, 下载次数: 19
最新热点 /1
ExcelHome每周都有线上直播公开课，
国内一流讲师真身分享，高手贴身答疑，
赶不上直播还能看录像，
关键居然是免费的！
厚木哥们都已经这么努力了，
你还好意思说学不好Office。
玩命加载中，请稍候
玩命加载中，请稍候
Powered by
本论坛言论纯属发表者个人意见，任何违反国家相关法律的言论，本站将协助国家相关部门追究发言者责任！ & & 本站特聘法律顾问：徐怀玉律师 李志群律师如何根据“关键字”选出Excel 2013中匹配的数据 如何用excel筛选出包含三个以上 - 小小知识站
如何根据“关键字”选出Excel 2013中匹配的数据 如何用excel筛选出包含三个以上
描述：...语言文学 ,A1,1))),NOT(ISERR(FIND( 不限 ,A1,1))))对B列进行筛选,选择内容为TRUE的 类似问题：excel 筛选关键字用函数:我想筛选某一行中有包含有“是 如何用excel筛选出包含三个以上的关键词如想再同一列中筛选 请问excel筛选时怎么筛选三个以上的关键词? excel筛选功能自定义中有包含选项 在excel本身的功能中,单列最多只能同时筛选两项(两个关
①启动Excel2013，如下图所示，关键字是广州、桂林和深圳。也就是说，要选出籍贯为广州、桂林和深圳这三地的内容。首先选中A1：C10表格区域，单击菜单栏--数据--高级。②弹出高级筛选对话框，我们在方式中，选择将筛选结果复制到其他位置，这样就不会覆盖源数据，便于对比观察。③设定条件区域为E1：E4，也就是图中的关键字区域。④将筛选结果显示到以G1开头的单元格区域，确定。⑤筛选完成，对比源数据，可以看到籍贯为武汉、珠海和湛江的三个数据被剔除了，只保留了含有关键字广州、桂林和深圳的数据。 注：更多精彩教程请关注电脑教程栏目，电脑办公群:欢迎你的加入
分享给小伙伴们：
主题：如何用excel筛选出包含三个以上的关键词如想再同
描述：...语言文学 ,A1,1))),NOT(ISERR(FIND( 不限 ,A1,1))))对B列进行筛选,选择内容为TRUE的 类似问题：excel 筛选关键字用函数:我想筛选某一行中有包含有“是 如何用excel筛选出包含三个以上的关键词如想再同一列中筛选 请问excel筛选时怎么筛选三个以上的关键词? excel筛选功能自定义中有包含选项 在excel本身的功能中,单列最多只能同时筛选两项(两个关
主题：怎样将两个EXCEL表格匹配起来
描述：...似问题：我有两个EXCEL表格,怎么匹配相同的跟不同的数据。 如何模糊匹配两个EXCEL表中数据的关键字? 2个excel表格如何匹配 如何把两个Excel表匹配并合并??急 如何把两个Excel表匹配并替换??急 能帮我解决一下,两个excel表匹配吗?怎么试都不成功的 如何让下面的两个excel表格匹配,并且将这两个excel表格合 如何匹配两个Excel表中的内容? 两个EXCEL表格多条件匹配问题
主题：vba语句select case End Select
描述：...形式为 expression，expression To
expression，Is parisonoperator expression的一个或多个组成的分界列表。To 关键字可用来指定一个数值范围。如果使用
To 关键字，则较小的数值要出现在 To 之前。使用 Is 关键字时，则可以配合比较运算符(除 Is 和 Like 之外)来指定一个数值范围。如果没有提供，则
Is 关键字会被自动 入。statements-n 可选参数。一条或多条语句，当 testexpression 匹配expressionlis...
主题： 旺旺如何添加 人
描述：...点击添加按钮;在查看/
人 页面， 可以修改 人 ，最后点击确定;方法二：按关键字查找，输入 /购买的关键字，点击查找按钮，如下图;在筛选出的条件中，找到需要添加的 人，点击加为 人按钮;其他 作步骤同方法一。您添加的商友也会收到系统 和电子 ， 他您已添加他为好友。
主题：用iOS设备中Safari在网页上查找关键字教程
描述：Safari被认为是iPhone/iPad上用来浏览网页体验最好的应用之一。你可以通过 快速的浏览不同的网页，并且在不同的设备上同步书签等。iPhone/iPad的Safari浏览器有一个很有用，但是却不太引人注目的功能用来搜索web页面上的文本 。 你在浏览 帖子，想要在一个页面上从众多的文本内容中找到特定主题时是很有用的一个功能。要实现这个功能，你只需要：从Safari主页上...
主题：在IE9 栏中启用BING搜索的最匹配结果
描述：...浏览器 栏中输入
。若不记得 的情况下，我们可能会选择在 栏中搜索 关键字，然后再打开的搜索结果页中单击打开
。要是我们在输入 关键字以后，按下回车键即可以直接打开
，这样的是不是会更加方便呢?Bing和Google搜索引擎都为我们提供了这种 ，在IE9浏览器中的功能名称为最匹配的结果搜索。上次我们便为大家介绍了如何开启...
06-1106-1106-1106-1106-11
09-1308-1408-1508-1408-26
也许你感兴趣