2015年12月18日 星期五

Excel-一列多欄轉多列一欄(OFFSET,INT,MOD,ROW)

網友想要將 Excel 資料表中的資料,由一列多欄轉多列一欄呈現(如下圖),該如何處理?
Excel-一列轉多列(OFFSET,INT,MOD,ROW)

【公式說明與解析】
儲存格F2:=OFFSET($A$1,INT(ROW(1:1)/4)+1,MOD(ROW(1:1),4)-1)
複製儲存格F2,貼至儲存格F2:F4。
INT(ROW(1:1)/4)+1:公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3。INT(ROW(1:1)/4)+1會傳回 1→1→1。
MOD(ROW(1:1),4)-1:公式向下複製時,會傳回 0→1→2。
在 OFFSET 函數中由儲存格A1當起點,取出相對位置,即可將一列中的三欄(A2:C2)轉為一欄中的三列(F2:F4)。相當於 OFFSET(A1,1,0)→OFFSET(A1,1,1)→OFFSET(A1,1,2)。
複製儲存格F1:F4,貼至儲存格F1:F24。
儲存格F7:F9:公式相當於 OFFSET(A1,2,0)→OFFSET(A1,2,1)→OFFSET(A1,2,2)。
儲存格F10:F13:公式相當於 OFFSET(A1,3,0)→OFFSET(A1,3,1)→OFFSET(A1,3,2)。
...
如此便可以將將一列中的三欄轉為一欄中的三列。
同理:
儲存格H2:=OFFSET($A$1,INT(ROW(1:1)/4)+7,MOD(ROW(1:1),4)-1)
(試著比較儲存格F2和H2的差異)
複製儲存格H2,貼至儲存格H2:H4。
複製儲存格H1:H4,貼至儲存格H1:H24。

沒有留言:

張貼留言

好康東東