學不完．教不停．用不盡

網友問到一個 Excel 的問題：在以下的資料清單中，如果想要執行分組亂數重排，該如何設計公式？

以下圖為例，若每 20 個一組，整個資料清單有若干列(幾列都有可能)，如何能在每一分組中，各自亂數重排。每按一次 F9 鍵，即可得到一個新的重排結果。

 

【公式設計與說明】

在工作表中我們需要一個輔助欄位，其中置入亂數，網友是提供了：=INT(RAND()*100)+ROW()/1000，其實如果需要亂數，只要使用=RAND()即可。

接著，我們要定義一個名稱：區間，

其參照設定為：=INT((ROW(工作表1!1:1)-1)/20)*20，其中的參數『20』，即為一組中的個數。

輸入公式：

儲存格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』，改為每一組中的個數。