index和match和if函数的使用方法（indexsmallif提取唯一值公式解释）

前几天有位朋友在我的文章下留言，想让我讲讲“index small”提取唯一值数组公式。说实在的，提取唯一值有很多的方法，数组公式不仅长，且计算慢，我并不推荐使用。不过我想借此讲讲怎样看网上别人写的公式。

我想朋友希望得到的效果是这样的。

朋友上网搜索了下“index、small、if”提取唯一值，我想他很快就会搜到类似这样的公式：

{=INDEX($A$1:$A$25,SMALL(IF(MATCH(A$2:A$25,A$2:A$25,0)=ROW($2:$25)-1,ROW($2:$25),4^8),ROW(A1)))}

可是……

一个唯一值都没有提取到，果然网上不靠谱多啊！

但，朋友还是想了想，觉得公式中的引用范围和自己的不一样，改了改。

于是就成功的将唯一值提取出来了。

可是，新的问题又出现了，公式写死了单元格引用范围，添加新数据，没反应！删除数据，结果却又都变成了“#N/A”了！

难不成，每次增减数字后，都要重新修改下公式吗？

No，No，No！还是放弃这个公式吧，重新找个新的有用的公式吧。

不停地找啊找，试啊试，最终他一定会找到一个管用的，例如：

{=INDEX(A:A,SMALL(IF(ROW($A$1:$A$100)=IFERROR(MATCH($A$1:$A$100,$A$1:$A$100,0),4^8),ROW($A$1:$A$100),4^8),ROW(A1)))&""}

虽然长得要死，可到底是管用的。

然而，没几天，老板的要求变了，不要唯一值，要动态统计出现过一次的数据。

于是，朋友又开始上网找公式，试公式……忙得不亦乐乎。

相信很多人都是这样的。其实，如果花一点时间理解下公式，才能举一反三。就比如上面那个长长的公式，理解了，相信会对你的excel水平有帮助的。

看公式

步骤 ①：找到公式主体函数。

步骤 ②：学会拆分，学会按F9键！

步骤 ③：将关键部分公式或难以理解的公式单独拎出来。

步骤 ④：学会替换公式中的函数。

步骤①

先找到公式主体函数。

如图，这个数组公式的主体部分是第一个INDEX函数，公式末尾的“&""”作用在于屏蔽错误，如果删去这部分，会得到一系列“0”。

步骤②

既然知道主体部分是INDEX函数，那就需了解下INDEX函数，它有多种使用方式，图中用到的是“=INDEX(单元格范围,第几行)”。INDEX第一个参数是“A:A”，所以SMALL部分公式将会得到一个数字。我们可以选中这一部分公式，按F9键查看公式计算结果。

步骤③

第一层的INDEX剥完之后，就到了SMALL函数，同样按F9键查看SMALL参数部分计算结果。

然后是第三层的IF函数。

最后是IFERROR和MATCH函数。

MATCH函数是最里层的函数，也是这个数组公式中很关键的部分。MATCH函数得到的结果是：

{1;2;3;4;5;5;3;2;9;#N/A;#N/A;;#N/A;#N/A;#N/A…;#N/A}

至此已经将数组公式解剖好了，现在要从最里层的MATCH理解起。

MTACH函数将得到查找值第一次出现的位置，所以当有重复值值，它也只会返回相同的数字。

前面已经知道了MATCH函数的结果是一对的数字和“#N/A”，所以需要个IFERROR过滤。公式“ROW($A$1:$A$100)”将得到1-100的数组，然后“ROW数组=IFERROR结果数组”计算后，将得到一堆的：

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

你会发现TURE的位置正好是不重复数据所在的行数，这样再用SMALL函数依次提取第1、2、3大的行数，最终再用INDEX根据行数得到对应的姓名。

步骤④

一般的公式都没有这个复杂，不过看公式的步骤都是类似的。理解了公式后，就要学会修改公式。

例如，给IF替换个条件公式。

{=INDEX(A:A,SMALL(IF(COUNTIF($A$1:$A$100,$A$1:$A$100)=1,ROW($A$1:$A$100),),ROW(A1)))}

这就是网上常见的另一个提取唯一值的公式。你会发现这个公式和我们前面说过的公式结果是不同的。

前一个公式用MATCH获得每个不重复姓名的行号，所以提取的是姓名下所有出现过的名字。

而这个COUNTIF公式计算的是姓名列下只出现一次（“=1”）的名字。

如果你在第1行前插入一行，你会发现公式计算结果又不正确了！

不过相信你已经会分析和修改公式了吧？

---

本文由解晴新生原创，欢迎关注，带你一起长知识！

,