學不完．教不停．用不盡

回答網友提問：在 Excel 中有一個如下圖左表格的資料清單，如何利用公式轉換為如下圖右的表格格式？

很多種的表格格式轉換都是「N列ＸＭ欄」轉「X列Y欄」，這一類的問題不外乎都會用到 OFFSET, INT, MOD 函數。

【公式設計與解析】

1. 轉換表格標題

儲存格F1：=OFFSET($A$2,INT((ROW(A1)-1)/3),0)&"-"&OFFSET($B$1,0,
MOD(ROW(A1)-1,3))

複製儲存格F1，貼至儲存格F1:F12。

OFFSET：傳回根據所指定列數及欄數之儲存格或儲存格範圍之範圍的參照。

INT：求兩數相除的整數商

MOD：求兩數相除的整數餘數

(1) OFFSET($A$2,INT((ROW(A1)-1)/3),0)

當公式向下複製時，INT((ROW(A1)-1)/3)會產生0,0,0,1,1,1,2,2,2,…。

在公式中的「/3」是因為原表格的內容有「3欄」，所以轉換後每3列相同的內容。

(2) OFFSET($B$1,0,MOD(ROW(A1)-1,3))

當公式向下複製時，MOD(ROW(A1)-1,3)會產生0,1,2,0,1,2,0,1,2,…。

在公式中的「/3」是因為原表格的內容有「3欄」，所以重覆取用第1欄～第3欄的內容。

將第(1)式和第(2)式的傳回值套用至 OFFSET 函數，傳回對應的儲存格內容。

2. 轉換表格內容

儲存格G1：=OFFSET($B$2,INT((ROW(A1)-1)/3),MOD(ROW(A1)-1,3))&""

複製儲存格G1，貼至儲存格G1:G12。

公式的原理同「1. 轉換表格標題」。

其中公式裡的「&""」是一個小技巧，可以將傳回值「0」轉換為空字串（顯示為空白）。