excel行列转换(Excel行列互转 多列转一列、多行转一行、一列转多列、一行转多行)

阅读文本大概需要1-2min.

Excel作为小型数据的载体,数据录入区域仅由行和列组成,上手简单、应用面广。

日常数据处理中经常会遇到行与列转换的情况,如多行转一行、一列转多列等,今天我们就系统的介绍下,如何用公式,来实现行与列的灵活转换。

多列转一列

excel行列转换(Excel行列互转 多列转一列、多行转一行、一列转多列、一行转多行)

多行转一行:

excel行列转换(Excel行列互转 多列转一列、多行转一行、一列转多列、一行转多行)

一列转多列:

excel行列转换(Excel行列互转 多列转一列、多行转一行、一列转多列、一行转多行)

一行转多行:

excel行列转换(Excel行列互转 多列转一列、多行转一行、一列转多列、一行转多行)

可以发现的是,这些转换全部是用OFFSET函数来实现的,所以先来了解下OFFSET函数的功能。

OFFSET函数

函数功能:以指定的引用为参照系,通过给定偏移量返回新的引用

表达式:

OFFSET(引用区域, 行偏移量, 列偏移量, [返回行高], [返回列宽]),一共五个参数,后面两个可以省略,举个例子加深对函数的理解:

例1:

OFFSET(A2,4,1)意思是参照A2单元格,向下偏移4行、向右偏移1列

A2单元格位于第2行、向下偏移4行就是第6行;

A2单元格位于第1列(A列)、向右偏移1列就是第2列(B列);

所以OFFSET(A2,4,1)最后返回的单元格是B6单元格的内容,即51。

例2:

OFFSET(C6,-3,-2)意思是参考C6单元格,向上偏移3行(参数2负号表示向上)、向左偏移2列(参数3负号表示向左)

excel行列转换(Excel行列互转 多列转一列、多行转一行、一列转多列、一行转多行)

C6单元格位于第6行、向上偏移3行就是第3行;

C6单元格位于第3列(C列)、向左偏移2列就是第1列(A列);

所以OFFSET(C6,-3,-2)最后返回A3单元格的内容,即56。

总结:

以第一个参数为参照物,向下(正数)或向上(负数)偏移X行(参数二的绝对值),向右(正数)或向左(负数)偏移Y列(参数三的绝对值),所得的单元格内容,即为公式返回值。

弄清楚函数的功能,再来看今天的行列转换就要轻松很多,我们以多列转一列为例,来看下函数的具体用法。

多列转一列

下图中,需要将A1:C5共15个单元的内容竖向排列(转1列),首先排列第一行A1-C1单元格,接着排列A2-C2单元格…依次类推;

F列是排列之后每个单元格的位置;G-H列为每个单元格通过A1单元格偏移量的值。

excel行列转换(Excel行列互转 多列转一列、多行转一行、一列转多列、一行转多行)

先来看偏移的行数,每隔三个数偏移行加1,这里可以用(ROW(A1)-1)/3来实现,ROW函数返回当前单元格的行数,公式下拉的时候:

ROW(A1)返回1,(1-1)/3等于0,OFFSET函数中返回0;

ROW(A2)返回2,(2-1)/3不足1,OFFSET函数中返回0;

ROW(A3)返回3,(3-1)/3不足1,OFFSET函数中返回0;

ROW(A4)返回4,(4-1)/3等于1,OFFSET函数中返回1;

ROW(A5)返回5,(5-1)/3不足2,OFFSET函数中返回1;

………..

每隔开三个数,行位移增加1;

再来看偏移的列数,0,1,2/0,1,2反复循环,因为只有三列,只需要偏移3次即可遍历数据源,这里用MOD(ROW(A1)-1,3)公式来实现,MID函数为取余函数:

MOD(ROW(A1)-1,3)等价于MOD(1-1,3)余数为0,OFFSET函数中返回0;

MOD(ROW(A2)-1,3)等价于MOD(2-1,3)余数为1,OFFSET函数中返回1;

MOD(ROW(A3)-1,3)等价于MOD(3-1,3)余数为2,OFFSET函数中返回2;

MOD(ROW(A4)-1,3)等价于MOD(4-1,3)余数为0,OFFSET函数中返回0;

……

0,1,2三个一循环。

所以最终的公式为:

= OFFSET($A$1,(ROW(A1)-1)/3,MOD(ROW(A1)-1,3))&“”

公式最后的【&””】是为了防止出现0(也可以不加)。

注意这里的相对引用与绝对引用($在Excel中表示绝对引用,即拖拽公式,单元格引用位置不发生变化,始终为A1单元格)

相对引用和绝对引用可以看下这篇文章:Excel相对引用与绝对引用,傻傻分不清?

小结

这里需要转变的区域只有3列,如果有N列的话,只需要将公式中的3换成N即可

其它三种行列的转换方法都是通过OFFSET函数来实现,原理基本一致,通过研究一个案例来举一反三,才能有所成长收获~

喜欢的小伙伴记得点个关注,不迷路~

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 16699894@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022年12月23日 14:13:32
下一篇 2022年12月23日 14:16:31

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注