3.9 考场座位安排表——随机安排考场座位号
案例背景
无论是中小学还是大学在组织考试时，布置考场都要安排单行独座（随堂测验除外），考试座位号S型排列，并且每个学生的考试座位号是随机抽取确定的，监考教师拿着随机安排考场座位安排表，提前进入考场后将“考场座位安排表”贴在教室门上以便学生查看，学生根据随机安排的座位号在制定位置参加考试。
考场座位安排表的考试座位号是如何随机抽取的呢？安排考试座位号有什么要求呢？首先要根据考场中准备实际安排的考生数抽取座位号，要求不重不漏，第二是由计算机随机抽取。本案例就以某个班级的30名学生为例，为每个学生随机安排考试座位号，通过学习本案例后您就可以自己制作考场座位安排表为您的学生随机安排考试座位号了。
关键技术点
要实现本案例中的功能，学员应该掌握以下EXCEL技术点。
●基础知识 选择性粘贴的“转置”功能
●函数应用 RAND，CEILING函数的应用 LARGE，ROUND ，COUNTIF，ROW函数的应用
●综合应用 函数嵌套数组公式 公式审核——公式求值
最终效果展示
3.9.1创建考试座位表
Step 1创建工作簿、重命名工作表
创建工作簿“考试座位表.xls“，然后将工作表重命名为”座位表“，并删除多余工作表。
Step 2输入“考试座位表”表格表格标题和字段名
在单元格A1输入表格标题“考试座位安排表”，选中单元格区域A1:C1，并单击“合并及居中”按钮。字体、字型、字号设置为黑体加粗16号，并适当调整列宽。
在单元格区域A2：C2分别输入“学籍号”，“姓名”和“座位号”。字体、字型、字号设置为标宋14号，居中显示。
Step 3输入学生学籍号和姓名
在单元格区域A3:B32输入本班30名学生的学籍号和姓名。
Step4随机确定座位号
①在单元格C3输入如下公式，然后按键确认。“=ROUND(RAND()*50,0) ”
②在单元格C4输入如下数组公式，按组合键确认。
“=LARGE((COUNTIF($C$2:C3,ROW($1:$30))=0)*ROW($1:$30),CEILING((30-COUNT($C$2:C3))*RAND(),1)) ”
③选中单元格C4，双击单元格C4右下角的填充柄即可完成其余学生座位号的确定工作。
Step5格式化表格
①选中单元格区域A2:C32，为表格设置边框。
②选中单元格区域A2:C32，设置文本居中显示
③选中单元格区域A2:C2，为表头设置底纹。
关键知识点讲解
1.RAND函数
函数名称：RAND
函数功能：返回大于等于 0 及小于 1 的均匀分布随机数，每次计算工作表时都将返回一个新的数值。
使用格式：RAND( )
函数说明：
●若要生成 a 与 b 之间的随机实数，请使用：
RAND()*(b-a)+a
●如果要使用函数 RAND 生成一随机数，并且使之不随单元格计算而改变，可以在编辑栏中输入“=RAND()”，保持编辑状态，然后按 F9，将公式永久性地改为随机数。
应用示例：
2.RANDBETWEEN函数
函数名称：RANDBETWEEN
函数功能：返回位于两个指定数之间的一个随机数。每次计算工作表时都将返回一个新的数值。
如果该函数不可用，并返回错误值 #NAME?，请安装并加载“分析工具库”加载宏。
操作方法
1.在“工具”菜单上，单击“加载宏”。
2.在“可用加载宏”列表中，选中“分析工具库”框，再单击“确定”。
3.如果必要，请遵循安装程序中的指示。
使用格式：RANDBETWEEN(bottom,top)
Bottom 函数 RANDBETWEEN 将返回的最小整数。
Top 函数 RANDBETWEEN 将返回的最大整数。
应用示例
3.CEILING函数
函数名称：CEILING
函数功能：将参数 Number 向上舍入（沿绝对值增大的方向）为最接近的 significance 的倍数。例如，如果您不愿意使用像“分”这样的零钱，而所要购买的商品价格为 $4.42，可以用公式 =CEILING(4.42,0.1) 将价格向上舍入为以“角”表示。
使用格式：CEILING(number,significance)
参数说明：
Number 要四舍五入的数值。
Significance 需要四舍五入的乘数。
函数说明：
●如果参数为非数值型，CEILING 返回错误值 #VALUE!。
●无论数字符号如何，都按远离 0 的方向向上舍入。如果数字已经为 Significance 的倍数，则不进行舍入。
●如果 Number 和 Significance 符号不同，CEILING 返回错误值 #NUM!。
应用示例：
4.TRUNC
函数名称：TRUNC
函数功能：将数字的小数部分截去，返回整数。
使用格式：TRUNC(number,num_digits)
参数说明：
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
函数说明：
函数 TRUNC 和函数 INT 类似，都返回整数。函数 TRUNC 直接去除数字的小数部分，而函数 INT 则是依照给定数的小数部分的值，将其四舍五入到最接近的整数。函数 INT 和函数 TRUNC 在处理负数时有所不同：
应用示例
5.LARGE函数
函数名称：LARGE
函数功能：返回数据集中第 k 个最大值。使用此函数可以根据相对标准来选择数值。例如，可以使用函数 LARGE 得到第一名、第二名或第三名的得分。
使用格式：LARGE(array,k)
参数说明：
Array 为需要从中选择第 k 个最大值的数组或数据区域。
K 为返回值在数组或数据单元格区域中的位置（从大到小排）。
函数说明：
●如果数组为空，函数 LARGE 返回错误值 #NUM!。
●如果 k ≤ 0 或 k 大于数据点的个数，函数 LARGE 返回错误值 #NUM!。
如果区域中数据点的个数为 n，则函数 LARGE(array,1) 返回最大值，函数 LARGE(array,n) 返回最小值。
应用示例：
3.9.2解读公式运算过程
由于上面公式是按30人设计的公式来随机抽取学生座位号，函数参数涉及含有30个元素的一维数组，致使运算过程显得过长，而不便于观察其运算过程，下面将学生数改为3，实际上运算过程一样的只是便于我们运用Excel提供的公式审核——公式求值的功能来观察该公式的主要运算步骤，从而理解该公式的设计思想。
在学习Excel过程中对于有的案例中复杂的公式设计不理解其运算过程，就可以应用Excel提供的公式审核——公式求值的功能来逐步观察公式运算过程来理解公式的设计思想。
Step 1修改公式
①选中单元格区域A1:C5，按组合键复制
②单击菜单“插入”→“工作表”，插入一张新的工作表“Sheet1”
③按组合键粘贴，选中单元格区域C3:C5，按键清除公式。
④在单元格C3输入公式“=ROUND(RAND()*3,0) ”，然后按键确认
⑤在单元格C4输入如下公式，然后按组合键确认。
“=LARGE((COUNTIF($C$2:C3,ROW($1:$3))=0)*ROW($1:$3),CEILING((3-COUNT($C$2:C3))*RAND(),1)) ”
⑥选中单元格C4，向下拖曳单元格C4右下角的填充柄至单元格C5完成公式填充。
Step2解读函数嵌套数组公式
①选中单元格C4，单击菜单“工具”→“公式审核”→“公式求值”。
②弹出“公式求值”对话框。
③在“公式求值”对话框中单击“求值”按钮。对话框显示公式的下一步运算过程。
④反复单击“求值”按钮直至公式运算过程最后一步得出运算结果。
至此单元格C4中数组公式的运算过程演示完毕，当您学习Excel过程中对于案例中比较负载的公式不理解时就可以应用“公式审核—公式求值”的功能来逐步演示公式的运算过程来解读公式。您不妨选中单元格C5，按照上面的做法操作一遍以加深理解。
|
