下图A2:D6是数据源,记录的是一些姓名。现在要将这个区域的姓名逐列提取,并放置在一列中,结果如F列所示。也就是先提取A列的姓名,然后是B列的、C列的、D列的。
这个问题用函数来完成还是挺困难的。除了要掌握基础函数的用法,重要的还得知道解题思路。
在F2单元格输入下面的公式,按ctrl+shift+enter,向下填充。公式也比较长。
=INDIRECT(TEXT(RIGHT(SMALL(IF(A$2:D$6<>"",COLUMN(A:D)*10001+ROW($2:$6)/1%,99999),ROW(A1)),4),"R0C00"),)&""
COLUMN(A:D)*10001+ROW($2:$6)/1%这部分是用A2:D6这个区域的列号乘以10001再加上对应的行号乘以100,返回的结果如下图所示。用到了加权的思想。
每一行中相邻的2个值相差10001,每一列中相邻的2个值相差100。如果让它们从小到大排列的话,肯定是先排第1列的,然后是第2列的,第3列的,第4列的。这样就符合了按列的方向取值。以10201为例,说一下这个数字的含义。后面的3位数201可以看做2行1列,代表的是单元格地址。前面的1万是为了按列的方向取数。
IF(A$2:D$6<>"",COLUMN(A:D)*10001+ROW($2:$6)/1%,99999)这部分用if函数对A$2:D$6这个区域进行判断,如果不等于空,则返回上面得到的数字;如果等于空,则返回一个较大的数字99999。最后的结果如下图所示。
然后用small函数从if函数返回的结果中分别提取第1,2,3,···个最小值,结果如下图所示。这样按列的方向排列,且把空白的单元格排在最后。
接下来用right从上面的结果中提取出右边的4位数,结果如下图所示。
再用text将上面的结果转为R1C1样式的文本型单元格地址,如下图所示。
最后用indirect函数将上面的文本型单元格地址转为真正的引用,返回对应的内容,结果如下图所示。可以看到当所有的姓名被排完后,公式再下拉会出现0,为了把0去掉,所以公式最后连接空文本,也就是&""。
公式的分步演示过程都说完了,可是还没有说为什么列号要乘以10001。如果你对这种题目比较熟悉,或者是对加权的方式比较熟悉的话,可以知道按列的方向取数的话,也可以用下面的方式来表达。把两部分的COLUMN(A:D)合起来就是COLUMN(A:D)*10001。如果你的数据量不大的话,还可以用时间函数来完成,输入下面的公式,按ctrl+shift+enter三键,向下填充。由于时间关系就不展开解释了。
=IFERROR(INDIRECT(TEXT(SMALL(IF(A$2:D$6<>"",TIME(,COLUMN(A:D),ROW($2:$6))),ROW(A1)),"rscm"),),"")
https://pan.baidu.com/s/1_zrSTq2qIYXc3z7BnGee5Q