2017年3月30日 星期四

Excel-手動資料轉置(OFFSET,ROW,COLUMN)2

網友問到:在 Excel 中有如下圖左的資料清單,如何能轉換為下圖右的排列方式?(共有二種)
轉換一:
Excel-手動資料轉置(OFFSET,ROW,COLUMN)
【公式設計與解析】
分析原始資料,資料分佈於間隔 2 列(2,4,6,8)、連續 1 欄(1,2,3,4,5,6)。
儲存格I1:=OFFSET($A$1,INT((ROW(1:1)-1)/6)*2+1,MOD((ROW(1:1)-1),6))
複製儲存格I1,貼至儲存格I1:I24。
(1) INT((ROW(1:1)-1)/6)*2+1
當公式向下複製時,依序產生 1,1,1,1,1,1,3,3,3,3,3,3,5,,5,5,5,5,5,7,7,7,7,7,7。
公式中的「/6」乃因為連續 6 欄;公式中的「*2」乃因為間隔 2 列。
(2) MOD((ROW(1:1)-1),6)
當公式向下複製時,依序產生 0,1,2,3,4,5,0,1,2,3,4,5, 0,1,2,3,4,5,0,1,2,3,4,5。
(3) OFFSET($A$1,第(1)式,第(2)式)
將第(1)式和第(2)式代入 OFFSET 函數中,依序取出對應的儲存格內容。

轉換二:
Excel-手動資料轉置(OFFSET,ROW,COLUMN)
【公式設計與解析】
分析原始資料,資料分佈於連續 1 列(2,3,4,5)、連續 1 欄(1,2,3,4,5,6)。
儲存格I1:=OFFSET($A$1,INT((ROW(1:1)-1)/6)+1,MOD((ROW(1:1)-1),6))
複製儲存格I1,貼至儲存格I1:I24。
(1) INT((ROW(1:1)-1)/6)+1
當公式向下複製時,依序產生 1,1,1,1,1,1,2,2,2,2,2,2,3,3,3,3,3,3,4,4,4,4,4,4。
(2) MOD((ROW(1:1)-1),6)
當公式向下複製時,依序產生 0,1,2,3,4,5,0,1,2,3,4,5, 0,1,2,3,4,5,0,1,2,3,4,5。
(3) OFFSET($A$1,第(1)式,第(2)式)
將第(1)式和第(2)式代入 OFFSET 函數中,依序取出對應的儲存格內容。

沒有留言:

張貼留言

檢視其他文章

好康東東