excel数据转置多列转一行(Excel行列互转多列转一列)

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

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

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

多列转一列

excel数据转置多列转一行(Excel行列互转多列转一列)(1)

多行转一行:

excel数据转置多列转一行(Excel行列互转多列转一列)(2)

一列转多列:

excel数据转置多列转一行(Excel行列互转多列转一列)(3)

一行转多行:

excel数据转置多列转一行(Excel行列互转多列转一列)(4)

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

OFFSET函数

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

表达式:

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

例1:

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

excel数据转置多列转一行(Excel行列互转多列转一列)(5)

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行列互转多列转一列)(6)

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行列互转多列转一列)(7)

先来看偏移的行数,每隔三个数偏移行加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函数来实现,原理基本一致,通过研究一个案例来举一反三,才能有所成长收获~

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

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页