本帖最后由 idnoidno 于 2017-4-24 22:39 编辑
附件是工作表數據
條件
第六列後,每一列的每一個儲存格(D:M)都和同一欄的相對應儲存格(D3:M3)做下列比較,EX:D6介於D3的60%~80@之間,D6的費率就是打八折,
所以,D6*D2(費率)*0.8,每一列的儲存格都依循這樣判斷,然後統計每一列小計{(D2:M2*D6:M6))到最右一欄,再將所有小計再總計到最下一列
D6*D2+E6*E2+F6*F2+G6*G2+H6*H2+I6*I2+J6*J2+K6*K2+L6*L2+M6*M2=N6 D6….M6依據表格的級距各乘上對應的百分比 類推 N6+N7+…………………………………………………………………………………………………..=N225
希望文字表達,路過的高手可以明白
小弟自己的語法,但是好像不太對,望請指點
Sub CalSum()
Dim irow, jcol, i, j As Integer
Dim ts1, ts2, ts3, ts4, ts5, ts6 As Long
Worksheets("統計表").Range("a1").Select
irow = ActiveCell(Rows.Count, 1).End(xlUp).Row
jcol = ActiveCell(1, Columns.Count).End(xlToLeft).Column
Worksheets("統計表").Cells(1, jcol + 1) = "合計"
For i = 6 To irow
Cells(i, jcol + 1) = 0
ts1 = 0
ts2 = 0
ts3 = 0
ts4 = 0
ts5 = 0
ts6 = 0
For j = 4 To jcol
If Cells(i, j) >= Cells(3, j) * 0.8 Then
ts1 = ts1 + Cells(i, j) * Cells(2, j)
ElseIf Cells(i, j) >= Cells(3, j) * 0.6 Then
ts2 = ts2 + Cells(i, j) * Cells(2, j) * 0.8
ElseIf Cells(i, j) >= Cells(3, j) * 0.4 Then
ts3 = ts3 + Cells(i, j) * Cells(2, j) * 0.6
ElseIf Cells(i, j) >= Cells(3, j) * 0.3 Then
ts4 = ts4 + Cells(i, j) * Cells(2, j) * 0.4
ElseIf Cells(i, j) >= Cells(3, j) * 0.1 Then
ts5 = ts5 + Cells(i, j) * Cells(2, j) * 0.15
Else
ts6 = ts6 + Cells(i, j) * 0
End If
Next j
Cells(i, jcol + 1) = ts1 + ts2 + ts3 + ts4 + ts5 + ts6 Cells(irow + 1, jcol + 1) = Cells(irow + 1, jcol + 1) + Cells(i, jcol + 1)
Next i
End Sub
- 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%”了,请自行修正
清测试
|