2016年2月10日 星期三

Excel-分組亂數排序(INT,MATCH,OFFSET)

網友問到一個 Excel 的問題:在以下的資料清單中,如果想要執行分組亂數重排,該如何設計公式?
以下圖為例,若每 20 個一組,整個資料清單有若干列(幾列都有可能),如何能在每一分組中,各自亂數重排。每按一次 F9 鍵,即可得到一個新的重排結果。
Excel-分組亂數排序(INT,MATCH,OFFSET)

【公式設計與說明】
在工作表中我們需要一個輔助欄位,其中置入亂數,網友是提供了:=INT(RAND()*100)+ROW()/1000,其實如果需要亂數,只要使用=RAND()即可。
Excel-分組亂數排序(INT,MATCH,OFFSET)
接著,我們要定義一個名稱:區間,
其參照設定為:=INT((ROW(工作表1!1:1)-1)/20)*20,其中的參數『20』,即為一組中的個數。
Excel-分組亂數排序(INT,MATCH,OFFSET)
輸入公式:
儲存格F2:=OFFSET($C$2,MATCH(LARGE(OFFSET($B$2,區間,,20,),ROW(1:1)-
區間),OFFSET($B$2,區間,,20,),0)-1+區間,)
(1) OFFSET($B$2,區間,,20,):找出區間的儲存格範圍。
(2) LARGE(OFFSET($B$2,區間,,20,),ROW(1:1)-區間):找出區間中的第幾個最大值。
MATCH(第(2)式,第(1)式,0):找出區間中的第幾個最大值位於區間的第幾個位置。
最後,透過 OFFSET 函數查出該位置的儲存格內容。
儲存格G2:=OFFSET($D$2,MATCH(LARGE(OFFSET($B$2,區間,,20,),ROW(1:1)-
區間),OFFSET($B$2,區間,,20,),0)-1+區間,)
其中的參數『20』,均為一組中的個數。
複製儲存格F2:G2,往下各列貼上。該公式乃不管清單中有多少筆資料,皆可使用。

【補充說明】
儲存格F2的原始公式:
=OFFSET($C$2,MATCH(LARGE(OFFSET($B$2,INT((ROW(1:1)-1)/20)*20,,20,),
ROW(1:1)-INT((ROW(1:1)-1)/20)*20),OFFSET($B$2,INT((ROW(1:1)-1)/20)*20,,
20,),0)-1+INT((ROW(1:1)-1)/20)*20,)
如果改變一組的數目,只要將參數『20』,改為分組的個數。

沒有留言:

張貼留言

檢視其他文章

好康東東