哈喽,小伙伴们,你们好呀~ 说到Excel中最经典的应用,核对数据肯定名列前茅。 同表比较差异,多表比较差异,是许多人一直头疼的问题。 今天就让我来给大家归纳总结一下,一共涉及到12种解决方案,囊括多种实用办公场景,下面,就来一一介绍吧! 1、快捷键对比Ctrl+\如下图所示,选中需要对比的两列数据,按下快捷键Ctrl+\,不同的数据就会被选中。 注意:此方法也适用于多列数据。 2、定位法对比(快捷键F5或Ctrl+G)选中两列,按快捷键F5(或Ctrl+G)调出定位窗口,选择定位条件为“行内容差异单元格”,单击“确定”按钮,不同的数据会处于选中状态。 3、IF函数对比(1)不需要区分字母大小写的if函数对比 下表A、B两列都是数字,不存在字母,不需要区分大小写。 可以在C2单元格输入公式=IF(A2=B2,'相同','不相同'),输好后向下拖动,如下表。 (2)区分字母大小写的if函数对比 如遇对比数据含字母,并且需要区分大小写,则上述公式不能准确对比。 此时可将C2公式更改为=IF(EXACT(A2,B2)=TRUE,'相同','不相同'),然后下拉填充,如下图。 2.找出两列数据的重复值1)IF + MATCH函数查找重复值现在要对下表找出连续两个季度中奖的名单,又有什么方法呢? 我们可以用IF+MATCH函数组合公式,在C2单元格输入公式: =IF(ISERROR(MATCH(A2,$B$2:$B$25,0)),'',A2) 然后下拉复制公式,见下表: 公式解析: MATCH用于返回要查找的数据A2在区域$B$2:$B$25中的位置。如果查到会返回一个行号(表示有重复),没有查到则返回错误#N/A(表示无重复)。 公式中加入ISERROR函数,用于判断MATCH返回的值是否是个错误#N/A,是错误#N/A则返回TRUE,不是错误#N/A则范围FALSE。 最外围的IF函数,根据ISERROR(MATCH())是TRUE还是FALSE,返回不同值。如果是TURE(也就是没有重复),则返回空;如果是FALSE,则返回A2。 如果我们要查找出1季度中奖但2季度没有中奖的名单,我们就可以将上述函数公式改成为:=IF(ISERROR(MATCH(A2,$B$2:$B$25,0)), A2, '')。 2)IF+COUNTIF函数查找重复值下表A、B两列都是客户的姓名,需要找到两列重复的客户名称,并在C列标识出来。 在C2单元格输入公式=IF(COUNTIF(A:A,B2)=0,'',B2),然后下拉完成,请看下图: COUNTIF函数是对指定区域中符合指定条件的单元格计数的一个函数。 3)IF+VLOOKUP函数查找重复值如下表所示,有这样两组员工号,我们也可以用if+VLOOKUP函数公式来完成比对。 在C2单元格中输入公式: =IF(ISNA(VLOOKUP(A2,$B$2:$B$25,1,)),' ',A2),然后下拉复制。 公式解析: ISNA函数用于判断值是否为错误值#N/A(即是值不存在),如果是,则返回TRUE;否则返回FALSE。 公式里面需要在查找区域的数据前都加上$符号,固定查找区域。否则在下拉填充的时候,查找区域也会跟着变化,这将会影响查找对比的结果。 如果是找不同呢? 譬如B组是标准数据,要把A组与B组不同的值找出来,公式可以写成: =IF(ISNA(VLOOKUP(A2,$B$2:$B$25,1,)), A2, ' ') 1、条件格式法对比两表差异以下面两表为例,要比对出哪些数值存在差异并突出显示。 首先,先选中一个表,新建规则,并选择“使用公式确定要设置格式的单元格”,然后输入=A1<>A8 , 对相对应的单元格进行判断,判断其是否相等。请看下面演示! 温馨提示:如果要清除条件格式,先选中单元格区域,依次执行“开始”- “条件格式”–“ 清除规则”–“ 清除所选单元格的规则”。 2、选择性粘贴法对比两表差异(只适合数字比较)如下图所示,两表格式、姓名、排序相同,要求快速找出两个表格的数据差异。 复制一个数值区域,然后按Ctrl+Alt+V选择性粘贴,设置为“减”运算,单击“确定”。 3、IF函数对比两表差异如下图所示,表a和表b,要求核对两个表格中的数值是否完全一致,并且能直观显示差异。 操作方法为,新建一个空白工作表,在A1单元格输入公式=IF(表a!A1<>表b!A1, '表a:'& 表a!A1&' vs表b:'& 表b!A1,''),然后在区域范围内复制填充公式。 4、按条件找出两个表数据的差异1)单条件找出两个表数据的差异如图,由两人汇总的成绩表,表格格式一致,但姓名排序不一样。现在需要对比两张表,核实汇总成绩是否正确。 此时,我们需要建立两个条件格式。 第一个格式:找出姓名差异 (1)选中第2个表姓名栏数据,选择“条件格式”中的“新建规则”,在弹出的对话框中选择“使用公式确定要设置格式的单元格”,然后输入公式=COUNTIF($A$2:$A$10,A14)=0 (2)单击格式按钮,选择一种填充颜色。 第二个格式:找出同姓名的分数差异。 (1)选中第2个表中所有分数单元格,新建规则,使用公式确定规则,输入公式 =VLOOKUP($A14,$A$1:$I$10,COLUMN(B1),0)-B14 (2)单击格式按钮,选择一种填充颜色。 确定后完成分数核对,结果如下: 橙色表明“刘小广”这个姓名与另一个表对不上,可能是名字写错了; 蓝绿色表明杨文雯的语文分数、何丛良的英语分数、候嫚嫚的语文分数对不上,可能存在错误。 2)多条件找出两个表数据的差异如下图所示,要求核对两表中同一仓库同一产品的数量差异,结果显示在D列。用什么方法可以完成呢? 在D15单元格中输入以下公式: =SUMPRODUCT(($A$3:$A$11=A15)*($B$3:$B$11=B15)*$C$3:$C$11)-C15 然后下拉完成该数值的对比: 好啦!以上就是今天的所有内容。 感谢大家耐心看完哟~ 如果你还有什么疑问的话,欢迎群里交流哟~ |
|
来自: 酒心1000 > 《1 Excel函数用法》