index、small、if提取唯一值公式解释，只有理解公式才能举一反三
格子里外2019-03-25 20:01
写Excel公式难？No，看Excel公式和改Excel公式才是最难的！
前几天有位朋友在我的文章下留言，想让我讲讲“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行前插入一行，你会发现公式计算结果又不正确了！
不过相信你已经会分析和修改公式了吧？
本文由解晴新生原创，欢迎关注，带你一起长知识！
举报/反馈
0
0
收藏
分享
