之前在Excel从起步到起飞的教程中给大家分享了如何在两列数据中找出不同的项目。如下:
A列中与B列中不同的项目已经用红色进行了标注。具体方法如下:
选中A列中的身份证信息,点击条件格式中的新建规则:
弹出的对话框中选择最下面一项:使用公式确定要设置格式的单元格,下方的编辑规则中输入:
公式:
=Not(or($A2=$B$2:$B$8))
完成输入之后,点击格式,将字体设为红色:
确定之后返回,再次确定即可得出结果:
从上述公式可以看出,$A2=$B$2:$B$8是一个数组的形式,理解起来就是:
将A列中的第一个数据逐个与B列中的数据进行比较,得出一堆的比较结果,如下:
{false;false;false;false;false;false;false}
外面嵌套一个OR,因为数据中都是false,所以结果也是false,最外面再嵌套一个Not,那么结果就是True了,因此说明第一个身份证在B列中没有找到相同的项,根据条件格式的设置,就要显示成红色字体。
接下来用A3单元格中的身份证再次与B列中的所有数据进行对比,得到的数据是:
{True;false;false;false;false;false;false}
嵌套OR,得到的结果就是True,最外面嵌套的NOT函数,得到的结果就是False,因此不满足条件格式,就不变色了。
如此循环到A列中最后一个数据,因此得出上述的结果。
上述的做法只能得出一个结果,如果小伙伴需要得到更多的结果,如B列中有哪些与A列相同或合并所有数据且不重复,又该如何去操作呢?
给大家分享一下在Power Query中可以如何不用函数就得出我们要的多种效果吧。
首先选中A列的数据,插入工具栏中选择表格,弹出对话框勾选表包含标题,点击确定:
第二列的数据同样这样操作,完成之后,选中A列的数据,点击数据菜单栏下的自表格/区域,将其导入到Power Query的界面:
点击最左侧的查询,点击复制:
将编辑栏中的表1改为表2:
这样两列身份证就以两张表的形式导入到了Power Query中。
接下来就是完成匹配的操作了。
一、找出A列中与B列中不同的身份证号:
选中查询中的表1,点击主页工具栏中的合并查询下的将查询合并为新查询,弹出对话框及选择设置如下:
分别点击表1下的Column1和表2下的Column1,最重要的就是下方的联接种类了。
这里最终的结果全靠联接种类来体现:
上述直接用一张图来说明:
要找出A列中的不同,通过上述图片,因此需要选择的是左反,确定之后:
然后直接删除表2列就可以了。
完成之后点击左上角的关闭并上载,系统自动生成一张新的工作表显示结果:
二、找出两列中相同的身份证号:
同样的方法,可以得出两个表中相同的身份证(内部):
三、显示所有不重复的身份证号:
如果要将两列数据进行合并去重呢?在Power Query中同样可以做到,只是跟上述的操作稍微有点不一样,选中查询表1,点击主页菜单栏下的追加查询中的将查询追加为新查询:
弹出对话框中设置如下:
直接点击确定,所有的数据就都在一列中了,这个时候只需要去除重复就可以了:
选择主页菜单栏下的删除行中的删除重复项:
结果如下:
最后完成关闭并上载即可。
Power Query跟普通的函数不一样的就是,如果今后有新增数据,只需要在数据源中修改数据,完成之后直接刷新,那么结果就会自动变化了。
是不是非常简单易操作呢?有兴趣的小伙伴赶紧练练手吧。
更多关于Excel的Power Query的技巧和基础操作,请参考: