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只适合单列数据的匹配，如果目标清单中包含了更多字段数据的差异对比，还是的扩展性更强一些。
上一篇： 下一篇：关于EXCEL批量删除单元格部分内容的问题
假如说由D列有一千个单元格，每个单元格的内容如下：&
怎么样才能批量的将每个单元格中的前半部分也就是张三李四都删除只留下后面的数字？（PS:人名都是不同的）
你在旁边插入一列单元格，在相邻的单元格里输入公式：（假如你的数据在A1里），公式在 B1 里
=RIGHT(A1,8) 公式说明：从A1 单元格的右边开始，取你要的个数，这里是 8 位
并往下拉（复制公式），全部好了以后，选中全部的 B列，复制——选择性粘贴——数值——确定。 然后再删除你原来的 A列
已投稿到：
以上网友发言只代表其个人观点，不代表新浪网的观点或立场。查看: 2611|回复: 8
Excel 如何删除单元格中的部分内容?
阅读权限10
在线时间 小时
本帖最后由 xqh0920 于
11:25 编辑
大家好，我是新手。想问下如何在excel单元格里面的某些不要的数字？
如A1单元3，B1单元70，那么问下C1单元里面，如何用公式去掉370这几个数字?得出D1单元1245689
& && && && &A& && && && & B& && && && && &&&C& && && && && && && && &&&D& && && && && &
1& && && & 3& && && && &07& && && & & && && && & 1245689
& && && && && && && && && && && && && && && && && && && && && && && && &要这个结果。
AB数字变化的，C为固定不变。
阅读权限95
在线时间 小时
C列是固定的吗？？
举例太少了，没有代表性。
阅读权限50
在线时间 小时
=IF(ISERR(FIND(&0&,A1&B1)),0,&&)&SUBSTITUTE(C1-SUM(MID(A1&B1,ROW(INDIRECT(&1:&&LEN(A1&B1))),1)*10^(9-MID(A1&B1,ROW(INDIRECT(&1:&&LEN(A1&B1))),1))),0,)
适用于A、B列数字不能重复，但公式太长了
阅读权限10
在线时间 小时
& & & & & & & &
袁勇 发表于
C列是固定的吗？？
举例太少了，没有代表性。
恩，C是固定的。A和B是变化的。
阅读权限10
在线时间 小时
sdc3211772 发表于
=IF(ISERR(FIND(&0&,A1&B1)),0,&&)&SUBSTITUTE(C1-SUM(MID(A1&B1,ROW(INDIRECT(&1:&&LEN(A1&B1))),1)*10^(9 ...
你好，这样只删A啊，B没效果？
阅读权限50
在线时间 小时
xqh0920 发表于
你好，这样只删A啊，B没效果？
数组公式，三键结束
阅读权限100
在线时间 小时
=IF(ISERR(FIND(&0&,A1&B1)),0,&&)&SUBSTITUTE(SUM(IF(ISERROR(FIND(ROW($1:$10)-1,A1&B1)),ROW($1:$10)-1)*10^(10-ROW($1:$10))),0,)复制代码
阅读权限10
在线时间 小时
看见星光 发表于
为什么结果是空的？不是想要剩余的数字？
数组公式，输入公式后，同时按CTRL+SHIFT+ENTER三个键结束&
阅读权限10
在线时间 小时
& & & & & & & &
看见星光 发表于
谢谢你。可以了。
玩命加载中，请稍候
玩命加载中，请稍候
Powered by
本论坛言论纯属发表者个人意见，任何违反国家相关法律的言论，本站将协助国家相关部门追究发言者责任！ & & 本站特聘法律顾问：徐怀玉律师 李志群律师苹果/安卓/wp
积分 155, 距离下一级还需 105 积分
权限: 自定义头衔
道具: 彩虹炫, 涂鸦板, 雷达卡, 热点灯, 金钱卡, 显身卡, 匿名卡下一级可获得
权限: 签名中使用图片
购买后可立即获得
权限: 隐身
道具: 金钱卡, 彩虹炫, 雷达卡, 热点灯, 涂鸦板
悲催签到天数: 16 天连续签到: 1 天[LV.4]偶尔看看III
如何删除EXCEL中有空格所在的行呀，太多了，不想一个个删除该怎么办呢
载入中......
先数据-&筛选，只留空行
然后就可以全选删除了
热心帮助其他会员
总评分:&经验 + 10&
论坛币 + 10&
如何快速删除工作表中的空行？
以下几种方法可以快速删除空行：
方法一：如果行的顺序无关紧要，则可以根据某一列排序，然后可以方便地删掉空行。
方法二：如果行的顺序不可改变，你可以先选择“插入”＼“列”，插入新的一列入在A列中顺序填入整数。然后根据其他任何一列将表中的行排序，使所有空行都集中到表的底部，删去所有空行。最后以A列重新排序，再删去A列，恢复工作表各行原来的顺序。
筛选后删除？
Welcome to the real world, it sucks! You gonna love it:)
不知道还有没有更简单的方法额？
选定数据范围，然后在工具栏数据中选择删除重复项，选定到底是否扩展到其他相应列，确定之后就只剩下一个空格了，然后整体挪动一下就行了
全部选中ctrl+A，然后定位ctrl+G，然后再选择空白就能选出所有的空格了，然后删除
无限扩大经管职场人脉圈！每天抽选10位免费名额，现在就扫& 论坛VIP& 贵宾会员& 可免费加入
加入我们,立即就学扫码下载「就学」app& Join us!& JoinLearn&
&nbsp&nbsp|
&nbsp&nbsp|
&nbsp&nbsp|
&nbsp&nbsp|
&nbsp&nbsp|
&nbsp&nbsp|
如有投资本站或合作意向，请联系（010-）；
邮箱：service@pinggu.org
投诉或不良信息处理：（010-）
京ICP证090565号
京公网安备号
论坛法律顾问：王进律师如何快速删除Excel数据表格中的空白行
在使用Excel处理数据时，如果数据表格中存在很多空白行，不仅会浪费大量的表格空间，而且会使表格显得不够专业和美观。当表格包含的空白行较多时，逐行查找并删除无疑非常繁琐，此时，我们可以借助Excel中的排序或筛选功能，快速地删除表格中的所有空白行。
利用排序法，快速删除数据表格中所有空白行的具体操作步骤如下。
（1）选中工作表中的整个数据区域，如图1所示。
图1 选中工作表中的整个数据区域
（2）在&数据&选项卡的&排序和筛选&选项组中，单击【升序】按钮，从而将数据表格中的空白行移至表格的尾部，如图2所示。
图2 通过排序将空白行移至表格尾部
（3）选中排列在数据表格尾部的所有空白行，在&开始&选项卡的&单元格&选项组中，单击【删除】下三角按钮，在随即打开的下拉列表中，执行【删除工作表行】命令，即可将选中的空白行全部删除，如图3所示。
图3 删除所有空白行
2．自动筛选法
使用自动筛选法删除空白行时，首先需要在整个数据表格中开启自动筛选功能。在默认情况下，如果在Excel 2007中对数据表格套用了表格样式，Excel将自动开启自动筛选功能；如果没有应用表格样式，也可以通过在&数据&选项卡的&排序和筛选&选项组中，单击【筛选】按钮，来开启自动筛选功能。
当对整个数据表格开启自动筛选功能后，可以按照如下步骤快速删除表格中的空白行。
（1）单击任意一个列标题中的下三角按钮，在随即打开的下拉列表中，只选中&空白&复选框，如图4所示。
图5 筛选出空白行
（2）单击【确定】按钮，即可筛选出该列为空的行，如图5所示。如果数据表格中不仅含有空白行，而且在某些列中还包含单独的空白单元格，则需要对这些列进行筛选，直到将不包含任何数据的空行全部筛选出来为止
（3）选中筛选出的空白行，在&开始&选项卡的&单元格&选项组中，单击【删除】下三角按钮，在随即打开的下拉列表中，执行【删除工作表行】命令，即可将选中的空白行删除。
（4）在&数据&选项卡的&排序和筛选&选项组中，单击【清除】按钮清除筛选，这样就完成了空白行的删除操作，如图6所示。
标签(Tag):
------分隔线----------------------------
------分隔线----------------------------