2017年3月20日 星期一

Excel-週期性的顯示資料(OFFSET,MATCH,MOD,ROW)

在 Excel 中如果要將一些資料資單列入另一個清單中,做規則性的排列,如下圖和下下圖所示,該如何處理?
Excel-週期性的顯示資料(OFFSET,MATCH,MOD,ROW)
Excel-週期性的顯示資料(OFFSET,MATCH,MOD,ROW)
1. 手動操作
選取儲存格K2:P5,然後拖曳儲存格P5右下角的控制點,至儲存格P21的位置,即可自動週期性的填滿整個區域,其結果如同複製儲存格的功能。
Excel-週期性的顯示資料(OFFSET,MATCH,MOD,ROW)

2. 使用公式
儲存格D2:=OFFSET(K$2,MOD(ROW(1:1)-1,4),0)
複製儲存格D2,貼至儲存格D2:I21。
(1) ROW(1:1)
當公式向下複製時,會依序產生ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。
ROW 函數會傳回儲存格的列號。
(2) MOD(ROW(1:1)-1,4)
當公式向下複製時,會依序產生 0,1,2,3,0,1,2,3,…。
MOD 函數用以求取兩數相除的餘數。
(3) 在 OFFSET 函數中代入第(2)式,取得對應的儲存格內容。
Excel-週期性的顯示資料(OFFSET,MATCH,MOD,ROW)

3.利用下拉式清單選取各種組合
首先,使用資料驗證來製作下拉式清單:
Excel-週期性的顯示資料(OFFSET,MATCH,MOD,ROW)
接著輸入公式,儲存格D2:
=OFFSET(OFFSET($E$2,0,MATCH($D$1,$F$1:$K$1,0)),MOD(ROW(1:1)-1,4),0)
(1) MATCH($D$1,$F$1:$K$1,0)
利用 MATCH 函數取得儲存格D1在儲存格F1:K1中的位置(傳回一個數值)。
(2) OFFSET($E$2,0,第(1)式)
利用 OFFSET 函數取得第(1)式傳回的位置所對應的儲存格內容。
(3) OFFSET(第(2)式,MOD(ROW(1:1)-1,4),0)
參閱「2. 使用公式」的說明。
Excel-週期性的顯示資料(OFFSET,MATCH,MOD,ROW)

沒有留言:

張貼留言

好康東東