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单元格。