ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¦h±ø¥ó²Î­p

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-12-4 16:28 ½s¿è

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C.¦r¨å»P¾î¦VÁa¦V±Æ§Ç...,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
¸ê®Æªí:
20231204_1.jpg
2023-12-4 15:54


µ²ªGªí°õ¦æ«e:
20231204_2.jpg
2023-12-4 15:54


°õ¦æ«áªºµ²ªGªí:
20231204_3.jpg
2023-12-4 15:55



Option Explicit
Sub TEST_A()
Dim Brr, Crr(2000, 100), v, Z, Q, i&, j%, R&, C%, N&, U%, T2$, T8$
Set Z = CreateObject("Scripting.Dictionary")
Brr = Range(Sheet2.[H1], Sheet2.[B65536].End(3)(1, 0))
For i = 5 To UBound(Brr)
   T2 = Trim(Brr(i, 2)): T8 = Trim(Brr(i, 8))
   If T2 = "" Or T8 = "" Then GoTo i01
   R = Z(T2): C = Z(T8): Crr(0, 0) = "¤é´Á"
   If R = 0 Then N = N + 1: R = N: Z(T2) = R: Crr(R, 0) = T2
   If C = 0 Then U = U + 1: C = U: Z(T8) = C: Crr(0, C) = T8
   Crr(R, C) = Crr(R, C) + 1
i01: Next
Sheet1.[B6:B2000].EntireRow.Delete
With Sheet1.[B6].Resize(N + 1, U + 2)
   .Value = Crr
   .Offset(0, 1).Sort KEY1:=.Item(1, 2), Order1:=1, Header:=0, Orientation:=2
   .Offset(1, 0).Sort KEY1:=.Item(2, 1), Order1:=1, Header:=0, Orientation:=1
   .Item(1)(2, U + 2).Resize(N, 1) = "=SUM(" & Range(.Cells(2, 2), .Cells(2, U + 1)).Address(0, 0) & ")"
   .Cells(1, U + 2) = "Total"
   .Borders.LineStyle = 1
End With
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

  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. For Each Z In S1.[R6:Z6]
  17. p(1) = "select ¤é´Á,¬P´Á,count(¥X¤f) as c " & f & " where ¥X¤f = '" & Z.Value & "' group by ¤é´Á,¬P´Á,¥X¤f"
  18. Z.Offset(1, 0).CopyFromRecordset cn.Execute(Join(p, ""))
  19. Next
  20. S1.[AA7].CopyFromRecordset cn.Execute("select " & Mid(x, 2, 9999) & " from [¤u§@ªí2$R6:Z]")
  21. End Sub
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : ·R¤£¬O­n¨D¹ï¤è¡A¦Ó¬O­n¥Ñ¦Û¨­ªº¥I¥X¡C
ªð¦^¦Cªí ¤W¤@¥DÃD