網友問到 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 函數取得各個儲存格的內容,完成轉置的動作。

arrow
arrow
    文章標籤
    Excel OFFSET INT MOD
    全站熱搜

    vincent 發表在 痞客邦 留言(0) 人氣()