数据分析（不使用数据透视表）

figfig

Sub fig8()
Set x = CreateObject("ADODB.CONNECTION")
x.Open = "Provider = MSDataShape;data provider=microsoft.jet.oledb.4.0;EXTENDED PROPERTIES='EXCEL 8.0;HDR=YES;';data source=" & ThisWorkbook.FullName
Set y1 = x.Execute(" shape (shape {select * from [Report$A1:D888]} as cc compute any(cc.CRD) as MMM,count(cc.CRD) as NNN ,CC  by Defect ,CRD  ) as dd")
Set y = x.Execute("shape (shape {select * from [Report$A1:D888]}  append ( dd relate Defect to Defect) as vv) as aa compute any(aa.Defect) as mm,count(aa.CRD) as nn,aa by Defect")
i = 2
y.Sort = "nn desc"
Do While Not y.EOF
Cells(i, 5) = y!mm
Cells(i, 6) = y!nn
Set y2 = y!aa.Value
Set y3 = y2!vv.Value
y3.Sort = "NNN desc"
j = 7
Do While Not y3.EOF
Cells(i, j) = y3!MMM
Cells(i, j + 1) = y3!NNN
y3.MOVENEXT
j = j + 2
Loop
y.MOVENEXT
i = i + 1
Loop
End Sub

praijna

figfig：
早上好!
这下可以了，谢谢老师的帮助!


谢谢!