- Option Explicit
- Sub lqxs()
- Dim arr, i&, aa, j&, Brr, z, nl
- Dim d, k, t
- Set d = CreateObject("Scripting.Dictionary")
- Sheet1.Activate
- arr = Sheet2.[a1].CurrentRegion
- For i = 3 To UBound(arr)
- d(arr(i, 16)) = d(arr(i, 16)) & i & ","
- Next
- k = d.keys
- t = d.items
- ReDim Brr(1 To d.Count, 1 To 27) '定义数组到27
- For i = 0 To UBound(k)
- Brr(i + 1, 1) = i + 1
- Brr(i + 1, 2) = k(i)
- aa = Split(t(i), ",")
- For j = 0 To UBound(aa) - 1 '在这里不要去最后一个元素即相当于去除最后一个逗号
- z = arr(aa(j), 4) '指定区域为第4列
- If Val(Mid(z, 17, 1)) Mod 2 = 1 Then '如果第四列的一组数第17位能被2整除=1,那么在sheet1第4列中+1
- Brr(i + 1, 4) = Brr(i + 1, 4) + 1
- Else
- Brr(i + 1, 5) = Brr(i + 1, 5) + 1 '否则在第五列中加1
- End If
- Brr(i + 1, 3) = Brr(i + 1, 3) + 1 '同时在第三列中+1
- nl = DateSerial(Mid(z, 7, 4), Mid(z, 11, 2), Mid(z, 13, 2)) '取值
- nl = DateDiff("yyyy", nl, Date)
- Select Case nl
- Case 16 To 35
- Brr(i + 1, 7) = Brr(i + 1, 7) + 1
- Case 36 To 44
- Brr(i + 1, 8) = Brr(i + 1, 8) + 1
- Case 45 To 59
- Brr(i + 1, 9) = Brr(i + 1, 9) + 1
- Case 60 To 69
- Brr(i + 1, 10) = Brr(i + 1, 10) + 1
- Case 70 To 79
- Brr(i + 1, 11) = Brr(i + 1, 11) + 1
- Case 80 To 89
- Brr(i + 1, 12) = Brr(i + 1, 12) + 1
- Case Is >= 90
- Brr(i + 1, 13) = Brr(i + 1, 13) + 1
- End Select
- Brr(i + 1, 6) = Brr(i + 1, 6) + 1
-
- Select Case arr(aa(j), 10)
- Case 100
- Brr(i + 1, 15) = Brr(i + 1, 15) + 1
- Case 200
- Brr(i + 1, 16) = Brr(i + 1, 16) + 1
- Case 300
- Brr(i + 1, 17) = Brr(i + 1, 17) + 1
- Case 400
- Brr(i + 1, 18) = Brr(i + 1, 18) + 1
- Case 500
- Brr(i + 1, 19) = Brr(i + 1, 19) + 1
- Case 600
- Brr(i + 1, 20) = Brr(i + 1, 20) + 1
- Case 700
- Brr(i + 1, 21) = Brr(i + 1, 21) + 1
- Case 800
- Brr(i + 1, 22) = Brr(i + 1, 22) + 1
- Case 900
- Brr(i + 1, 23) = Brr(i + 1, 23) + 1
- Case 1000
- Brr(i + 1, 24) = Brr(i + 1, 24) + 1
- End Select
-
- If arr(aa(j), 13) <> "" Then Brr(i + 1, 25) = Brr(i + 1, 25) + 1 '这里是处理特殊人群的。
- Select Case arr(aa(j), 8)
- Case "新型农村社会养老保险"
- Brr(i + 1, 26) = Brr(i + 1, 26) + 1
- Case "城镇居民社会养老保险"
- Brr(i + 1, 27) = Brr(i + 1, 27) + 1
- End Select
- Next
- Brr(i + 1, 14) = Brr(i + 1, 15) + Brr(i + 1, 16) + Brr(i + 1, 17) + Brr(i + 1, 18) + Brr(i + 1, 19) _
- + Brr(i + 1, 20) + Brr(i + 1, 21) + Brr(i + 1, 22) + Brr(i + 1, 23) + Brr(i + 1, 24)
- Next
- [b7].Resize(15, 27).ClearContents
- [a7].Resize(UBound(Brr), 27) = Brr
- End Sub
复制代码 |