隨機數是一個事先不確定的數,在隨機安排順序、隨機抽獎或是生成隨機測試數據時,都需要使用隨機數進行處理。RAND 函數和 RANDBETWEEN 函數都能夠生成隨機數。

示例 從題目庫中隨機抽取題目

圖 5-75 展示了某學校教學題庫的部分內容,需要從“題庫”工作表中隨機抽取部分題目,對學生進行考核評測。

圖5-75隨機抽取題目

步 驟 1在“題庫”工作表的 C2 單元格中輸入以下公式生成一組隨機數,將公式向下復制填充到數據表最后一行。=RAND()

步 驟 2在“題庫”工作表的 D2 單元格中輸入以下公式得到隨機數的排名,將公式向下復制填充到數據表最后一行。=RANK(C2,$C$2:$C$34)此時的效果如圖 5-76 所示。

圖5-76隨機數及其排名效果

步 驟 3切換到“隨機題目”工作表,在 B2 單元格輸入以下公式,將公式向下復制填充到B6 單元格。

=INDEX( 題庫 !B:B,MATCH(ROW(A1), 題庫 !D:D,0))

圖5-77隨機提取題目

步 驟 4在【公式】選項卡下,單擊【計算選項】下拉按鈕,在下拉菜單中選擇【手動】命令,如圖 5-78 所示。設置完成后,每按一次< F9 >鍵,即可得到不同的隨機題目。

圖5-78設置手動重算

在“題庫”工作表中,使用了 RAND 和 RANK 兩個函數。RAND 函數不需要參數,可以隨機生成一個大于等于 0 且小于 1 的小數,而且產生的隨機小數幾乎不會重復。

RANK 函數的作用是返回數字在列表中的排名,函數語法如下。

RANK(number,ref,[order])

第一參數是要進行排名的數字。

第二參數是對數字列表的引用,其中的非數字值會被忽略。

第三參數可選,以數字來指定數字排位的方式。如果該參數為 0(零)或省略,表示將列表中的最大數值排名為 1。如果該參數不為零,則將列表中的最小數值排名為 1。

使用 RANK 函數排名時,如果出現相同數據,并列的數據也占用名次,比如對 5、5、4進行降序排名,結果分別為 1、1 和 3。

本例中,先使用 RAND 函數在每一道題目后生成一個隨機數,然后使用 RANK 函數計算該隨機數在列表中所有隨機數的排名結果,相當于給每道題目都添加了一個隨機變化的序號。

“隨機題目”工作表 B2 單元格使用的公式如下。=INDEX( 題庫 !B:B,MATCH(ROW(A1), 題庫 !D:D,0))

公式中使用了 INDEX 函數、MATCH 函數及 ROW 函數的組合。ROW 函數的作用是返回參數的行號,函數語法如下。

ROW([reference])

ROW 函數的參數可選,用于指定要得到行號的單元格或單元格區域。如果省略參數,將返回公式所在單元格的行號。

本例“隨機題目”工作表中 B2 單元格的公式中, ROW(A1) 的作用是得到 A1 的行號 1,當公式向下復制時,參數會依次變成 A2,A3,A4…最終得到從 1 開始的連續遞增序號。

再以 ROW 函數得到的序號作為 MATCH 函數的查詢值,在“題庫”工作表 D 列中查找出該序號的位置,最后使用 INDEX 函數返回“題庫”工作表對應位置的信息。

按< F9 >鍵的目的是刷新工作表,工作表每次刷新,RAND 函數結果都會自動變化,“題庫”工作表中 D 列的排名結果也會隨之變化。MATCH 函數在隨機變化的排名結果中查詢序號的位置,再把序號的位置信息用作 INDEX 函數的參數,從而實現隨機抽取題目的效果。

使用 RANDBETWEEN 函數能夠生成指定范圍的隨機整數。

示例 5-52 制作數學加減計算題

圖5-79展示的是一份使用 RANDBETWEEN函數制作的數學加減計算題,每按 一次< F9 >鍵,即可得到不同的隨機數據。

圖5-79隨機生成數學加減題

步 驟 1A1 單元格輸入以下公式,向下復制填 充到 A9 單元格。=RANDBETWEEN(IF(B1="-",C1,1),10)

步 驟 2 B1 單元格輸入以下公式,向下復制填 充到 B9 單元格。=MID(" -",RANDBETWEEN(1,2),1)

步 驟 3 C1 單元格輸入以下公式,向下復制填 充到 C9 單元格。=RANDBETWEEN(1,10)

步 驟 4D1 單元格輸入等號“=”,向下復制填充到 D9 單元格。

步 驟 5在【公式】選項卡下依次單擊【計算選項】→【手動】命令按鈕。設置為手動計算后,可以按< F9 >鍵使公式重新計算。

RANDBETWEEN 函數的語法結構如下。

RANDBETWEEN(bottom,top)兩個參數分別為下限和上限,用于指定產生隨機整數的范圍,最終生成一個大于等于下限值且小于等于上限值的整數。

以 B1 單元格公式為例,先使用 RANDBETWEEN 函數產生 1~2 的隨機數,結果作為MID 函數的第二參數。MID 函數在字符串” -”中,從隨機位置開始提取出一個字符,結果用作算式中的運算符號。

在 A1 單元格公式中,RANDBETWEEN 函數的第一參數使用 IF(B1="-",C1,1),如果 B1單元格的運算符號為減號“-”,生成隨機數的下限值使用 C1 單元格的數值,否則使用 1。該部分的作用是當B1運算符為減號時,能夠使A1單元格的被減數不會小于C1單元格的減數。

C1 單元格公式的作用是生成 1~10 的隨機數。

注意:使用 RAND 函數和 RANDBETWEEN 函數生成的隨機數,指的是在指定范圍內的任意數字。如果使用多個公式批量生成隨機數,有可能會得到重復的數字。

END

推薦閱讀
北京大學出版社
Excel數據處理與分析應用大全