返回目录:excel表格制作
EXCEL如何比较两个多列表格,并筛选出不完全相同的行?
观察题主的问题,相同的条件是两个表格的行完全相同为之相同,所以题主将所有的字段合并在一个单元格内的方法是可取的,也是常常用到的技巧。
下面我模拟少量数据用函数辅助列的方式得出结果。当然涉及到大量的数据VBA或者powerquery都是比较快速有效的方法,但这里仅以函数进行展示,仅供参考。
原始的数据如下图,为方便操作,仅仅以3个字段作比较,每个表的第4列为合并字段。
左边表格辅助列公式为:
=IF(COUNTIF($J$2:$J$6,D2)=0,COUNTIF(E$1:E1,">0")+1,"")
结果如下图,将不同于右边表格的数据分别计数标记序号。
公式主要运用countif计数函数,COUNTIF(E$1:E1,">0")+1公式,当第一条不相同的数据时,初始值为1;从第二条不相同数据开始,E$1:E1相对引用单元格(注意范围)计算E列中大于0的单元格个数,从而达到累计的效果。
2、右边的表格同样处理。公式为:
=IF(COUNTIF($D$2:$D$7,J5)=0,COUNTIF(K$1:K4,">0")+1,"")
结果如下图:
3、使用index+match函数分别提取两个表格标记了数字的数据,也就是不完全相同的数据。
3.1、左边表格的公式为,右拉:
=IFERROR(INDEX(A$2:A$7,MATCH(ROW(A1),$E$2:$E$7,0),),"")
结果如下图:
3.2、右边表格同样处理,公式为,右拉:
=IFERROR(INDEX(G$2:G$6,MATCH(ROW(A3),$K$2:$K$6,0),),"")
结果为:
至此,两个表格的不同数据就被提取出来了。
4、若要将两个表格的数据合二为一,可以如下处理:
公式为:
=IFERROR(IFERROR(INDEX(A$2:A$7,MATCH(ROW(A1),$E$2:$E$7,0),),INDEX(G$2:G$6,MATCH(ROW(A1)-MAX($E$2:$E$7),$K$2:$K$6,0),)),"")
结果:上面为左边表格,下面为右边表格。
以上函数方法仅供参考。
题主尽可以按此方法试验,若效率比较低下,函数方法就不可取了。
更佳的方法,还是使用VBA比较快捷有效。