在 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)

arrow
arrow
    文章標籤
    EXCEL OFFSET MATCH MOD ROW
    全站熱搜

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