Excel中两列数据的差异对比,方法非常多,比如简单的直接用等式处理,到使用Excel2016的新功能Power Query(Excel2010或Excel2013可到微软官方下载相应的插件)实现各种复杂的数据整理后再进行对比,可以根据实际需要选择使用。
简单的直接等式对比进适用于数据排列位置顺序完全一致的情况,如下图所示:
通过vlookup函数法可以实现从一个列数据读取另一列数据,从而形成匹配对比。vlookup函数除了适用于两列对比,还可以用于表间的数据对比,如下图所示:
对于大规模的数据对比来说,数据透视法非常好用,具体使用方法也很简单,即将2列数据合并后,构造成明细表,然后进行数据透视——这种方法适用于多表数据对比,甚至可以在一些数据不太规范的场合下,减少数据对比的工作量,如下例子:
表间数据不规范统一,用数据透视递进巧比对
比如很多公司的盘点数据对比问题,手工录的表里货品代码就经常少一个横杠、多一个横杠的,有的“文艺”干脆就写成“文”,对起来很麻烦。
这种数据汇总后就有各种问题,很难用公式去匹配。于是可以考虑用数据透视,先对大类,看看哪些大类是对不上的,然后再针对有差异的大类对明细,缩小对照范围。比如把2个数据透视都放到一张表里看看。显然,大类的时候“厨具”、“卫生”、“文艺”三类都有差异。
接着把细类放进去,把没有差异的大类筛选掉。
继续对细类筛选比对:
最后对细类进行比对,就双击生成明细:
结果如下图所示:
新建窗口并重排后进行核对:
在垂直并排的窗口中分别进行对比即可:
对于以上的方法,最推崇的其实是Excel2016新功能Power Query(Excel2010或Excel2013可到微软官方下载相应的插件)的方法,因为用Power Query进行的数据对比,可以随着数据源的更新而达到一键更新对比结果的效果。
比如,有两个表的数据要天天做对比,找到差异的地方,原来用Excel做虽然也不复杂,但要频繁对比,就很麻烦了,因此,可以考虑使用Power Query来实现直接刷新的自动对比。
PowerQuery最大的优势就是只干一次,以后有新数据就刷新一下就搞定,尤其适合这些需要频繁重复操作的工作。 1、将需要对比的2个表的数据加载到Power Query
2、以完全外部的方式合并查询
3、展开合并的数据
4、添加差异比对列
5、按需要筛选去掉无差异部分
6、按需要调整相应的列就可以将差异结果返回Excel里了
在线M函数快查及系列文章链接(建议收藏在浏览器中):
https://app.powerbi.com/view?r=eyJrIjoiZDVhZDBlMTYtNDkzNC00YWFjLWFhMmMtMmI3NTk2Y2ZhMzc3IiwidCI6ImUxMTAyMjkxLTNkYzUtNDA1OC1iMDc3LWQ0YzU4YWJkMWRkOCIsImMiOjEwfQ%3D%3D
本文分享自 Excel到PowerBI 微信公众号,前往查看
如有侵权,请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体同步曝光计划 ,欢迎热爱写作的你一起参与!