基于Excel多功能统计表的设计与实现.docx

?

?

基于Excel多功能统计表的设计与实现

?

?

李艳徐运武

摘要：为了解决手工录入成绩费时又易错位的问题，通过使用VLOOKUP函数将模考成绩快速搜索并写入汇总统计表功能，然后利用lNDEX函数、MATCH函数和MAX三个函数结合实时查找最高分人的姓名，并使用COUNTIF函数实时统计及格人数和及格率等，经实验测试实现了快速搜索并写入数据，实时查找和统计功能，最后对模拟实验后成绩提取和生成进行了探讨。

关键词：函数;快速;搜索;写入;实时;统计

：TP399：A

：1009-3044（2019）34-0204-02

随着计算机和网络技术的快速发展，高等院校的学生不仅要有过硬的专业技能，还要有解决实际问题的能力。通过对本校往届毕业生回访，发现《计算机应用基础》是学生工作中最急需、最实用的一门课程，因而广东松山职业技术学院把这门课程作为非计算机专业一级统考课程，并作为锻炼学生的实际操作技能和创新开发能力重要课程之一。该校学生在往年的广东省一级统考中一直名列前茅，最关键是因为该校教师注重培养学生的上机实践操作能力，并将模拟实验课作为重要突破环节。为了激励学生不断进步取得更好的成绩，该课程任课教师会制作一个汇总统计表来统计学生每次模拟实验成绩，几乎都是手动将成绩复制粘贴到统计表，这样即费时又会出现成绩错位情况，为了解决以上问题使用VLOOKUP函数快速搜索外来多个工作簿中的数据并写入统计表，使用INDEX函数、MATCH函数和MAX三个函数结合实时查找每次模拟成绩中最高分人的姓名，使用COUNTIF函数统计及格人数，将COUNTIF与COUNTA结合统计及格率。

1设计思路

此统计表主要实现快速搜索并写入每次模拟成绩，统计各小班的及格人数、及格率、安全线上人数、最高分及最高分人姓名等，从而实现快速将所有的模拟实验成绩综合在一个工作表中，实时反映学生的学习情况。

2搜索并写入所有模拟成绩后统计表的局部效果

3功能分析与实现

3.1功能分析

主要在汇总统计表中完成以下几个功能：

①快速搜索交卷后生成的成績表中的准考证号并将对应的成绩写入汇总统计表中;②实时统计各小班及格人数、大于等于75分人数;③实时统计各小班及格率，合班及格率;④实时统计每次模拟最高分、查找最高分人的姓名;⑤使用丰富多彩的颜色和箭头直观显示不同分数段值。

3.2关键技术与功能实现

3.2.1使用VLOOKUP函数快速搜索每次模考成绩并写入汇总统计表

函数格式简写：=VLOOKUP（查找值，查找区域，返回值在查找区域中的列序号（第几列），TRUE或FALSE）[1]快速搜索并写入模拟l成绩的实现方法：第一步：在汇总统计表CIO单元格中输入公式：=VLOOKUP（$BIO，[scoreOl.xls]Sheetl，！$A$2：$B$85，2，FALSE）并按Enter键;第二步：将公式复制到C11：C85。

公式分析：公式中的$B1I为查找值即准考证号，因为要查找所有人的准考证号即查找值是变量所以使用单元地址，由于要进行公式复制并且准考证号放在汇总统计表B列，所以此处用混合地址;[scoreOl.xls]Sheetl！$A$2$B$85表示在Excel文件scoreOl.xls的Sheetl工作表的$A$2：$B$85区域中查找，其中$A$2：$B$85区域A列数据必须是准考证号，B列必须是成绩，这样才能保证搜索结果的正确性;找到此准考证号后返回此行第2列的值即成绩，FALSE表示精确查找。特别强调：该公式中查找区域必须以查找值为首列;如要进行公式复制，查找值用混合地址，查找区域要用绝对地址。

将CIO中的公式横向复制到D10中，然后将D10公式中的scoreOl.xls改为对应的存放模拟2成绩的Excel文件名，再将修改后的公式复制到D11：D85即可搜索并写入模拟2成绩，以此类推即可完成其他模拟成绩的搜索和写入。

3.2.2及格人数、及格率等统计实现

实时统计模拟1成绩相应及格人数、及格率等实现方法：

在C1中输入公式：=COUNTIF（CIO：C45，”=60”）并按Enter键，统计l班及格人数;在C2中输入公式：=COUNTIF（CIO：C45，”=60”）/COUNTA（$A$10：$A$45）并按Enter键，统计1班及格率，其中$A$10：$A$45为1班所有人的姓名;在C3中输入公式：=COUNTIF（C46：C85.”=60”）并按Enter键，统计2班及格人数;在C4中输入公式：=COUNTIF（C46：C85.”=60”）/COUNTA（$A$46：$A$85）并按Enter键，统计2班及格率;在C5中输入公式：=COUNTIF（CIO：C85，”=60”）/COUNTA（$A$10：$A$85）并按Ent