2011年11月20日 星期日

Excel-產生不重覆的隨機亂數

在 Excel 的工作表中,如果想產生一組不重覆的隨機亂數,例如:1~36,分別置於 6X6 儲存格中,該如何處理?(參考下圖)。

【準備工作】

1. 在儲存格H1中輸入公式「=RAND()」,按下 Enter 鍵後,即會產生一個亂數值。

2. 複製儲存格H1,貼至儲存格H1:H36,共計 36 個亂數。(每按一下 F9 鍵,即可重新產生一組亂數。)

 

【產生亂數】

提供二組公式來產生不重覆的 1~36 的亂數值:

(1) 儲存格A1:=RANK(INDIRECT("H"&((ROW(1:1)-1)*6)+COLUMN()),$H$1:$H$36):

複製儲存格A1,貼至儲存格A1:F6。

公式的原理為:

將亂數值來拿排序(RANK函數),產生 1~36 的數值(基不上名次重覆的機率應該是很低的)。

INDIRECT("H"&((ROW(1:1)-1)*6)+COLUMN()):將 6X6 的儲存格對照到儲存格H1:H26。

(2) 儲存格A1:=RANK(OFFSET($H$1,(ROW(1:1)-1)*6+COLUMN(A:A)-1,,,),$H$1:$H$36)

本式的原理和 (1) 很接近,只是將 INDIRECT 函數以 OFFSET 函數來取代,以取得位址。

 

【延伸練習】

試著產生 1~64 的不重覆亂數,置於 8X8 的儲存格中。假設亂數置於J欄中。

 

……

 

參考答案:

儲存格A1:=RANK(INDIRECT("J"&((ROW(1:1)-1)*8+COLUMN())),$J$1:$J$64)

儲存格A1:=RANK(OFFSET($J$1,(ROW(1:1)-1)*8+COLUMN(A:A)-1,,,),$J$1:$J$64)

沒有留言:

張貼留言

檢視其他文章

好康東東