如何将一列數據轉成多行多列?之前跟大家分享了2個方法,數據透視表和Power Query,今天我跟大家分享幾個用函數公式實現的方法。
下圖中,我們需要将A列的數據轉成C:G多行多列的數據。
方法一:使用INDEX COLUMN ROW函數組合。
具體操作步驟如下:
1、選中C1單元格,在編輯欄中輸入公式:=INDEX($A:$A,COLUMN(A$1) 5*(ROW($A1)-1))&"",按Enter鍵回車。
2、将公式右拉填充至G1單元格,下拉填充至G4單元格即可。
3、公式解析。
COLUMN(A$1):COLUMN函數返回一引用的列号。COLUMN(A$1)返回A1單元格所在列号,為1,如果往右填充,列号會依次增加1,如果往下填充,同一列的列号不變。
ROW($A1):ROW函數返回一個引用的行号。ROW($A1)返回A1單元格所在的行号,為1,如果往右填充, 同一行所在行号不變,如果往下填充,同一列的行号依次增加1。
=INDEX($A:$A,COLUMN(A$1) 5*(ROW($A1)-1))&"":INDEX函數是在給定的單元格區域中,返回特定行列交叉處單元格的值或引用。把COLUMN(A$1)和ROW($A1)的值代入公式,公式變成=INDEX($A:$A,1)&"",也就是在A列單元格區域中,返回第1個單元格的值。公式後面用&符号跟""連接起來,也就是在A列單元格區域中,如果沒有匹配到值,就返回空,把錯誤值給屏蔽掉。
4、完整的動圖演示如下。
方法二:使用INDEX COLUMNS ROWS函數組合。
1、選中C1單元格,在編輯欄中輸入公式:=INDEX($A:$A,COLUMNS($C$1:C$1) 5*(ROWS($A$1:$A1)-1))&"",按Enter鍵回車。
2、将公式右拉填充至G1單元格,下拉填充至G4單元格即可。
3、公式解析。
COLUMNS($C$1:C$1):COLUMNS函數返回某一引用或數組的列數。COLUMNS($C$1:C$1)返回C1單元格所在列号,為1。公式往右填充,同一行的列号依次增加1,公式往下填充,同一列的列号不變。
ROWS($A$1:$A1):ROWS函數返回某一引用或數組的行數。ROWS($A$1:$A1)返回C1單元格所在的行号。公式往右填充,同一行所在的行号不變,公式往下填充,同一列所在的行号依次增加1。
=INDEX($A:$A,COLUMNS($C$1:C$1) 5*(ROWS($A$1:$A1)-1))&"":INDEX函數是在給定的單元格區域中,返回特定行列交叉處單元格的值或引用。把COLUMNS($C$1:C$1)和ROWS($A$1:$A1)的值代入公式,公式變成=INDEX($A:$A,1)&"",也就是在A列單元格區域中,返回第1個單元格的值。公式後面用&符号跟""連接起來,也就是在A列單元格區域中,如果沒有匹配到值,就返回空,把錯誤值給屏蔽掉。
4、完整的動圖演示如下。
方法三:使用INDIRECT COLUMN ROW函數組合。
1、選中C1單元格,在編輯欄中輸入公式:=INDIRECT("a"&COLUMN(A$1) 5*(ROW($A1)-1))&"",按Enter鍵回車。
2、将公式右拉填充至G1單元格,下拉填充至G4單元格即可。
3、公式解析。
COLUMN(A$1):COLUMN函數返回一引用的列号。COLUMN(A$1)返回A1單元格所在列号,為1,如果往右填充,列号會依次增加1,如果往下填充,同一列的列号不變。
ROW($A1):ROW函數返回一個引用的行号。ROW($A1)返回A1單元格所在的行号,為1,如果往右填充, 同一行所在行号不變,如果往下填充,同一列的行号依次增加1。
=INDIRECT("a"&COLUMN(A$1) 5*(ROW($A1)-1))&"":INDIRECT函數返回文本字符串所指定的引用。把COLUMN(A$1)和ROW($A1)代入公式,公式變成:=INDIRECT("a"&1)&"",&符号是連接符,"a"&1表示a和1相連接,變成a1,也就是用INDIRECT函數去引用A1單元格的内容,公式後面用&符号跟""連接起來,也就是在A列單元格區域中,如果沒有匹配到值,就返回空,把錯誤值給屏蔽掉。
4、完整的動圖演示如下。
方法四:使用OFFSET COLUMN ROW函數組合。
1、選中C1單元格,在編輯欄中輸入公式:=OFFSET($A$1,COLUMN(A$1) 5*(ROW($A1)-1)-1,0)&"",按Enter鍵回車。
2、将公式右拉填充至G1單元格,下拉填充至G4單元格即可。
3、公式解析。
COLUMN(A$1):COLUMN函數返回一引用的列号。COLUMN(A$1)返回A1單元格所在列号,為1,如果往右填充,列号會依次增加1,如果往下填充,同一列的列号不變。
ROW($A1):ROW函數返回一個引用的行号。ROW($A1)返回A1單元格所在的行号,為1,如果往右填充, 同一行所在行号不變,如果往下填充,同一列的行号依次增加1。
=OFFSET($A$1,COLUMN(A$1) 5*(ROW($A1)-1)-1,0)&"":OFFSET函數,是以指定的引用為參照系,通過給定偏移量返回新的引用。把COLUMN(A$1)和ROW($A1)代入公式,公式變成:=OFFSET($A$1,1,0)&"",也就是說,以A1單元格為參照系,向下移動一格,向右移動0格,也就是不移動,所以還是A1單元格的内容,公式後面用&符号跟""連接起來,也就是在A列單元格區域中,如果沒有匹配到值,就返回空,把錯誤值給屏蔽掉。
4、完整的動圖演示如下。
好了,今天就跟大家分享到這裡,如果你覺得文章有用,可以轉發分享給你的朋友,希望多多支持,謝謝!
,更多精彩资讯请关注tft每日頭條,我们将持续为您更新最新资讯!