2019教资成绩查询,利用Excel制作学生成绩管理及成绩分析系统（三）

3 .设置动态试卷分析表

创建名为“试卷分析”的工作表，结合D2和E2的单元格，在“测试时间”、F2的单元格中创建测试时间，在G2中创建“名称”，在H2中创建“期末考试”，在I2中创建“科目”、J2的单元格中创建下拉菜单科目之间用英语逗号隔开。 例如，合并“计算机基础测试”L2、M2的单元以创建类的下拉菜单，并在源中输入类名称。 班级之间用英语逗号隔开，例如，输入“2019级环境科学、2019级数学和应用数学、2019级农业资源与环境、2017级交叉专业”，N2输入“教师姓名”，F2输入“提交时间”。

合并D2-E2单元格，输入“试卷”，在F3-F6单元格中分别输入“题号”、“题型”、“分数”、“达成值”，在G3-L3单元格中分别输入题号“1”、“2”、“3”

在G4单元中输入"=if($j$2=)计算机基础和应用)、"计算机基本操作""填充" "的公式，如果J2单元是"计算机基础和应用"则显示为"计算机期末操作"否则显示为"填充"

在H4小区中输入“=if($j$2=&； #039； 计算机基础与应用&； #039；&； #039； wimdows&； #039；&； #039； 选择题&； #039； )中输入。

在I4单元格中输入“=if($j$2=&； #039； 计算机基础与应用&； #039；&； #039； word&； #039；&； #039； 判断问题&； #039； )”

在J4单元格中输入“=if($j$2=&； #039； 计算机基础与应用&； #039；&； #039； excel&； #039；&； #039； 简单解答&； #039； )中输入。

在K4单元格中输入“=if($j$2=&； #039； 计算机基础与应用&； #039；&； #039； powerpoint&； #039；&； #039； 综合问题&； #039； )中输入。

在L4单元格中输入“=if($j$2=&； #039； 计算机基础与APP应用&； #039；&； #039； 网络基础&； #039；&； #039； &； #039； )”，则J2单元格为&； #039； 计算机基础与APP应用&； #039； 如果是，则表示“网络基础”，否则为空。 另一节课表示没有六个问题。

在G5中输入“=if($g$4=&； #039； 计算机基本操作&； #039；&； #039； 5&； #039；&； #039； 10&； #039； )中输入。

在H5中输入“=if($h$4=&； #039； wimdows&； #039；&； #039； 15&； #039；&； #039； 20&； #039； )中输入。

在I5中输入“=if($I$4=&； #039； word&； #039；&； #039； 30&； #039；&； #039； 10&； #039； )中输入。

在J5中输入“=if(j4=&； #039； excel&； #039；&； #039； 25&； #039；&； #039； 40&； #039； )中输入。

在K5中输入“=if(k4=&； #039； PowerPoint&； #039；&； #039； 15&； #039；&； #039； 20&； #039； )中输入。

在L5中输入“=if(L4=&； #039； 网络基础&； #039；&； #039； 10&； #039；&； #039； )”

在g6-l6单元格中分别输入数组公式。

"=average(if ) r8:r1000=L2，U8:U1000 ) )/G5 "

"=average(if ) r8:r1000=L2，V8:V1000 ) )/H5 "

"=average(if ) r8:r1000=L2，W8:W1000 ) )/I5 "

(=average(if ) r8:r1000=L2，X8:X1000 ) ) )/J5 "

"=average(if ) r8:r1000=L2，Y8:Y1000 ) )/K5 "

(=iferror(average ) if ) r8:r1000=L2，Z8:Z1000 ) ) )/L5，&； #039； &； #039； )”

因为是数组表达式，所以输入表达式后，必须按Ctrl Shift Enter进行确认。

合并M4、M5和M6单元格，然后输入100。

通过高级筛选创建动态成绩单：将需要进行数据分析的学生从题型分数表复制到R7单元格中，包括“班级”、“班级”、“姓名”和每个题型的名称。 在R5中键入类，在R6中键入=L2。

以下操作很重要！

单击“开发工具”/“记录宏”。 “宏”的名称为“卷分析”。 按一下「确定」。 单击“数据”/“筛选器”/“详细信息”。 打开“详细信息筛选器”对话框，然后单击“列表区域”右侧的按钮。 用鼠标拖动R7:AA1000区域，单击“确定”，单击“筛选条件”右侧的按钮，用鼠标拖动R5:R6区域，单击“确定”，然后将与“类”对应的类名固定如果选择“将筛选器结果复制到其他位置”，并在“复制到”文本框中输入D7，则会将筛选器结果复制到从D7单元格开始的区域。 按一下「确定」。 为D7单元格的第一个区域生成了满足条件的过滤结果。 然后在“开发工具”中“停止宏记录”。 因为R6单元格显示L2单元格的内容，L2单元格的内容是可变的，所以R6单元格的内容会根据L2类的变化而变化，得到不同的过滤结果，可以得到动态的表格效果。

创建类切换按钮：在“开发工具”中单击“插入”/“按钮”(窗体控件)，然后在任意位置拖动鼠标以创建一个名为“类切换”的按钮，然后单击打开Visual Basic输入代码：

子卷分析() ) )。

试卷分析. range(r7:aa1000 ) ).ClearContents

range(r7:aa100 ) ).advancedfilteraction :=xlfiltercopy，CriteriaRange:=Range _ ) u

(R5:R6 )、复印到范围：=范围)、Unique:=False

4 .设置包裹分析文本：创建一个名为“包裹分析文本”的新工作表，在A1中输入“类”，在B1中输入“包裹分析”。 A2以下输入类名，然后输入B2。

在“答案分析”工作表中，合并从N4到Q43的单元，并输入函数表达式“=vlookup(L2，答案分析文本！ a 2: b 10，2，0 ) "

隐藏不需要的工作表，只保留“成绩查询”、“成绩分析”、“答案分析”这三个工作表。

案例总结：案例通过excel函数联系数据，通过按钮或下拉菜单设置动态数据源，随着数据源的变化，实现动态数据、动态图表，结合高级过滤和“宏”功能在教学中可以理解excel函数、数据分析、数据管理的案例。

此案例由公众号“帮officer”、“老徐的excel”、“绝对不要学excel”完成，需要更多内容，请关注公众号。