Sub 统计()
Dim m, i, n, q As Long
Dim arr, arr1, arr2, arr3, arr4, arr5
m = Sheets("货品数据").Range("a" & Rows.Count).End(xlUp).Row
n = Sheets("进货明细").Range("C" & Rows.Count).End(xlUp).Row
q = Sheets("销售明细").Range("c" & Rows.Count).End(xlUp).Row
arr1 = Sheets("货品数据").Range("a2:a" & m)
arr2 = Application.SumIf(Sheets("进货明细").Range("C2:C" & n), arr1, Sheets("进货明细").Range("j2:j" & n))
arr3 = Application.SumIf(Sheets("进货明细").Range("C2:C" & n), arr1, Sheets("进货明细").Range("l2:l" & n))
arr4 = Application.SumIf(Sheets("销售明细").Range("C2:C" & q), arr1, Sheets("销售明细").Range("j2:j" & q))
arr5 = Application.SumIf(Sheets("销售明细").Range("C2:C" & q), arr1, Sheets("销售明细").Range("l2:l" & q))
arr = Sheets("货品数据").Range("a2:s" & m)
For i = 1 To UBound(arr)
arr(i, 11) = arr2(i, 1)
arr(i, 13) = arr3(i, 1)
If arr(i, 11) <> 0 Then arr(i, 12) = arr(i, 13) / arr(i, 11)
arr(i, 14) = arr4(i, 1)
arr(i, 16) = arr5(i, 1)
If arr(i, 14) <> 0 Then arr(i, 15) = arr(i, 16) / arr(i, 14)
arr(i, 17) = arr(i, 8) + arr(i, 11) - arr(i, 14)
arr(i, 19) = arr(i, 10) + arr(i, 13) - arr(i, 16)
If arr(i, 17) <> 0 Then arr(i, 18) = arr(i, 19) / arr(i, 17)
Next i
Sheets("进销存统计").Range("a3:s" & Rows.Count).ClearContents
Sheets("进销存统计").Range("A3").Resize(UBound(arr), 19) = arr
End Sub |