sunsili 发表于 2023-6-4 10:58:42

【EXCEL公式】 INDIRECT公式用法讲解


【EXCEL公式】 INDIRECT公式用法讲解


小伙伴们好啊,今天给大家带来的是一个用函数实现行列转换的技巧。如下图所示,是一份员工名单:这个表中的姓名只有一列,却有40多行。打印之前,需要将A列姓名转换为适合打印的5列多行。接下来咱们就说说用函数公式如何来实现这样的效果。
D2单元格输入以下公式,向右向下复制:
[*]
=INDIRECT("a"&5*ROW(A1)-4+COLUMN(A1))&""思路解析:“5*ROW(A1) -4+COLUMN(A1)”部分,用于得到有规律的序列值,起始计算结果为2。公式向下复制填充时ROW(A1)依次变为ROW(A2)、ROW(A3)……,计算结果分别为7、12……,即生成步长值为5的自然数序列。公式向右复制填充时COLUMN(A1)依次变为COLUMN(B1)、COLUMN(C1)……,计算结果分别为3、4……即生成步长值为1的自然数序列。
这部分的计算结果与字符"a"连接成一个单元格地址“An”,最后用INDIRECT函数返回文本字符串所指定的引用,得到相应单元格的内容。公式最后使用&"",目的是为了屏蔽在引用空白单元格时返回的无意义的0值。如果你使用的是Excel 2021,还可以在D2单元格输入以下公式,直接按回车:
=INDEX(A:A,SEQUENCE(10,5,2,1))&""先使用SEQUENCE函数,得到从2开始,步长值为1的10行5列数组:再以此作为INDEX函数的参数,从A列返回对应的内容就可以了。

要把D2:H10单元格区域中多行多列的数据转换为一列,该怎样处理呢?在J2单元格输入以下公式,下拉到出现空白单元格为止:

[*]
=OFFSET($D$2,(ROW(A1)-1)/5,MOD(ROW(A1)-1,5))思路解析:先来看看这部分:(ROW(A1)-1)/5 ,作用是生成一个特殊的递增序列0、0.2、0.4、0.6、0.8、1、1.2……OFFSET函数将公式结果作为向下偏移的行数,并且会自动向下舍入到整数。就相当于得到从0开始,公式每下拉5行,结果递增1的序列。MOD(ROW(A1)-1,5)部分,仍然是得到一个特殊的循环序列,公式下拉时,结果为0、1、2、3、4、0、1、2、3、4…… OFFSET函数将这部分的结果作为向右偏移的列数。将上面两部分结合起来,OFFSET函数以D2单元格为基准点,公式下拉时依次偏移到数据源第二行的E、F、G、H列。当公式下拉到第5行时,OFFSET函数从D2开始向下偏移1行到D3单元格,然后继续向右依次偏移到第三行的E、F、G、H列。如此循环往复……如果你使用的是Excel 365,在D2单元格输入以下公式,直接按回车就可以了:=TOCOL(A2:H10)
图文制作:周庆麟

页: [1]
查看完整版本: 【EXCEL公式】 INDIRECT公式用法讲解