|
楼主 |
发表于 2017-1-24 21:46
|
显示全部楼层
Sub main()
a1 = [e1]
rowMAX = Cells(Rows.Count, 2).End(3).Row
row1 = rowMAX - a1 + 1
arr = Range(Cells(row1, 2), Cells(rowMAX, 2))
ReDim brr(1 To UBound(arr), 1 To 2)
For i = 1 To UBound(brr)
Select Case arr(i, 1)
Case 1 To 10
brr(i, 1) = 1
Case 11 To 20
brr(i, 1) = 2
Case 21 To 30
brr(i, 1) = 3
Case 31 To 39
brr(i, 1) = 4
End Select
brr(i, 2) = brr(i, 1) + x
x = brr(i, 2)
ha5 = ha5 + brr(i, 2)
If i <= 3 Then qia3 = qia3 + brr(i, 2)
If i >= 3 Then ho3 = ho3 + brr(i, 2)
Next
ha6 = Round(1.5 * (ho3 - qia3) / 3 + ha5 / 5, 0)
haq6 = ha6 - brr(UBound(brr), 2)
[d16:f16] = Array(haq6 - 1, haq6, haq6 + 1)
End Sub
Sub main2()
a1 = [e1]
rowMAX = Cells(Rows.Count, 2).End(3).Row
row1 = rowMAX - a1 + 1
Cells(row1, 20) = Cells(row1, 2).Value
Dim 合计数
For i = row1 To rowMAX
Cells(i, 20) = Cells(i - 1, 20) + Cells(i, 2)
合计数 = 合计数 + Cells(i, 20)
Next
合计数 = 合计数 / a1
前三平均 = (Cells(row1, 20) + Cells(row1 + 1, 20) + Cells(row1 + 2, 20)) / 3
后三平均 = (Cells(rowMAX, 20) + Cells(rowMAX - 1, 20) + Cells(rowMAX - 2, 20)) / 3
[e5] = 合计数 + 后三平均 - 前三平均
End Sub
|
|