2016年12月12日 星期一

Excel-格式轉換(一列轉三列)(OFFSET,INT,ROW)

網友問到 Excel 的問題:如下圖,當資料超過 10000 筆時,想要將一列轉換為三列時,該如何處理?
參考下圖,資料由一列轉三列時,依其色彩放在不同的位置上。
Excel-格式轉換(一列轉三列)(OFFSET,INT,ROW)
參考以下的公式:
Excel-格式轉換(一列轉三列)(OFFSET,INT,ROW)
【公式設計與解析】
善加利用 INT(ROW(1:1)/3) 公式,當公式向下複製時:
ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→ROW(4:4)=4→...。
INT(ROW(1:1)/3)=0→INT(ROW(2:2)/3)=0→INT(ROW(3:3)/3)=0→INT(ROW(4:4)/3)=1→INT(ROW(5:5)/3)=1→INT(ROW(6:6)/3)=1→...。
如此,便可以將一列轉三列。
再透過 OFFSET 函數,藉著調整起起始儲存格位置,即可定義新的儲存格位置。每個儲存格的公式如下:
儲存格B7:=OFFSET($B$1,INT(ROW(1:1)/3),0)
儲存格C7:=OFFSET($C$1,INT(ROW(1:1)/3),0)
儲存格D7:=OFFSET($E$1,INT(ROW(1:1)/3),0)
儲存格E7:=OFFSET($E$1,INT(ROW(1:1)/3),0)
儲存格F7:=OFFSET($J$1,INT(ROW(1:1)/3),0)
儲存格G7:=OFFSET($K$1,INT(ROW(1:1)/3),0)
儲存格H7:=OFFSET($L$1,INT(ROW(1:1)/3),0)
儲存格I7:=OFFSET($M$1,INT(ROW(1:1)/3),0)
儲存格D8:=OFFSET($F$1,INT(ROW(1:1)/3),0)
儲存格E8:=OFFSET($G$1,INT(ROW(1:1)/3),0)
儲存格H8:=OFFSET($N$1,INT(ROW(1:1)/3),0)
儲存格I8:=OFFSET($O$1,INT(ROW(1:1)/3),0)
儲存格D9:=OFFSET($H$1,INT(ROW(1:1)/3),0)
儲存格E9:=OFFSET($I$1,INT(ROW(1:1)/3),0)
儲存格H9:=OFFSET($P$1,INT(ROW(1:1)/3),0)
儲存格I9:=OFFSET($Q$1,INT(ROW(1:1)/3),0)
選取儲存格B7:I9,往下各列貼上。當資料超過 10000 筆時,透過公式來轉換,還是比較方便一些。

沒有留言:

張貼留言

檢視其他文章

好康東東