Excel 实战:学生成绩查询

某大学语言测试成绩表如图15-35所示。现在需要实现只输入学生姓名,就能够查询某一学生的成绩或其他信息。

图15-35 学生成绩表

下面介绍如何使用查找与引用函数中的INDEX函数和MATCH函数来实现这种查询功能。

步骤1:选中H3单元格,输入以下公式,如图15-36所示。


=INDEX(E:E,MATCH($G$3,$A:$A,0))

图15-36 在单元格H3输入公式

说明:该公式使用INDEX函数返回成绩表中$G$3所在行对应E列的值,使用MATCH函数查找成绩表中A列与单元格$G$3相同的值。

步骤2:按Enter键确认公式输入。

步骤3:在G3单元格中输入一个学生的姓名,然后按Enter键,可以看到在H3单元格中显示出其成绩,如图15-37所示。

图15-37 输入姓名查询成绩

步骤4:在H5单元格中输入以下公式,如图15-38所示。


=INDEX(E:E,MATCH($G$5,$B:$B,0))

图15-38 在单元格H5中输入公式

说明:该公式使用INDEX函数返回成绩表中$G$5所在行对应E列的值,使用MATCH函数查找成绩表中A列与单元格$G$5相同的值。

步骤5:按Enter键确认公式输入。

步骤6:在G5单元格中输入一个学生的准考证号码,然后按Enter键,可以看到在H5单元格中显示出其成绩,如图15-39所示。

图15-39 使用准考证号码查询学生成绩

Excel 统计某个单项成绩小于80分的最低总成绩

统计某一列数据的最低纪录时,还可通过指定具体条件来统计。以统计成绩表中某一列的成绩小于80分的最低总成绩为例,操作如下。

01 打开需要统计的工作表,选中“A19:F19”单元格区域,在其中输入与表格相同的列标题,选中“D20”单元格,输入“<80”,将其作为计算的条件区域。

02 选中需要显示结果的单元格,在其中输入公式:=DMIN(A2:F10,6,A19:F20),按下“Enter”键确认,即可得到“成绩3”大于90的学生的最高总成绩了。

alt

注意 使用条件范围统计最小纪录时,如果表格中的数据都不满足条件,则将返回“0”。例如若指定本例的条件为“<60”,输入公式后确认,返回值即为“0”。

Excel 统计某个单项成绩大于90分的最高总成绩

统计某一列数据中的最高纪录时,还可以通过指定具体的条件来统计。下面以统计成绩表中某一列的成绩大于90分的最高总成绩为例,具体操作如下。

01 打开需要统计的工作表,选中“A15:F15”单元格区域,在其中输入与表格相同的列标题,选中“E16”单元格,输入“>90”,将其作为计算的条件区域。

02 选中需要显示结果的单元格,在其中输入公式:=DMAX(A2:F10,6,A15:F16),按下“Enter”键确认,即可得到“成绩3”大于90的学生的最高总成绩了。

alt

小提示 使用条件范围统计最高纪录时,如果表格中的数据都不满足条件,则将返回“0”。例如若指定本例的条件为“>98”,输入公式后确认,返回值即为“0”。

Excel 计算A组的平均成绩:DAVERAGE函数

DAVERAGE函数用于对列表或数据库中满足指定条件的记录字段(列)中的数值求平均值。

DAVERAGE函数的语法为:=DAVERAGE(database, field, criteria),各参数的含义介绍如下。

※ database:构成列表或数据库的单元格区域,或者单元格区域的名称。

※ field:指定函数所使用的数据列。

※ criteria:为一组包含给定条件的单元格区域。

下面以计算成绩表中A组学生的平均成绩为例,具体操作如下。

01 打开工作表,输入学生姓名、团队、各项成绩和总成绩等相关数据。

02 选中“A15:F16”单元格区域,在其中输入与表格相同的列标题,将其作为计算的条件区域。

03 选中需要显示结果的单元格,在其中输入公式:=DAVERAGE(A2:F10,6,A15:F16),按下“Enter”键确认,可得到A组学生的平均成绩。

alt

Excel 计算A组总成绩:DSUM函数

如果需要返回数据库的列中满足指定条件的数字之和,可通过DSUM函数实现。DSUM函数的语法为:=DSUM(database,field,criteria),各参数的含义介绍如下。

※ database:构成列表或数据库的单元格区域,或者单元格区域的名称。

※ field:指定函数所使用的数据列。

※ criteria:为一组包含给定条件的单元格区域。

下面以计算A组学生的比赛总成绩为例,具体操作如下。

01 打开比赛成绩表,输入每个学生的比赛相关信息,然后选中需要显示该学生总成绩的表格,输入公式:=C3+D3+E3,然后按下“Enter”键,得到此名学生此次比赛的总成绩。

02 使用填充柄功能将公式复制到此列中的其他单元格中,得到所有参加比赛的学生总成绩。

03 将A12:F13作为检索的条件区域,即将表格的标题栏复制到此区域中,然后在B13单元格中输入“A”。

04 选中“B14”单元格,在其中输入公式:=DSUM(A2:F10,6,A12:F13),按下“Enter”键确认,得到A组学生的比赛总成绩。

注意 用户可以为参数criteria指定任意区域,只要此区域包含至少一个列标签,并且列标签下方包含至少一个用于指定条件的单元格。

alt

Excel 计算多个工作表中成绩的最大平均值

假设3个班的成绩分别置于同一个工作簿的3个工作表中,现在需要计算3个班的最高平均成绩是多少。

01 打开工作簿,分别在3个工作表中输入3个班的成绩,其中单元格区域须一致,如A2:A6为学生姓名,B2:B6为成绩。

02 在单元格中输入公式:=MAX (SUBTOTAL(1, INDIRECT({“1″,”2″,”3″}&”班!B2:B6”))),按下“Enter”键确认,得到3个班中的最高平均成绩。

alt

小提示 本例先利用常量数组分别表示3个工作表的数据区,接着利用INDIRECT函数将字符串转换为区域引用,而AVERAGE函数无法跨表计算平均值,故使用SUBTOTAL函数计算平均值,最后则使用MAX函数求取最大值。

Excel 利用IF函数自动给出成绩的优劣等级

利用IF函数的判断结果,可以对学生成绩给出优劣等级。假设等级条件为:总成绩大于600为优,550~599为良,500~549为中,450~499为差,小于450为劣。具体操作如下。

01 在工作表中输入需要给优劣等级的成绩。

02 在G3单元格中输入公式:=IF(F3>600,”优”,IF (F3>550,”良”,IF(F3>500,”中”,IF(F3>450,”差”,”劣”)))),然后按下“Enter”键确认。

03 利用填充柄将公式复制到其他单元格即可。

alt

Excel 能否形象化地展示跳远成绩?

图11.32中的数据是8个运动员的跳远成绩,能否使用图表形象化地展示出这些数据?

图11.32 跳远成绩

解题步骤

跳远成绩宜用直方图来展示,具体操作步骤如下。

1.选择 A1 单元格,然后单击功能区的“插入”→“插入柱形图或条形图”→“簇状条形图”,默认生成的图表效果如图11.33所示。

图11.33 条形图的默认效果

2.双击图表系列(本图表的系列“成绩(米)”由8个系列点组成,双击任意点皆可),从而在工作表右方产生“设置数据系列格式”任务窗格。

3.在任务窗格中将分类间距修改为5%,此时图表将显示为图11.34所示的效果。

4.从“格式”选项卡左上角的“图表元素”列表中选择“水平 (值) 轴主要网格线”,然后按下Delete键删除网格线。

5.双击水平轴,然后在工作表右方的任务窗体中将最小值修改为0,将最大值修改为7,设置界面如图11.35所示。

图11.34 将分类间距调整5%

图11.35 修改水平轴的最大值和最小值

6.选择图表的系列,然后单击功能区的“设计”→“添加图表元素”→“数据标签”→“数据标签外”,此时图表将显示为图11.36所示的效果。

图11.36 添加数据标签

知识扩展

1.直方图和柱形图的区别在于方向不同,其他方面都没有区别。

2.当图表中只有单个系列时,图表不会生成图例。尽管可以手工将图例调用出来,但是在这种前提下图例已失去了存在价值,可以不用显示图例。

3.图表系列的默认分类间距修改为5%后,系列点变得更宽,从而使图表更美观,同时保留了5%的间距,使图表系列看起来像一条一条跑道,配合“跳远”这个主题,使图表更加形象化。

Excel 2016 如何展示成绩升降趋势线?

图 11.1 是某学生一年级到六年级的语文成绩和数学成绩,查看文字很难瞬间判断成绩升降趋势,能否将数字图形化,从而瞬间判断数据的变化趋势呢?

图11.1 成绩表

解题步骤

折线图可以展示数据的升降趋势,设计折线图的步骤如下。

1.选择 A1 单元格,打开“插入”选项卡,然后单击“插入折线图或面积图”右方的倒三角按钮,最后从列表中选择“折线图”。图11.2展示了“折线图”的位置,而图11.3则为单击菜单后生成的默认样式的折线图。

图11.2 “折线图”位置展示

图11.3 折线图的默认样式

2.单击图表的标题,然后输入“成绩走势图”。

3.选择图例,并将它移到图表的右上角,然后将绘图区向下拉大,从而让绘图区填满下方的图表区。图11.4所示的箭头表示在该处按下鼠标拖动即可调整绘图区大小,图11.5是调整后的效果。

图11.4 扩大绘图区

图11.5 扩大绘图区后的效果

查看图表时的重点在于垂直轴的刻度、图例和图表系列,其中图例的功能是通过颜色来区分图表系列,图表系列则是图表的重中之重,它用于展示数据的大小和发展趋势。

图 11.6 展示了一个图表的基本组件,其中浮在网格线上方的两条折线即为图表系列,它们展示了语文和数学成绩的发展趋势。

图11.6 图表基本组件说明

从图11.6中可以看到语文成绩和数学成绩都在上下波动,但是整体上语文成绩呈上升趋势、数学成绩呈下降趋势。

如果要让图表系列显示数据,应按以下方式操作。

1.单击系列“语文”,然后单击功能区的“设计”→“添加图表元素”→“数据标签”→“上方”。

2.以同样的方式对系列“数学”添加数据标签,最终效果如图11.7所示。

图11.7 为图表系列添加数据标签

知识扩展

1.使用图表展示数据时有一个基本规则:如果没有特别指定图表的种类,那么当数据源不存在时间递进关系时,应使用柱形图或条形图,反之采用折线图。

本例中的数据来自六个年级的成绩,而这六个年级存在时间递进关系,因此宜用折线图展示数据的发展变化趋势。对于图11.8这种数据,则宜用柱状图或条形图,图表效果如图11.9所示。

图11.8 成绩表

图11.9 柱状的成绩比较图

线折图通常展示数据的变化趋势,柱状图则用于比较大小。当然也可以在柱状图中添加趋势线表明趋势,或者直接在一个图表中同时呈现柱形和折线,效果如图11.10所示。

图11.10 柱形与折线同时存在于一个图表中

图11.10的设计过程不再讲解,但是会提供一个已经设计好的成品供读者下载学习。

2.如果要突出显示数据的变化、差异,将数值轴(也称垂直轴)的最大值和最小值的差值缩小即可。操作方法是双击数值轴,然后在工作表右方的任务窗格中将最小值由 0 修改为 50,将最大值由100修改为95。图11.11是数值轴设置界面,图11.12则是调整后的效果。

图11.11 缩小最大值与最小值的差值

图11.12 调整后的图表效果

3.如果需要让图表显示效果更美观,如将图表区调整为圆角、阴影,那么可以双击图表靠边框的空白区域,然后在右方的任务窗格中依次单击“填充与线条”→“边框”→“圆角”,然后再依次单击“效果”→“阴影”→“右下斜偏移”。圆角与阴影的设置界面见图11.13和图11.14,而图11.15则是设置完成后的图表效果。

图11.13 让图表区显示为圆角

图11.14 让图表区显示为阴影

图11.15 添加圆角和阴影后的图表

Excel 是否可以根据姓名和科目自动查找对应成绩?

图 7.53 所示的成绩表中行标题为姓名,列标题为科目,能否在输入姓名和科目名称后自动给出对应的成绩?

成绩表

解题步骤

根据已知的两个条件查询对应的数据,达成此需求有多种方法,其中使用公式灵活性最好,具体操作步骤如下。

1.在I1:K1中分别输入“姓名”、“科目”和“成绩”。

2.在I2:J2分别输入“朱明”和“化学”。

3.在K2单元格输入以下公式:

此时公式返回朱明的化学成绩94,效果如图7.54所示。

图7.54 查找朱明的化学成绩

4.将姓名修改为“曹锦荣”,将科目修改为“语文”,此时K2的公式会返回成绩“100”(见图7.55)。

图7.55 查找曹锦荣的语文成绩

知识扩展

1.MATCH函数用于计算一个字符串在一维数组或单行/单列区域的出现位置。例如,在图7.55中,曹锦荣在A2:C11区域中的出现位置是第8位,那么公式“=MATCH(I2,A2:A11,0)”的返回值就是8。MATCH函数的语法如下:

其中第一参数是查找对象,第二参数是一维数组或单列区域/单行区域,第三参数则用于控制查找方式,赋值为0时表示精确查找,赋值为1或-1时表示模糊查找,工作中用得最多的是精确查找。简言之,MATCH函数的功能就是在第二参数中查找第一参数的位置,并由第三参数决定查找方式。

2.INDEX函数的功能是在一个区域或数组中按位置查找对应的值,由于MATCH函数的功能正是计算位置,因此INDEX函数经常搭配MATCH函数使用,本例正是此类型的应用案例。

3.在本例公式中,第一个MATCH函数用于计算纵向位置,第二个MATCH函数用于计算横向位置,两者的交叉点刚好是B2:G11区域中对应的成绩。

4.了解公式的含义和计算过程的最好方法是使用公式求值工具。以图7.54中的公式为例,了解公式的方法是选中K2单元格,然后单击功能区的“公式”→“公式求值”,并在对话框中单击“求值”按钮,图7.56所示的窗口中表达式“MATCH(I2,A2:A11,0)”的下画线表明下一步会计算这个表达式,此时再次单击“求值”按钮,对话框会变为图 7.57 所示的效果,图中说明了两个问题,一是表达式“MATCH(I2,A2:A11,0)”的计算结果为6,二是下一步要计算的表达式是J2的值。

图7.56 表示下一步要计算MATCH(I2,A2∶A11,0)

图7.57 表明下一步要计算J2

再次单击“求值”按钮,窗口内容会变成图7.58所示的状态,表示J2的计算结果是“化学”,下一步要计算的表达式是“MATCH(J2,B1:G1,0)”。

图7.58 表明下一步要计算MATCH(J2,B1∶G1,0)

再次单击“求值”按钮,窗口内容会变成图7.59所示的状态,表示“MATCH(J2,B1:G1,0)”的计算结果是4,下一步要计算的表达式是“INDEX(B2:G11,6,4))”。

图7.59 表明下一步计算INDEX(B2∶G11,6,4))

再次单击“求值”按钮,窗口内容会变成图7.60所示的状态,表明公式的最终结果是94。

图7.60 公式的最终计算结果