|
本帖最后由 fightpanda 于 2014-10-29 10:09 编辑
目前数据有些不同:结果变量不都在一列中,会分布在几列,而且顺序不一致,可见下面的草表演示
A1编码 A2类型 B1第一种疾病 C1第二种疾病 D1第三种疾病 ···
1 1 X Y Z
2 2 X
3 3 Y Z
4 2 Z X
5 1 Z Y
X、Y、Z表示具体的疾病名称,B1、C1、D1只是存放某个人第一、二、三种疾病
请教该如何用数据透视表之类分别做出具体疾病X、Y、Z的分层分析(根据类型A2分)
即
类型 疾病X 疾病Y 疾病Z
1 具体数量
2
3
谢谢
- Sub Macro1()
- Dim arr, brr, d, d2, d3, i&, j%, zf$$
- Set d = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- Set d3 = CreateObject("scripting.dictionary")
- arr = Range("a1").CurrentRegion
- For i = 2 To UBound(arr)
- For j = 2 To UBound(arr, 2)
- If j = 2 Then
- d(arr(i, j)) = ""
- Else
- If arr(i, j) <> "" Then d2(arr(i, j)) = ""
- End If
- If j > 2 Then
- zf = arr(i, 2) & "," & arr(i, j)
- d3(zf) = d3(zf) + 1
- End If
- Next
- Next
- ReDim brr(1 To d.Count + 1, 1 To d2.Count + 1)
- a = d.keys: b = d2.keys
- For i = 0 To d.Count - 1
- brr(i + 2, 1) = a(i)
- For j = 0 To d2.Count - 1
- brr(1, j + 2) = b(j)
- brr(i + 2, j + 2) = d3(a(i) & "," & b(j))
- Next
- Next
- Range("j1").Resize(UBound(brr), UBound(brr, 2)) = brr
- End Sub
复制代码
|
|