分时量比公式Excel 实验指导——函数的使用

实验八 学生成绩表------数学函数、统计函数

一、 实验目的
1、掌握数组公式、Sum()函数
2、掌握Averageif()和Sumif()
3、掌握COUNT()、COUNTA()、COUNTIF()、Countblank()
4、
5、数据库函数的使用
二、 实验内容
1、利用数组公式或Sum()函数来统计每个同学上学期的总分。
2、利用Averageif()和Sumif()统计平均分和总分。
3、利用统计函数统计班级人数，每门课程不及格人数，缺考科目数。
4、对班级同学的考试情况进行排名。
5、选择合适的数据库函数统计信息
三、 实验任务
小王是班级学习委员，现正值新学期评优时期，班主任委托小王统计班级同学上学期的
考试成绩情况。小王要应用函数分析学生信息、计算考试成绩，分析每科成绩的最高分、最
低分和平均 分，统计每个学生的总分排名，并统计不同寝室的学习情况。
本例效果图如图9- 1所示，小王需要完成的工作包括：
（1）统计每个同学各门课程的总分并排名。
（2）统计每个寝室的平均分。
（3）统计每门课程的不及格人数和缺考人数。
（4）统计符合特定条件的学生信息。

图9- 1 学生成绩表效果图

9. 3 案例实现
9．3．1统计班级每个学生的考试总分
1．使用一般公式方法
公式是Excel工作表中进行数值计算的等式，公式输入是以“=” 开始的，简单的公式
有加、减、乘、除等计算。
我们可以在I3单元格中编辑公式，输入“= D3+E3+F3+G3+H3”，回车后即可，其他同
学的总分可以通过填充柄拖动来求得。
2．数组公式计算总分
Excel中数组公式非常有用，尤其在不能使用工作表函数直接得到 结果时，数组公式显
得特别重要，它可建立产生多值或对一组值而不是单个值进行操作的公式。
输入数组公式首先必须选择用来存放结果的单元格区域（可以是一个单元格），在编辑
栏输入公式，然 后按Ctrl＋Shift＋Enter组合键锁定数组公式，Excel将在公式两边自动加上
花括号 “{}”。注意：不要自己键入花括号，否则，Excel认为输入的是一个正文标签。
利用数组公式 计算I3：I32单元格的总分。选中I3：I32单元格，然后按下“=”键编辑
加法公式计算总分， 因为数组公式是对一组值进行操作，所以直接用鼠标选择D3：D32，
按下“+”号，再用鼠标选择其 余科目成绩依次累加，然后按Ctrl＋Shift＋Enter组合键完成
数组公式的编辑，如图9- 2所示。

图9- 2 数组公式
在数组公式的编辑过程中，第一步选中I3：I 32单元格尤为关键。绝不能开始只选中I3
单元格，在最后用填充柄填充其他单元格，那样其他单元格 的左上角将会出现绿色小三角，
是错误的方法。
3．使用Sum()函数计算总分
Sum()求和函数，可以用来计算总分列。选择I3单元格，使用“公式”→“插入函数”
或“自动求 和”按钮，可选择Sum()函数，选中求和区域D3:H3，如图9- 3所示，按Enter
键，求和结果显示在单元格中。
通过填充操作完成其余各行总分的计算。


图9- 3 Sum()函数参数对话框
9．3．2统计班级相关人数
1．使用Count()、Counta()函数统计班级人数
Count()函数统计含有 数字的单元格个数，统计全班学生人数的时候可以选择统计寝室
号或各科成绩列单元格的个数。
选中N3单元格，使用“公式”→“插入函数”按钮，在搜索函数中输入“count”后
单击“转到 ”按钮，如图9- 4所示，打开“函数参数”对话框，在Value1文本框中输入C3:C32,
表 示统计该区域包含数字的单元格个数，如图9- 5所示，单击确定按钮，完成输入。

图9- 4 插入Count()函数


图9- 5 Count()函数参数对话框
Counta()函数统计区域中不为空的单元的数目，它不仅对包含 数值的单元格进行计数，
还对包含非空白值（包括文本、日期和逻辑值）的单元格进行计数。该函数的使 用方法与
Count()函数使用方法一致。
2．使用Countif()函数统计总分大于400分的学生人数
Countif()函数 是对指定区域中符合某一条件的单元格计数的统计函数。选中N4单元格，
使用“公式”→“插入函数” 按钮，找到Countif()函数后打开参数对话框，在Range参数
中输入要统计的区域范围，总 分区域为I3:I32，Criteria参数中输入条件“>400”，注意在
Excel中输入的标 点符号均为英文格式，单击确定按钮完成计算，如图9- 6所示。

图9- 6 Countif()函数参数对话框
3．使用Countblank()函数统计缺考人次数
Countblank()函数用来计算指定区域中空白单元格的个数。选中N5单元格，使用“公
式 ”→“插入函数”按钮，找到Countblank()函数后打开参数对话框，在Range参数中输
入要统计的区域范围，课程成绩区域为D3:H32，单击确定按钮完成计算。
9．3．3统计平均分、总分
1．使用Average()函数统计语文平均分
在N6单元格中计算语文平均分。选中N6单元格，使用“公式”→“插入函数”按钮，
找到Average()函数后打开参数对话框，在Number1参数中输入要统计的区域范围，语文成< br>绩区域为D3:D32，如图9- 7所示，单击确定求得平均成绩。

图9- 7 Average()函数参数对话框
2．使用Averageif()函数统计男生C语言程序设计平均分
在N7单元格中计算班 级所有男生C语言程序设计的平均分。选中N7单元格，使用“公
式”→“插入函数”按钮，找到Ave rageif()函数后打开参数对话框，在Range参数中输入
要统计平均值的条件所在的区域，条 件区域为性别男即B3:B32，Criteria参数中输入条件，
我们可以直接输入“男”，也可以 从性别列中引用内容为男的单元格如B3，Average_range
参数中输入要计算平均值的实际 单元格区域，输入C程课程考分G3:G32，如图9- 8所示，
单击确定求得男生C程的平均成绩。

图9- 8 Averageif()函数参数对话框
3．使用Sumif()函数统计1401寝室学生总分
Sumif()函数是根据指定条件 对若干个单元格、区域或引用进行求和。选中N8单元格，
使用“公式”→“插入函数”按钮，找到su mif()函数后打开参数对话框，在Range参数中
输入要统计求和值的条件所在的区域，条件区域 为寝室号1401即C3:C32，Criteria参数中
输入条件，我们可以直接输入“1401” ，也可以从寝室号列中引用内容为1401的单元格如

C3，Sum_range参数中输入 要计算求和值的实际单元格区域，输入总分I3:I32，如图9- 9
所示，单击确定求得1401寝室学生的总分。

图9- 9 Sumif()函数参数对话框
9．3．4使用()统计班级学生排名
在J列中统计每个学 生的总分由高分到低分的排名，我们可以使用()函数，该
函数功能为返回某个数字在数字列表中的排名 。选择J3单元格，使用“公式”→“插入函
数”按钮，找到()函数后打开参数对话框，在Numbe r参数中输入要计算哪个数值
的排名，在此我们输入I3；在Ref中输入要进行排名的区域，在此我们 输入I3:I32；前两
个参数合并起来的意思就是计算I3在I3:I32区域中的大小排名；Ord er参数为排序的方式，
如果忽略不填或填0表示降序排列，否则就是升序排列。如图9- 10所示， 单击确定按钮，
这时在J3单元格中显示结果为14，表示I3单元格的数据在I3:I32区域中从高 到低降序排
列为第14名。

图9- 10 ()函数参数对话框
对于J 列其余单元格的排名，如果简单的使用填充柄进行填充，我们将会发现是错误的，
因为函数参数有所改变 。但是Ref参数I3:I32应该是不变的，因此需要对Ref参数I3:I32
使用绝对地址，使其 在用填充柄填充的时候不发生变化。
选中已经编辑好的J3单元格，单击编辑栏上的“插入函数”按钮，直接弹出编辑好

的()函数对话框，选中Ref参数中的I3:I32,按下功能键F4，直接给行列号添加上$$绝
对地 址引用符号，如图9- 11所示，单击确定完成，之后我们就可以使用填充柄正确计算其
余同学的总分排名。

图9- 11 ()函数参数绝对地址引用
9．3．5数据库函数的使用
Exce l数据库函数共有12个，每个数据库函数的首字母都是D，我们也称为D函数。
常用数据库函数有Dm in()、Dmax()、Daverage()、Dsum()、Dcount()。这些函数去掉前面的D就和普通的常用函数一样，分别用来计算数据库清单在一定条件下的最小值、最大值、平
均值、求 和、计数。对于数据库函数，我们可以理解为多条件求值函数，如无条件计数函数
为Count()，单 条件计数函数为Countif()，而多条件计数函数为Dcount()，因此在使用数据
库函数前 必须先设计好条件区域以供引用。
数据库函数的参数均是一样的，都包含三个参数：
Datebase：所求的数据清单或数据库单元格区域。
Field：指定函数运算所使用 的列，可输入加上双引号的字段名或直接引用该字段名，
也可以输入代表列表中列位置的数字。
Criteria：给定的条件区域。
1．使用Dcount()函数统计男生语文高于90分的学生人数
要统计男生语文高于90 分的学生人数，需要两个条件，分别是性别为男和语文成绩高
于90，属于多条件计数，因此用数据库函 数Dcount()。
先在P2:Q3设置条件区域如图9- 12所示，注意：数据清单中能复制的 内容尽量复制。
在N14单元格中，使用“公式”→“插入函数”按钮，找到Dcount ()函数后 打开参数对话
框，在Datebase参数中输入数据清单范围，即A2:J32，在此注意第二行必须 选，作为数据
清单的首行；Field中引用D2，表示要算计大学语文的个数；Criteria中输 入P2:Q3，如图
9- 13所示，单击确定后完成计算。

图9- 12 条件区域


图9- 13 Dcount()函数参数对话框
2．使用Dget()函数统计语文高于90分的女生姓名
Dget()函数的功能是提取符 合条件的唯一记录，如果记录不唯一则返回#NUM!。条件区
域设置如图9- 14所示，函数参数如图9- 15所示。

图9- 14 条件区域

图9- 15 Dget()函数参数对话框
3．使用Dmax()函数统计女生中网络技术的最高分
Dmax()函数的功能是提取满足条件记录指定列的最大值，函数参数如图9- 16所示。


图9- 16 Dmax()函数参数对话框
4．使用Daverage()函数统计排名前10名中男生的平均总分
Daverage()函数的功能是求满足条件记录指定列的平均值，条件区域如图9- 17所示，
函数参数如图9- 18所示。

图9- 17 条件区域

图9- 18 Daverage()函数参数对话框
5．使用Dproduct()函数统计排名前5名中的1403寝室学生物理成绩的乘积
D product()函数的功能是将数据库中符合条件记录的特定字段中的值相乘。条件区域如
图9- 19所示，函数参数如图9- 20所示。

图9- 19 条件区域


图9- 20 Dproduct()函数参数对话框
9. 4 案例总结
本项目主要学习了Excel中数组公式的使用，基本函数中计数函数Count()、Counta()、Countif()、Countblank()用以统计个数，求和函数Sum()、Sumif()，平 均值函数Average()，
排序函数()并进一步强化了绝对地址的应用。
项目中也介绍 了数据库函数，用于对数据清单中满足条件的记录中指定列的数据进行分
析，数据库函数具有相同的参数 ，求解的结果是由函数自身决定的。
9. 5 课后练习
打开采购情况表.xlsx，完成如下设置，效果如图9- 21所示。
1．使用数组公式， 计算采购情况表中每种产品的采购总额，将结果填到“采购总额”
列中，采购总额的计算方法为：采购总 额=单价*每盒数量*采购盒数。
2．使用统计函数统计采购表中共采购产品单数，填入K2单元格中。
3．使用统计函数计算未知寿命产品类数，填入K3单元格中。
4．计算不同种类的白炽灯平均单价，填入K4单元格中。
5．使用Sumif()统计不同种类产品的总采购盒数和总采购金额。
6．使用数据库函数 及设置好的条件区域，计算商标为上海，寿命小于100的白炽灯平
均单价，并将结果填入在G22单元 格中，保留两位小数。
7．使用数据库函数及设置好的条件区域，计算产品为白炽灯，瓦数小于等于1 00且大
于等于80的品种数，将结果填入在G23单元格中。


图9- 21 采购情况表完成后效果图