2017年2月15日 星期三

Excel-多欄轉少欄且少列轉多列(INT,MOD,OFFSET)

網友提問:下圖是 Excel 中的資料表,如何將下圖(上)的資料轉換為下圖(下)的呈現方式?
該資料表是由 3 組數值合成一列,每組數值有 6 個,共有 3 列的資料:
Excel-多欄轉少欄且少列轉多列(INT,MOD,OFFSET)

【公式設計與解析】
儲存格A7:=OFFSET($A$1,INT((ROW(1:1)-1)/3),MOD(INT(ROW(1:1)-1)*6+
COLUMN(A:A)-1,18))
複製儲存格A7,貼至儲存格F15。
(1) INT((ROW(1:1)-1)/3)
產生三組 0,0,0,0,0,0→產生三組 1,1,1,1,1→產生三組 2,2,2,2,2 。
(2) MOD(INT(ROW(1:1)-1)*6+COLUMN(A:A)-1,18)
產生三組 0,1,2,3, ..., 17。
第(1)式和第(2)式,如果向右和向下複製公式,將可產生以下的數列。每個儲存格由「第(1)式,第(2)式」組成。
Excel-多欄轉少欄且少列轉多列(INT,MOD,OFFSET)
(3) OFFSET($A$1,第(1)式,第(2)式)
利用第(1)式和第(2)式的傳回結果代入 OFFSET 函數,可以取得對應的儲存格內容。

沒有留言:

張貼留言

好康東東