用Excel2003统计学生测试成绩

1、用Excel 2003统计学生测试成绩在平时的工作中，看到很多教师用计算器统计学生测试成绩，实在是太麻烦。这项工 作如果交给计算机来做，既准确又轻松。在统计学生测试成绩时，我们主要是统计学生参加各学科测试的人数、总分、平均分, 最高分、最低分，及格人数、及格率，优秀人数、优秀率等项内容。下面我们就用“Excel 2003 ” 这个电子表格程序，通过实例，边学习边操作，掌握统计这些项目的方法和技能。、先画一个学生能力测试登分统计表（如下图）在实际操作中，我们要根据本校学生人数最多的班级来设计表的行数，这里为了说明 方便，我只按一页大小来确定行数。说明：平时的登分表里一般不设计学生的性别、民族和评

2、价，表里设计了这样几个项 目，目的是在学习完统计内容后，再学习一点其它有关的知识。二、分别给语文学科统计项目的单元格内添加函数或计算公式。1在语文学科“参考人数”单元格添加如下函数:二COUNT(D3:D22)说明:、“=”是输入函数或公式的标志性符号，只有输入“=”后才能输入函数或公式。、“COUNT ”是返回指定区域内含有数字单元格个数的统计函数。、括号内表达的是指定区域，D3是指定区域的起始单元格，D22是指定区域的结束单元格，中间用“：”连接。表达的意思是“从D3到D22”。（这里假设最多有 22名学生。）、输入的方法和步骤是： 先在单元格内输入“=”，然后依次点击菜单栏上的 “插入”

3、、“函数”，从中选择所需要的函数。选择函数后，会出现一个对话框，在对话框的提示下输 入需要的参数即可。输入完成后，我们可以在工作表界面上的编辑栏中看到刚输入函数的表 达形式，掌握了它的表达形式后，我们也可以直接在单元格内或编辑栏内输入该函数。注意：在单元格中输入的“：”“，”和引号等符号，必须是英语半角符号；输入的英语 字母不区分大小写。2、在语文学科“最高分”单元格内添加如下函数:=MAX(D3:D22)说明：“ MAX ”是返回指定区域内最大数值的函数，括号内表示的是指定区域。3、在语文学科“最低分”单元格添加如下函数:二MIN(D3:D22)说明：“ MAX ”是返回指定区域内最小数值的

4、函数，括号内表示的是指定区域。4、在语文学科“总分”单元格添加如下函数:=SUM(D3:D22)说明：“SUM ”是返回指定区域内所有数值总和的函数，括号内表示指定区域。5、在语文学科“平均分”单元格添加如下函数:二AVERAGE(D3:D22)说明：“AVERAGE ”是返回指定区域内所有数值平均值的函数，括号内表示的是指定区域。5、在语文学科“及格人数”单元格添加如下函数:二COUNTIF（D3:D22” =60”）。这里假设60分以上为及格。说明：“COUNTIF ”是返回指定区域内符合条件个数的统计函数，这里的条件可以是一个数值，可以是一个表达式，也可以是一个字符串，条件前后加英文的双

5、撇号（注意不是汉语的括号），” =60 ”是一个表达式。这个函数表达的意思是返回单元格大于等于60分的个数。6、在语文学科“及格率”单元格添加如下计算公式:第一步:输入：=“及格人数” 单元格/ “参考人数”单元格如果“及格人数”单元格是D25, “参考人数”单元格是 D20，输入的算式就是:=D25/ D20 （引用的单元格应该是实际的单元格）计算公式添加完之后，计算结果表现的是小数形式。第二步:函数和公式，区别是把大于等于60改为大于等于90。这里假设90分以上为优秀。选中此单元格，依次点击菜单栏的“格式”、“单元格”、“数字”、“百分比”，确定保留小数位数后退出，现在返回的数值是百分比形

6、式,符合设计要求了。7、按照第5步和第6步的方法步骤，在“优秀人数”和“优秀率”单元格内分别输入至此，所有要求统计计算语文学科各种项目的函数或公式就全部输入完成了，老师们 可以在学生语文成绩中随意添写成绩，验证一下结果，看是不是非常准确、快捷、轻松、惬 意。三、函数或公式的复制与工作表的复制。（一）函数或公式的复制。语文成绩统计计算的函数或公式设计完成后，其它学科如数学、英语直接复制就可以 了。复制的方法有以下 3种形式:，再分别在数学、英语的“参1、点击语文学科的“参考人数”的单元格进行“复制”考人数”单元格进行“粘贴”。然后用同样的办法复制其它项目。2、把鼠标移到语文学科的“参考人数”的单

7、元格右下角，当鼠标变成细黑十字形时,向右拖动鼠标到数学、英语的单元格，放开鼠标后，复制完成。这种复制方法比第一种要简便。3、先从上到下拖动鼠标，把语文学科的“参考人数”“最高分”、“最低分”、“总分”、“平均分”、“及格人数”、“及格率”、“优秀人数”、“优秀率”单元格全部选中，然后把鼠标 移到“优秀率”单元格右下角，当鼠标变成细黑十字时向右拖动鼠标复制。这样，一下就把所有要复制的函数或公式复制完成了。（二）工作表的复制。在这三种方法中，这种方法最便捷。通过以上操作，我们完成了需要设计的全部内容，但这只是一个年级的（如三年级）工作表。其它年级都需要一个这样的工作表来统计成绩，如果每一个年级的工

8、作表都这样设方法步计，那就太麻烦了。实际上我们只要复制这个工作表到其它年级的工作表就可以了。骤如下:1、插入工作表。打开 Excel 2003进入工作界面时，我们看到程序默认的工作表只有三、“工作表”，直到工作表个，还需要三个，这就需要插入工作表。方法是依次点击“插入”的个数达到所需工作表的个数为止。2、给工作表重命名。右击工作表下面的标签、“重命名”（也可以直接双击标签），分别给工作表命名为“一年级”、“二年级”、“三年级”、“四年级”、“五年级” “六年级”。3、复制工作表。方法是:第一步，先打开已经设计好的工作表，如“三年级”的工作表,点击工作表行标（1、2、3、4、5”）和列标（ A、

9、B、C、D,）左上角的方块，选中整个工作表，单击“复制”。第二步，再打开一个工作表（如“四年级”），点击行标和列标左上角的方块，选中整个工作表后，再单击“粘贴”即可。紧接着，用同样的方法分别复制其它年级的工作表，所有年级的工作表就复制完成了。第三步，分别打开复制的工作表，把“页面设置”得与设计好的工作表的一样，再把每一个工作表题目的年级改成不同的年级即可。复制工作表的方法有很多，但是用这种方法复制的工作表，不但把表格的结构、表格内的函数与公式全部复制了，也把表格的大小、行间距、列间距等全部复制过来了，只是改一下“页面设置”，再改一下表格题目就可以了，个人认为这是最好的一种复制工作表的方法。至此

10、，一个统计计算全校各年级学生成绩的电子表格就全部设计完成了。四、各工作表之间的跨表计算。在Excel 2003中，不但工作表内部能够进行各种计算，工作表之间也能够进行跨表计算。下面用一个简单的例子来讲解一下:假如每一个工作表中都有一个年级的学生人数，包括总人数、其中男生多少人、女生多少人（女生人数可以用公式“总人数”-“男人数”，这是最简单的表内计算）。下面我们就用跨表计算的方法汇总全校总人数。1、为了学习方便，请设计7个工作表，表名分别是“汇总人数”、“一年级”、“二年级”、“三年级”、“四年级”、“五年级”、“六年级”，在每一个工作表中画一个如下的表格:我们可以先在“汇总人数”工作表中画好

11、这个表格，然后用前面学过的复制工作表的方法复制出各年级的工作表。当然，如果每一个工作表中的表格位置不一样也能够进行跨表计算。2、选中“汇总”工作表中“总人数”下的单元格“B6 ”，输入“=”。再次提醒大家,等号是输入函数或公式的标示性符号，只有输入等号，才能输入函数或公式，千万不要忽略。3、输入完等号后，点击“一年级”工作表的标签进入一年级工作表后，点击相应的单在编元格，再输入加号；然后用同样的方法点击其它年级的相应单元格。全部输入完成后, 辑栏左边点击一下“2”确认，完成跨表计算的公式。这时已经看到汇总的结果了。跨表计算的公式输入完成后，在工作表的编辑栏中我们会看到如下公式:如果能够记住跨表

12、计算公式的这种表达形式，直接在编辑窗口输入这个公式即可。4、计算总人数的公式输入完成后，把总人数单元格的公式复制到右边男、女生人数的单元格内，就完成了对男、女生人数的跨表汇总计算。五、单元格的“相对引用”和“绝对引用”在函数或公式设计中的实际应用。电子表中的单兀格是用坐标形式表达的，如D3指的是D列第3行的单兀格。我们在前面讲述设计函数或计算公式时，都要指定区域如（D3:D22 ），这些都是相对引用。如果我们把某一个函数或公式复制到它下面的单元格里时,它指定的区域就自动变成了（D4:D23）；如果我们把这个函数或公式复制到它右边的单元格里时，它指定的区域就自动变成了（E4:E22 ）。也就是说

13、指定区域的 相对区域不会变。这就是 相对引用。但从上面的变化看，当指定区域变成（D4 : D23 ）时，就把第一个学生 D3扔掉了，而我们需要的是不论把函数或公式复制到下面的哪个单元格里,指定区域的第一单元格都不能变，总是D3，要求行号3是绝对的，不能变化的。这就用到了绝对引用。绝对引用的表达方法很简单，就是在行号（或列号前面加上绝对符号“$”，以上的D3的3前面加上“ $”，把指定区域写成（D$3:D22 ），这样无论把此函数或公式复制到下面的哪个单元格里，起始单元格的 行数都不会改变了。还有的时候，我们在向右复制函数或公式时，要求起始单元格的列不能发生变化。如下图:首先，我们在 D4单元格

14、即三年级“合格率”里设计公式为:=C4/B4*100 ,然后向下复制此公式到四年级、五年级、六年级，因为引用的单元格是相对引用，行号也随之变化,结果符合要求。但是，我们把此公式向右复制到“优秀率”、“良好率”和“及格率”时，就出现问题了，复制单元格向右移动几个格，公式引用单元格的 列也同时向右移动了几个单元格，被除数符合要求，可除数变了。我们希望的是“参加测试人数”一列不能变。这就要求我们把这一列设计为绝对引用。把公式设计为：C4/$B4，也就是在列号B前面加上一个绝对符号 “$”。还有第三种情况，就是 行和列都是绝对引用，也就在说不论把公式设计并复制到在哪F3”是“ 1.50元”，在下面任何

15、一个单元格里，都只和一个单元格进行计算。举一个简单的例子，假如有一个计算出售西红柿 金额的表格，在表格的上面有一个西红柿单价的单元格“ 个单元格里设计公式， 并复制公式到任何单元格时， 都要用出售的数量乘以这个单元格来计算金额，那么这个单元格的行号和列号都要用绝对引用。表达的形式就是：$F$3。当然, 如果改变单价，所有计算结果也会随之改变。相信通过上面深入浅出的讲解，大家对什么叫相对引用、什么叫绝对引用，以及在实 际设计函数或公式时如何应用，应该都掌握了。F面再向大家讲一点应用电子表格的其它知识。六、给单元格设计下拉菜单。我们在电子表格中输入学生或教师的基本情况时，有的项目只是几个固定的内容

16、，如“性别”列只能填男、女两项，“民族”列一般也只有“汉、蒙、满、回”几项，如果我们 给这样的列设计下拉菜单进行输入，不但能提高输入速度，还能避免输入错误。现以“性别”一列为例，讲解一下设计下拉菜单的方法。1、点击“性别”一列上面的列标“B”，选中整个列。2、依次点击“菜单”、“数据”、“有效性”、“设置”，在“允许”对话框中选择“序列”出现“来源”对话框。3、在“来源”对话框中直接输入“男，女”，确定退出。注意，中间的符号是英文的逗点，不是中文的逗号。现在我们只要在“性别”列下面的单元格一点，就会在单元格的右边出现一个黑三角, 点一下黑三角就会出现一个下拉菜单，点击菜单中的“男”或“女”就完

17、成输入了。如果输 入其它内容，就会出现输错警告，不允许输入。请按以上方法给“民族”列设计下拉菜单，要求下拉菜单中的民族是“汉族、蒙古族、 满族、回族”。七、用逻辑函数对学生的成绩进行智能判断。逻辑函数有条件“ IF ”、与“ AND ”、或“OR”和非“ ONT”，（字母不区分大小写）前三个最常用，灵活应用逻辑函数，能够根据不同的条件做出比较复杂的智能判断。1、下面我们先以语文成绩来学习一下应用逻辑函数的基本方法和形式。基本形式是：=IF（条件，结果1,结果2），注意：括号和逗号都是英文半角的，不是中文 全角的。2。它表达的意思是如果条件成立，就返回结果1 ;如果条件不成立，就返回结果条件和结

18、果可以是一个具体的数值，可以是一个表达式，也可以是一个字符串，但如果是字符串的话，字符串两边要加上双撇号”，不是中文的引号。假设语文成绩在 60分以上（包括60分）为及格，否则为不及格，我们在“评价”下第一名学生的单元格中输入以下函数即可:=IF（D3=60, ” 及格”，”不及格”）因为评价语言是字符串，所以两边加了双撇号。D3是第一名学生语文成绩的单元格。这个函数表达的意思是：如果 D3格中的数值大于等于 60，就返回“及格”二字；否则返回“不及格”二字。以上函数输入完成后，向下复制此单元格即可。7层。逻辑函2、下面我们以语文成绩来学习一下嵌套逻辑函数的基本方法和形式。函数里可以再套函数，

19、一层一层地套下去，这就是嵌套，但最多只能套 数嵌套的基本形式是:=IF（条件1,结果1, IF （条件2,结果2，IF（条件3，结果3，IF（条件4”它表达的意思是：如果条件 1成立就返回结果1，如果条件2成立就返回结果 2,如果条件3成立就返回结果3,如果条件4成立”假设语文成绩大于等于 90分为优秀，大于等于 80分为良好，大于等于 70分为较好,大于等于60分为一般，小于60分为较差，可以设计如下函数:=IF(D3=90, ” 优秀”，IF(D3=80, ” 良好”，IF(D3=70, ” 较好”，IF(D3=60, ”一般”，”较差”）注意：括号要成对出现，有几个左括号，就一定要有几个

20、右括号，不能多也不能少。3、通过判断多个条件返回判断结果。IF逻辑函数不但能够判断一个条件得出结果，还能同时判断多个条件。这就要用到逻辑与“ AND ”或逻辑或“ OR”。表达式如下:逻辑与的表达式：AND（条件1,条件2,条件3,）它表示几个条件必须都成立。逻辑或的表达式：OR（条件1,条件2,条件3,）它表示几个条件只要有一个条件成立就 可以。还是以以上评价学生成绩等级为例，假如在评价学生成绩时，必须语文、数学、英语 三个学科都在90分以上才是“优秀”，三个学科都在80分以上才是“良好”，三个学科都在70分以上才是“较好”，三个学科都在60分以上才是“一般”，否则就是“较差”。我们就可以这

21、样编辑函数公式:=IF(AND(D3=90,E3=90,F3=90),” 优秀”，IF(AND(D3=80,E3=80,F3=80),“良好” ，IF(AND(D3=70,E3=70,F3=70), ” 较好” ，IF(AND(D3=60,E3=60,F3=60,” 一般”，”较差”)注意：最后要补齐右括号，使括号成对出现。把评价第一名学生的函数公式编辑完成后，向下复制这个公式到其他学生即可。如果把公式中的“ AND ”换成“ OR”，就表示几个条件中只要有一个条件成立就返回确定的结果。八、几个非常实用的小技巧。1如果数据表格有多页，想要打印出来的每一页都有表格题目和列标题(即表头)怎么办?点

22、击“文件”、“页面设置”、“工作表”，然后在“顶端标题行”对话框中输入表头所占的起止行数即可。假如表头共有三行，就输入“$1：$3”，确定退出即可。这里的 $是绝对符号，也可以不用输入，直接输入“1:3”，绝对符号电脑会自动给补上的。2、在单元格内的文字如果比较多，需要强制换行，怎样换行?与在Word中不同，直接点回车就会跳出单元格，正确方法是“Alt+回车”。3、在Excel中，怎样给表格画斜线表头？如下图:一条斜线的表头两条斜线的表头(1 )、画一条斜线表头的方法:表格设计完之后，选中需要画斜线的单元格，设置对齐方式为左对齐，输入“学科” 后强制回车两次，再输入“姓名”；然后用空格键把“学

23、科”二字向后推。最后点击“格式”、“单元格”、“边框”，从中选从左上到右下的斜线点击，确定退出即可。(2)、画两条斜线表头的方法:选中需要画斜线的单元格，设置对齐方式为左对齐，输入“学科”后强制换行，输入“成绩”后强制换行，再输入“姓名”；然后用空格键把“学科”二字推动格靠后的位置, 直线工具在单元格合适的位置画上两条斜线即可。4、怎样在单元格中输入开头为0 （如“ 001”）的数字序号?选中需要输入序号的列，点击“格式”、“单元格”、“数字”，从下拉列表中选择“自定义”，然后在“类型”中输入”000”占位，表示要输入三位数字，确定退出即可。5、如何快速输入学生学籍号?自定义单元格。我们今年的

24、学生学籍号是“090501001 ”到“ 090501113”。这里的09表示是2009年，0501表示的是当铺地学区，只有后三位数才是学生的序列号。设置的方法是：选中需要输入序号的列，点击“格式”、“单元格”、“数字”，从下拉列表中选择“自定义”，然后在“类型”中输入”090501” 000，确定退出即可。在输入学生学籍号时，只要输入学生的序号（如 3）即可。用同样的方法可以实现自动给数字后面添加单位,或自动给数字前后都添加说明文字。如自定为0”千克”，输入“ 52”时就显示为“ 52千克”；自定义为”第”0” “名”，输入2时就显示为“第2名”。6、如何快速输入“年月日”？在输入年月日时，

25、用“-”或“ /”代替年、月即可。如输入“ 1967年8月12日”，可以输入为“ 1967-8-12 ”，也可以输入为“ 1967/8/12 ”，这样就行了。7、如何改变按回车后光标移动的方向?我们在某一个单元格输入完成，一点回车光标就移动到下面的单元格，这是程序默认的，在按列输入时很方便。 但是如果按回车键后让光标向后移动，按行输入，设置的方法是: 依次点击“工具”、“选项”，在选项表中点击“编辑”，然后在“按Enter后移动方向”对话 框中，把“向下”改为“向右”就行了。8、在表格较大超出显示窗口时，怎样才能保证输入的内容不错格?折分窗口。假设要添写的教师基本情况比较多，列标题早就从上面移出去了，为了始终能看到列标题，就可以把它折分成上下两个窗口。方法是把右边上下滚动条上面的小长方 块向下拉，就形成了两个窗口，在上窗口中显示出列标题，在下窗口中输入。如果表格横向较长，在输入时保证能看到左边的教师姓名，就用同样的方法把下面的 左右滚动条右的小长方块向左拉，就折分在了左右两个窗口。9、如何把两个单元格中的内容自动填到一个单元格里?使用连接符号。如下图:假设以前填写过一个教师基本情况表，毕业学校在一个单元格里，毕业时间在另一个 单元格里。现在又要填一个教师基本情况表，毕业学校和毕业时间“或何时毕业于何学校”& ”。却是在一个单元格里，怎样才能把以前两个单元格中