singo1232001

1. Sub t5()
   
2. i = Split("Provider=Microsoft.,Jet.OLEDB.4,.0;Extended Properties=Excel ,8,.0;Data Source=", ",")
   
3. If Application.Version > 12 Then i(1) = "ACE.OLEDB.12": i(3) = 12
   
4. Set cn = CreateObject("adodb.connection"): cn.Open Join(i, "") & ThisWorkbook.FullName
   
5. Set S1 = Sheet1: Set s2 = Sheet2: S1.[B7:AA9999].ClearContents:
   
6. f = " from [¸ê®Æ$B4:I] "
   
7. S1.[B7].CopyFromRecordset cn.Execute("select distinct ¤é´Á,¬P´Á " & f)
   
8. S1.[P7].CopyFromRecordset cn.Execute("select distinct ¤é´Á,¬P´Á " & f)
   
9. p = Split("select b.c from [¤u§@ªí2$B6:B] as a left join (;;) as b on a.¤é´Á=b.¤é´Á ", ";")
   
10. For Each Z In S1.[d6:L6]
    
11. p(1) = "select ¤é´Á,¬P´Á,count(¤J¤f) as c " & f & " where ¤J¤f = '" & Z.Value & "' group by ¤é´Á,¬P´Á,¤J¤f"
    
12. Z.Offset(1, 0).CopyFromRecordset cn.Execute(Join(p, ""))
    
13. x = x & "+ iif(IsNull(" & Z.Value & "),0," & Z.Value & ") "
    
14. Next
    
15. S1.[M7].CopyFromRecordset cn.Execute("select " & Mid(x, 2, 9999) & " from [¤u§@ªí2$D6:L]")
    
16. 
    
17. For Each Z In S1.[R6:Z6]
    
18. p(1) = "select ¤é´Á,¬P´Á,count(¥X¤f) as c " & f & " where ¥X¤f = '" & Z.Value & "' group by ¤é´Á,¬P´Á,¥X¤f"
    
19. Z.Offset(1, 0).CopyFromRecordset cn.Execute(Join(p, ""))
    
20. Next
    
21. S1.[AA7].CopyFromRecordset cn.Execute("select " & Mid(x, 2, 9999) & " from [¤u§@ªí2$R6:Z]")
    
22. End Sub

½Æ»s¥N½X