indirect+text：多行多列转单列的改进(按列的方向)

小伙伴们好，今天对前面文章《indirect+text：多行多列转单列（一）》中的方法做个改进，主要改进的是按列的方向排列的。当时的公式有一部分重复用了2次，所以公式会比较长，不知道你是否还有印象？下面来看下今天的公式吧。

1.将下列城市转为单列(按列的方向排列）

在F2单元格输入公式=INDIRECT(TEXT(MOD(SMALL(COLUMN(A:D)/1%%+ROW($2:$5)/1%+COLUMN(A:D),ROW(A1)),10^4),"R0C00"),)，按ctrl+shift+enter三键结束，向下填充。

我们一段一段的来拆解公式，=ROW($2:$5)/1%+COLUMN(A:D)这部分用A2:D5这个区域的行号乘以100再加上列号，返回的结果如下图红色框所示。这样做的目的是为了后面得到文本型的单元格引用。比如拉萨所在的单元格是A2，也就是2行1列，那我们就用201来表示。

虽然我们得到了表示单元格引用的数字，但是题目要求按列的方向排列成一列，也就意味着我们先要提取第1列的数字201，301，401，501；然后提取第2列的数字202，302，402，502；再是第3列的，最后是第4列的。如果我们直接用small提取第1个，第2个，第3个。。。第n个最小值，那么结果是201，202，203，204。。。504，这样就是按行的方向了，与我们的要求不符。那怎么样才能按列的方向提取数字呢？那就是在列的方向上加权。

=COLUMN(A:D)/1%%+ROW($2:$5)/1%+COLUMN(A:D)这部分就是在上一步的基础上在列的方向上加权处理，用列号乘以1万再加上一步的结果，返回的结果如下图所示。这样的话，第1列是1万开头，第2列是2万开头，第3列是3万开头。。。然后用small从小到大的提取就是按列的方向提取了。

=SMALL(COLUMN(A:D)/1%%+ROW($2:$5)/1%+COLUMN(A:D),ROW(A1))这部分就是用small提取上一步的第1个最小值10201，公式下拉提取第2个，第3个。。。第n个最小值，如下图所示。这样就按列的方向提取出来了，但前面的1万，2万我们不要，只要后面的3位数201，301等。所以下一步还要把前面的万去掉，这属于“过河拆桥”。

=MOD(SMALL(COLUMN(A:D)/1%%+ROW($2:$5)/1%+COLUMN(A:D),ROW(A1)),10^4)这部分用mod把上一步的结果除以1万，取余数，得到了我们要的结果，如下图所示，完成“过河拆桥”。

=TEXT(MOD(SMALL(COLUMN(A:D)/1%%+ROW($2:$5)/1%+COLUMN(A:D),ROW(A1)),10^4),"R0C00")这部分用text把上一步的结果转为r1c1样式的文本型单元格引用，如下图所示。

=INDIRECT(TEXT(MOD(SMALL(COLUMN(A:D)/1%%+ROW($2:$5)/1%+COLUMN(A:D),ROW(A1)),10^4),"R0C00"),)这部分就是用indirect返回真正的单元格引用，实现了多行多列转单列的效果。

高手的公式是这样的，在H2单元格输入公式=INDIRECT(TEXT(SMALL(DATE(COLUMN(A:D),ROW($2:$5),COLUMN(A:D)),ROW(A1)),"RmCd"),)按三键结束，向下填充。

利用时间日期函数，将行号、列号和加权的方向放在3个参数中，组成一个日期或时间；然后用text取出行号和列号得到文本型的单元格地址。由于加权的方向是主要关键字，所以这里要放在第1参数年份中；行号和列号按情况放在第2或第3参数中。

不过我认为数据量足够大，超出月份或天数时，这种用法会出错，不知道高手有没有解决方法。对于这种用法，我也是第1次见到，所以理解不足。如有错误，欢迎大家纠正。

写一个我自己想出来的公式，主要是为了解决之前一段公式重复用2次的问题。在J2单元格输入公式=INDIRECT(TEXT(SUM(MMULT(TRUNC(SMALL(COLUMN(A:D)/1%+ROW($2:$5),ROW(A1)),{0,-2}),{1,0;-1,1})*10^{2,-2}),"R0C00"),)，不用三键，向下填充。公式还是挺长，用了很多常量数组，但可以将代表1列2行的102变为2行1列的201。

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

文件链接：

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

提取码：ot32