Excel公式练习26：比较两列的值并提取不相同的数据经典的两列数据比较之提取在其中一列而

微信公众号：excelperfect

本次的练习是：如下图1所示，要提取列C中西区超市有而列A中东区超市没有的水果，如何编写公式呢？

图1

先不看答案，自已动手试一试。

公式

在单元格A10中的数组公式：

=IFERROR(INDEX($C$2:$C$6,SMALL(IF(ISNA(MATCH($C$2:$C$6,$A$2:$A$5,0)),ROW($C$2:$C$6)-ROW($C$2)+1),ROWS(A$10:A10))),"")

如图2所示。

图2

向下拖至单元格中没有数据为止。

公式解析

公式中的：

MATCH($C$2:$C$6,$A$2:$A$5,0)

在单元格区域A2:A5中依次查找单元格区域C2:C6中的值，如果找到则返回该值在单元格区域A2:A5中的位置，否则返回#N/A值，最后生成的数组为{#N/A;4;1;2;#N/A}。将数组传递给ISNA函数，生成数组{TRUE;FALSE;FALSE;FALSE;TRUE}，作为IF函数的第1个参数。

公式中的：

ROW($C$2:$C$6)-ROW($C$2)+1

生成数组{1;2;3;4;5}，作为IF函数的第2个参数。

这样，IF函数就生成数组{1;FALSE;FALSE;FALSE;5}，作为SMALL函数的第1个参数。

公式中的：

ROWS(A$10:A10)

根据当前单元格生成一个数字，在A10中的数字为1，在A11中为2，将此数字作为SMALL函数的第2个参数。

公式演化为：

=IFERROR(INDEX($C$2:$C$6,SMALL({1;FALSE;FALSE;FALSE;5},1)),"")

SMALL函数求值后公式为：

=IFERROR(INDEX($C$2:$C$6,1),"")

获取单元格C2中的值。

扩展

如果要提取两列中相同的数据。如本例中，提取两个超市中都有的水果，数组公式为：

=IFERROR(INDEX($C$2:$C$6,SMALL(IF(ISNUMBER(MATCH($C$2:$C$6,$A$2:$A$5,0)),ROW($C$2:$C$6)-ROW($C$2)+1),ROWS(E$10:E10))),"")

小结

SMALL函数忽略参数中的任何非数字的数据。

ISNA函数将错误值转换为TRUE，否则为FALSE。ISNUMBER函数的运用同理。

IF函数一次性判断获取数组。

公式适用于Excel2007及以上版本。

下期预告

Excel公式练习27：从数据区域中提取满足多条件的值

如下图所示，单元格区域A8:D18中是销售数据，如何使用公式提取张三在2017年5月1日至2017年12月1日之间的销售数据？

本文属原创文章，转载请注明出处。

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

欢迎关注[完美Excel]微信公众号：

方法1—在微信“添加朋友”或者通讯录中搜索“完美Excel”或者“excelperfect”后点击关注。

方法2—扫一扫下面的二维码