实验七 公式与函数的使用

实验七公式与函数的使用

2.1实验内容

利用公式、函数对图2-1所示的“学生成绩表”实现自动计算，统计出每个学生的总分、平均分、是否通过、等级；统计各科的最高分、最低分；统计所有学生成绩的通过率、优秀率。

要求：

1．利用函数产生学生的“是否通过”结果：平均分在60分以上计为“是”，60分以下计为“否”。

2．“等级”结果分三档：平均分在80分以上计为“优秀”，60-80之间为“合格”，60分以下为“不合格”。

3．“通过率”是计算“是否通过”结果为“是”的学生占总人数的百分比。

4．“优秀率”是计算“等级”为“优秀”的的学生占总人数的百分比。

图2-1 学生成绩表

2.2 实验目的

熟练掌握工作表中公式、常用函数的使用。

熟练掌握公式的复制操作。

2.3 实验步骤

2.3.1新建工作薄，建立工作表

启动Excel2003后，在新工作簿的Sheet1中建立如图2-1所示的学生成绩表。在相应的单元格中输入数据。

2.3.2使用公式计算总分

在H3单元格中输入公式“=SUM(D3:G3)”, 按下Enter键或单击确定按钮，计算出第1个学生的总分。

当然，也可输入公式“=D3+E3+F3+G3”，或者使用“自动求和”。

利用“填充柄”复制公式到H3:H12，计算出其他学生的总分。

2.3.3使用函数计算平均分

1．使用“函数向导”输入函数

①选定单元格I3，单击“插入”菜单 “函数”命令，或者单击工具栏上的“插入函数”按纽，屏幕显示“粘贴函数”对话框，如图2-2所示。

②在“选择函数”列表框中选择函数“AVERAGE”，单击“确定”。

③在弹出的“函数参数”对话框中，如图2-3所示, 单击“Number1”右侧的箭头，用鼠标在工作表中选定区域D3:G3，单击“确定”，计算结果显示在单元格中。

图2-2 “插入函数”对话框

图2-3 “函数参数”对话框

2．使用“自动求和”命令按钮输入函数

①选定单元格I3，按下键盘上的Delete键，先删除单元格中的内容；再单击自动求和按钮右侧的向下箭头，弹出“常用函数”菜单。

②选择“平均值”，会自动填充函数并用闪动的边框围住被选择的区域，再用鼠标选定

区域D3:G3，如图2-4所示，然后按Enter键，结果显示在单元格。

图2-4 选择或输入参数

3．手工输入函数

选定单元格I3，按下键盘上的Delete键，先删除单元格中的内容；

然后输入函数：=AVERAGE(D3:G3)。按下Enter键或单击确定按钮，计算出第1个学生的平均分。

单击I3单元格，利用“填充柄”复制公式，计算出其他学生的平均分。

2.3.4利用函数产生“是否通过”结果

1．使用“函数向导”输入函数

①选定单元格J3，单击“插入”菜单 “函数”命令，或者单击工具栏上的“插入函数”按纽，屏幕显示“粘贴函数”对话框，如图2-2所示。

②在“选择函数”列表框中选择函数“IF”，单击“确定”。

③在弹出的“函数参数”对话框中，如图2-5所示,在logical_test编辑框中输入条件“I3>=60”；在value_if_true编辑框中输入“是”；在value_if_false编辑框中输入“否”，单击“确定”，计算结果显示在单元格中。

图2-5 IF“函数参数”对话框

2．手工输入函数

选定单元格J3，按下键盘上的Delete键，先删除单元格中的内容；

然后输入函数：=IF(I3>=60,”是”,”否”)，按下Enter键或单击确定按钮，判断出第1个学生的成绩是否通过。

单击J3单元格，利用“填充柄”复制公式，产生其他学生的“是否通过”结果。

2.3.5函数嵌套，产生“等级”结果

选定单元格K3，输入公式：=IF(I3>=80,"优秀",IF(I3>=60,"合格","不合格"))，按下Enter键或单击确定按钮，判断出第1个学生的“等级”结果。

当然此处也可使用“函数向导”输入函数，方法同上。

单击K3单元格，利用“填充柄”复制公式，产生其他学生的“等级”结果。

2.3.6使用函数计算各科“最高分”和“最低分”

①选定单元格D14，输入公式：=MAX(D3:D12), 按下Enter键或单击确定按钮，计算出数学的最高分。单击D14单元格，利用“填充柄”复制公式，计算其他课程的最高分。

②选定单元格D14，输入公式：=MIN(D3:D12)，按下Enter键或单击确定按钮，计算出数学的最低分。单击D14单元格，利用“填充柄”复制公式，计算其他课程的最低分。

此处也可使用“函数向导”或“自动求和”命令按钮输入函数。

2.3.7使用公式计算“通过率”、“优秀率”

①选定单元格D17，输入公式：=COUNTIF(J3:J12,"=是")/COUNT(I3:I12)，按下Enter 键或单击确定按钮。

②选定单元格D18，输入公式：COUNTIF(K3:K12,"=优秀")/COUNT(I3:I12)，按下Enter 键或单击确定按钮。

注：公式中用COUNT(I3:I12)可统计出表中记录个数（即统计人数），其中I3:I12区域也可换成其他数值列，如：区域D3:D12, 区域H3:H12。

2.3.8编辑工作表

1．设置数值数据的格式

①选择单元格区域I3:I12，设置小数位数为2。

②选择单元格区域D17:D18，单击“格式”工具栏的“百分比样式”按钮，使得相应区域的数值采用百分比样式。

2．设置边框和底纹

①选择单元格区域A2:K12，单击“格式”菜单❼“单元格”，在弹出的“单元格格式”对话框中选择“边框”选项卡，选择“外边框”和“内部”，为表格设置边框。如图2-6所示。或者单击“格式”工具栏上“边框”按钮右边的小箭头，屏幕出现“边框”各小图标，单击“所有框线”进行设置。

②选择单元格区域A2:K2，单击“格式”菜单❼“单元格”，在弹出的“单元格格式”对话框中选择“图案”选项卡，在颜色列表中选择“淡灰色”，为单元格设置底纹。如图2-7所示。或者单击“格式”工具栏上“填充颜色”按钮右边的小箭头，在展开的颜色列表中选择“淡灰色”。

图2-6 “单元格格式”对话框边框选项卡图2-7 “单元格格式”对话框图案选项卡3．对表中的“等级”列设置条件格式

对等级为“优秀”的，加“淡紫色”底纹。

①选定K3:K12单元格区域。

②单击“格式”菜单☐“条件格式”，弹出“条件格式”对话框，如图2-8所示。