Excel学习笔记：行列转换

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

offset函数
行列转换的三种方式
- 1.右键转置
- 2.转置公式TRANSPOSE
- 3.引用函数OFFSET+ROWS/COLUMN（支持随时更新数据）
一行（列）转多行多列

offset函数

功能：

以指定的（单元格或相连单元格区域的引用）为参照系，通过给定偏移量得到新的引用。返回的引用可以是一个单元格也可以是一个区域（可以指定行列数，即数组）。

语法：

=offset( reference, rows, cols, [height], [width])

=offset(参照单元格，行偏移量，列偏移量，返回几行，返回几列)

参数解释：

参数Reference —— 参照单元格（单元格或相连单元格区域的引用）。否则，offset函数返回错误值#VALUE!
参数Rows——行偏移量。正数（代表在参照单元格的下方），负数（代表在参照单元格的上方）。
参数Cols——列偏移量。正数（代表在参照单元格的右边）或负数（代表在参照单元格的左边）。
参数Height——返回几行（必须为正数），即所要返回的引用区域的行数。
参数Width——返回几列(必须为正数），即所要返回的引用区域的列数。

最后2个参数不指定时，默认返回1行1列。

实例：

输入公式：=offset(A1,1,1,1,1) -- 返回A1的右下角单元格

输入公式：=sum(offset(A1,1,1,2,2)) -- 返回A1的右下角2*2单元格，并进行求和

可通过F9按钮查看数据。

另外，也可以通过选定2*2单元格，再进行输入公式，按住三键CTRL+SHIFT+ENTER进行数组计算。

三键（CTRL+SHIFT+ENTER） -- 数组计算

行列转换的三种方式

1.右键转置

复制源数据--右键--选择性粘贴--勾“转置”，确定。

2.转置公式TRANSPOSE

列转行 → 先选中横向连续的N个空白单元格，在编辑栏输入公式=TRANSPOSE($A$1:$A$10)，然后按三键CTRL+SHIFT+ENTER即可；
反之，行转列则先选中竖向的单元格区域，后续操作类似。

$表示绝对引用，可按F4直接切换。

3.引用函数OFFSET+ROWS/COLUMN（支持随时更新数据）

方法一、方法二只能一次性转置，后期无法随时更新数据。

列转行 → 输入公式 =OFFSET($A$1,COLUMN(A1)-1,0,1,1)向右拉
行转列 → 输入公式=OFFSET($A$1,0,ROW(A1)-1,1,1)向下拉

一行（列）转多行多列

结合OFFSET函数与ROW/COLUMN函数进行嵌套使用，即可实现。

1.一列转多列多行（或多行多列）

①先列后行，比如一列数据转为四列多行。

在目标区域第一个单元格输入以下公式，然后向右拉到第4列，再下拉直到出现空白。

=OFFSET($A$1,COLUMN(A1)-1+(ROW(A1)-1)*4,)&""

②先行后列，比如一列数据转为四行多列。

在单元格输入以下公式，然后向下拉到第4行，再右拉直到出现空白。

=OFFSET($A$1,ROW(A1)-1+COLUMN(A1)*4-4,)&""

2.一行转多列多行（或多行多列）

①先列后行，比如一行数据转为三列多行。

同样，此例在单元格输入以下公式，然后向右拉到第3列，再下拉直到出现空白。

=OFFSET($A$1,,(COLUMN(A1)-1)+(ROW(A1)-1)*3,)&""

②先行后列，比如一列数据转为三行多列。

在单元格输入以下公式，然后向下拉到第3行，再右拉直到出现空白。

=OFFSET($A$1,,(ROW(A1)-1)+(COLUMN(A1)-1)*3,)&""

3.多行多列转一列

①先向下引用源数据，再向右引用。

单元格输入以下公式，下拉直到出现空白。

=OFFSET($A$1,MOD(ROW(A6),6),ROW(A6)/6-1,)&""

②先向右引用源数据，再向下引用。

单元格输入以下公式，下拉直到出现空白。

=OFFSET($A$1,ROW(A5)/5-1,MOD(ROW(A5),5))&""

4.多行多列转一行

多行多列数据转成单行的情况实际应用中应该不算常见，不过这里也一并整理了，若需要可直接套用公式。

①先向右引用源数据，再向下引用。

单元格输入以下公式，右拉直到出现空白。

=OFFSET($A$1,INT(COLUMN(F1)/6)-1,MOD(COLUMN(F1),6))&""

②先向下引用源数据，再向右引用。

单元格输入以下公式，右拉直到出现空白。

=OFFSET($A$1,MOD(COLUMN(E1),5),INT(COLUMN(E1)/5)-1)&""

①ROW()返回行号，比如ROW(A2)=2；
②COLUMN()返回列号，比如COLUMN(F1)=6；
③MOD()求余数，比如MOD(1,5)=1，即1÷5余数是1，再比如MOD(5,5)=0，因为5被5整除，没有余数；
④上面每个公式最后都加了&""，作用是把引用空单元格得到的0去掉，显示为空白；
⑤上面公式中用到不少逗号,（英文半角状态下），不能随意省略或移动位置，每一个小豆芽都有它存在的意义。