2.成绩分析
新建一个工作表,命名为“成绩分析”在E1单元格中输入“班级:”F1单元格输入公式“=IF(R1=1,"2019级环境科学与工程",IF(R1=2,"2019级数学与应用数学",IF(R1=3,"2019级农业资源与环境",IF(R1=4,"2017级跨专业"))))”,R2、R3、R4、R5单元格分别输入班级名称,如:2019级环境科学、2019级数学与应用数学、2019级农业资源与环境、2017级跨专业等,R1输入“1”。
在G1单元格制作班级切换按钮:光标定位在任何位置,单击“开发工具”/“插入”,在“表单控件”中选择“组合框(窗体控件)”,在表格中拖动鼠标,画出一个按钮,右键按钮,在出现的菜单中单击“设置控件格式”,在“设置控件格式”的“数据源区域”中输入“$R$2:$R$5”,或者用鼠标直接拖选V2到V5区域,“单元格链接”中输入“$R$1”,“下拉显示项数”中输入4(因为只有4个班级),单击“确定”,可以看到在按钮中已经显示出班级名称,调整按钮大小,拖放至G1单元格。点击按钮可进行班级切换。
H1单元设置下拉菜单,来源中输入科目位置“=$U$2:$U$3”,U2、U3中分别输入“计算机基础与应用”、“计算机组装与维护”。
合并I1到K1单元格,输入“期末考试成绩分析”。
合并E2到E5单元格,输入“考试基本情况”,合并E7到E11单元格,输入“各分数段情况”,F2到F5单元格中分别输入“原有人数”、“实考人数”、“缺考人数”、“优秀人数”。F7到F11单元格分别输入“90-100分”、“80-89分”、“70-79分”、“60-69分”、“60分以下”。
G2、G3单元中分别输入公式“=COUNTIF(成绩记录!C2:C169,F1)”;
G4单元中输入公式“=COUNTIFS(成绩记录!C2:C169,F1,成绩记录!I2:I169,0)”
G5单元中输入公式“=COUNTIFS(成绩记录!C2:C169,F1,成绩记录!I2:I169,">=85")”。
G7单元中输入公式“=COUNTIFS(成绩记录!C2:C169,F1,成绩记录!I2:I169,">=90")”;
G8单元中输入公式“=COUNTIFS(成绩记录!C2:C169,F1,成绩记录!I2:I169,">=80",成绩记录!I2:I169,"<90")”;
G9单元中输入公式“=COUNTIFS(成绩记录!C2:C169,F1,成绩记录!I2:I169,">=70",成绩记录!I2:I169,"<80")”;
G10单元中输入公式“=COUNTIFS(成绩记录!C2:C169,F1,成绩记录!I2:I169,">=60",成绩记录!I2:I169,"<70")”;
G11单元中输入公式“=COUNTIFS(成绩记录!C2:C169,F1,成绩记录!I2:I169,"<60")”。
H2到H5单元格中分别输入“及格人数”、“缺考率”、“及格率”、“优秀率”,合并H7到H10单元格,输入“自定义等级”,H11中输入“最高成绩姓名”。
I2单元格输入公式“=COUNTIFS(成绩记录!C2:C169,F1,成绩记录!I2:I169,">=60")”,I3单元格输入“=G4/G3”,I4中输入“=I2/G3 ”,I5中输入“=G5/G3”,I7到I10单元格中分别输入“A级”、“B级”、“C级”、“D级”,I11单元格输入公式“=INDEX(IF(成绩记录!C$2:C$169=$F$1,成绩记录!$B$2:$B$169,""),MATCH(MAX(IF((成绩记录!$C$2:$C$169=$F$1),成绩记录!$I$2:$I$169,"")),IF(成绩记录!$C$2:$C$169=$F$1,成绩记录!$I$2:$I$169,""),0))”,这是一个数组公式,输入完成后,按下组合键“Ctrl Shift Enter”。
J2到J5单元格中分别输入“平均分”、“最高分”、“最低分”、“差生率”,J7到J10单元格中分别输入“>=85”,“>=70”,“>=60”,“<60”,J11单元格输入“最低成绩姓名”。
K2中输入“=AVERAGEIF(成绩记录!C2:C169,F1,成绩记录!I2:I169)”,K3中输入“=MAX(IF(成绩记录!C2:C169=F1,成绩记录!I2:I169))”,K4中输入“=MIN(IF(成绩记录!C2:C169=F1,成绩记录!I2:I160))”,这是两个是数组公式,输入完成后,按下组合键“Ctrl Shift Enter”。
K5中输入“=(G3-I2)/G3”。
K7中输入“=COUNTIFS(成绩记录!C2:C169,F1,成绩记录!I2:I169,">=85")”
K8中输入“=COUNTIFS(成绩记录!C2:C169,F1,成绩记录!I2:I169,">=70",成绩记录!I2:I169,"<85")”
K9中输入“=COUNTIFS(成绩记录!C2:C169,F1,成绩记录!I2:I169,">=60",成绩记录!I2:I169,"<70")”
K10中输入“=COUNTIFS(成绩记录!C2:C169,F1,成绩记录!I2:I169,"<60")”
K11中输入“=INDEX(IF(成绩记录!C$2:C$169=$F$1,成绩记录!$B$2:$B$169,""),MATCH(MIN(IF((成绩记录!$C$2:$C$169=$F$1),成绩记录!$I$2:$I$169,"")),IF(成绩记录!$C$2:$C$169=$F$1,成绩记录!$I$2:$I$169,""),0))”,这是一个是数组公式,输入完成后,按下组合键“Ctrl Shift Enter”。
利用图表分析成绩
C15到F15中分别输入“成绩A级”、“成绩B级”、 “成绩C级”、 “成绩D级”。
C16到F16单元格中分别输入“=K7”、“=K8”、“=K9”、“=K10”。
制作饼图:选中C15到F16区域,单击“插入”/“图表”右下角“推荐的图表”按钮,单击“所有图表”选项卡,选择“饼图”,单击“确定”,在“图表设计”的“图表样式”中选择适当的样式,添加图表标题“成绩比例分析图”。
制作带箭头的柱型图:选中$C$15:$F$16区域,单击“插入”/“图表”/“柱状图”,选择“堆积柱形图”,在工作表数据区域任何位置绘制一个箭头,并设置相应颜色,选中箭头并复制,然后右击已制作的图表柱状系列,选取“设置数据系列格式”命令,在“设置数据系列格式”对话框的“填充”选项卡中的“图片或纹理”,单击“剪贴板”,excel将绘制的箭头取代了图表中的柱形条。设置图表标题为“学生成绩直观分析”。
调整两个图表大小,将其图表放置到A13:N19区域中,将图表数据源覆盖。当点击G1的按钮选择“班级”并在H1单元格的下拉菜单中选择科目后,F1的班级、H1的科目也随着改变,成绩分析工作表中所有数据和图表也发生变化,实现动态成绩分析。