2016年12月26日 星期一

Excel-表格的欄和列轉置(OFFSET,INT,MOD)

網友問到 Excel 的問題:如下圖,要將一個表格中的資料以公式做欄和列轉置的動作,該如何處理?
Excel-表格的欄和列轉置(OFFSET,INT,MOD)
【公式設計與解析】
儲存格J2:
=OFFSET($B$1,COLUMN(A:A)+INT((ROW(1:1)-1)/4)*3,MOD(ROW(1:1)-1,4)+1)
複製儲存格J2,往下各列貼上。
(1) COLUMN(A:A)+INT((ROW(1:1)-1)/4)*3
當公式向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3。
當公式向下複製時,INT((ROW(1:1)-1)/4)產生:1, 1, 1, 1, 4, 4, 4, 4, 7, 7, 7, 7, …。
兩者合併時會產生:1,2,3→1,2,3→1,2,3→1,2,3→4,5,6→4,5,6→4,5,6→4,5,6→ …。
(2) MOD(ROW(1:1)-1,4)+1
當公式向右複製時,
會產生:1,1,1→2,2,2→3,3,3→4,4,4→1,1,1→2,2,2→3,3,3→4,4,4→ …。
參考下圖:
Excel-表格的欄和列轉置(OFFSET,INT,MOD)
將第(1)式和第(2)式代入 OFFSET 函數取得各個儲存格的內容,完成轉置的動作。

沒有留言:

張貼留言

檢視其他文章

好康東東