微信公众号:yhjbox(永恒君的百宝箱),获取最新文章、资源。

Excel数据核对技巧汇总,让你从此不再烦恼!

实用技巧 永恒君 1893℃ 0评论
Excel表格的数据核对,这让很多人最头痛的工作。
永恒君这里把之前介绍过的一些数据核对方法整理汇总一下,希望能够对各位小伙伴有帮助。
1、在同一列数据中查找重复项
如下图
用公式 =IF(COUNTIF(B:B,B6)>1,"重复","")
公式的意思是:如果在B列中有B6这个数据,则标识“重复”
小技巧
日常当中经常也会需要核对身份证号码,这个时候你会发现上面的方法都没用。
其实,核对身份号码的重复项,应当这样操作:
原因是这样的:目前的我们国家的身份证号是18位的,而Excel中上面两个方法对超过15位以上的数据都会忽略不计的。因此需要以在后面加上&"*"强制以文本方式进行匹配,这样系统就能识别全部的数据。
2-1、两列数据中查找重复项(或不同项)
如下图,查找“样品1” “样品2”两列的不同项
用如下的公式:=IF(F4<>G4,"T",""),标T的就是所查找的不同的项目。
如果查找重复项,就用=IF(F4=G4,"T","")
2-2、两列数据中查找包含数据,即一列数据是否在另一列中存在?
具体公式有下面两个:
=if(isna(MATCH(A1,B:B,0)),“”,A1) 
其中A1为需要查找的数据,B:B为需要查找的列。
执行的结果就是存在即标注出来,否则就显示#NAME?

或者用这个=VLOOKUP(A1,$B:$B,1,FALSE)
其中A1为需要查找的数据,$B:$B为需要查找的列。
执行的结果就是存在即标注出来,否则就显示#N/A

把需要对比的数据分别复制到excel同一个表格的A列和B列,随意复制上面的一个公式到C1单元格,然后向下填充公式即可看见比对结果了。

如果需要复制结果,将C列排序或者筛选后,剔除#NAME?或者"#NA/A"的内容即可。
2-3、两列数据中,对比相同名称的属性值
如下图:核对两个表(表一和表二)中的相同名称的数量,如表一中A的数量和表二中A的数量是否相同。
运用公式=VLOOKUP(E4,H:I,2,0)-F4,即通过Vlookup搜索找到对应的“名称”后的数量,然后相减,来完成核对。结果为0则相同,出现其他数值则为不同。
如果出现了多个条件的话,那就需要用到sumifs函数。如下图
需要核对同样商品、品牌下的数量是否相同。
方法为:
运用公式 =SUMIFS(G:G,E:E,I4,F:F,J4)-K4,即在E列中查找商品为I4(电视),在F列查找品牌为J4(小米)的数量减去K4,来完成核对。返回结果为0则表示相同,其他数值则不同。
3、不同表格间对比查找重复项(或不同项)

如下图:查找Book1表中“样品1”与Book2中“样品2”两列的不同项(Book1与Book2在不同的excel文件当中)

方法如下:
1、数据复制粘贴到同一张表上,然后用上面的两列之间对比查找重复项(或不同项)方法即可。
 
2、公式法。基本公式和上一篇的一样,只不过引用数据的时候要注意更改一下就行。以下图为例,需要修改为
=IF([Book1]Sheet1!C6<>Sheet1!C4,"T","")

如果两个表在同一个excel文件当中,就可以写为
=IF(Sheet1!C6<>Sheet2!C4,"T","")
 
如下图:
4、快速找出两个Excel表格不同的地方
上面的情况只是查找某一列或者两列的情况,如果要对比两个Excel表格里面内容的不同地方,应该怎么办?
这里说的两个Excel表格,可以是同一个工作簿的不同sheet,也可以是两个不同的Excel文件,如下图:
黄色的部分是我手工标注出来的,数据其实不止两列,多列同样可以进行对比。
当然用上面介绍的方法一列一列去比较也可以,数据多了稍微有点繁琐。这里介绍一款软件来实现 - Excel比较大师
这个软件可以对比同一个excel文件里的不同sheet,也可以对比不同excel文件里的不同sheet。但是注意,这个软件只能支持xls格式。
软件使用比较简单,选择好excel文件和需要对比的sheet,再确定好报告输出方式和类型,开始比较即可。
程序会自动生成一个report.xls,如下图,
会很详细把两个excel表格里面的不同之处写的非常详细。

 

需要的这里尽快取吧:
https://pan.baidu.com/s/1WN4I6muZQquX4YbsBe9w2Q 
提取码: i24y

 

欢迎交流!

 

微信公众号:永恒君的百宝箱
个人博客:http://www.yhjbox.com

转载请注明:永恒君的百宝箱 » Excel数据核对技巧汇总,让你从此不再烦恼!

喜欢 (1)
发表我的评论
取消评论
表情