|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
学习下
请参考
Sub 汇总()
Dim i, j, a, b, z, k, p, o, m, g, h, d, dd, az As Integer
[A:F] = ""
k = 2
For i = 2 To Sheets("销售明细").Cells(Rows.Count, 6).End(xlUp).Row
For j = 1 To b + 1
If Sheets("销售明细").Cells(i, 6) = Sheets("汇总").Cells(j, 11) Then
a = a + 1
End If
Next
If a = 0 Then
b = b + 1
Sheets("汇总").Cells(b, 11) = Sheets("销售明细").Cells(i, 6)
End If
a = 0
Next
Sheets("汇总").Range("a1:f1").Merge
Sheets("汇总").Cells(1, 1) = "客户月销明细表"
Sheets("汇总").Cells(1, 1).HorizontalAlignment = xlCenter
Sheets("销售明细").Range("D1:J1").Copy Sheets("汇总").Range("A2")
For p = 1 To Sheets("汇总").Cells(Rows.Count, 11).End(xlUp).Row
For o = 2 To Sheets("销售明细").Cells(Rows.Count, 6).End(xlUp).Row
If Sheets("汇总").Cells(p, 11) = Sheets("销售明细").Cells(o, 6) Then
m = m + 1
End If
Next
Sheets("汇总").Cells(p, 12) = m
m = 0
Next
For g = 1 To Sheets("汇总").Cells(Rows.Count, 11).End(xlUp).Row
d = d + 1
For h = 1 To Sheets("销售明细").Cells(Rows.Count, 6).End(xlUp).Row
If Sheets("汇总").Cells(g, 11) = Sheets("销售明细").Cells(h, 6) Then
k = k + 1
Sheets("销售明细").Range("d" & h & ":" & "j" & h).Copy Sheets("汇总").Range("A" & k)
End If
Next
k = k + 1
Sheets("汇总").Cells(k, 3) = "小计"
dd = Sheets("汇总").Cells(d, 12)
Sheets("汇总").Cells(k, 7) = Application.WorksheetFunction.Sum(Range(Sheets("汇总").Cells(k - dd, 7), Sheets("汇总").Cells(k - 1, 7)))
hj = hj + Sheets("汇总").Cells(k, 7)
Next
Sheets("汇总").Range("b:b,k:l").Delete
az = Sheets("汇总").Cells(Rows.Count, 2).End(xlUp).Row + 1
Sheets("汇总").Range("a2:a" & az).Copy Range("h2")
Sheets("汇总").Range("b2:b" & az).Copy Range("a2")
Sheets("汇总").Range("h2:h" & az).Copy Range("b2")
Sheets("汇总").Range("h2:h" & az).Delete
az = Sheets("汇总").Cells(Rows.Count, 2).End(xlUp).Row
Sheets("汇总").Range("a" & az + 3) = "合计"
Sheets("汇总").Range("F" & az + 3) = hj
End Sub
|
评分
-
1
查看全部评分
-
|