Excel公式技巧71：查找一列中有多少个值出现在另一列中
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