Excel在学校中的应用7-学生学习成长记录表

2.1 学生学习成长记录表

案例背景

每逢测验或期中考试、期末考试完毕，任课教师都要录入学生成绩，计算所教各班的平均分，班主任还要计算每个学生每门课程成绩的总分，有时还要排名次，以致计算每个班里的最高分、最低分等等。

不论处于那个时代的教师，教学效果都是作为教师最为关心的事情之一，成绩统计是每个教师必须完成的本职工作，只不过完成成绩统计工作的工具在随着时代的发展而不断变换，教师统计学生成绩所使用的计算工具按时代划分一般来说，上世纪70年代以前主要以笔和算盘为主，八十年代发展到使用计算器，但无论是笔算、借助算盘或计算器等计算工具，都很难保证百分之百的准确性，80年代本人曾在中学任教，比如我们以前在求班级总分和平均分这项工作，就是一件非常麻烦的事情，往往要几个教师共同协作，一个人念学生成绩，其他的人进行累加，如果依次计算完毕，几个人累加结果相同还则罢了，如果不一致，往往要重复多次，直到几个人的累加结果完全相同，考试后计算成绩既耗时又费力，可以说这种计算办法既不能保证科学性，而且工作效率也很低，随着时代的发展，计算机在我国早已进入家庭和学校，Excel等办公软件的功能也日趋完善，用Excel实现上述功能可以说是再简单不过的事情了。只要输入的学生成绩无误，Excel的计算结果的应该是百分之百准确。用Excel计算和统计教育中的相关问题，既可以提高处理问题的科学性，又可以增强处理问题的高效性，学会使用Excel办公软件，您会发现可以极大地提高您的工作效率，从而使您能够有更多的精力关注教学本身。

本案例就以第一单元中某中学初一1 班10名学生初一上下两个学期的语、数、外三门主课的期中、期末和总评成绩为例，介绍公式和常用函数的初步应用。按照时间顺序通过不断记录学生每个阶段的考试情况，可以动态的观察每个学生的在该学段的学习情况和发展趋势，从而可以有针对性的制定教育方案。

关键技术点

要实现本案例中的功能，学员应该掌握以下EXCEL技术点。

●基础知识：基本的数学四则运算，选择性粘贴 工作组

●单元格引用：一维引用、二维引用和三维引用。

绝对引用、相对引用和混合引用 。

●函数应用：SUM函数、AVERAGE函数，MAX函数和MIN函数的应用

最终效果展示

2.1.1创建初一上学期期中考试成绩表

Step 1创建一个新的工作簿文件Book1，按照“最终效果展示”的样式输入表格的字段栏目信息及学生个人信息，学号和姓名，在单元格A12,A13,A14分别输入平均分，最高分和最低分。 然后进行边框设置

2.1.2创建其它成绩表

Step 1 创建工作组

按住键，单击工作表标签“Sheet2”和“Sheet3“，同时选中三张工作表“Sheet1”、“Sheet2”和“Sheet3“，创建Excel工作组。这时文件标题栏显示为Microsoft Excel-Book1[工作组]

Step 2批量插入工作表

单击菜单“插入”→“工作表”。就一次性插入了三张工作表“Sheet4”、“Sheet5”和“Sheet6“。

Step 3 重命名工作表

双击“Sheet1“工作表标签将其重命名为“一上期中“。..

Step 4 复制单元格区域

选中工作表“一上期中“的单元格区域A2:F14，单击常用工具栏”复制“，

Step 5 创建工作组

光标切换到工作表“Sheet2“，选中A1单元格，按下键，依次单击工作表标签”Sheet3“、”Sheet4“、”Sheet5“和”Sheet6“，选中五张工作表。

Step 6，批量粘贴单元格区域

单击常用工具栏”粘贴“，这样使用”Excel工作组“就把工作表”一上期中“的成绩表的框架结构的样式和内容一次性复制到其它五张工作表上了。

2.1.3输入学生原始成绩

Step 1输入初一上学期期中成绩

①鼠标单击工作表标签“一上期中“，这时就解除了Excel工作组，同时将工作表 “一上期中“变为”活动工作表“，选中单元格区域C2:E11，准备输入第一学期学生成绩。

②从单元格C2开始依次纵向输入学生成绩。

Step 2输入初一上学期期末成绩

①鼠标单击工作表标签“sheet2“，选中单元格区域C2:E11

②依次横向输入学生成绩。

2.1.4重命名工作表，保存工作簿

①双击sheet2工作表标签将其激活称重命名状态，重新命名为“一上期末“，照此法依次将工作表”Sheet3“、”Sheet4“、”Sheet5“和”Sheet6“分别命名为：”一下期中“，”一下期末“，”一上总评“和”一下总评“。

②单击常用工具栏“保存“，从弹出的”另存为“对话框中的文件名选项框输入文件名”学生成长记录“，单击”保存 “按钮即可完成文件的命名和保存工作。

2.1.5计算“总分”

Step 1插入求和函数

鼠标单击“一上期中”工作表标签，光标放到F2单元格，鼠标单击常用工具栏的“自动求和按钮”，按键确认，即可算出第一个学生的总分，这时的编辑栏中显示“=SUM（C2:C4）”其中，”=”是公式的标志，“SUM”为求和函数的函数名，括号内的C2:C4为求和函数的参数（函数默认准备进行求和的单元格附近的数字单元格区域为函数参数，如果默认区域C2:C4不是你准备求和的数据源区域，则可以在编辑栏直接修改，或者用鼠标选择正确的单元格区域。）

Step 2复制公式

选中F2单元格，鼠标单击常用工具栏”复制“，选中准备复制公式的F3单元格，鼠标单击常用工具栏”粘贴“即可完成公式复制从而算出第二个学生的总分。至于计算第三个学生到第十个学生的总分照此办理就可以了。

Step 3复制公式的简捷方法

光标移动到F3单元格，将鼠标放置到单元格左下角，当鼠标的光标由空心十字变为黑色实心十字时按下鼠标左键直接向下拖拽到F11单元格即可完成公式复制。从而第三个学生到第十个学生的总分。更简捷的的方法是：只要准备用公式计算的单元格区域E4：E11中间没有没有空的单元格，当鼠标的光标由空心十字变为黑色实心十字时左键双击也能完成公式复制。这种方法对于需要复制公式行数很多的情况，比如计算整个年级或学校的成绩，更显示其计算速度的优势。

2.1.6计算“平均分”

Step 1插入平均值函数

光标移到C12单元格，鼠标单击常用工具栏的【fX】“插入

函数”弹出对话框，

②在“或选择类别”里的下拉菜单中选择“统计”，在“选择函数”选项框中选择 AVERAGE函数，

③在函数参数的第一个选项框“Number1”中直接输入准备计算平均

分的数据区域C2:C11，

④单击确定按钮或按键确认均可完成计算平均分的操作。

Step 2复制公式

光标移动到C12单元格，将鼠标放置到单元格左下角，当鼠标的光标由空心十字变为黑色实心十字时按下鼠标左键直接向右拖拽到F12单元格，松开鼠标即可完成数学、外语和总分的平均分的计算工作。

2.1.7计算“最高分”和“最低分”

Step 1插入最大值函数

光标移到C13单元格，鼠标单击常用工具栏的【fX】“插入函数”

弹出对话框，

②在“或选择类别”里的下拉菜单中选择“统计”，在“选择函数”选项框中拖动“选择函数”选项框旁的“”垂直滚动条找到“MAX”函数，点击 MAX，

单击确定按钮，在弹出的函数参数对话框中，在第一个选项框“Number1”中直接输入准备计算最高分的数据区域C2:C11，

④然后单击确定按钮或按键确认均可完成计算最高分的操作。

Step 2插入最小值函数

光标移到C14单元格，参照计算最高分的方法，插入MIN函数即可计算出最低分。

Step 3复制公式

①光标移动到C13单元格，选中单元格区域C13:C14，

②将鼠标的光标放到C14单元格右下角，当鼠标的光标由空心十字变为黑色实心十字时按下鼠标左键直接向右拖动到单元格F14，松开鼠标左键即可完成公式的复制，得到数学、外语和总分的最高分与最低分。

Step 4 重新设置被修改的边框

①选中单元格区域F12:F14，

②单击菜单“格式“→”单元格“，弹出“单元格格式”对话框，切换到“边框”选项卡，在“线条样式“选框中选择双线，在”颜色“选框中选择与原先边框一致的蓝色，在”边框“选框中选择右边框。

③单击确定按钮，即可修复在复制公式时被破坏的边框的线型。

2.1.8输入原始分数

参照上面介绍的输入成绩的方法，分别在工作表“一上期末“，“一下期中“和”一下期末“中输入原始分数。

2.1.9批量复制公式

Step 1复制粘贴总分

①鼠标单击“一上期中“工作表标签，选中单元格区域F2:F11，单击常用工具栏”复制“，

②鼠标单击”一上期末“工作表标签，按下键，鼠标单击”一下期中“和”一下期末“工作表标签，组成Excel工作组，选择准备粘贴公式相应单元格区域F2:F11，单击常用工具栏”粘贴“按钮，即可一次性完成”一上期末“、”一下期中“和”一下期末“三张工作表的”总分“栏目的公式复制粘贴。

Step 2复制粘贴“平均分“、”最高分“和”最低分“

①鼠标单击“一上期中“工作表标签，选中单元格区域C12:F14，单击常用工具栏”复制“按钮，

②鼠标单击”一上期末“工作表标签，按下键，鼠标单击”一下期中“和”一下期末“工作表标签，组成Excel工作组，选择准备粘贴公式相应单元格区域C12:F14。

③单击常用工具栏”粘贴“，即可一次性完成”一上期末“、”一下期中“和”一下期末“三张工作表的平均分、最高分和最低分公式复制粘贴。这样利用”Excel工作组“技术同时完成了三张工作表的平均分、最高分和最低分的统计工作。

2.1.10计算总评成绩

工作表“一上总评“和”一下总评“中没有原始成绩需要输入，每学期的总评成绩是按照期中考试成绩占40%，期末考试成绩占60%的比例计算的，因此需要在工作表“一上总评“和”一下总评“中设计公式计算出每个学生的学期总评成绩。

Step 1计算第一个学生语文学期总评成绩

①鼠标单击“一上总评“工作表标签，选中第一个学生语文成绩的位置单元格C2，准备编辑公式。 ②在编辑栏中输入“=“，鼠标左键单击工作表标签”一上期中“，选中单元格C2，输入乘号”*“，然后输入40%,再输入加号”+“，然后继续鼠标左键单击工作表标签”一上期末“，选中单元格C2，再次输入乘号”*“，然后输入60%, 按键确认，即可完成第一个学生上学期语文总评成绩的计算工作。

Step 2计算其余学生的语文期末总评成绩

将鼠标的光标放到C2单元格右下角，当鼠标的光标由空心十字变为黑色实心十字时右键双击即可完成C3:C11单元格区域的公式的复制，得到其余学生的语文总评成绩。

Step 3计算所有学生的数学、外语的期末总评成绩

选中单元格区域C2:C11，将鼠标的光标放到C11单元格右下角，当鼠标的光标由空心十字变为黑色实心十字时，向右拖动鼠标到E列，松开鼠标即可完成数学、外语的期末总评成绩统计工作。

Step 4计算初一下学期总评成绩

参照工作表“一上总评“中总评成绩的公式设计计算出工作表”一下总评“的语文、数学和外语总评成绩。

Step 5计算总分

①鼠标单击“一上期中“工作表标签，选中单元格区域F2:F11，单击常用工具栏”复制“，

②鼠标单击”一上总评“工作表标签，按下键，鼠标单击”一下总评“工作表标签，组成Excel工作组，选择准备粘贴公式的相应单元格区域F2:F11，

③单击常用工具栏”粘贴“，即可完成工作表”一上总评“和”一下总评“的总分统计工作。

Step 6计算平均分、最高分和最低分

①鼠标单击“一上期中“工作表标签，选中单元格区域C12:F14，单击常用工具栏”复制“，

②鼠标单击”一上总评“工作表标签，按下键，鼠标单击”一下总评“工作表标签，组成Excel工作组，选择准备粘贴公式相应单元格区域C2:F14

③单击常用工具栏”粘贴“，即可完成工作表”一上总评“和”一下总评“，的平均分、最高分和最低分的统计工作。

至此工作簿内六张成绩统计表的成绩统计和表格的格式设计工作全部完成。这些工作表内的成绩可以作为1.2节中的案例“学生成绩档案页“中的成绩项的数据来源，通过使用查询函数实现每个学生档案页的动态查询，这个内容在后面的学习中陆续进行讲解。

关键知识点讲解

一、单元格引用

单元格引用是函数中最常见的参数，引用的目的在于标识工作表单元格或单元格区域，并指明公式或函数所使用的数据的位置，便于它们使用工作表各处的数据，或者在多个函数中使用同一个单元格的数据。根据实际需要既可以引用同一工作表中的单元格或单元格区域，也可以引用同一工作簿不同工作表的单元格或单元格区域，甚至可以引用其他工作簿文件中的数据。

引用单元格区域时，冒号表示以冒号左右两边所引用的单元格为左上角和右下角之间的所有单元格组成的矩形区域。

当右下角单元格与左上角单元格处在同一行或者同一列时这种引用称为一维引用，如A1：C1或者B1:B6。

当引用单元格区域处于同一张工作表，但是所引用单元格区域的右下角单元格与左上角单元格处不在同一行或者同一列时，这种引用称为二维引用，如A1:C4或者D3:E5，A1:C4表示从以A1单元格为左上角，C4单元格为右下角的4行3列的矩形区域，形成了一个二维的面，因此该引用称为二维引用。

当跨工作表引用单元格区域时，这种引用称为三维引用。

因为当引用区域不只在构成二维平面的方向出现时，其引用就是多维的，是一个由不同层次上多个平面组成的空间模型，如公式：

“=SUM(Sheet1!A1:C2,Sheet2!B1:C3)”

表示对工作表Sheet1的单元格区域A1:C2，和Sheet2的单元格区域B1:C3求和，在此公式的引用范围中Sheet1!A1:C2是一个二维平面，Sheet2!B1:C3又是另一个二维平面，多个二维平面在行、列和表三个方向上构成了三维引用。

根据公式所在单元格的位置发生变化时，单元格引用的变化情况，我们可以将引用分为“相对引用”、“绝对引用”和“混合引用”三种类型。

1.相对引用

相对引用是指对于包含公式的单元格或单元格区域的相对位置。以本案例中存放在F2单元格中的公式“=SUM（C2:E2）”为例，当公式由F2单元格复制到F3单元格以后，公式中的引用也会变化为“=SUM（C3:E3）”。若公式自F列向下继续复制，计算公式仍然是左边三个单元格进行求和，只是“行标”每增加1行，公式中引用的单元格区域的行标也自动加1。

2.绝对引用

绝对引用是指引用单元格或单元格区域的绝对地址，如果上述公式改为“=SUM（$A$3:$E＄3）”，则无论公式复制到何处，其引用的位置始终是“A3:E3”区域。称这种引用为绝对引用。绝对应用的标志是须在引用的单元格或单元格区域的“列标”和“行号”的前面添加美元符号（$）。

3. 混合引用

混合引用分成“绝对列和相对行”，或是“绝对行和相对列”两种形式。前者如“=SUM（$A3:$E3）”，后者如“=SUM（A$3:E$3）”，这两个公式中的引用就属于“混合引用”。

4.相对引用和绝对引用之间的切换

如果创建了一个公式并希望将相对引用更改为绝对引用（反之亦然），其操作步骤如下;

步骤1选定包含该公式的单元格。

步骤2 在编辑栏中用鼠标的光标选择要更改引用形式的单元格或单元格区域并按键。

步骤3 每按一次键，Excel会在以下组合间切换。

①绝对列与绝对行引用SUM（$A$3:$E＄3）     ②绝对列与相对行引用SUM（$A3:$E3）

③相对列与绝对行引用SUM（A$3: E＄3）     ④相对列与相对行引用SUM（A3: E3）

上面的几个实例引用的都是同一工作表中的数据，如果要分析同一工作簿中多张工作表上的数据，就要使用三维引用。假如公式放在工作表“一上总评”的C2单元格的公式为：= “一上期中!C2*40%+一上期末!C2*60%”，要引用工作表“一上期中!”的C2单元格和“一上期末!” 的C2单元格进行加法和乘法混合运算，也就是说三维引用中不仅包含单元格或区域引用，还要在前面加上带“!”的工作表名称。假如你要引用的数据来自另一个工作簿，如工作簿Book1中的SUM函数要绝对引用工作簿Book2中的数据，其公式为“=SUM（[Book2]Sheet1! $A3:$E3,[Book2]Sheet2! $A3:$E3）”，也就是在原来单元格引用的前面加上“[Book2]Sheet1!”。放在中括号里面的是工作簿名称，带“!”的则是其中的工作表名称。也就是说跨工作簿引用单元格或区域时，引用对象的前面必须用“!”作为工作表分隔符，再用中括号作为工作簿分隔符。

二、函数的输入方法

对Excel公式而言，函数是其中的主要组成部分，因此公式输入可以归结为函数输入的问题。

1.“插入函数”对话框“     插入函数”对话框是Excel输入公式的重要工具，以公式“=SUM（Sheet2!A1:A6,Sheet3!B2:B9）”为例，Excel输入该公式的具体过程是：首先选中存放计算结果（即需要应用公式）的单元格，单击编辑栏（或工具栏）中的“fx”按钮，则表示公式开始的“=”出现在单元格和编辑栏，然后在打开的“插入函数”对话框中的“选择函数”列表找到“SUM”函数。如果你需要的函数不在里面，可以打开“或选择类别”下拉列表进行选择。最后单击“确定”按钮，打开“函数参数”对话框。对SUM函数而言，它可以使用从number1开始直到number30共30个参数。对上面的公式来说，首先应当把光标放在对话框的“number1”框中，单击工作簿中的“Sheet2!”工作表标签，“Sheet2!”即可自动进入其中，接着鼠标拖动选中你要引用的区域即可。接着用鼠标单击对话框的“number2”框，单击工作簿中的“Sheet3!”工作表标签，其2名称“Sheet3!”即可自动进入其中，再按相同方法选择要引用的单元格区域即可。

上述方法的最大优点就是引用的区域很准确，特别是三维引用时不容易发生工作表或工作簿名称输入错误的问题。

2.编辑栏输入

如果你要套用某个现成公式，或者输入一些嵌套关系复杂的公式，利用编辑栏输入可能更加快捷。

首先选中存放计算结果的单元格；鼠标单击Excel编辑栏，按照公式的组成顺序依次输入各个部分，公式输入完毕后，单击编辑栏中的“输入”（即“√”）按钮（或回车）即可。手工输入时同样可以采取上面介绍的方法引用区域，以公式“=SUM（Sheet2!A1:A6,Sheet3!B2:B9）”为例，你可以先在编辑栏中输入“=SUM（）”，然后将光标插入括号中间，再按上面介绍的方法操作就可以引用输入公式了。但是分隔引用之间的逗号必须用手工输入，而不能像“插入函数”对话框那样自动添加。

三、函数的应用

1. SUM函数

函数名称：SUM

主要功能：计算所有参数数值的和。

使用格式：SUM（Number1,Number2……）

参数说明：Number1、Number2……代表需要计算的值，可以是具体的数值、引用的单元格（区域）、逻辑值等。

应用举例：如图7所示，在D64单元格中输入公式：=SUM(D2:D63)，确认后即可求出语文的总分。

特别提醒：如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略；如果将上述公式修改为：=SUM(LARGE(D2:D63,{1,2,3,4,5}))，则可以求出前5名成绩的和。

特别提醒：如果采取自动筛选，无论function_num参数选用什么类型，SUBTOTAL函数忽略任何不包括在筛选结果中的行；SUBTOTAL函数适用于数据列或垂直区域，不适用于数据行或水平区域。

2. AVERAGE函数

函数名称：AVERAGE

主要功能：求出所有参数的算术平均值。

使用格式：AVERAGE(number1,number2,……)

参数说明：number1,number2,……：需要求平均值的数值或引用单元格（区域），参数不超过30个。

应用举例：在B8单元格中输入公式：=AVERAGE(B7:D7,F7:H7,7,8)，确认后，即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。

特别提醒：如果引用区域中包含“0”值单元格，则计算在内；如果引用区域中包含空白或字符单元格，则不计算在内。

3. MAX函数

函数名称：MAX

主要功能：返回给定参数表中的最大值。

使用格式：MAX(number1,number2……)

参数说明：number1,number2……代表需要求最大值的数值或引用单元格（区域），参数不超过30个。

应用举例：如果A1=71、A2=83、A3=76、A4=49、A5=92、A6=88、A7=96，则输入公式 “=MAX(A1:A7)”返回96。

特别提醒：如果参数中有文本或逻辑值，则忽略

4. MIN函数

主要功能：返回给定参数表中的最小值。

使用格式：MIN(number1，number2，...)。

参数说明：Number1，number2，... 代表需要求最小值的数值或引用单元格（区域），参数不超过30个。

应用举例：如果A1=71、A2=83、A3=76、A4=49、A5=92、A6=88、A7=96，则公式“=MIN(A1:A7)”返回49;而=MIN(A1:A7，0，-8)返回-8。