利用随机函数实现考试座位随机编排

Posted by Ray on October 29, 2019

昨天晚上从网上下了个Excel模板,学习了一下就能活学活用了。

使用原理

先用RAND()函数产生随机数,再用RANK函数给随机数排序,最后用VLOOKUP函数和数组公式将对应的文字输出。

RAND 函数

RAND()函数是Excel中产生随机数的一个随机函数。返回的随机数是大于等于 0 及小于 1 的均匀分布随机实数,RAND()函数每次计算工作表时都将返回一个新的随机实数。

语法:RAND()

参数:无

RANK 函数

RANK函数是排名函数。RANK函数最常用的是求某一个数值在某一区域内的排名。即返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值,如果列表已排过序,则数字的排位就是它当前的位置。

语法:RANK(number,ref,[order])

参数:

  • Number 必需。 要找到其排位的数字。

  • Ref 必需。 数字列表的数组,对数字列表的引用。 Ref 中的非数字值会被忽略。

  • Order 可选。 一个指定数字排位方式的数字。

如果 order 为 0(零)或省略,Microsoft Excel 对数字的排位是基于 ref 为按照降序排列的列表。

如果 order 不为零,Microsoft Excel 对数字的排位是基于 ref 为按照升序排列的列表。

VLOOKUP 函数

VLOOKUP函数是Excel中的一个纵向查找函数,它与LOOKUP函数和HLOOKUP函数属于一类函数,在工作中都有广泛应用,例如可以用来核对数据,多个表格之间快速导入数据等函数功能。功能是按列查找,最终返回该列所需查询序列所对应的值;与之对应的HLOOKUP是按行查找的。

语法:VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

参数:

  • lookup_value 要查找的值 (数值、引用或文本字符串)

  • table_array 要查找的区域 (数据表区域)

  • col_index_num 返回数据在查找区域的第几列数 (正整数)

  • range_lookup 模糊匹配/精确匹配 (TRUE(或不填)/FALSE)

数组公式

数组公式可以看成是有多重数值的公式。与单值公式的不同之处在于它可以产生一个以上的结果。一个数组公式可以占用一个或多个单元。

使用:输入数组公式首先必须选择用来存放结果的单元格区域(可以是一个单元格),在编辑栏输入公式,然后按Ctrl+Shift+Enter组合键锁定数组公式,Excel将在公式两边自动加上花括号“{}”。注意:不要自己键入花括号,否则,Excel认为输入的是一个正文标签。

列如公式:{=VLOOKUP({1;5;9;13;17}+{0,1,2,3},$A:$D,3,TRUE)}

解读:函数里面有大扩号,也代表的是数组数据。大括号中的是数组。 数组公式中可使用数组常量,但必须自己键入大括号“{}”将数组常量括起来,并且用“,”和“;”分离元素。其中“,”分离不同列的值,“;”分离不同行的值。

首先要理解VLOOKUP函数,第一个参数是需要查找的数据,它来自第二个参数的第1列,第二参数是范围,第三参数是返回的列,第四个参数是返回精确度。

这里第一个参数{1;5;9;13;17}+{0,1,2,3}得到一个1+0、1+1、1+2、1+3、5+0、5+1、5+2、5+3以此类推的新数组{1,2,3,4;5,6,7,8;9,10,11,12;13,14,15,16;17,18,19,20},是一个5行,4列的数据表,那这个公式输入时,就先选择5行4列的范围,再按数组输入,就可看到结果了。

参考文章

[1].RAND 函数 - Office 支持百度百科

[2].RANK 函数 - Office 支持百度百科

[3].VLOOKUP 函数 - Office 支持百度百科

[4].数组公式 - Office 支持百度百科

[5].VLOOKUP 数组运算 - 百度知道回答1百度知道回答2