Excel-多欄轉換為單欄

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

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

Excel-多欄轉換為單欄

【設計與解析】

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 函數來操作,

Excel-多欄轉換為單欄

【設計與解析】

公式設計如下:

儲存格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 為始。

學不完.教不停.用不盡文章列表

    文章標籤

    Excel OFFSET INT MOD INDEX

    全站熱搜

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