excel指导

Office高级应用技巧实验指导书

实验4 学生综合考核管理

一、 实验目的

1． 掌握Excel2003工作表多表间的数据选择和表示、多表间的公式与函数的使用、

多表间的合并计算、选择性粘贴的正确使用以及超链接等功能的综合应用。 2． 熟练掌握Excel中的数据链接、数据调用、数据计算、相对与绝对引用、合并计

算等功能。

二、 知识要点 在数据处理过程中，一般情况下很难用一张表格把所有的信息反映出来，因此

对数据进行分类、分析，找出数据间的联系和规律，然后通过多张一系列有关联的表来处理，就成为我们处理数据必须要做的工作，即对数据表格进行系统化设计。Excel2003提供了良好且操作方便的数据链接、数据调用、数据计算、相对与绝对引用、合并计算等功能，并提供了丰富的函数，因此，进行数据表格处理时，应该灵活应用Excel2003的各项操作，进行组合式多表关联数据综合处理，并结合Excel2003函数功能进行系统化设计，以便更快更有条理地处理复杂的数据。 在处理较多且有一定规律的数据的时候，可以利用Excel2003软件设计一个应用

系统工作簿，先设置一系列表格及格式，并将一些基本原始资料事先输入电子表中，通过自定义公式和函数设定计算公式，在使用时只要输入对应项数据就可实现综合数据的自动计算了，如才艺比赛成绩管理系统，在比赛时只要输入各位评委的各项亮分，计算汇总后将自动获得选手的得分，最后得出获奖名单。而且可以通过链接把各张表功能集中在一张表的主界面上，使应用更加方便。

Excel可对复杂数据进行处理，只要理清思路，通过数据分析、表的建立、数据

共享、表与表之间的数据传递、通过函数建立各类自动计算模块，并以数据管理系统的菜单形势进行操作，从而建立起系统化的概念，实现对复杂数据的轻松处理。在Excel2003中，对处理复杂数据十分有用的函数有统计函数中的SUM、MAX、MIN、COUNT、RANK等，逻辑函数中的IF，查找与引用函数中的LOOKUP、VLOOKUP。还可使用“插入”菜单中的“超链接”功能、“格式”菜单中的“字体”功能、“数据”菜单中的“合并计算”功能等进行相关设置。 对于复杂的数据，要进行系统化处理主要有以下几个步骤。 （1）分析数据规律，建立所需用表。

（2）建立各表标题，输入相关原始数据，运用Office数据共享功能，提高速度。 （3）美化数据表格。

（4）利用函数实现各数据表格只见的数据调取。

（5）利用函数实现依据部分数据对事务作出逻辑判断处理。 （6）利用自定义公式计算部分数据。

（7）利用合并计算功能对各表的数据进行综合计算。

（8）通过图形图像、艺术字等制作美化工具制作出漂亮的主界面。 （9）通过超链接功能实现主界面与各张表之间的连接。

1

Office高级应用技巧实验指导书

通过以上系统化设计，对数据处理的十分简便，可以生成为典型的自动处理数

据系统，使用者只需要输入相应的数据，表格即能自动处理，使用起来十分方便。 三、 实验内容及步骤 学生综合考核管理：燕山大学某班级要进行学生学年综合考核，要求通过计算机

采用Excel进行快速评分与计算，得出综合测评成绩并确定最后排名名单，取前五名学生获得奖学金。 学生综合测评实施细则摘要

综合测评成绩是考核学生的主要依据，由品德行为表现分、学业表现分和文

体表现分三个单项分构成，每个单项又分为若干部分。

考核方法：本实验中测评小组由7位同学组成，对本班30位同学进行测评。

由7个评委给出成绩后，去掉一个最高分和一个最低分求其该项目的平均得分，

最后加权得出总分。计算方法为每学年学生综合测评的最终结果（记作F）是品德

行为表现总评得分、学业表现总评得分、文体表现总评得分的加权之和，其计算

公式为：F＝F1*20％+F2*65％+F3*15％。各专业同年级学生按总分排定名次，确

定专业前五名学生获得奖学金。

评委组成：在个人总结基础上，班级综合测评领导小组给班级每位同学以无

记名方式进行，测评小组的同学要本着公正、实事求是的原则工作，工作要求在

辅导员的组织指导下进行。

本实验主要参考步骤如下：

1．通过插入的方式添加所需的工作表

启动Excel2003后，单击“插入”→“工作表”，插入一张工作表，利用同样的方

法再插入5张工作表。利用“重命名”，把左数第一张表标签改为“主菜单”。同理，从左数第二张表的标签开始，对表标签重命名为“考核方法”、“学生资料”、“品行表现”、“学业表现”、“文体表现”、“总分”、“测评小组分析”，如图4.1所示。

图4.1 综合测评所需表格

2．通过剪贴板实现Office之间的数据共享

考核方法的输入：在“考核方法”工作表中，选定单元格范围，例如A1:K40，

根据具体文字多少而定，对其单元格进行合并；打开考核方法的Word文档（可按照上述“学生综合测评实施细则摘要”建立一个Word文档），选定有关内容并做复制操作；回到“考核方法”工作表，单击“编辑”栏中的“粘贴”按钮，将内容复制到刚刚选定的区域；然后根据实际情况设置文本格式，如字体、字号等，使布局整齐、美观。

3．通过合并居中建立各表的标题

单击“学生资料”工作表，在A1单元格中输入“××班级参加测评学生资料一

2

Office高级应用技巧实验指导书

览表”；选择范围后，“合并及居中”，根据实际情况设置字体、字号等格式。通过同样的方法建立各个工作表的标题，并设置格式。 4．输入数据

根据实际情况，输入本班需要进行综合测评学生数据，可采用自动填充、按序

列填充、复制、粘贴等操作辅助完成。参考样式：“学生资料”表中，可输入“序号”、“学号”、“姓名”、“性别”等项目，并对应输入16位学生的资料；在品行表现、学业表现、文体表现评分表输入相关栏目，如“测评序号”、“学号”、“姓名”、“评委1”、“评委2”、“评委3”、?、“得分”；在总分表，可复制“学生资料表”，再加上“品行”、“学业”、“文体”、“总分”、“前五否”等项目；测评小组分析表原始数据可以通过对各表中对应数据进行复制等操作来完成。 5．通过边框等美化表格

选定数据单元格，单击“格式”→“单元格”→“边框”，系统弹出“单元格格

式”对话框，将“边框”中的内部“线条”设置为细线，“外边框”设置为粗线。注意：先选定线条样式，再点预置边框。

6．利用VLOOKUP函数从“学生资料”表中获取姓名 在品行表现评分表中需要的单元格位置，“插入”→“函数”，系统弹出如图4.2

所示的“插入函数”对话框。在“或选择类别”下拉列表中选择“查找与引用”，函数列表中选择“VLOOKUP”，系统弹出如图4.3所示的“函数参数”对话框。在Look_value（搜索的条件值）输入“B3”，即“序号”，在Table_array（被搜索的区域）中输入“学生资料!$A$3:$C$20”，在Col_index_num（返回值所在列号）输入“3”，即“姓名”列，在Range_lookup输入“TURE”或缺省，单击“确定”按钮。如果出现错误信息“#N/A”，这是因为B3中无数值，即“序号”没有输入，没有条件值所以出现错误信息，一旦输入序号，则显示与序号对应的姓名。也可在编辑栏输入如下公式：＝VLOOKUP(B3,学生资料!$A$3:$C$20,3)。为了让该表不出现错误信息，使用IF函数排除错误信息。如图4.4所示。使用公式如下： ＝IF(B3=\学生资料!$A$3:$C$20,3))。

图4.2 “插入函数”对话框

3

Office高级应用技巧实验指导书

图4.3 “函数参数”对话框

7．用IF函数计算每位学生的得分

在品行表现评分表中单元格如K3，输入公式：

=IF(SUM(D3:J3)=0,\，该公式的含义是：当评委的亮分没有输入时，“得分”为空，即所有平稳的分未输入时，SUM(D3:J3)=0，单元格值为空；否则计算出总和SUM(D3:J3)，减去一个最高分，再减去一个最低分，最后除以总评委数COUNT(D3:J3)减2就可以得出该选手的最终平均得分。注意：学业表现表、文体表现评分表对应栏目只要将品行表现评分表的内容复制就可以很快完成。

8．利用LOOKUP函数自动获取学生得分

总分表中的学生得分可以利用LOOKUP函数从“品行”、“学业”、“文体”表中

自动获得。“插入”→“函数”，系统弹出如图4.2所示的“插入函数”对话框。在“或选择类别”下拉列表中选择“查找与引用”，函数列表中选择“LOOKUP”，系统弹出如图4.5所示的“选定参数”对话框，此时选择Lookup_value、lookup_vector、result_vector，“确定”后，打开如图4.6所示的“函数参数”对话框。编辑栏公式为：＝LOOKUP(A3,品行!$B$3:$B$20,品行!$K$3:$K$20)。同理，学业和文体的得分只要修改表名就可获取。值得注意的是：Lookup或VLookup函数在被查询的关键列中必须事先进行排序。

图4.5“选定参数”对话框

4