李莉

【摘 要】Excel是电脑普及以来应用最广泛的办公软件之一，在很多公司中，Excel在各个部门的核心工作中发挥着重要的作用。文章以某集团技能大赛考试的考场编排为例，阐述利用Excel函数实现快速编排考场。

【关键词】Excel;多工种考试;编排考场

【中图分类号】TP391.3 【文献标识码】A 【文章编号】1674-0688（2019）07-0157-03

某集团公司是一家国有大型煤矿企业，该公司重视人才培养，利用多种方式培养选拔人才，尤其是“以考促学、以赛促学”的方式，在建设学习型企业中发挥了重要的作用。从2006年开始，该公司每年举办职工技能大赛，有上百个工种，几千名职工同时进行考试，形成“百个工种大比武，万名员工争状元”的比、学、赶、超氛围。大赛分理论考试和实践操作，在理论考试中，为了防止考生作弊，要求在编排考场时，相邻的座位按不同工种间隔排开。在大赛开始前，编排考场就成为一项非常艰巨的任务，往往是几个老师逐个考场地编排，还时常发生疏漏。在大赛进行3年后，笔者有幸参加了考场的编排工作，利用Excel的函数功能，快速完成了考场编排任务。不仅如此，还实现了在每个考场安排表中标注考场工种编号、考试人数等功能，极大地方便了考场封装试卷。下面笔者就某年该公司有2 000多名员工参加的技能大赛为例，对Excel函数在编排考场中的应用做阐述。

1 基础数据准备

（1）考场情况：需对考场数、每个考场的容纳量进行核查，以便按考场的容纳量安排考生，本例中每个考场容纳量为35人。

（2）考生数据：包括考生姓名、单位、考号。考号编排：前3位为工种编号（如以“0”开头，请将单元格设置为文本型后再录入），后2位为工种排队顺序号。

（3）根据考生数据，编排考场人员。操作步骤：①将考生数据中的考生考号复制到一个辅助工作表中（工作表名称为“考场编排”，可自行定义），此表仅作为编排考场用，不需打印。②利用文本函数LEFT，将编号中的工种编号提取出来，以便后面进行排序。B列中的公式：=LEFT（A2，3），提取A列前3位文本，即提取出了工种编号。③利用文本函数RIGHT，将编号中的工种序号提取出来。C列中的公式：=RIGHT（A2，2）*1，提取A列后2位文本，即提取出了工种序号。提取出序号后，因为是文本型数值，为了以后应用方便，在公式后“*1”，将文本型数值转换为数值型。④安排考场。一般编排的规则是，每个考场安排2～4个工种。在本例中，35人的考场，每个工种安排不能超过18人，如超过，则会出现同工种考生座位相邻的情况。如遇工种人数较少，或剩余人数较少的情况，可多工种凑数，形成“18+17”人数的形式，人数多的工种排在最前或最后，以保证打乱工种时，人数多的工种不会相邻。根据排序序号，可很直观地看出每个工种的人数，在本例中，序号为001的工种一共28人，序号为002的工种29人，在001工种中选择“考场”列的前17（或18）个，统一录入1，即将1～17号安排到1考场。剩下的18～28序号的输入“2”，将这些考生安排到2考场。序号为002的工种，1～18号的“考场”列中输入“1”，将2号工种的前18人安排到1考场，剩余19～29号安排至2考场。此时，1考场有001工种17人，002工种18人，本考场安排完毕。2考场有001工种11人，002工种11人，还可容纳13人，此时可从后面的工种中选择13人安排至2考场，依次累推。如遇某工种安排到第2个考场后，还有超过18人未安排，超过的人数则需往后安排到第3考场……按以上规则将“考场”列全部录入考场号。⑤按考场进行排序。以“考场”列为第1关键字，“排序”列为第2关键字进行排序。排序后可发现，所有编号均按考场排列好。因此，工种号为001和002的数据按原有的排序号进行了排列，实现了工种的交错。

（4）考场安排统计表：考场编排完毕后，依照制作“考场安排统计表”（工作表名称为“考场安排统计表”），通过此表，将每个考场的人数安排、工种安排、每个工种安排的人數及考号范围都一一录入，以便今后封装试卷和进行人数的核验、统计。

（5）工种统计表：此表中录入的是工种对应的编号、总人数、每个考场安排人数等。主要有两个用途：一为工种编号对应表，通过工种与编号的对应，在考场安排表中快速输入工种;二是人数核对，每个工种安排到哪个考场考试，所有人员是否全部安排（如图1所示）。

此表A-I列需在“考场安排统计表”安排好后进行录入，“人数核对”列中公式为“=E4+G4+I4”，与参赛人数进行对比，如不一致，则需检查是否有考生未安排至考场。

（6）最终实现的考场安排表如图2所示。

2 制作考场安排表

（1）建立新的工作表（如图3所示），名称为“1”（1考场）。根据考场情况，先录入空的考场安排表：此考场为7行5列模式，每个考场可容纳35人。为了更加直观和美观，在表中每个座位旁边均有留空。

（2）将“考场编排”工作表中排好的第1考场人员编号复制到M列中，并在L列加入序号，以便核对人数（辅助单元格）。

（3）在相应的单元格中依次录入公式，即可将编号填入“考场安排表”中（可按实际需要调整编号安排顺序）。“编号”列中内容修改，“考场安排表”中的数据也可相应更改。

（4）在“考场安排表”中，根据录入的编号，查询对应的工种，并显示出来。①工种、编号对应的数据区域定义名称，以便在查询的公式中使用。可利用现有的“工种统计表”中数据定义。选中数据区域，在名称框中输入“gz”，按回车键，即可定义名称为“gz”的数据区域。②根据编号查询对应的工种名称。在B5单元格中，输入“=IF（ISERROR（VLOOKUP（MID（B6，1，3）*1，gz，2，FALSE）），""，VLOOKUP（MID（B6，1，3）*1，gz，2，FALSE））”。③将上述公式复制到需要显示工种内容的所有单元格中（注意：此处仅需复制，不需逐个输入），Excel会根据不同单元格，自动引用相应的单元格。至此，考场安排表基本信息录入完毕。为了美观，可将辅助单元格中的内容字体改为白色。

3 在每个考场显示工种、起止编号、人数

在多工種的考试中，为了更好地让监考人员核实考场中各工种的人数及试卷，在考场安排表中显示每个工种的人数无疑是很重要的。我们可以看到，在最终实现的“考场安排表”中从D26单元格开始，到J27单元格是显示考场的工种、编号和人数的，这些内容可以随着考场号的变化自动更新。步骤如下。

（1）B2单元格中内容为考场号，因此我们以B2单元格确定当前的考场。为了更方便地在公式中查询到考场号，B2单元格中需要一个数值而不是字符串。如“第1考场”，在B2单元格中输入“1”，设置单元格格式—自定义数字格式— "第"#"考场"，在B2单元格中直接输入数值，即可自动显示“第*考场”。

（2）B26-B29单元格（辅助单元格）中输入以下内容：=MATCH（$B$2，考场安排统计表！A：A，0）;=MATCH（$B$2，考场安排统计表！A：A，0）+1;=MATCH（$B$2，考场安排统计表！A：A，0）+2;=MATCH（$B$2，考场安排统计表！A：A，0）+3。使用MATCH函数查找B2单元格中的内容（考场号），在“考场安排统计表”A列中对应数字的行号。因最多一个考场只有4个工种，B27-B29中依次+1，即可将本考场中所有工种都查找到对应的行号，本例返回的结果依次是2、3、4、5。

（3）A26-A29单元格（辅助单元格）输入下列公式：="考场安排统计表！A"&B26&"：$I$185"。这个公式结果是一个字符串，旨在将B26单元格查找到的行号与“考场安排统计表！A”文本串及后面的"：$I$185"连接起来。本例中，B26查询到“1”考场第1个工种在第2行，与上述字符串连接的结果如下：“考场安排统计表！A2：$I$185”，这是“考场安排统计表”中A2单元格到I185单元格的一个区域，这个字符串将在后面的公式中用到。依次向下填充3行，实现相对应内容的查询。

（4）D26-D29单元格输入下列公式：=IF（ISERROR（VLOOKUP（$B$2，INDIRECT（A26），3，0）），""，VLOOKUP（$B$2，INDIRECT（A26），3，0））。

此公式用来返回B2单元格中的内容（即考场号），在A26单元格的字符串引用区域中第3列的内容，即返回了当前考场的工种编号，在这里同样可以用“自定义数字格式”来显示“*号工种”，用法同前。IF函数和ISERROR函数用来判断VLOOKUP函数是否返回了数值，如返回的是错误值，即显示为空。公式中用到了INDIRECT函数，用来返回由文本字符串指定的引用。依次向下填充3行，实现相对应内容的查询。

（5）F26-F29单元格输入下列公式：=IF（ISERROR（VLOOKUP（D26，gz，2）&"："），""，VLOOKUP（D26，gz，2）&"："），此公式用来返回D26单元格的内容在之前定义的“gz”数据区域（“工种统计表”中的数据）中第2列的内容，即工种号对应的工种名称。依次向下填充3行，实现相对应内容的查询。

（6）G26-G29单元格输入下列公式：=IF（ISERROR（VLOOKUP（$B$2，INDIRECT（A26），6，0）），""，VLOOKUP（$B$2，INDIRECT（A26），6，0））&IF（ISERROR（VLOOKUP（$B$2，INDIRECT（A26），7，0）），""，VLOOKUP（$B$2，INDIRECT（A26），7，0））&IF（ISERROR（VLOOKUP（$B$2，INDIRECT（A26），8，0）），""，VLOOKUP（$B$2，INDIRECT（A26），8，0））。

此公式用来返回B2单元格中的内容，在A26单元格的字符串引用区域中第6～8列的内容，即返回了当前考场工种的起止编号。依次向下填充3行，实现相对应内容的查询。

（7）J26-J29单元格输入下列公式：=IF（ISERROR（VLOOKUP（$B$2，INDIRECT（A26），5，0）），""，VLOOKUP（$B$2，INDIRECT（A26），5，0））。

此公式用来返回B2单元格中的内容，在A26单元格的字符串引用区域中第5列的内容，即返回了当前考场工种的人数。依次向下填充3行，实现相对应内容的查询。

4 总结

Excel在多工种、多单位需打乱次序的考试中得到普遍的应用，从2009年至今，笔者单位的技能大赛一直在运用Excel工作簿进行考场的编排工作。因笔者水平有限，所以仅用Excel函数实现了上述功能，在实际运用中，需要具备一定Excel基础的人才能熟练掌握。今后，笔者将进一步探索利用编程语言+数据库完成此工作，在数据的严谨性、程序的易用性方面会更趋完善。

参 考 文 献

[1]Microsoft.Excel帮助文件[EB/OL].https：//support.office.com/zh-cn/excel，2003-05-03.

[2]Excel Home.Excel 2013函数与公式应用大全[M].北京：北京大学出版社，2016.

[3]Excel Home.Excel 2013实战技巧精粹[M].北京：人民邮电出版社，2015.

[责任编辑：陈泽琦]