2014年9月26日 星期五

Excel-週期性取出儲存格資料重新排列(INT,MOD,OFFSET)

有網友問到:在 Excel 的工作表中有一連續排列的資料,若要取出儲存格重新排列,例如,不要取出第1~18筆資料、要取出第19~64筆資料(有46筆)、不要取出65~82筆資料(18筆)、取出第83~128筆資料(46筆)、...,該如何處理?

為了方便理解,先簡化內容。參考下圖為例來說明,其中項次 1-4 不取用(4個)、5-10 要取用(6個)、11-14 不取用(4個)、15-20 要取用(6個)、...。

依題意,每10個為一個週期,前4個不顯示,後6個要顯示。

【解法一】藉助輔助欄位(D欄)

(1) 在儲存格D2輸入 5。

(2) 輸入公式:儲存格D3:=IF(MOD(D2,10)<4,D2+5,D2+1)

MOD(D2,10):使用 MOD 函數判斷儲存格D2除以10的餘數。

IF(MOD(D2,10)<4,D2+5,D2+1) :如果上式的餘數小於 4,則輸出儲存格D2+5,否則輸出儲存格D2+1。結果為:5,6,8,9,10,1516,17,18,19,20, ... 。

複製儲存格D3,往下各列貼上公式。

儲存格E2:=OFFSET($B$1,D2,,,)

透過 OFFSET 函數,利用儲存格D2來取用根據儲存格B1的相對位址。

複製儲存格E2,往下各列貼上公式。

 

【解法二】不需藉助輔助欄位(D欄)

儲存格E2:=OFFSET($B$1,MOD(ROW(1:1)-1,6)+INT((ROW(1:1)-1)/6)*10+5,,,)

MOD(ROW(1:1)-1,6):產生 0,1,2,3,4,5,0,1,2,3,4,5,0,1,2,3,4,5, ... 數列。

其中參數 6 為要顯示的個數。

INT((ROW(1:1)-1)/6)*10+5:產生 5,5,5,5,5,5,15,15,15,15,15,15,25,25,25,25,25,25, ... 數列。

其中參數 6 為要顯示的個數,參數 10 為一個週期的個數,參數 5 為一個週期的起始值。

複製儲存格E2,往下各列貼上公式。

 

【本題解答】

儲存格E2:=OFFSET($B$1,MOD(ROW(1:1)-1,46)+INT((ROW(1:1)-1)/46)*64+19,,,)

其中參數 46 為要顯示的個數,參數 64 為一個週期的個數,參數 19 為一個週期的起始值。

複製儲存格E2,往下各列貼上公式。

沒有留言:

張貼留言

好康東東