網友提問:在 Excel 中,如下圖左的資料清單格式,如何轉換為下圖右的資料格。

觀察原始資料清單,其中每一列中的人名裡,各有 4 筆資料。

轉換資料主要是要將人名每 4 列顯示,而資料部分則是由欄轉列。

Excel-資料轉換:欄轉為列(INT,MOD)

【公式設計與解析】

1. 處理人明每 4 列顯示

儲存格H2:=IF(MOD(ROW(1:1),4)=1,OFFSET($A$1,INT((ROW(1:1)-1)/4),0),"")

複製儲存格H2,貼至儲存格H2:H16。

(1) MOD(ROW(1:1),4)=1

利用 MOD 函數,判斷列號除以 4 的餘數,若是為 1 時,顯示資料,否則顯示空白(空字串)。

當公式向下複製時,會產生數列:0,1,2,3,0,1,2,3, ...

(2) INT((ROW(1:1)-1)/4)

利用 INT 函數取得計算列號減 1 後再除以 4 的商。

當公式向下複製時,會產生數列:0,0,0,0,1,1,1,1,2, ...

(3) OFFSET($A$1,INT((ROW(1:1)-1)/4))

將第(2)式代入 OFFSET 函數取得對應的儲存格內容。

 

2. 處理資料由欄轉列

儲存格G2:=OFFSET($A$1,INT((ROW(1:1)-1)/4),MOD(ROW(1:1)-1,4)+1)

複製儲存格G2,貼至儲存格G2:G16。

(1) INT((ROW(1:1)-1)/4)

當公式向下複製時,會產生數列:0,0,0,0,1,1,1,1,2, ...

(2) MOD(ROW(1:1)-1,4)+1

當公式向下複製時,會產生數列:0,1,2,3,0,1,2,3, ...

(3) OFFSET($A$1,第(1)式,第(2)式)

將第(1)式和第(2)式代入 OFFSET 函數取得對應的儲存格內容。

各項數值變化,請參考下圖。

Excel-資料轉換:欄轉為列(INT,MOD)

文章標籤

全站熱搜

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