分享

indirect+text:多行多列中提取不重复值(二)

 刘卓学EXCEL 2021-04-02

小伙伴们,大家好。今天要分享的内容还是多行多列中提取不重复值,但是要比昨天的难一点,今天的区域中包含空单元格。如下图所示。F列和H列是提取后的结果。F列是按行提取的,H列是按列提取的。

还是先来看按行的方向提取的,在F2单元格输入公式=INDIRECT(TEXT(MIN((COUNTIF(F$1:F1,A$2:D$6)+(A$2:D$6=""))/1%%+ROW($2:$6)/1%+COLUMN(A:D)),"r0c00"),)&"",按ctrl+shift+enter三键结束,向下填充。

今天的公式比昨天的公式多了(COUNTIF(F$1:F1,A$2:D$6)+(A$2:D$6=""))/1%%红色字体的部分,主要是用来处理空单元格的。实际上这里有2个条件,是或的关系。意思就是已经出现过的或者是空单元格的返回一个较大的数字,简单来说就是把重复出现的和空单元格的排除掉。

COUNTIF(F$1:F1,A$2:D$6)这部分就代表重复出现的,(A$2:D$6="")这部分代表空单元格的。二者相加就是或的关系。

我们也可以反过来想,我们要的是既没有出现过的又不为空的,那么这2个条件可以写为(COUNTIF(F$1:F1,A$2:D$6)=0)*(A$2:D$6<>""),这样的话既没有出现过的又不为空的返回1,其他的返回0。但是这里我们把想要的转为0,不想要的转为1,这样后续取最小值才能取出我们想要的。所以还要用1减,也就是1-(COUNTIF(F$1:F1,A$2:D$6)=0)*(A$2:D$6<>"")这部分。

这么写的话,最后的公式为=INDIRECT(TEXT(MIN((1-(COUNTIF(F$1:F1,A$2:D$6)=0)*(A$2:D$6<>""))/1%%+ROW($2:$6)/1%+COLUMN(A:D)),"r0c00"),)&"",按三键结束,向下填充。

具体运算过程就不截图了,大家可以像我之前那样分步查看结果。这个公式也是有些复杂的,不理解也没有关系的。

按列提取的公式为在H2单元格输入公式=INDIRECT("r"&MOD(MIN((COUNTIF(H$1:H1,A$2:D$6)+(A$2:D$6=""))/1%%+COLUMN(A:D)/1%+ROW($2:$6)),100)&"c"&INT(MIN((COUNTIF(H$1:H1,A$2:D$6)+(A$2:D$6=""))/1%%+COLUMN(A:D)/1%+ROW($2:$6))%),)&"",按三键结束,向下填充。

和之前文章一样的思路,MIN((COUNTIF(H$1:H1,A$2:D$6)+(A$2:D$6=""))/1%%+COLUMN(A:D)/1%+ROW($2:$6))这部分重复用了2次,所以公式很长。

如果你有其他的方法,欢迎在留言区写出你的答案,让我们一起学习。

文件链接:

https://pan.baidu.com/s/1G_OjwQB7YnlaM1Iw8Z5bkw

提取码:gkwe

    转藏 全屏 打印 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多