请用VBA完成这个表格,谢谢.
Sub analyzing()
lastcolumn = [iv3]。End(xlToLeft)。Column - 2
For i = 7 To lastcolumn '做最后分析的数据
Columns(i)。 ColumnWidth = 7。4
Cells(lastrow + 1, i) = Cells(2, i) '得出科目
Cells(lastrow + 2, i) = "=sum(r3c[0]:r" & lastrow & "c[0])" '计算各个科目总分
Cells(lastrow + 5, i) = "=sumproduct(--(r3c[0]:r" & lastrow &...全部
Sub analyzing()
lastcolumn = [iv3]。End(xlToLeft)。Column - 2
For i = 7 To lastcolumn '做最后分析的数据
Columns(i)。
ColumnWidth = 7。4
Cells(lastrow + 1, i) = Cells(2, i) '得出科目
Cells(lastrow + 2, i) = "=sum(r3c[0]:r" & lastrow & "c[0])" '计算各个科目总分
Cells(lastrow + 5, i) = "=sumproduct(--(r3c[0]:r" & lastrow & "c[0]>0))" '算出有多少人参考
Cells(lastrow + 7, i) = "=max(r3c[0]:r" & lastrow & "c[0])" '算出最高分
Cells(lastrow + 8, i)。
FormulaArray = "=min(if(r3c[0]:r" & lastrow & "c[0]>0,r3c[0]:r" & lastrow & "c[0]))" '用数组公式算出最低分
Cells(lastrow + 3, i) = "=round(r" & lastrow + 2 & "c[0]/r" & lastrow + 5 & "c[0], 2)" '算出平均分
Cells(lastrow + 4, i) = "=round(r" & lastrow + 6 & "c[0]/r" & lastrow + 5 & "c[0], 4)" '算出及格率
Cells(lastrow + 6, i) = "=sumproduct(--(r3c[0]:r" & lastrow & "c[0]>=getdata(r2c[0])))" '算出各科目及格人数
Next
Range(Cells(lastrow + 4, 7), Cells(lastrow + 4, 16))。
NumberFormatLocal = "0。00%"
End Sub
Function getdata(rng As Range) '获取各科目对应的及格分数
arr = Array("语文", "数学", "英语", "政治", "历史", "地理", "物理", "化学", "生物", "文综", "理综", "总分")
brr = Array(90, 90, 90, 60, 60, 60, 60, 60, 60, 60, 60, 630)
getdata = brr(Application。
Match(rng。
Value, arr, 0) - 1)
End Function
至于年级排序,不要公式,就用两个小别号,改为注释句,要就去掉别号,搞不懂,年武总分0,也排名"250"位?
这样用函数灵活一点,但也要修改代码中的数组brr()中的值。收起
