2015年11月16日 星期一

Excel-多欄資料重組在一欄(OFFSET,COUNT,ROW,MOD)

在 Excel 的工作表中常會用到要重組資料,例如下圖中,每天會輸入幾筆資料,隨著日期不斷的增加,如何能將每天不同欄位的資料重組在一欄中?
下圖中,每天都要輸入甲、乙、丙、丁、戊、己、庚等項目的資料,要將這些資料重組在一個欄位中。
Excel-多欄資料重組在一欄(OFFSET,COUNT,ROW,MOD)

【公式設計與解析】
儲存格J2:=OFFSET($B$2,MOD(ROW(1:1)-1,COUNT(A:A)),COLUMN(A:A)-1+
INT((ROW(1:1)-1)/COUNT(A:A)),,)
COLUMN(A:A):計算日期欄位中含有幾個日期儲存格,本例傳回 7。
ROW(1:1):ROW(1:1)=1,當公式向下複製時會產生 ROW(1:1)=1→ROW(2:2)=2→
ROW(3:3)=3→ ...。
MOD(ROW(1:1)-1,COUNT(A:A)):本例為 MOD(ROW(1:1)-1,7) 傳回 0,當公式往下複製時依序傳回 0, 1, 2, 3, 4, 5, 6, 0, 1, 2, ..., 6, 0, ...。
COLUMN(A:A)-1+INT((ROW(1:1)-1)/COUNT(A:A)):本例為 7-1+INT((ROW(1:1)-1)/7) 傳回 0,當公式往下複製時依序傳回 0,0,0,0,0,0,0, 1, 1, 1, 1, 1, 1, 1, 2, 2,  ...。
將以上各式代入 OFFSET 函數中,即可重組多欄變為一欄。
本例的做法可以在新增一個日期時,不用改變公式也可以重組多欄變為一欄。

沒有留言:

張貼留言

好康東東