写Excel公式难?No,看Excel公式和改Excel公式才是最难的!

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

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

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

朋友上网搜索了下“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)))}

可是……

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

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

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

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

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

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

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

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

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和match和if函数的使用方法(indexsmallif提取唯一值公式解释)(5)

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

步骤②

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

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

步骤③

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

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

然后是第三层的IF函数。

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

最后是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}

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

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

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

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

前面已经知道了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}

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

你会发现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)))}

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

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

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

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

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

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


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

,