首页 > Excel专区 > Excel教程 >

excel函数查找重复的数据集

Excel教程 2022-01-04 21:49:24

如下图1所示,工作表中有11组数据,每组数据有6个数字,现在要统计多少组相同的数据,怎么使用公式实现?注意,每组中的数据可以是任意顺序。

图1

公式

公式1:使用辅助列

使用辅助列将复杂的步骤拆分成几步,可能更好理解。如下图2所示,添加了6个辅助列用来将每组中的6个数字按从小到大的顺序排列,在单元格H4中的公式:

=SMALL($B4:$G4,1)

取B4:G4中的最小值。

单元格I4中的公式:

=SMALL($B4:$G4,2)

取B4:G4中第2小值。

依此类推。

对于下面的各行也是如此。

图2

然后,在列N中使用TEXTJOIN函数将排好序的6个数字连接:

=TEXTJOIN(“,”,TRUE,$H4:$M4)

在列O中使用COUNTIF函数统计:

=COUNTIF($N$4:$N$14,$N4)

公式2:使用辅助列

将上面解决方案中的6列辅助列合并,如下图3所示。

图3

在单元格H4中的公式:

=TEXTJOIN(“,”,TRUE,SMALL($B4:$G4,{1,2,3,4,5,6}))

用来对前面的6个数组排序并连接。

在单元格I4中的公式使用COUNTIF函数统计:

=COUNTIF($H$4:$H$14,$H4)

公式3:使用数组公式

在单元格H2中输入数组公式:

=SUM(IF(MMULT({1,1,1,1,1,1},TRANSPOSE(COUNTIF(B2:G2,$B$2:$G$12)))=6,1))

其中,COUNTIF函数检查单元格区域中的每行,查看是否其每个数字与公式所在行的数字匹配,生成一个11行6列的数组。对于H2中的公式,其生成的数组如下图4所示。

图4

MMULT函数将返回一个1行11列的数组,其元素值代表每行匹配的数字个数。这样传递给它的第一个数组是一个1行6列的由1组成的数组,第二个数组为上述生成的数组转置为一个6行11列的数组。单元格H2中生成的中间数组为:

{6,5,3,2,3,2,0,2,0,4,3}

然后与6比较,得到数组:

{TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}

传递给IF函数,得到:

{1,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}

传递给SUM函数,得到结果:

1

即只有公式所在行本身与其匹配,没有找到与该行重复的行。

公式4

更简洁一些的公式。在单元格H2中的公式:

=SUM(–(MMULT(COUNTIF($B2:$G2,$B$2:$G$12),{1;1;1;1;1;1})=6))

注:MMULT函数执行两个行列式相乘的操作,即m行s列的行列式与s行n列的行列式相乘,结果为m行n列的行列式,也就是说,两个相乘的行列式中第一个的列数与第二个的行数相等。一个示例如下图5所示。

图5


标签: Excel图表制作Excel常用函数excel数据透视表excel教程

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.