|
本帖最后由 hui3643616 于 2017-8-31 09:40 编辑
用VBA匹配另二张表,返回那二张表的相关数据,然后分类统计,生成一张或多张表
求高手帮帮忙,谢谢!
附件里面有详细的说明和步骤(先前附件说的还有点不明白,我又重新作了补充,也做了个样式,样式表的数据是准确的,不是瞎编的,在另外2张表都用相同的颜色标记出来了)
重新做了个字典的。和数据库的结果一致。
数据库代码中 union all 需改为 union,原因是“查实”表中有相同记录。
- Sub 字典()
- Set reg = CreateObject("vbscript.regexp")
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- reg.Global = True
- reg.Pattern = "(高血压|糖尿病|老年人|精神病|结核病)"
- brr = Sheets("查实").[a1].CurrentRegion
- For i = 2 To UBound(brr): d1(brr(i, 3)) = "": Next
- Dim arr(1 To 10000, 1 To 7)
- For k = 1 To 2
- brr = Sheets(k).[a1].CurrentRegion
- For i = 2 To UBound(brr)
- If d1.exists(brr(i, 2)) Then
- n = n + 1
- x = brr(i, 7): bz = ""
- Set ma = reg.Execute(x)
- For Each m In ma
- d(brr(i, 1) & "-" & m) = d(brr(i, 1) & "-" & m) + 1
- bz = bz & "," & m
- Next
- arr(n, 7) = Mid(bz, 2)
- For j = 1 To 6
- arr(n, j) = brr(i, j)
- Next
- End If
- Next
- Next
-
- With Sheets("匹配")
- .Cells.ClearContents
- .[a1].Resize(1, 7) = Array("乡镇街道", "身份证号", "姓名", "地址", "线索类型", "领取时间", "备注")
- .[L1].Resize(1, 2) = Array("乡镇街道-疾病类型", "出现次数")
- .[a2].Resize(n, UBound(arr, 2)) = arr
- .[L2].Resize(d.Count, 2) = Application.Transpose(Array(d.keys, d.items))
- .[a2].Resize(n, UBound(arr, 2)).Sort key1:=.[a2], key2:=.[b2]
- .[L2].Resize(d.Count, 2).Sort key1:=.[L2]
- End With
- End Sub
复制代码
|
|