实验七公式与函数的使用.doc

实验七 公式与函数的使用 实验七 公式与函数的使用 2.1 实验内容 利用公式、函数对图2-1所示的“学生成绩表”实现自动计算，统计出每个学生的总分、平均分、是否通过、等级；统计各科的最高分、最低分；统计所有学生成绩的通过率、优秀率 要求： 1．利用函数产生学生的“是否通过”结果：平均分在60分以上计为“是”，60分以下计为“否” 2．“等级”结果分三档：平均分在80分以上计为“优秀”，60-80之间为“合格”， 60分以下为“不合格” 3．“通过率”是计算“是否通过”结果为“是”的学生占总人数的百分比 4．“优秀率”是计算“等级”为“优秀”的的学生占总人数的百分比 图2-1 学生成绩表 2.2 实验目的 纯熟掌握工作表中公式、常用函数的使用 纯熟掌握公式的复制操作 2.3 实验步骤 2.3.1新建工作薄，建立工作表 启动Excel2023后，在新工作簿的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,\优秀\合格\不合格\，按下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,\是\，按下Enter键或单击确定按钮 ②选定单元格D18，输入公式：COUNTIF(K3:K12,\优秀\，按下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所示 ③在“条件1”的第二个下拉列表中选择“等于”选项，在右边文本框中输入“优秀”。

④单击“格式”按钮，在弹出“单元格格式”对话框中选择“图案”选项卡，再从颜色”列表中选择“淡紫色”，单击“确定”按钮，返回“条件格式”对话框 ⑤设置完后，“条件格式”对话框中的单击“确定”按钮 之后，将“邓必勇”的英语成绩加5分，观察其等级和底纹的变化是否正确 图2-7 “条件格式”对话框 图2-8 最终表格效果 2.3.9保存〔保护〕工作簿 以上操作完成之后〔最终效果如图2-8所示〕，保存该工作簿，同时可对文件设置翻开密码 ①单击“文件”菜单?“保存”〔或“另存为”〕， 在“另存为”对话框中设置好保存位置和文件名〔文件名为“成绩表”〕 ②在“另存为”对话框中选择“工具”?“常规选项”，弹出如图2-9所示的保存选项对话框在“翻开权限密码”文本框中输入密码，单击“确定”按钮 ③在弹出的“确认密码” 对话框中再输入一遍密码，单击“确定”按钮，最后单击“另存为”对话框中的“保存”按钮 图2-9 “保存选项”对话框 2.4 理论题 请新建一个工作簿，在新工作簿的Sheet1中建立如图2-10所示的工资汇总表再用条件函数处理电费、水费，并计算出实发工资〔电费、水费的收取方法为：假如用电量没有超出50度，应按平价电费收费，假如用电量超过50度，那么超过局部按议价电收费；假如用水没有超过20吨，应按平价水费收费，假如用水量超过20吨，那么超过局部按议价水费收费。

〕 图2-10 工资汇总表 第 页 共 页。