excel办公技巧获取同(大猫闲聊--excel中如何快速提取两列中的相同数据)

要处理的问题类型，如图1所示：

图1

图1中有两列数据，如何快速识别出两列的相同项，并提取出来。

下边猫哥就教你们怎么装×

：

同样，高阶的装×行为需要高阶的技能，此处就需要利用数组，能否熟练应用数组，是一个excel猎手进1阶的标志。

此次共要达到如图2所示的3种效果：

图2

第1种：提取出左列独有的项目

第2种：提取出右列独有的项目

第3种：提取出双边都有的项目

第1种解答：提取出左列独有的项目

在E3单元格中输入公式（同时按Ctrl shift enter键，然后下拉）

=INDEX(B:B,SMALL(if(COUNTIF(C:C,$B$3:$B$22)=0,ROW($B$3:$B$22),1000), ROW(A1)))&""

公式解析：

先拆：

第1层：index()，也是最外边的一层

第2层：small（）

第3层：if()

第4层：countif（）最里边的一层

从里到外：

countif函数，COUNTIF(C:C,$B$3:$B$22)=0，这里即用到数组，即c:c是备查找的区域，$B$3:$B$22是要查找的目标值组合，此处用数组代替以前你们常用的单个单元格的值，即判断数组$B$3:$B$22中的每一个单元格的值在区域C:C中是否有数，即如果都没有，则返回false，因为false参与计算是值为0。

注意，看黑板，重点来了

数组的一个特性就是逐一判断，比如上边提到的这个公式：

COUNTIF(C:C,$B$3:$B$22)=0，即是先判断b3单元格1猫在c列中是否有对应的值，如果有则判断一次，同时if函数也判断一次，返回值集合见图3：

图3

因为1猫在c列中不存在，故countif函数结果为0，if函数返回ROW($B$3:$B$22)，对应位置的数组值为3，同理推敲至b4值2猫，在c列中有对应的值，则countif函数结果不为0，则if函数返回值为1000，如上图所示，依次类推。

if函数，这个就简单了，如果COUNTIF(C:C,$B$3:$B$22)=0成立，则返回数组ROW($B$3:$B$22)，否则返回值1000（这个1000是随便设定的，只要大于数组的元素数即可，比如数组ROW($B$3:$B$22)的元素个数是20，1000大于20了）。

此处仍然有一个数组ROW($B$3:$B$22)，这个数组返回值为如图4所示：

图4

如何理解呢？建议去单独学习一下数组，这里简单介绍一下，数组无法在单元格中单独全部显示，单元格只能显示出一个元素值，如果要全部显示数组的值，需要根据数组的维度，选择对应的区域，同时按Ctrl shift enter键，完成输入，之后你会看到函数中会出现{}这个大括号，即为数组形式，手动敲一下就明白了。

small函数：

语法small（数组，第n个最小值）

small函数是专用于数组计算的，即返回数组中的第n个最小值

row(a1)，是辅助用于生成small函数中的n，用以参与计算数组元素的取值

但是此例子中，参与small函数判断的是数组

IF(COUNTIF(C:C,$B$3:$B$22)=0,ROW($B$3:$B$22),1000), ROW(A1))，该数组的值见下图5所示：

图5

small（上述数组，row（a1）），返回值为3，因为

row（a1）=1，所以返回数组中第1个最小值为3。

接着判断small（上述数组，row（a2）），因为row(a2)=2，则返回上述数组中的第二个最小值，即为13，依次类推。最后生成的数组为下图所示：

index函数：

index（）返回目标区域的目标位置的值，small函数生成的值为3，则返回在目标区域中的位置3，即第3行，即1猫

最后公式后边&""，是为了将0转化为空值，美化视图，如果不加这个，空单元格的返回值是0，不加这个符合也无所谓。

第2种解答：提取出右列独有的项目

在如图所示f3单元格输入（（同时按Ctrl shift enter键，然后下拉）

=INDEX(C:C,SMALL(IF(COUNTIF(B:B,$C$3:$C$12)=0,ROW($B$3:$B$12),1111),ROW(A1)))&""

具体逻辑同理第一种方法，只是将查找区域与查找值调换个位置，比如index函数的查找区域有b:b变为右列的c:c，同理countif函数中的查找值、查找区域一样调换一下，仔细比较一下即可，此处不做详细讲解。

第3种解答：提取出双边都有的项目

这个与上述两种方法变动有点大。大体逻辑也是一样的。

按照此例子excel模板图6所示：

图6

在g3单元格输入（同时按Ctrl shift enter键，然后下拉）

=INDEX(B:B,SMALL(IF(COUNTIF(C:C,$B$3:$B$22)>0,ROW($B$3:$B$22),1000), ROW(A1)))&""

此公式中，将countif函数改为>0，而不是等于0，即改为判断目标值数组$B$3:$B$22中的元素是否在目标区域中存在，存在则>0成立，返回对应的数组值，继续重复方法1中的逻辑。