學不完．教不停．用不盡

在 Excel 裡有一個多欄的資料清單，如果想要將多欄轉換為單欄(參考下圖)，該如何處理？

首先，以 OFFSET 函數為主來設計。

【設計與解析】

1. 列出人員

儲存格A14：=OFFSET($A$2,MOD(ROW(1:1)-1,6),0)

(1) MOD(ROW(1:1)-1,6)

公式向下複製時，會產生 0,1,2,3,4,5,0,1,2,3,4,5, …。

MOD 函數為兩數相除求餘數。

(2) OFFSET($A$2,MOD(ROW(1:1)-1,6),0)

以儲存格A2為起點，利用第(1)式傳回控制列的變化，傳回對應位置的儲存格內容。

2. 列出月份

儲存格B14：=OFFSET($B$1,0,INT((ROW(1:1)-1)/6))

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

公式向下複製時，會產生 0,0,0,0,0,0,1,1,1,1,1,1, …。

INT 函數為兩數相除求商數。

(2) OFFSET($B$1,0,INT((ROW(1:1)-1)/6))

以儲存格A2為起點，利用第(1)式傳回控制欄的變化，傳回對應位置的儲存格內容。

3. 列出數值

儲存格C14：=OFFSET($B$2,MOD(ROW(1:1)-1,6),INT((ROW(1:1)-1)/6))

公式原理同1.和2.。

接著，如果你想以 INDEX 函數來操作，

【設計與解析】

公式設計如下：

儲存格A14：=INDEX($A$2:$A$7,MOD(ROW(1:1)-1,6)+1,0)

儲存格B14：=INDEX($B$1:$M$1,0,INT((ROW(1:1)-1)/6)+1)

儲存格C14：=INDEX($B$2:$M$7,MOD(ROW(1:1)-1,6)+1,INT((ROW(1:1)-1)/6)+1)

試著觀察 OFFSET 函數和 INDEX 函數在參數上的差異，OFFSET 是以 0 為始，INDEX 是以 1 為始。