阅读文本大概需要1-2min. 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负号表示向左): C6单元格位于第6行、向上偏移3行就是第3行; C6单元格位于第3列(C列)、向左偏移2列就是第1列(A列); 所以OFFSET(C6,-3,-2)最后返回A3单元格的内容,即56。 总结: 以第一个参数为参照物,向下(正数)或向上(负数)偏移X行(参数2的绝对值); 向右(正数)或向左(负数)偏移Y列(参数3的绝对值),所得的单元格内容,即为公式返回值。 弄清楚函数的功能,再来看今天的行列转换就要轻松很多,我们以多列转一列为例,来看下函数的具体用法。 下图中,需要将A1:C5共15个单元的内容竖向排列(转1列),首先排列第一行A1-C1单元格,接着排列A2-C2单元格…以此类推。 F列是最终每个单元格的位置;G-H列为每个单元格通过A1单元格偏移量的值。 先来看偏移的行数,每隔三个数偏移行加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三个一循环。 所以最终的公式为: 注意这里的相对引用与绝对引用($在Excel中表示绝对引用,即拖拽公式,单元格引用位置不发生变化,始终为A1单元格) 相对引用和绝对引用可以看下这篇文章: 注:这里需要转变的区域只有3列,如果有N列的话,只需要将公式中的3换成N即可。 其它三种行列的转换方法都是通过OFFSET函数来实现,原理基本一致,通过研究一个案例来举一反三,才能有所成长收获,快去试试吧~ |
|
来自: 黄*绿 > 《.Excel 技巧》