Excel练习及解题分析.docVIP

第一部分：Excel 打开“学生成绩表.xls”，可以看到如下图所示的Excel工作表，请按照下面各小题中的要求对学生的成绩数据进行处理，所有的数值型数据处理结果均保留1位小数： 一、操作要求： （1） 使用用户自定义公式，计算“五科总分”（ 五科总分 = 数学 + 语文 + 英语 + 物理 + 化学 ）、“五科平均”（ 五科平均 =（ 数学 + 语文 + 英语 + 物理 + 化学 ）/ 5），并通过“排序”的方式填写“五科名次”的相关数据； （2） 使用Excel中的相关函数，计算“三科总分”、“三科平均”和“三科名次”（三科是指“数学”、“语文”和“英语”），以及五门学科的最高分、最低分； （3） 使用Excel中的函数计算成绩表中“是否通过”列中的数据，判断依据是“五科平均≥85”者，填写“优秀”，“五科平均≥60”者，填写“合格”，“五科平均＜60”者，填写“不合格”； （4） 将“原始数据”表中的数据（A1：O26）复制到sheet2表中，并重命名sheet2表名为“分类汇总”，表标签颜色为“鲜绿色”，然后统计男女生及全班的各科平均分； （5） 将“原始数据”表中的数据（A1：O26）复制到sheet3表中，并重命名sheet3表名为“需关注生”，然后将该表五门学科中不及格的分数用红色、加粗形式凸显出来； （6） 在“需关注生”表后插入将名称为“五科前五三科比较”工作表，五科总分前五名的学生的“语文”、“数学”和“英语”三门成绩设计为簇状柱形图，并将图表保存在名称为“五科前五三科比较”工作表中，作图区域为A1：H20。 二、考查知识点： （1）基本操作，例如新表的插入、重命名以及表标签的颜色设置等； （2）用户自定义公式及各种函数的使用，包含常用函数sum，average，max，min，rank，if函数等； （3）条件格式的设定应用； （4）分类汇总功能的应用； （5）根据样张制作数据图表。 三、分析： （1）适用范围：Excel函数多用于对学生成绩等数据进行处理。 （2）解题分析：对于使用过Excel函数功能的人而言，sum，average，max，min都是比较熟悉的内容，相对较简单，但是rank，countif，if则相对不熟悉，所以在在备考过程中可以适当扩大复习范围，尽量熟悉各种常用函数，如果考试时遇到的函数是从来没见过的，也不必惊慌，可以通过Excel的帮助或者“搜索函数”查到所需函数的使用方法。 四、解题过程： 第（1）题： ① 计算“五科总分” 单击选中I2单元格 → 键盘输入“＝”→ 单击D2单元格 → 键盘输入“+” → 单击E2单元格 → 键盘输入“+” → 单击F2单元格 → 键盘输入“+” → 单击G2单元格 → 键盘输入“+” → 单击H2单元格 → 单击编辑栏左侧的“输入”按钮（绿色钩） → 设定单元格数据保留一位小数 → 使用“自动填充柄”工具拖曳到I26单元格。 ② 计算“五科平均” 单击选中I2单元格 → 在编辑栏中复制自定义的公式“=D2+E2+F2+G2+H2” → 单击编辑栏左侧的“输入”按钮 → 单击选中J2单元格 → 在编辑栏中中粘贴复制的公式，将公式修改为“=（D2+E2+F2+G2+H2）/5” → 单击编辑栏左侧的“输入”按钮 → 设定单元格数据保留一位小数 → 使用“自动填充柄”工具拖曳到J26单元格。 ③ 通过“排序”填写“五科名次”数据 　　选中A2：O26数据区域 → 选用“数据 / 排序”菜单命令 → “排序”对话框中设定“主要关键字”为“五科总分”、“降序”，单击“确定”按钮退出对话框 → 在K2单元格中输入“1”，在K3单元格中输入“2” → 选中K2、K3单元格后，使用自动填充柄拖曳到K26单元格。 第（2）题： ① 使用sum函数计算“三科总分” 选中L2单元格 → 使用“插入 / 函数”菜单命令 → “插入函数”对话框中，设定选择类别为“全部”、选择函数为“sum” → 在“函数参数”对话框中设置Number1单元格区域为“D2：F2”（如下图所示），单击确定按钮 → 设定单元格数据保留一位小数 → 使用自动填充柄工具拖曳到L26单元格。 ② 使用average函数计算“三科平均” 选中M2单元格 → 使用“插入 / 函数”菜单命令 → “插入函数”对话框中，设定选择类别为“全部”、选择函数为“average” → 在“函数参数”对话框中设置Number1单元格区域为“D2：F2”（如下图所示），单击确定按钮 → 设定单元格数据保留一位小数 → 使用自动填充柄工具拖曳到M26单元格。 ③ 使用rank函数计算“三科名次” 选中N2单元格 → 使用“插入 / 函数”菜单命令 → “插入函数”对话框中，设定选择类别为“全部”