Excel在学生机位分配中的应用

张运明 周世林

摘 要 学生上机可能面临机少人多这种机位不足的问题。人工分配机位，比较麻烦和费时。借助Excel超强的计算能力，精心设置函数，可以打造出机位分配模板，大大提高分配效率，轻松实现机位分配。

关键词 Excel；函数；机位

中图分类号：TP317.3 文献标识码：B

文章编号：1671-489X（2016）04-0056-02

Application of Excel in Student Assignment//ZHANG Yunming， ZHOU Shilin

Abstract Students may face the problem that the number of computers is less than that of the students. It takes time to assign the computers by people. With the help of Excel， we can make the template assign the computers which can improve the efficiency of assignment.

Key words Excel； function； reservation

1 前言

诸多原因可能造成一些学校学生上机时人数多于计算机数这种机位不足的问题。即便有条件将学生分到两间教室上机，也将带来教学不便、管理缺位的新问题和安全风险。机少人多，需要教师公平分配上机机会，改进教学管理。如果人工分配，肯定能够做到公平分配。分配一个班容易，分配多个班就费时了；班级人数和机数都恒定不变容易，变化较大时也很费力。

如果借助Excel超强的计算能力，打造出机位分配模板，可大大提高分配效率。下面就在Excel 2013环境中介绍如何构建模板。

2 建立和设置表头

工作表取名“高1.1班”，A～H列为学生用表，J～P列为教师用表，R～S列为辅助列，表头样式如图1所示。

设置单元格格式 右击J2单元格，在弹出的快捷菜单中选择“设置单元格格式”，弹出“设置单元格格式”对话框；选择“数字”标签，从“分类”中选择“自定义”，在“类型”框中输入“G/通用格式"人"”（英文状态下双引号）；单击“确定”按钮，完成设置。这样，在J2单元格中输入“60”，会显示“60人”。

为K2单元格设置自定义单元格格式“G/通用格式"机"”，

输入“40”，会显示“40机”。为B3：H3和J3：P3区域自定义单元格格式“"第"G/通用格式"次"”，输入“1”，会显示“第1次”。

提取工作表名称 在B2单元格中输入公式“=MID（CELL

（“FileName”，A2），FIND（“]”，CELL（“FileName”，A2））+

1，255）”。公式会自动提取本工作表的名称“高1.1班”；如果本工作表的名称作了修改，B2单元格中显示的内容就会随之变化。式中，CELL函数提取文件路径和文件名，如文件放在桌面，文件名为“机位分配”，提取结果就为“"C：＼Users＼Administrator＼Desktop＼[机位分配.xlsx]高1.1班"”；FIND函数查找出现字符“]”的位置并加上“1”，这里计算结果为“43”，作为MID函数查找的起始位置；MID函数就从第43个字符的位置查起，查找长度为“255”个字符，最后截取结果为“高1.1班”。

引用标题 在B1单元格中输入公式“=J1”，直接从J1单元格中引用标题。

3 为辅助列设置公式

辅助列排定轮次，起过渡作用。

选择R2：R4000区域，输入数组公式“{=INDEX（$A$4：

$A$103，MOD（ROW（）-2，$J$2）+1）}”。花括号是数组公式的标志，输完公式后同时按下键盘上的Ctrl+Shift+Enter组合键会自动产生。式中，A4：A103是指A列的一个班，按100人设计。ROW函数产生当前单元格的行号。MOD函数以J2单元格的值“60”为除数计算余数，结果加上“1”，就产生1～60的序数，且不断循环。INDEX函数就从A列中以1～60的循环序数取得实际的学生名字，学生名字就按1～60的周期不断循环。

选择S2：S4000区域，输入数组公式“{=MOD（ROW（）-2，

$K$2）+1}”。MOD函数以K2单元格的值“40”为除数计算余数，结果加上“1”，就产生1～40的机号，且不断循环。

4 为师生用表设置公式

学生用表按照学生姓名来排列机号，方便学生查阅。

在B4单元格中输入公式“=IFERROR（VLOOKUP（$A4，OFFSET（$R$2，$K$2*（B$3-1），，$K$2，2），2，），）”。OFFSET函数

以A4单元格为起点，偏移的行数为“$K$2*（B$3-1）”，列数为0，以K2和B3单元格中数字的计算结果来偏移，会形成一个个动态的起点；然后以K2单元格的机数为偏移的高度，宽度为2列。这样就形成每40人为一组的人、机一一对应的数组。VLOOKUP函数就根据A4单元格的名字在这些数组的第2列查找机号，如果查找不到，IFERROR函数就将错误值显示为0。

利用填充柄将B4单元格的公式复制到B103单元格，再将B4：B103区域的公式复制到H103单元格。

教师用表按照机号来排列学生姓名，方便教师查阅。

为J4：J63区域输入数组公式“{=IF（ROW（）-3>$K$2，，ROW

（INDIRECT（"1："&K2）））}”。INDIRECT函数产生引用“″1：40″”，

ROW函数产生行号“{1；2；3；4；5；6；7；8；9；10；11；12；13；14；15；16；17；18；19；20；21；22；23；24；25；26；27；28；29；30；31；32；33；34；35；36；37；38；39；40}”，实际为40台计算机的机号。IF函数用于屏蔽错误值。

为K4：K63区域输入数组公式“{=IFERROR（IF（ROW（）-3>$K$2，，INDEX（$A：$A，MATCH（$J$4：$J$103，B$4：B$103，）+3）

），）}”。MATCH函数根据J列的机号在B列的机号中精确查找位置，结果加上“3”（指表头的3行），再利用INDEX函数返回A的相应位置，得到学生姓名。

将K4：K63区域的公式复制到P63单元格。

公式输入完毕，效果如图2所示。

5 结语

使用时，在学生用表中录入或粘贴学生姓名，在教师用表中设置计算机数量、学期，修改工作表名称，其他数据全部自动生成。教师可以对学生姓名按一定顺序自主排序，显得机位分配更为合理公平。当然，也可以进一步用随机函数产生随机顺序，实现完全的机会公平。机器故障可能导致学生上机机会不公平，可预留一定数量机器暂时不分配。

插入列，再复制公式，可增加上机次数。复制工作表，修改工作表名称，可排定多个班级。学生用表还可以用作点名册，方便教师加强课堂管理。