|
小伙伴们好啊，今天要分享的内容是从多行多列中提取不重复值，用的还是indirect+text。先来看下数据源和提取后的效果。下图左表是数据源，是一些城市，其中有重复的。现在要提取出不重复的，并且放在一列，结果如F列和H列所示。F列是按行的方向提取的，H列是按列的方向提取的。 先来看下按行的方向提取的，在F2单元格输入公式=INDIRECT(TEXT(MIN(COUNTIF(F$1:F1,A$2:D$5)/1%%+ROW($2:$5)/1%+COLUMN(A:D)),"r0c00"),)&""，按ctrl+shift+enter三键结束，向下填充。 这个公式中由于要去重复的，所以用到countif的动态区域用法，不好讲解。我就先用另外一种方式说下大概的思路，虽然和公式的运行原理不太一样，但大体是相似的。 用每个城市(绿色区域中)在F列的空白区域(红色区域)中查找，如果找不到就把对应的城市放在F列的区域中。比如，第1次，在空白区域中查找杭州，找不到就把杭州放在空白区域的第1个单元格中。 第2次，在空白区域中查找哈尔滨，找不到把哈尔滨放在空白区域的第2个单元格中。 以此类推，。。。第5次，在F列的区域中查找北京，找到了就跳过，再查找下一个城市天津。大概就是这样的方式，直到把所有的城市都查找完。 下面来说下公式的计算过程：=COUNTIF(F$1:F1,A$2:D$5)这部分是在F1单元格中查找所有的城市，找不到的返回0，找到的返回1。结果如下图红色框所示，第1次肯定全都找不到。其实我们要找的就是第1个0对应的单元格位置，大家后面就会明白的。 =COUNTIF(F$1:F1,A$2:D$5)/1%%这部分就是用上一步的结果乘以10000,1%%就是万分之一，除以万分之一就是乘以一万，结果还是0。 =COUNTIF(F$1:F1,A$2:D$5)/1%%+ROW($2:$5)/1%+COLUMN(A:D)这部分是用上一步的结果加上行号乘以100，再加上列号，结果如下图红框所示。 =MIN(COUNTIF(F$1:F1,A$2:D$5)/1%%+ROW($2:$5)/1%+COLUMN(A:D))这部分用min取出上一步的最小值201，它刚好对应的就是第1个0出现的位置。剩下的就是用text转为文本型地址r2c01，然后用indirect转为真正的引用。就不详细说明了，参考昨天和前天的文章。这样F2单元格的结果就返回杭州。 当公式下拉到F3单元格时，公式会有所变化，countif的区域由F1:F1变为F1:F2。=COUNTIF(F$1:F2,A$2:D$5)这部分返回的结果如下图红色框所示，由于F2的结果变为杭州，所以城市中的杭州被找到了，会返回1。如绿色箭头所示。我们下一个要取的就是第1个0对应的哈尔滨。 =COUNTIF(F$1:F2,A$2:D$5)/1%%这部分是用上一步的结果乘以一万，这样1就变为10000，目的是让出现过的城市变为一个比较大的数字，用min取最小值的时候就不会把它们取到。 =COUNTIF(F$1:F2,A$2:D$5)/1%%+ROW($2:$5)/1%+COLUMN(A:D)这部分是加上行号和列号的效果，如红色框所示。再下一步我们就用min取出最小值202，然后用text转为r2c02，用indirect返回2行2列对应的哈尔滨。F3单元格就返回哈尔滨。其他单元格也是按这样的方式，大家自己查看，就不再详细说明。 再来看下按列的方向提取的，在H2单元格输入公式=INDIRECT("r"&MOD(MIN(COUNTIF(H$1:H1,A$2:D$5)/1%%+COLUMN(A:D)/1%+ROW($2:$5)),100)&"c"&INT(MIN(COUNTIF(H$1:H1,A$2:D$5)/1%%+COLUMN(A:D)/1%+ROW($2:$5))%),)&""，按Ctrl+shift+enter三键结束，向下填充。 思路主要是今天和昨天的结合，由于按列的方向提取，所以在列号上乘以100；用min取出的最小值对应的是几列几行，所以要把它拆分开再合并为几行几列，用的mod和int，目前没有想到其他办法。MIN(COUNTIF(H$1:H1,A$2:D$5)/1%%+COLUMN(A:D)/1%+ROW($2:$5))这一段公式重复用了2次，所以公式会比较长。 如果你有其他的方法，欢迎在留言区写出你的答案，让我们一起学习。 文件链接： https://pan.baidu.com/s/1ndtlLsPsxlMYa6hStcChog 提取码：3xrx |
|
|
