|
- Sub Macro1()
- Dim arr, brr, d, i&, j%, s&, k%, rng As Range
- '声明字典对象
- Set d = CreateObject("scripting.dictionary")
- '源数据数据放入数组arr
- arr = Sheets("数据源").Range("a1").CurrentRegion
- '结果放入数组brr,首先找出户主,因为源数据较乱,有时是家庭成员在前
- ReDim brr(1 To UBound(arr), 1 To 11)
- '循环数组arr,
- For i = 2 To UBound(arr)
- If arr(i, 8) = "本人" Then d(arr(i, 4)) = i
- Next
- '把和户主身份证相同的行放在同一条目
- For i = 2 To UBound(arr)
- If arr(i, 8) <> "本人" And d.exists(arr(i, 8)) Then d(arr(i, 8)) = d(arr(i, 8)) & " " & i
- Next
- a = d.keys: b = d.items
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- '清空单元格并设置单元格格式
- Range("a2:k60000").Clear
- With [a:k]
- .NumberFormatLocal = "@"
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- End With
- '按户主循环,户主和成员归队
- For i = 0 To d.Count - 1
- '条目分列,具体到每个家庭成员所在的行
- x = Split(b(i))
- For j = 0 To UBound(x)
- s = s + 1: n = x(j)
- brr(s, 1) = i + 1 '户数序号
- If j = 0 Then
- '分别按每户人数合并单元格
- Cells(s + 1, 1).Resize(UBound(x) + 1).Merge
- '把户主所在的单元格放入rng ,方便统一设置格式
- If rng Is Nothing Then
- Set rng = Cells(s + 1, 4)
- Else
- Set rng = Union(rng, Cells(s + 1, 4))
- End If
- End If
- If j = 1 Then Cells(s + 1, 2).Resize(UBound(x)).Merge
- brr(s, 2) = IIf(j = 0, "户主", "成员") '家庭关系
- brr(s, 3) = j + 1 '家庭人员序号
- '按数组arr对应的行放进数组brr
- For k = 2 To 9
- brr(s, k + 2) = arr(n, k)
- Next
- Next
-
- Next
- '填充结果
- Range("a2").Resize(s, 11) = brr
- '设置户主所在的单元格字体格式
- If Not rng Is Nothing Then
- rng.Font.ColorIndex = 3
- rng.Font.Bold = True
- End If
- '设置边框
- Range("a1").CurrentRegion.Borders.LineStyle = 1
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
- End Sub
复制代码 |
评分
-
查看全部评分
|