这个公式查找，有点难

标签：Excel公式练习

在《判断两个区域是否具有相同的值》中，我们比较了两个相同大小的区域，看看这两个区域中的值是否完全相同。下面更进一步，查找一组数据在另一个表中出现的位置。如下图1所示，单元格区域B4:B8命名为“source”，单元格区域D4:I8命名为“target”，如果source在targe中出现，求出现的位置（即该区域上方标注的数字）。

图1

注：示例数据来源于chandoo.org。

示例中，target的第2列与source中的数据相同，所以编写的公式应返回2。

如何编写出这个公式？是不是有点头疼。

看来这次确实要借助于数组了。

首先，使用COUNTIF函数分别统计source中的各个值在target各列中出现的次数：

COUNTIF(source,TRANSPOSE(target)

得到数组：

{0,1,1,1,0;1,1,1,1,1;1,1,1,0,0;1,1,0,0,1;0,1,1,0,1;1,0,1,1,0}

然后，求该数组中每行之和：

MMULT(COUNTIF(source,TRANSPOSE(target)),{1;1;1;1;1})

即：

MMULT({0,1,1,1,0;1,1,1,1,1;1,1,1,0,0;1,1,0,0,1;0,1,1,0,1;1,0,1,1,0},{1;1;1;1;1})

得到：

{3;5;3;3;3;3}

再与source的个数比较，与其相等的表明该列中的值与source中的值相同：

MMULT(COUNTIF(source,TRANSPOSE(target)),{1;1;1;1;1})=COUNTA(source)

即：

{3;5;3;3;3;3}=5

得到：

{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}

查找该列的位置：

MATCH(TRUE,{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},0)

返回结果：

2

综上，得到的公式：

=MATCH(TRUE,MMULT(COUNTIF(source,TRANSPOSE(target)),{1;1;1;1;1})=COUNTA(source),0)

这是一个数组公式，输入完后要按Ctrl+Shift+Enter组合键。

与上述原理相同，也可以使用下面的数组公式：

=MATCH(5,MMULT({1,1,1,1,1},COUNTIF(source,target)),0)

或者：

=MATCH(COUNTA(source),MMULT(TRANSPOSE(ROW(source))^0,COUNTIF(source,target)),0)

然而，以上公式都存在缺点，就是target中的各列中的值不能重复，如果存在重复的值，公式结果不变。