|
Option Explicit
Sub test()
Dim A(), B(), d, i%, j%, x$
A = Sheet2.Range("a1").CurrentRegion '数据源
B = Sheet1.[C1:H17].Value '输出区域
Set d = CreateObject("scripting.dictionary")
'D列
For i = 2 To UBound(A)
d(A(i, 1)) = d(A(i, 1)) + 1
Next i
For i = 2 To UBound(B)
B(i, 2) = d(B(i, 1))
Next i
d.RemoveAll
'F列(多条件)
For i = 2 To UBound(A)
d(A(i, 1) & A(i, 2)) = d(A(i, 1) & A(i, 2)) + 1
Next i
For i = 2 To UBound(B)
If B(i, 1) = "" Then B(i, 1) = B(i - 1, 1) '修改了C列合并单元格
If B(i, 3) = "" And B(i, 1) = B(i - 1, 1) Then B(i, 3) = B(i - 1, 3) '修改了E列合并单元格
B(i, 4) = d(B(i, 1) & B(i, 3))
Next i
d.RemoveAll
'H列 (多条件)
For i = 2 To UBound(A)
d(A(i, 1) & A(i, 2) & A(i, 3)) = d(A(i, 1) & A(i, 2) & A(i, 3)) + 1
Next i
For i = 2 To UBound(B)
If B(i, 5) = "" And B(i, 3) = B(i - 1, 3) And B(i, 1) = B(i - 1, 1) Then B(i, 5) = B(i - 1, 5)
B(i, 6) = d(B(i, 1) & B(i, 3) & B(i, 5))
Next i
d.RemoveAll
Sheet1.Range("c1").Resize(UBound(B), UBound(B, 2)) = B
End Sub
如果待统计的远不止CEF三列,我这么做就不好了。
在制作含有计算的表格时,应先考虑怎样利于计算,再考虑如何查看方便。
|
|