閱讀文本大概需要1-2min.

Excel作為小型數據的載體，數據錄入區域僅由行和列組成，上手簡單、應用面廣。

日常數據處理中經常會遇到行與列轉換的情況，如多行轉一行、一列轉多列等，今天我們就系統的介紹下，如何用公式，來實現行與列的靈活轉換。

多列轉一列：

多行轉一行：

一列轉多列：

一行轉多行：

可以發現的是，這些轉換全部是用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行（參數二的絕對值），向右（正數）或向左（負數）偏移Y列（參數三的絕對值），所得的單元格内容，即為公式返回值。

弄清楚函數的功能，再來看今天的行列轉換就要輕松很多，我們以多列轉一列為例，來看下函數的具體用法。

下圖中，需要将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三個一循環。

所以最終的公式為：

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

公式最後的【&""】是為了防止出現0（也可以不加）。

注意這裡的相對引用與絕對引用（$在Excel中表示絕對引用，即拖拽公式，單元格引用位置不發生變化，始終為A1單元格）

相對引用和絕對引用可以看下這篇文章：Excel相對引用與絕對引用，傻傻分不清？

小結：

這裡需要轉變的區域隻有3列，如果有N列的話，隻需要将公式中的3換成N即可。

其它三種行列的轉換方法都是通過OFFSET函數來實現，原理基本一緻，通過研究一個案例來舉一反三，才能有所成長收獲~

喜歡的小夥伴記得點個關注，不迷路~

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!