|
发表于 2017-4-23 09:38
|
显示全部楼层
本楼为最佳答案
- Sub CalSum()
- Dim i, j As Integer, arr, brr, crr(), drr()
- With Sheets("統計表")
- .Activate
- .Range("n1:n" & Rows.Count).ClearContents
- .Cells(1, 14) = "合計"
- arr = .Range("D6:M" & Cells(Rows.Count, 3).End(xlUp).Row)
- brr = .Range("D2:M3")
- End With
- ReDim crr(1 To UBound(arr), 1 To 10)
- ReDim drr(1 To UBound(arr))
- For i = 1 To UBound(arr)
- For j = 1 To 10
- If arr(i, j) > brr(2, j) * 0.8 Then
- crr(i, j) = arr(i, 1) * brr(1, j)
- ElseIf arr(i, j) > brr(2, j) * 0.6 And arr(i, j) <= brr(2, j) * 0.8 Then
- crr(i, j) = arr(i, 1) * brr(1, j) * 0.8
- ElseIf arr(i, j) > brr(2, 1) * 0.4 And arr(i, j) <= brr(2, j) * 0.6 Then
- crr(i, j) = arr(i, 1) * brr(1, j) * 0.6
- ElseIf arr(i, j) > brr(2, 1) * 0.3 And arr(i, j) <= brr(2, j) * 0.4 Then
- crr(i, j) = arr(i, 1) * brr(1, j) * 0.4
- ElseIf arr(i, j) <= brr(2, 1) * 0.3 Then
- crr(i, j) = arr(i, j) * brr(1, j) * 0.15
- End If
- drr(i) = drr(i) + crr(i, j)
- Next j
- Next i
- With Sheets("統計表")
- .[n6].Resize(UBound(drr)) = Application.Transpose(drr)
- End With
- End Sub
复制代码
注释没写
由于没指定小于10%时应该如何处理,把条件“10%≦儲存格≦30%”改成“儲存格≦30%”了,请自行修正
清测试 |
|