【原】indirect+text：多行多列转单列（一）

刘卓学EXCEL 2021-04-02

展开全文

小伙伴们，大家好。今天要分享的内容是多行多列转单列的方法，主要利用的是indirect和text函数。先来看下数据源和实现后的效果。下图左表是数据源，转换成单列的效果如F列和H列所示。F列是按行的方向排列，H列是按列的方向排列。

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

首先看=ROW($2:$7)/1%+COLUMN(A:D)这部分，将行号扩大100倍，再加上列号，得到下图红色框所示的结果。由于是方向不同的一维数组的运算，得到二维数组。从结果中我们可以看到，数字的大小按行的方向来看是从小到大排列的。

=SMALL(ROW($2:$7)/1%+COLUMN(A:D),ROW(A1))这部分是从上一步的结果中取出第1个最小值201。公式下拉的话就是第2个最小值202，第3个最小值203，第4个最小值204，第5个最小值301，。。。以此类推，得到的数字顺序刚好就是按行的方向排列。

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

=INDIRECT(TEXT(SMALL(ROW($2:$7)/1%+COLUMN(A:D),ROW(A1)),"r0c00"),)这部分就是最后的公式，利用indirect将文本型单元格地址转为真正的单元格引用，如下图红色框所示。最终的效果如F列所示。

其实像这种规则的区域，没有空单元格，也可以用=INDIRECT("r"&INT((ROW(A1)-1)/4)+2&"c"&MOD(ROW(A1)-1,4)+1,)这个公式来完成，还不用按ctrl+shift+enter三键。

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

=COLUMN(A:D)/1%+ROW($2:$7)这部分是将列号乘以100，再加上行号，得到下图红色框的结果。可以看到数字的大小按列的方向来看是从小到大排列的。所以用small提取最小值的时候就是按列的方向来提取。

=SMALL(COLUMN(A:D)/1%+ROW($2:$7),ROW(A1))这部分就是用small取出第1个最小值102，公式下拉提取第2个最小值103，104。。。第1列的提完提第2列的202，203，。。。一直提完。但是这里的102可不是1行2列，是1列2行，所以要想办法变为2行1列。

=MOD(SMALL(COLUMN(A:D)/1%+ROW($2:$7),ROW(A1)),100)这部分是用mod求余，比如102除以100得到余数2，这个2就是2行。同样的=INT(SMALL(COLUMN(A:D)/1%+ROW($2:$7),ROW(A1))%)这部分是用int取整，比如102除以100得到1.02，用int取整得到1，这个1就是1列。

="r"&MOD(SMALL(COLUMN(A:D)/1%+ROW($2:$7),ROW(A1)),100)&"c"&INT(SMALL(COLUMN(A:D)/1%+ROW($2:$7),ROW(A1))%)这部分是将上面得到的行号和列号重新组合成文本型的单元格地址，如下图红色框所示。

最后完整的公式为=INDIRECT("r"&MOD(SMALL(COLUMN(A:D)/1%+ROW($2:$7),ROW(A1)),100)&"c"&INT(SMALL(COLUMN(A:D)/1%+ROW($2:$7),ROW(A1))%),)，用indirect返回真正的单元格引用，如红色框所示，也就是H列的结果。