Excel练习题及解题分析

第一部分：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单元格→使用“插入/ 函数”菜单命令→“插入函数”对话框中，设定选择类别为“全部”、选择函数为“average”→在“函数参数”对话框中设置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）在“原始数据”表中选中“B1：B6”数据域，按住Ctrl键后，再选“D1：F6”数据域；
（3）选用“插入/ 图表”菜单命令，设置图表类型为“柱形图”，子图表类型为“簇状柱形图”，注意系列产生在“行”，图表标题的输入，以及图例的位置；
（4）图表生成时注意插入到的应该是“五科前五三科比较”工作表；
（5）调整图表在工作表中的大小和位置。