- Sub Macro1()
- Dim arr, brr, d, i&, s&, x&
- Set d = CreateObject("scripting.dictionary")
- Sheet1.Activate
- arr = Range("a1").CurrentRegion
- ReDim brr(1 To UBound(arr), 1 To 7)
- For i = 3 To UBound(arr)
- If Not d.exists(arr(i, 5)) Then
- s = s + 1
- d(arr(i, 5)) = s
- brr(s, 1) = arr(i, 5)
- brr(s, 2) = arr(i, 2)
- brr(s, 3) = arr(i, 4)
- If arr(i, 3) = "女" Then
- brr(s, 4) = arr(i, 2)
- brr(s, 5) = arr(i, 4)
- Else
- brr(s, 6) = arr(i, 2)
- brr(s, 7) = arr(i, 4)
- End If
- Else
- x = d(arr(i, 5))
- brr(x, 2) = brr(x, 2) + arr(i, 2)
- brr(x, 3) = brr(x, 3) + arr(i, 4)
- If arr(i, 3) = "女" Then
- brr(x, 4) = brr(x, 4) + arr(i, 2)
- brr(x, 5) = brr(x, 5) + arr(i, 4)
- Else
- brr(x, 6) = brr(x, 6) + arr(i, 2)
- brr(x, 7) = brr(x, 7) + arr(i, 4)
- End If
- End If
- Next
- Sheet2.[a6].Resize(s, 7) = brr
- End Sub
- Sub Macro2()
- Dim rng As Range
- Sheet3.Activate
- Set rng = Sheet1.Columns(6).Find([b6], lookat:=xlWhole)
- If Not rng Is Nothing Then [e19] = rng.Row
- End Sub
复制代码 |