學不完．教不停．用不盡

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

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

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

【公式設計與解析】

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 函數取得對應的儲存格內容。

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