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

[µo°Ý] ½Ð±Ð¤@­Ó°ÝÃD¡G¦p¦ó±N¨ç¼ÆCOUNTIF»PSUMIF¥H¥¨¶°¤è¦¡§e²{

[µo°Ý] ½Ð±Ð¤@­Ó°ÝÃD¡G¦p¦ó±N¨ç¼ÆCOUNTIF»PSUMIF¥H¥¨¶°¤è¦¡§e²{

½Ð±Ð¤@­Ó°ÝÃD¡G¦p¦ó±N¨ç¼ÆCOUNTIF»PSUMIF¥H¥¨¶°¤è¦¡§e²{
¦pªþ¥ó

¨ç¼ÆCONUNTIF»PSUMIF§ï¥Î¥¨¶°°õ¦æµ{¦¡.zip (7.57 KB)

¦^´_ 1# lone_tiger0303


   «Øij¨Ï¥Î¼Ï¯Ã¤ÀªR
­Y¥Hvba¥i°Ñ¦Ò¬Ý¬Ý
  1. Sub MySumIF()
  2. ar = Array("¾Ç¥Í", "¹Å¼ú", "¤p¥\", "¤j¥\", "¤p­p")
  3. Set d = CreateObject("Scripting.Dictionary")
  4. Set d1 = CreateObject("Scripting.Dictionary")
  5. Set d2 = CreateObject("Scripting.Dictionary")
  6. With Sheet4
  7. For i = 1 To 3
  8. mysum = 0
  9.    For Each a In .Range(.[B2], .[B65536].End(xlUp))
  10.       d(a & ar(i)) = d(a & ar(i)) + a.Offset(, i)
  11.       If IsNumeric(a.Offset(, i)) Then _
  12.       mysum = mysum + a.Offset(, i): _
  13.       d1(a & "") = d1(a & "") + a.Offset(, i): _
  14.       d2(a & "") = d2(a & "") + a.Offset(, i)
  15.    Next
  16.    d("¤p­p" & ar(i)) = mysum
  17.    d1("¤p­p") = ""
  18.    d2("¤p­p") = d2("¤p­p") + mysum
  19. Next
  20. End With
  21. With Sheet3
  22. .[B5:F65536] = ""
  23. .[B5].Resize(, 5) = ar
  24. .[B6].Resize(d1.Count, 1) = Application.Transpose(d1.keys)
  25. For Each a In .Range(.[B6], .[B65536].End(xlUp))
  26.    For i = 1 To 4
  27.    a.Offset(, i) = IIf(i = 4, d2(a & ""), d(a & ar(i)))
  28.    Next
  29. Next
  30. End With
  31. End Sub
  32. Sub MyCountIF()
  33. Set d = CreateObject("Scripting.Dictionary")
  34. Set d1 = CreateObject("Scripting.Dictionary")
  35. Set d2 = CreateObject("Scripting.Dictionary")
  36. With Sheet2
  37.    For Each a In .Range(.[A2], .[A65536].End(xlUp))
  38.       d(a & a.Offset(, 1)) = d(a & a.Offset(, 1)) + 1
  39.       d1(a & "") = "": If a.Offset(, 1) <> "¦¨ÁZ" Then d2(a.Offset(, 1).Value) = ""
  40.    Next
  41.    
  42. End With
  43. With Sheet1
  44. .[B6:M65536] = ""
  45. .[B5].Resize(d1.Count, 1) = Application.Transpose(d1.keys)
  46. .[C5].Resize(, d2.Count) = d2.keys
  47. ar = d2.keys
  48. For Each a In .Range(.[B6], .[B65536].End(xlUp))
  49.    For i = 0 To UBound(ar)
  50.       a.Offset(, i + 1) = d(a & ar(i)): cnt = cnt + d(a & ar(i))
  51.    Next
  52.    a.Offset(, UBound(ar) + 2) = cnt: cnt = 0
  53. Next
  54. Set a = .[B65536].End(xlUp).Offset(1, 0)
  55. For i = 0 To UBound(ar)
  56.    a.Offset(, i + 1) = Application.Sum(.Range(.[B5].Offset(, i + 1), a.Offset(-1, i + 1)))
  57. Next
  58. End With

  59. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

·PÁ±zÀ°¦£~~

TOP

­è­è´ú¸ÕSub MySumIF()
·|¥X²{  ¦³NEXT «o¨S¦³FOR
¿ù»~¼Ð¥Ü¦b²Ä¤@­ÓNEXT

TOP

        ÀR«ä¦Û¦b : °µ¸Ó°µªº¨Æ¬O´¼¼z¡A°µ¤£¸Ó°µªº¨Æ¬O·Mè¡C
ªð¦^¦Cªí ¤W¤@¥DÃD