Excel练习题及解题分析.doc

第一部分：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单元格 使用“插入 / 函数”菜单命令 “插入函数”对话框中，设定选择类别为“全部”、选择函数为“rank” 在“函数参数”对话框中设置Number1单元格区域为“L2”、Ref单元格区域为“$L$2：$L$26”、Order值为“0”（如下图所示），单击确定按钮 使用自动填充柄工具拖曳到N26单元格。参数说明：Number代表需要排序的数值；Ref代表排序数值所处的单元格区域；Order代表排序方式参数（如果为“0”或者忽略，则按降序排名，即数值越大，排名结果数值越小； 使用min函数计算五门单科最低分选中D27单元格 使用“插入 / 函数”菜单命令 “插入函数”对话框中，设定选择类别为“全部”、选择函数为“min” 在“函数参数”对话框中设置Number1单元格区域为“D2：D26”（如下图所示），单击确定按钮 设定单元格数据保留一位小数 使用自动填充柄工具拖曳到H27单元格。 使用max函数计算五门单科最高分选中D28单元格 使用“插入 / 函数”菜单命令 “插入函数”对话框中，设定选择类别为“全部”、选择函数为“max” 在“函数参数”对话框中设置Number1单元格区域为“D2：D26”（如下图所示），单击确定按钮 设定单元格数据保留一位小数 使用自动填充柄工具拖曳到H28单元格。第（3）题：选中O2单元格 使用“插入 / 函数”菜单命令 “插入函数”对话框中，设定选择类别为“全部”、选择函数为“if” 在“函数参数”对话框中设置如下图所示（注意：双引号为西文），单击确定按钮 使用自动填充柄工具拖曳到O26单元格。第（4）题：鼠标双击“sheet2”工作表标签，输入“分类汇总”字符 鼠标右击“分类汇总”工作表标签，选用快捷菜单中的“工作表标签颜色”，设置为鲜绿色 选中“原始数据”表中的A1：O26数据域后复制 选中“分类汇总”表A1单元格后粘贴复制的数据 选中“分类汇总”表A1：O26数据域，以“性别”为主要关键字进行“升序”排序 继续选中表A1：O26数据域，选用“数据 / 分类汇总”菜单命令 在“分类汇总”对话框中，设置分类字段为“性别”、汇总方式为“平均值”、选定汇总项为“数学”、“语文”、“英语”、“物理”和“化学”（如下图所示） 单击确定按钮退出对话框。第（5）题：鼠标双击“sheet3”工作表标签，输入“需关注生”字符 选中“原始数据”表中的A1：O26数据域后复制 选中“需关注生”表A1单元格后粘贴复制的数据 选中“需关注生”表D2：H26数据域 选用“格式 / 条件格式”菜单命令 在“条件格式”对话框中，设置如下图所示（单击“格式”按钮，可设置符合条件的数据格式，例如“加粗”、“红色”等） 单击确定按钮退出对话框。第（6）题：该题是一个比较普通的根据数据做图表的操作题，需要注意以下几点：（1）首先，必须在“需关注生”工作表后，新建一张名称为“五科前五三科比较”的工作表；（2）在“原始数据