破釜沉舟的意思：用数组公式提取一列中的重复项

2020年11月22日发(作者：柯雪初)
用数组公式提取一列中的重复项
来源：excel格子社区
如果Excel工作表 的某列中包含有重复的数据，要提
取该列中所有重复的数据，可以用下面的数组公式。
假如数据 在A2:A30区域中，现在要在B列中提取该区
域中有重复的数据，在B2单元格中输入下列数组公式 ：
=INDEX($$A$$2:$$A$$30,MATCH(0,COUNTIF($$B$$1:< br>B1,$$A$$2:$$A$$30)+IF(COUNTIF($$A$$2:$$A$$30,$$A$$2:$$A$$3< br>0)>1,0,1),0))
公式输入完毕后按Ctrl+Shift+Enter结 束，
然后拖动填充柄向下填充，直到出现“#NA”错误为
止。
1

格子社区- Excel互助交流平台

说明：

用INDEX函数返回A2:A30区域中的重复项，
该函数的行号参数“row_n um”为MATCH函数。MATCH
函数可在区域或数组中搜索指定项，然后返回该项在
区域 或数组中的相对位置。由于本例中包含重复数据
的区域为A2:A30，所以可以用最简单的“1”和“ 0”
2

格子社区- Excel互助交流平台
来构造一个数组，用MATCH函数返回“0”在数组中的
位置。
由于要返 回重复项，首先对于A2:A30区域（包
含29个数据）中的重复项，在数组中的对应位置用“0”< br>来表示，非重复项就用“1”来表示，用COUNTIF函数
和IF函数组合后就可以实现：
IF(COUNTIF($$A$$2:$$A$$30,$$A$$2:$$A$$30)>1,0,1
)
其中COUNTIF函数在本例中的说明见后。由于
上述部分公式处于MATCH函数的“lookup _array”参
数中，它返回一个包含29个元素的一维垂直数组：
{1;0;0 ;1;1;0;0;0;1;0;0;0;1;1;1;1;0;1;1;
1;1;0;1;1;1;1 ;1;0;1}
这个数组可以通过选择公式中的这部分后按
F9键来查看。
其次，对于在B列中已出现的数据，也应该在
数组中用“1”来表示将其排除掉。以B 6单元格中的
公式（返回结果为“欧阳锋”）为例，对于B2:B5区
域中已返回的数据，数组 中对应的位置要用“1”来表
示，未出现的数据用“0”来表示，这用COUNTIF函数
来实 现：
COUNTIF($$B$$1:B5,$$A$$2:$$A$$30)
3

格子社区- Excel互助交流平台
上述COUNTIF函数对区域中满足单个指定条
件的单元格进行计数，语法为：
COUNTIF(range, criteria)
本例中的“range”参数为$$B$$ 1:B5，
“criteria”参数为$$A$$2:$$A$$30。因需要对其上的区
域进行判断 ，还要能向下填充，所以“range”参数从
B1单元格开始，并且采用绝对引用与相对引用混合的< br>方式，填充到B6单元格，就是$$B$$1:B5。该函数将对
$$A$$2:$$A$$30区域中的每个 数据（29个），都统计其在
区域$$B$$1:B5中出现的次数，由于$$B$$1:B5区域中没有
重复项，所以它返回另一个由“0”和“1”组成的、
包含29个元素的一维垂直数组：
{0;1;1;0;0;1;1;1;0;1;1;0;0;0;0;0;1;0;0;
0;0;0;0 ;0;0;0;0;1;0}
将上述两个数组合并，即：
COUNTIF ($$B$$1:B5,$$A$$2:$$A$$30)+IF(COUNTIF(
$$A$$2:$$A$$30,$$A$$ 2:$$A$$30)>1,0,1)
返回数组：
{1;1;1;1;1;1 ;1;1;1;1;1;0;1;1;1;1;1;1;1;
1;1;0;1;1;1;1;1;1;1 }
然后用MATCH函数判断数组中第一个“0”出
现的位置，此时公式可理解为：
4

格子社区- Excel互助交流平台
=INDEX($$A $$2:$$A$$30,MATCH(0,{1;1;1;1;1;1;
1;1;1;1;1;0;1;1; 1;1;1;1;1;1;1;0;1;1;1;1;1;1;1}
,0))
即：
=INDEX($$A$$2:$$A$$30,12)
最终B6单元格中的公式返回“欧阳锋”。
在B7单元格中，由于合并后的数组中元素都为“1”，MATCH函数找不到“0”，故返回“#NA”
错误，表示重复项已全部列出。
另外，如果要在其他列中显示所提取的重复项，
例如要在F列中从F7单元格开始提取 ，需将公式中用
红色标识的“$$B$$1:B1”改为“$$F$$6:F6”，数组公式
输入完毕后 再向下填充。

5

格子社区- Excel互助交流平台