隨機數是一個事先不確定的數,在隨機安排順序、隨機抽獎或是生成隨機測試資料時,都需要使用隨機數進行處理。
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隨機生成數學加減題
步 驟 1
A1 單元格輸入以下公式,向下複製填 充到 A9 單元格。=RANDBETWEEN(IF(B1=“-”,C1,1),10)
步 驟 2
B1 單元格輸入以下公式,向下複製填 充到 B9 單元格。=MID(“+-”,RANDBETWEEN(1,2),1)
步 驟 3
C1 單元格輸入以下公式,向下複製填 充到 C9 單元格。=RANDBETWEEN(1,10)
步 驟 4
D1 單元格輸入等號“=”,向下複製填充到 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資料處理與分析應用大全