Excel公式练习67： 查找重复的数据集

excelperfect

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

图1

注：本案例整理自contextures.com和myspreadsheetlab.com。

先不看答案，自已动手试一试。

公式

公式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