基于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）并按Enter键，统计合班及格率;在C6中输入公式：=COUNTIF（CIO：C85，”>=75”）并按Enter键，统计高于或等于75分人数;在C7中输入公式：=MAX（C10：C85）并按Enter键，求最高分。
　　将这几个公式进行横向复制到相应的单元格就可对相应模拟成绩进行相关统计。
　　3.2.3 实现查找最高分人的姓名
　　将INDEX函数、MATCH函数和MAX三个函数结合应用可实现查找最高分人的姓名。MATCH函数功能是返回在指定方式下与指定数值匹配的数组中元素的相对位置。INDEX函数功能是返回列表或数组中的元素值，此元素由行序号和列序号的索引值进行确定。
　　INDEX函数格式简写：=INDEX（单元格区域或数组常量，行序号，列序号）。 　　在C8中输入公式：=INDEX（$A$10：$A$85，MATCH（MAX（CIO：C85），CIO：C85，0》后按回车键即可实现查找模拟1成绩最高分人的姓名。
　　公式分析：公式中的MAX（CIO：C85）用来统计区域CIO：C85中的最大值;MATCH函数用于在区域CIO：C85中查找MAX函数的结果，它的返回值是最高分在区域CIO：C85中的相对位置：71，然后再使用该返回值作为INDEX函数的行序号参数。因此本例中的INDEX函数就成了如下的形式：=INDEX（$A$lO：$A$85，71），此公式返回区域AIO：A85中的第71行中的值。特别强调：此处的行序号参数是相对于所引用的单元格区域而言，并不是Excel工作表中的行序号。
　　将C8中的公式横向复制到D8：L8区域中即可查找其他模拟成绩中最高分人的姓名。
　　也可使用VLOOKUP函数来实现查找最高分人的姓名，但使用该函数必须在数据清单右边再复制粘贴姓名列，否则无法通过复制公式来得到正确结果，并且还要对复制粘贴后的公式进行修改，操作太烦琐所以不予采用。
　　3.2.4 使用丰富多彩的颜色和箭头直观显示不同分数段值。
　　如果统计表数据量大，都采用同一种颜色显示会加重视觉疲劳，并且不能直观区分不同分数段的值。适当的应用条件格式中的“突出显示单元格规则”功能设置多种字体、填充颜色能更直观地显示不同分数段值;使用条件格式中“图标集”中的“五向箭头（彩色）”功能来设置不同颜色不同方向的箭头标注每个单元格中的数据，能直观显示数据的变化情况：低于60分的用红色斜体显示;低于75分的用绿色字体显示，并在数据左侧添加由左下向右上的斜体金黄色箭头，高于（包含）75分并且低于79分的数据用绿色字体显示，并在数据左侧添加由左下向右上的斜体金黄色箭头;高于（包含）80分并的数据用绿色字体显示，并在数据左侧添加向上的绿色箭头;为方便浏览每条记录的所有数据可选中CIO单元格后进行窗口冻结。
　　3.2.5 匯总统计表的使用方法
　　使用Excel函数制作完基于多工作溥及工作表的复杂统计表，未写入任何数据的汇总统计表的局部效果如图2所示。
　　以搜索并写入模拟1成绩为例来讲解汇总统计表的使用方法：可将汇总统计表与模拟1成绩表scoreOl.xls放在同一路径下，然后打开汇总统计表，选中单元格CIO，双击该单元格右下方的填充柄（+）即可将模拟1成绩快速写入汇总统计表并实时完成所要求的统计工作，也可在系统弹出的“打开”对话框中选择打开相应模拟成绩表后完成数据的写入和相应统计。用同样的方法可完成其他模拟成绩的快速探索写入和实时统计。
　　4 探讨与研究
　　每个学生交卷之后会在服务器自动生成以学生准考证号为文件名的文件夹，在该文件夹中有一个以此准考证号为文件名的文本文件，该文本文件中的三位整数就是学生成绩。下一步准备研究设计一个应用程序，实现以下功能：第一步：搜索以准考证号为文件名的文件夹，并提取该文件夹名写入新生成的一个Excel文件中且按从小到大顺序排列;第二步：打开文件夹中以准考证号为文件名的文本文件，提取该文件中的三位整数并除以10得到学生成绩，并将该成绩写入到此Excel文件对应的准考号证号后，从而完成每次模拟实验所有学生的成绩提取。
　　5 结束语
　　使用Excel函数制作成绩汇总统计表，只要打开该统计表就能迅速完成所有学生成绩的搜索和写入以及统计计算，大大节省了教师的工作时间，使老师能迅速把握学生学习情况和效果，并适时对教学做出相应的调整，从而大大提高教学效率和教学效果;相关函数的应用对高级办公人员在查询与统计数据方面起示范作用。
　　参考文献：
　　[1]罗亚玲.计算机应用基础应用教程（windows7+office2010）[M].北京：清华大学出版社，2014：270-274.
　　[2]陈永松.计算机应用基础应用教程[M].广州：中山出版社，2008：244-248.
　　[3]（美）考特（Courter，G.），（美）马奎斯（Marquis，A）Exce12002从入门到精通[M].魏江力，等，译.北京：电子工业出版社，2002：170.
　　[4]（美）麦克菲德里斯（McFedries，P）.巧学巧用Excel2003公式与函数[M].马树奇，金燕译.北京：电子工业出版社，2005：310-314.
　　[5]龙马工作室.Excel 2010中文版完全自学手册[M].北京：人民邮电出版社，2011：210-212.
　　[6]李斌.Excel2010应用大全[M].北京：机械工业出版社，2010：180-183.
　　[7]华诚科技.Office 2010从入门到精通[Ml.北京：机械工业出版社，2011：201-202.
　　[8]龙马工作室.Office 2010办公应用从新手到高手[M].北京：人民邮电出版社，2011：295-298.
　　【通联编辑：梁书】
　　收稿日期：2019-08-01
　　基金项目：广东省教育厅资助项目（粤教高函[2012]54号- A12）
　　作者简介：李艳（1978-），女，湖南新邵人，本科，讲师，主要研究方向为计算机应用与软件技术;徐运武，高级实验师，本科.广东松山职业技术学院，主要研究方向为电子信息。
转载注明来源:https://www.xzbu.com/8/view-15122543.htm