利用Excel制作学生成绩管理及成绩分析系统（一）

要求：

1.可以通过学号和姓名查询学生平时和期末考试成绩

2.平时成绩含多个记录

3.班级、学科通过按钮进行切换

4.自动统计班级考试期末情况、分数段，并通过图表进行成绩分析

5.对单科考试进行试卷分析，有题型、分值和达成值，卷面分析可随着班级和科目的改变而动态变化。

操作：

1.制作成绩查询

在打开的excel工作薄中，将工作表“sheet1”改名为“成绩查询”，再新建一个工作表，命名为“成绩记录”，在A1单元格到I1单元格中分别输入“学号”、“姓名”、“班级”、“出勤”、“作业”、“期中考试”、 “实验报告”、“作品设计”、“期末成绩”。将需要管理的数据复制到相应的单元格中。

在“成绩查询”工作表中的F5单元格中输入公式“=IF(L6="按学号","请输入学号","请选择姓名")”，F6单元格输入“过程考核查询”，F7单元格输入“期末考试查询”。G5单元格制作下拉菜单，“数据”/“数据验证”/“数据验证”，在打开的“数据验证”的“设计”选项卡中“允许”选择“序列”，“来源”中输入公式“=IF($L$6="按学号",成绩记录!A2:A199,IF($L$6="按姓名",成绩记录!B2:B199))”，

G6单元格制作下拉菜单，“来源”中输入“出勤,作业,期中考试,实验报告,作品设计”，表示“过程考核”包含这五项内容，可根据具体情况适当修改内容，G7单元格输入“期末成绩”。合并H5到L5单元格，输入“=IF(L6="按学号","Query by student number","Query by name")”，合并H6到K7单元格，输入“请选择查询方式:”，合并L6、L7单元格，制作下拉菜单，在“来源”中输入“按学号,按姓名”。合并F3到L3单元格，输入“="当前操作："&L6&"查询成绩"”，合并F9到L9单元格，输入公式“=IF(F11<>"",G11&"同学：过程考核您选择了"&G6&"，考试成绩如下:","您切换了查询方式,请选择"&IF(L6="按姓名","姓名!","学好!"))”，F10、G10单元格分别输入“学号”、“姓名”，H10、I10单元格分别输入“=G6”、“=G7”，J10到L10单元格分别输入“总评”、“等级”、“名次”。

为确保数据可靠性，制作信息验证功能，合并S9、T9单元格，输入“查询验证”，合并U9、U10单元格输入“查学号”，合并S10、T10单元格，输入“=G6”，合并S11、T11，单元格中输入“=DGET(成绩记录!$A$1:$M$169,$S$10,$G$10:$G$11)”，U11中输入“=VLOOKUP(G5,IF({1,0},成绩记录!B2:B169,成绩记录!A2:A169),2,0)”。

F11单元格输入“=IFERROR(IF(L6<>"按姓名",VLOOKUP($G$5,成绩记录!A2:M4200,COLUMN(A3),),VLOOKUP(G5,IF({1,0},成绩记录!B2:B200,成绩记录!A2:A200),2,0)),"")”；

G11中输入“=IFERROR(IF(L6<>"按姓名",VLOOKUP($G$5,成绩记录!A2:M200,COLUMN(B3),),VLOOKUP(G5,成绩记录!B2:B200,1,)),"")

H11中输入“=INDEX(成绩记录!A2:M220,MATCH(F11,成绩记录!A2:A220,0),MATCH(G6,成绩记录!A1:M1,0))”；

I11中输入“=DGET(成绩记录!$A$1:$M$169,$G$7,$F$10:$G$11)”

J11中输入“=IF(L6="按学号",VLOOKUP($G$5,成绩记录!A1:M81,MATCH($J$10,成绩记录!1:1,0),0),INDEX(成绩记录!A:M,MATCH($G$5,成绩记录!B:B,0),MATCH($J$10,成绩记录!1:1,0)))”

K11中输入“=IFERROR(IF(I11<>"缺考",IF(J11="","",IF(J11<60,"差",IF(J11<=70,"中",IF(J11<85,"良","优")))),""),"")”

L11中输入“=VLOOKUP(F11,成绩记录!A2:M220,13)”。

注意：为提高对函数的理解，采用了不同函数进行查询。

在F2单元格输入“班级：”，在V2到V5单元格中输入班级名称（制作切换班级按钮时使用），如：“2019级环境科学、2019级数学与应用数学、2019级农业资源与环境、2017级跨专业”。

班级切换按钮制作：光标定位在任何位置，单击“开发工具”/“插入”，在“表单控件”中选择“组合框（窗体控件）”，在表格中拖动鼠标，画出一个按钮，右键按钮，在出现的菜单中单击“设置控件格式”，在“设置控件格式”的“数据源区域”中输入“$V$2:$V$5”，或者用鼠标直接拖选V2到V5区域，“单元格链接”中输入“$V$1”，“下拉显示项数”中输入4（因为只有4个班级），单击“确定”，此时在V1单元格中输入1，可以看到在按钮中已经显示出班级名称，调整按钮大小，拖放至F1单元格的“班级：”右侧。

G1单元格输入“=IF(V1=1,"2019级环境科学与工程",IF(V1=2,"2019级数学与应用数学",IF(V1=3,"2019级农业资源与环境",IF(V1=4,"2017级跨专业"))))”，通过切换按钮，可以在G1单元格中显示班级名称。

学科切换按钮制作：在H1单元格中输入“学科:”，单击“开发工具”/“插入”，在“表单控件”中选择“数值调节钮（窗体控件）”，在表格中拖动鼠标，画出一个按钮，右键按钮，在出现的菜单中单击“设置控件格式”，在“设置控件格式”的“当前值”输入“1”，最小值输入“1”，“最大值”输入“3”（学科只有3门），“步长”输入“1”，“单元格链接”输入“$S$1”，单击“确定”，此时在S1单元格中输入“1”，调整按钮大小，拖放至H1单元的“班级：”右侧。点击上下前头可以进行学科切换。

设置表格格式。

（未完）

本案例通过公众号“officer帮帮忙”、“老徐的excel”、“千万别学excel” 学习完成，需要更多的内容，请关注公众号。