谷动谷力

 找回密码
 立即注册
查看: 1299|回复: 0
收起左侧

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

[复制链接]
发表于 2023-6-4 10:58:42 | 显示全部楼层 |阅读模式

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


小伙伴们好啊,今天给大家带来的是一个用函数实现行列转换的技巧。
如下图所示,是一份员工名单:
640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1.jpg
这个表中的姓名只有一列,却有40多行。打印之前,需要将A列姓名转换为适合打印的5列多行。接下来咱们就说说用函数公式如何来实现这样的效果。

D2单元格输入以下公式,向右向下复制:

=INDIRECT("a"&5*ROW(A1)-4+COLUMN(A1))&""
640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1.jpg
思路解析:
“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列数组:
640?wx_fmt=png.jpg
再以此作为INDEX函数的参数,从A列返回对应的内容就可以了。

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

=OFFSET($D$2,(ROW(A1)-1)/5,MOD(ROW(A1)-1,5))
640?wx_fmt=png&wxfrom=5&wx_lazy=1&wx_co=1.jpg
思路解析:
先来看看这部分:(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)

图文制作:周庆麟


+10
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|Archiver|手机版|深圳市光明谷科技有限公司|光明谷商城|Sunshine Silicon Corpporation ( 粤ICP备14060730号|Sitemap

GMT+8, 2024-7-19 03:20 , Processed in 0.156639 second(s), 41 queries .

Powered by Discuz! X3.2 Licensed

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表