2016年7月26日 星期二

Excel-矩陣資料內容轉換為一欄(OFFSET,MOD,INT,ROW)

網友問到:如何在 Excel 中將一個矩陣資料內容轉換為一欄?參考下圖,儲存格A1:D5中的資料要轉換至儲存格A7:A26。
矩陣資料內容轉換為一欄(OFFSET,MOD,INT,ROW)

【公式設計與解析】
儲存格A7:=OFFSET($A$1,MOD(ROW(1:1)-1,5),INT((ROW(1:1)-1)/5),1,1)
複製儲存格A7,往下各列貼上。
(1) MOD(ROW(1:1)-1,5)
本例中矩陣資料每一欄有 5 筆資料,公式中的參數『5』,即為每一欄有幾筆資料。
ROW(1:1)=1,當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。公式結果會產生 { 0,1,2,3,4,0,1,2,3,4, ... }。
(2) INT((ROW(1:1)-1)/5)
本例中矩陣資料每一欄有 5 筆資料,公式中的參數『5』,即為每一欄有幾筆資料。
當公式向下複製時,公式結果會產生 { 0,0,0,0,0,1,1,1,1,1, ... }。
矩陣資料內容轉換為一欄(OFFSET,MOD,INT,ROW)
將第(1)式和第(2)式代入 OFFSET 函數中,即可取得對應的儲存格內容。。

【延伸練習】
如何將矩陣資料內容轉換為一列?

沒有留言:

張貼留言

好康東東