函数公式用的好,下班休想走的早
小伙伴们好啊,今天给大家带来的是一个用函数实现行列转换的技巧。
如下图所示,是一份员工名单:
这个表中的姓名只有一列,却有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值。
如果要把D2:H10单元格区域中多行多列的数据转换为一列,该怎样处理呢?
任意单元格中输入以下公式,下拉到出现空白单元格为止:
=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列。
如此循环往复……
最后留给大家两个课后题:
1、如果要将A列数据转换为6列多行,需要使用什么公式呢?
2、如果要将6列多行的数据转换为1列,公式该怎么修改呢?
图文制作:周庆麟