Excel indirect 函数(1)

先前的小技巧意外得到好评，现在分享一下用函数实现这个需求的方法。

今天要讲到一个新函数 indirect，这个函数也是神级函数之一，因为太强大，我决定为它的每种应用场景开一个案例专讲。

我用的版本是 Excel 2016，其他版本的界面可能略有不同。

案例：

怎么把 A 列数据快速填充到黄色区域？按先行后列的习惯排列？

脑洞版解决方案请参考 Excel快速将一列数据排列成m行*n列

点击加载图片

解决方案：

在黄色区域的第一行第一列输入以下公式，向右向下拖动：

=IF(COLUMN(A1)<5,INDIRECT("A"&4*ROW(A1)-4+COLUMN(A1))&"","")

点击加载图片

点击加载图片

公式详解：

1. INDIRECT(ref_text,[a1])

1) indirect 函数引用的是文本参数，加“”和不加“”的用法不同，比如下图

=INDIRECT("a1")：文本引用，引用 A1 单元格所在的文本，即 b1

=INDIRECT(a1)：地址引用， A1 单元格内的 b1 被当作地址，该地址的内容为 ”excel"

* 如果各位看官有任何一门计算机语言的编程基础，就比较容易理解了，加""相当于返回文本值，不加相当于调用参数值。

点击加载图片

点击加载图片

2) [a1] 参数可选，为True或省略时，是常规的A1引用样式，为FALSE时，则是R1C1引用样式。

3) 回到本案例，INDIRECT("A"&4*ROW(A1)-4+COLUMN(A1))&""：

“4*ROW(A1)-4+COLUMN(A1)”部分的计算结果为 1，公式向下复制时，ROW(A1) 依次变为ROW(A2)、ROW(A3)…，计算结果分别为 5、9、13、17、21，就生成了一个步长为4的自然数序列。重要的是，COLUMN(A1)、COLUMN(A2)…的值在向下复制时，不变化！

公式向右复制时，COLUMN(A1)依次变为COLUMN(B1)、COLUMN(C1)…，计算结果分别为2、3、4，就生成了一个步长为1的自然数序列。而这时，ROW(A1)、ROW(B1)…的值在向右复制时，不变化！

&""：是为了让公式向下拖动到黄色区域以下时，返回空值，如果不加这段，空值区域就会显示 0。下图是不加这段代码的拖动效果

点击加载图片

4) IF(COLUMN(A1)<5,..,"")：

这段代码是为了控制向右拖动最多 4 列，即不超过黄色区域，超过就返回空值。

如果不加这段代码，向右拖动就会一直顺序显示数据列，就像下图这样

点击加载图片