[發問] 請教一個問題：如何將函數COUNTIF與SUMIF以巨集方式呈現

lone_tiger0303

請教一個問題：如何將函數COUNTIF與SUMIF以巨集方式呈現
如附件

lone_tiger0303

剛剛測試Sub MySumIF()
會出現  有NEXT 卻沒有FOR
錯誤標示在第一個NEXT

lone_tiger0303

感謝您幫忙~~

Hsieh

回復 1# lone_tiger0303


   建議使用樞紐分析
若以vba可參考看看

1. Sub MySumIF()
   
2. ar = Array("學生", "嘉獎", "小功", "大功", "小計")
   
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("小計" & ar(i)) = mysum
    
17.    d1("小計") = ""
    
18.    d2("小計") = d2("小計") + 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) <> "成績" 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. 
    
60. End Sub

複製代碼