Excel公式技巧71：查找一列中有多少个值出现在另一列中

学习Excel技术，关注微信公众号：

excelperfect

有时候，我们想要知道某列中有多少个值同时又出现在另一列中，例如下图1所示，列B中有一系列值，列D中有一系列值，哪些值既出现有列B中又出现在列D中。因为数据较少，不难看出，在列B中仅有2个值出现在列D中，即“完美Excel”和“Office”。

图1

可以使用数组公式：

=COUNT(MATCH(IF(MATCH(B3:B13,B3:B13,0)=ROW(B3:B13)-ROW(B3)+1,B3:B13,""),D3:D16,0))

得到结果：

2

公式中：

MATCH(B3:B13,B3:B13,0)

查找单元格区域B3:B13中每个单元格的值在该区域首次出现的位置，得到数组：

{1;2;3;1;5;6;2;3;5;1;2}

公式中：

ROW(B3:B13)-ROW(B3)+1

得到单元格区域B3:B13中每个单元格的值在该区域的相对位置，生成数组：

{1;2;3;4;5;6;7;8;9;10;11}

将上述生成的两个数组相比较，得到数组：

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

其中TRUE表明该单元格中的值首次在该区域出现，FALSE表明该单元格中的值已经在前面出现过。

代入IF函数中，公式：

IF(MATCH(B3:B13,B3:B13,0)=ROW(B3:B13)-ROW(B3)+1,B3:B13,"")

转换为：

IF({TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE},B3:B13,"")

得到数组：

{"完美Excel";"Office";"Excel";"";"excelperfect";"Word";"";"";"";"";""}

即由该区域中不重复值和空组成的数组。

该数组传递给MATCH函数，公式：

MATCH(IF(MATCH(B3:B13,B3:B13,0)=ROW(B3:B13)-ROW(B3)+1,B3:B13,""),D3:D16,0)

转换为：

MATCH({"完美Excel";"Office";"Excel";"";"excelperfect";"Word";"";"";"";"";""},D3:D16,0)

查找上述不重复值组成的数组在单元格区域D3:D16中出现的位置，得到数组：

{1;5;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A}

其中#N/A表明没有找到该值。

传递给COUNT函数统计数组中数字的个数：

COUNT({1;5;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A})

得到结果：

2

即列B中有两个值在列D中出现，如下图2所示。

图2

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料。