indirect+text：多行多列转单列（二）

小伙伴们，大家好。昨天说了多行多列转单列的方法，不过区域中都是非空单元格；如果区域中有空单元格，又该怎么办呢？今天就来说说这个问题。先来看下数据源和转换后的效果。下图左表是数据源，F列和H列是转换后的效果。F列是按行的方向排列，H列是按列的方向排列。

先来看按行方向排列的，在F2单元格输入公式=INDIRECT(TEXT(SMALL((A$2:D$7="")/1%%+ROW($2:$7)/1%+COLUMN(A:D),ROW(A1)),"r0c00"),)&""，按ctrl+shift+enter三键结束，向下填充。

=(A$2:D$7="")/1%%这部分返回的结果如下图红色框所示，将空单元格的变为10000，非空单元格的变为0。

=(A$2:D$7="")/1%%+ROW($2:$7)/1%+COLUMN(A:D)这部分是在上一步的基础上加上行号扩大100倍，再加上列号。目的就是将空单元格的变成比较大的数字，然后非空单元格的数字按行的方向从小到大排列。

=SMALL((A$2:D$7="")/1%%+ROW($2:$7)/1%+COLUMN(A:D),ROW(A1))这部分就是用small取出上一步的第1个最小值201，公式下拉取出第2个最小值203，然后204，301，302，。。。如下图红色框所示，201代表2行1列。这样刚好就是按行的方向提取，并且把空单元格排除出去。

=TEXT(SMALL((A$2:D$7="")/1%%+ROW($2:$7)/1%+COLUMN(A:D),ROW(A1)),"r0c00")这部分就是用text将上一步的数字转为文本型单元格地址，也就是r1c1样式，如下图红色框所示。

=INDIRECT(TEXT(SMALL((A$2:D$7="")/1%%+ROW($2:$7)/1%+COLUMN(A:D),ROW(A1)),"r0c00"),)这部分就是用indirect将文本型单元格地址转为单元格引用。结果如下图红色框所示，其实就是F列的结果。最后&""是为了将空单元格返回空文本。

也可以用if函数来完成，if相对会好理解一点，公式为=INDIRECT(TEXT(SMALL(IF(A$2:D$6<>"",ROW($2:$6),4^8)/1%+COLUMN(A:D),ROW(A1)),"r0c00"),)&""，按三键结束，向下填充。

再来看按列方向排列的，在H2单元格输入公式=INDIRECT("r"&MOD(SMALL((A$2:D$6="")/1%%+COLUMN(A:D)/1%+ROW($2:$6),ROW(A1)),100)&"c"&INT(SMALL((A$2:D$6="")/1%%+COLUMN(A:D)/1%+ROW($2:$6),ROW(A1))%),)&""，按三键结束，向下填充。

这个公式的思路和昨天的差不多，只不过多了个条件，对空单元格的判断处理，这里就不再详细说明了，可以参考昨天的文章。

用if来完成的话，公式为=INDIRECT("r"&MOD(SMALL(IF(A$2:D$6<>"",COLUMN(A:D),4^7)/1%+ROW($2:$6),ROW(A1)),100)&"c"&INT(SMALL(IF(A$2:D$6<>"",COLUMN(A:D),4^7)/1%+ROW($2:$6),ROW(A1))%),)&""，按三键结束，向下填充。

如果你还有其他的方法，欢迎在留言区写出答案，让我们共同学习。

文件链接：

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

提取码：psj6