综合测评计算方法(选修课)
先介绍一下sumproduct 函数,很实用的一个函数。
【含义】
在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。
【语法】
SUMPRODUCT (array1,array2,array3, ...) Array1,array2,array3, ... 为 2 到 30 个数组,其相应元素需要进行相乘并求和。
【说明】
• 数组参数必须具有相同的维数,否则,函数 SUMPRODUCT 将返回错误值 #VALUE!。 • 函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。
好了,进入正题。首先,拿到一份一个班级的成绩单,如图1
一.首先,把所有必修课所在的列以及学生学号、姓名列复制到一个新的excel 文档中。然后,为确保工作表中的所有数据都为可以进行计算的数值格式,需要进行以下几个操作:
1. 单元格中的数字都为文本格式,每个数字后面其实都有一个特殊的空格(不同于键盘上的space 键,如图2),需
要将其全部清除。具体做法为:在图2所示的编辑栏中复制该空格,ctrl+H 打开替换窗口,在查找内容中粘贴此空格,替换内容为空。全部替换即可。替换效果如图3
2. 带有“补”、“重”等字样的单元格,成绩不应算入本年度的综合测评,将其全部替换为0即可(不影响最终的平均成绩)
3. “-”即该生未选此门课程,也将“-”替换为0,如图4
4. 为了方便计算与操作,对数据进行行列转置,做法为:ctrl+A选中工作表中的所有内容,ctrl+C复制选中内容,将光标定位到sheet2的A 1单元格,单击开始选项卡中的粘贴,选中转置,如图5,效果如图6
二. 数据准备完毕,接下来,我们就可以进行计算了。
首先可留出一行,进行选修课总学分的计算,计算方法如下:
以乙为例,乙的某课程如果成绩大于0,说明乙选择了此课程,否则乙就为选择此课程。依此原理,即可写出公式:=SUMPRODUCT((F3:F10>0)*$C$3:$C$10),(因学分的一列在所有人的学分计算中要保持不变,所以要进行绝对引用,绝对引用与相对引用的切换快捷键为F4)回车,即可得乙的选修课总学分为13.5。如图7,经手动计算验证,计算结果无误。其他人的总学分直接用鼠标横向拖动单元格即可。
接下来可计算所有选修课的加权平均,具体算法为:所有选修课成绩*对应选修课的学分/总学分,以乙为例,计算公式为:=SUMPRODUCT(F3:F10*$C$3:$C$10)/F11,因未选的课程成绩都置为零,所以对最终的计算结果无影响,且方便了计算过程。其他人的平均成绩直接横向拖动即可。
选修课的数据处理到此为止。最终结果见图
8
综合测评计算方法(必修课)
行求和即可。处理过程及结果见图9,图10
数据处理步骤与选修课类似,只是不需对“-”进行替换,总学分的计算也简单很多,直接用sum 函数对学分列进
必修课涉及优良率的计算,此时要用到countif 函数,也比较简单,用法如图11
综合测评计算方法(汇总)
总成绩的计算方法为:必修课成绩*0.8+选修课成绩*0.2
至此,除了加分项,综合测评智育部分处理完毕。
打开一个新的工作表,将前面所得到的数据粘贴进去,粘贴时要选择“数值”并且“转置”,如图12
以上的计算过程仍然显得臃肿而繁琐,且有些操作容易出错,由于excel 学得有限,且时间不够充裕,错误在所难免。望各位高手批评指正。有更好的方法请与我交流
综合测评计算方法(选修课)
先介绍一下sumproduct 函数,很实用的一个函数。
【含义】
在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。
【语法】
SUMPRODUCT (array1,array2,array3, ...) Array1,array2,array3, ... 为 2 到 30 个数组,其相应元素需要进行相乘并求和。
【说明】
• 数组参数必须具有相同的维数,否则,函数 SUMPRODUCT 将返回错误值 #VALUE!。 • 函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。
好了,进入正题。首先,拿到一份一个班级的成绩单,如图1
一.首先,把所有必修课所在的列以及学生学号、姓名列复制到一个新的excel 文档中。然后,为确保工作表中的所有数据都为可以进行计算的数值格式,需要进行以下几个操作:
1. 单元格中的数字都为文本格式,每个数字后面其实都有一个特殊的空格(不同于键盘上的space 键,如图2),需
要将其全部清除。具体做法为:在图2所示的编辑栏中复制该空格,ctrl+H 打开替换窗口,在查找内容中粘贴此空格,替换内容为空。全部替换即可。替换效果如图3
2. 带有“补”、“重”等字样的单元格,成绩不应算入本年度的综合测评,将其全部替换为0即可(不影响最终的平均成绩)
3. “-”即该生未选此门课程,也将“-”替换为0,如图4
4. 为了方便计算与操作,对数据进行行列转置,做法为:ctrl+A选中工作表中的所有内容,ctrl+C复制选中内容,将光标定位到sheet2的A 1单元格,单击开始选项卡中的粘贴,选中转置,如图5,效果如图6
二. 数据准备完毕,接下来,我们就可以进行计算了。
首先可留出一行,进行选修课总学分的计算,计算方法如下:
以乙为例,乙的某课程如果成绩大于0,说明乙选择了此课程,否则乙就为选择此课程。依此原理,即可写出公式:=SUMPRODUCT((F3:F10>0)*$C$3:$C$10),(因学分的一列在所有人的学分计算中要保持不变,所以要进行绝对引用,绝对引用与相对引用的切换快捷键为F4)回车,即可得乙的选修课总学分为13.5。如图7,经手动计算验证,计算结果无误。其他人的总学分直接用鼠标横向拖动单元格即可。
接下来可计算所有选修课的加权平均,具体算法为:所有选修课成绩*对应选修课的学分/总学分,以乙为例,计算公式为:=SUMPRODUCT(F3:F10*$C$3:$C$10)/F11,因未选的课程成绩都置为零,所以对最终的计算结果无影响,且方便了计算过程。其他人的平均成绩直接横向拖动即可。
选修课的数据处理到此为止。最终结果见图
8
综合测评计算方法(必修课)
行求和即可。处理过程及结果见图9,图10
数据处理步骤与选修课类似,只是不需对“-”进行替换,总学分的计算也简单很多,直接用sum 函数对学分列进
必修课涉及优良率的计算,此时要用到countif 函数,也比较简单,用法如图11
综合测评计算方法(汇总)
总成绩的计算方法为:必修课成绩*0.8+选修课成绩*0.2
至此,除了加分项,综合测评智育部分处理完毕。
打开一个新的工作表,将前面所得到的数据粘贴进去,粘贴时要选择“数值”并且“转置”,如图12
以上的计算过程仍然显得臃肿而繁琐,且有些操作容易出错,由于excel 学得有限,且时间不够充裕,错误在所难免。望各位高手批评指正。有更好的方法请与我交流