延續上一篇文章:Excel-輪值表轉換不同呈現方式(OFFSET,MATCH)

下圖中,左半部的排班規劃表,如何轉換為下圖左側的清單?

如果只是要使用一次的話,只要用手拉一拉資料就好,如果要長期套用相同的排班表,則不妨把公式操作一遍。

Excel-1欄清單轉換為N列M欄表格(OFFSET,MOD,INT)

 

【公式設計與解析】

儲存格A3:

=OFFSET($G$2,MOD(ROW(A1)-1,4)*5+COLUMN(A1)-1,INT((ROW(A1)-1)/4))

複製儲存格A3,貼至儲存格A3:E5。再複製儲存格A3:E5,則至儲存格A7:E9、A11:E13、…。

(1) MOD(ROW(A1)-1,4)*5+COLUMN(A1)-1

ROW 函數傳回列號、COLUMN 函數傳回欄數。

其中參數「4」是指每 4 列一段資料,參數「5」是指每列有 5 欄資料。產生:

第 1 列:0,1,2,3,4

第 2 列:5,6,7,8,9

第 3 列:10,11,12,13,14

Excel-1欄清單轉換為N列M欄表格(OFFSET,MOD,INT)

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

第 1 區段4列:產生0

第 2 區段4列:產生1

第 3 區段4列:產生2

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

以儲存格G2為起始,將第(1)式和第(2)式代入 OFFSET 函數傳回對應的內容。

結果可將 1 欄 15 筆資料轉換為 3列 X 5 欄資料,並且每 4 列資料為一個區段對應 1 欄的資料。

Excel-1欄清單轉換為N列M欄表格(OFFSET,MOD,INT)

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

    文章標籤

    Excel OFFSET MOD INT

    全站熱搜

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