|
|
及格分
F3 =SUMPRODUCT((成绩表!$A$4:$A$518=分析汇总!C3)*((成绩表!$B$4:$B$518=NUMBERVALUE(6&MID(D3,1,2)))+(成绩表!$B$4:$B$518=NUMBERVALUE(6&MID(D3,4,2)))+(成绩表!$B$4:$B$518=NUMBERVALUE(6&MID(D3,7,2))))*(OFFSET(成绩表!$E$4:$E$518,,MATCH(分析汇总!B3,成绩表!$F$3:$J$3,0))>任课表!$J$4))
平均分
H3 =SUMPRODUCT((成绩表!$A$4:$A$518=分析汇总!C3)*((成绩表!$B$4:$B$518=NUMBERVALUE(6&MID(D3,1,2)))+(成绩表!$B$4:$B$518=NUMBERVALUE(6&MID(D3,4,2)))+(成绩表!$B$4:$B$518=NUMBERVALUE(6&MID(D3,7,2)))),OFFSET(成绩表!$E$4:$E$518,,MATCH(分析汇总!B3,成绩表!$F$3:$J$3,0)))/E3
疯狂嵌套，仅以此表为例，而且还得修正成绩表里的班级成为数值才能正确计算；虽然算出来了，不过感觉有点low |
|
