EXCEL在煤矿安全理论考试成绩统计中的应用.docVIP

EXCEL在煤矿安全理论考试成绩统计中的应用 　　摘 要：Microsoft Excel是微软公司的办公软件Microsoft office的组件之一，它可以进行各种数据的处理、统计分析和辅助决策操作。针对当前大量考试成绩的统计分析工作日益繁复，利用该软件的函数、模板和统计等功能处理成绩数据，对提高工作的效率和准确性有着十分重要的现实意义。文章以山东唐口煤业有限公司为例，阐述如何运用Excel软件对员工考试成绩进行汇总统计，并形成分析模版。 　　关键词：Excel；成绩；统计分析；函数 　　引言 　　Excel是微软办公软件Microsoft Office的组件之一，它是一款功能强大的电子表格处理软件，广泛应用于管理、统计、财经、金融等众多行业领域。利用它可以制作表格、分析处理数据、创建图表等。[1]近年来，随着煤矿企业逐渐由劳动力密集型向技术密集型转变，企业对高技能人才的需求越来越迫切。为了督促职工提升自身技能知识水平，企业对职工技能知识的各种考核也日渐增多，随之而来的，对大量的各类成绩的统计分析工作变得更加繁琐复杂。目前，山东唐口煤业有限公司共有21个部门（单位）参加岗位技能知识考试，实际参加考试共计约2300人左右。每月月末，安培中心都要讲上述人员的成绩按照部门、工种和成绩分布分别统计分析报送至相关部门。下面就如何运用Excel软件对员工考试成绩进行汇总统计作具体阐述。 　　1 准备工作 　　1.1 原成绩表导出和录入 　　目前，我矿职工考试使用的是在线考试平台，考试结束后，教师可以根据需要将成绩以Excel表格的形式导出，便于后续的成绩汇总统计，该平台的成绩表导出后有以下字段：序号、编号、身份证号、姓名、班级、工种、成绩、工区。教师可以直接利用该成绩表的源数据进行统计和分析，省去了过往将笔答试卷成绩一一录入的繁琐，有效的提高了成绩统计效率，缩短了考核周期。 　　1.2 设计成绩汇总表 　　新建一个工作簿，更改sheet1的表名为“成绩汇总表”，在第一行逐列输入“单位名称”、“考试人数”、“成绩分布”、“平均分”、“平均分与上次比较”、“违纪”、“名次”，然后在第二行成绩分布下方插入列，并逐列输入“100分”、“90分以上”、“80-90”、“80分以下”接下来合并除成绩分布列之外的第一二行，效果如图1所示。 　　1.3 设计成绩明细表 　　在上述成绩汇总表的同一工作簿中，更改sheet2的表名为“成绩明细表”，在第一行逐列输入“序号”、“单位”、“姓名”、“成绩”、“身份证号”、“工种”、“备注”，然后将由在线考试平台中导出的原成绩表根据表头信息逐列粘贴到“成绩明细表”中。 　　2 汇总表函数设计 　　汇总表的成绩统计主要包括：（1）统计相应单位的各分数段人数；（2）统计各分数段人数占总人数的比例；（3）统计各单位平均分和排名情况。由于“成绩汇总表”工作表中的数据都是通过“成绩明细表”工作表中的数据统计出来的，不需要做任何修改。为了防止使用者无意中修改其中的数据，必须将“成绩汇总表”工作表中的所有单元格保护起来，不允许使用者修改。 　　2.1 成绩分布函数设计 　　由于各单位的成绩分布需要分别统计，所以用到了SUMPRODUCT函数[2]，具体公式设计如下： 　　100分：SUMPRODUCT（（成绩明细表！$B：$B=成绩汇总表！A3）*（成绩明细表！$D：$D=100）） 　　90～99分：SUMPRODUCT（（成绩明细表！$B：$B=成绩汇总表！A3）*（成绩明细表！$D：$D=90））-C3-I3 　　80～89分：SUMPRODUCT（（成绩明细表！$B：$B=成绩汇总表！A3）*（成绩明细表！$D：$D=80））-D3-C3-I3 　　80分以下：SUMPRODUCT（（成绩明细表！$B：$B=成绩汇总表！A3）*（成绩明细表！$D：$D80）） 　　上述公式中的A3，为当前所计算成绩分布对应的“单位名称”。将第一行编辑完成后，拖动填充柄至最后一个单位所在行。计算完各分数段人员占总人数比例后，可通过插入图表的方式，直接绘制出其分布的饼状图，使成绩的分布更直观地显示出来。 　　2.2 平均分函数设计 　　平均分的计算则是将“成绩明细表”中相应单位的全体成绩取平均值，所用到的函数是AVERAGEIF函数，具体公式为：AVERAGEIF（成绩明细表！$B：$B，A3，成绩明细表！$D：$D），上述公式中的A3，为当前所计算平均分所对应的“单位名称”。将第一行编辑完成后，拖动填充柄至最后一个单位所在行。 　　2.3 违纪人数统计的函数设计 　　目前，有两种情况被定义为考试违纪：一是正常出勤，无故不参加考试的；二是在考试中出现替考等违纪行为的。在成绩统计时，上