返回列表下一主題上一主題發帖

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

lone_tiger0303

中學生 Rank: 2

帖子: 76
主題: 19
精華: 0
積分: 145
點名: 0
作業系統: win xp
軟體版本: OFFICE 2003
閱讀權限: 20
註冊時間: 2010-5-24
最後登錄: 2014-6-24

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 2010-6-9 13:54 | 只看該作者

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

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

函數CONUNTIF與SUMIF改用巨集執行程式.zip (7.57 KB)

樓主熱帖

Hsieh

超級版主 Rank: 8 Rank: 8

帖子: 4901
主題: 44
精華: 24
積分: 4916
點名: 280
作業系統: Windows 7
軟體版本: Office 20xx
閱讀權限: 150
性別: 男
來自: 台北
註冊時間: 2010-4-30
最後登錄: 2024-12-27

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

2^#

發表於 2010-6-9 17:01 | 只看該作者

回復 1# lone_tiger0303

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

Sub MySumIF()
ar = Array("學生", "嘉獎", "小功", "大功", "小計")
Set d = CreateObject("Scripting.Dictionary")
Set d1 = CreateObject("Scripting.Dictionary")
Set d2 = CreateObject("Scripting.Dictionary")
With Sheet4
For i = 1 To 3
mysum = 0
For Each a In .Range(.[B2], .[B65536].End(xlUp))
d(a & ar(i)) = d(a & ar(i)) + a.Offset(, i)
If IsNumeric(a.Offset(, i)) Then _
mysum = mysum + a.Offset(, i): _
d1(a & "") = d1(a & "") + a.Offset(, i): _
d2(a & "") = d2(a & "") + a.Offset(, i)
Next
d("小計" & ar(i)) = mysum
d1("小計") = ""
d2("小計") = d2("小計") + mysum
Next
End With
With Sheet3
.[B5:F65536] = ""
.[B5].Resize(, 5) = ar
.[B6].Resize(d1.Count, 1) = Application.Transpose(d1.keys)
For Each a In .Range(.[B6], .[B65536].End(xlUp))
For i = 1 To 4
a.Offset(, i) = IIf(i = 4, d2(a & ""), d(a & ar(i)))
Next
Next
End With
End Sub
Sub MyCountIF()
Set d = CreateObject("Scripting.Dictionary")
Set d1 = CreateObject("Scripting.Dictionary")
Set d2 = CreateObject("Scripting.Dictionary")
With Sheet2
For Each a In .Range(.[A2], .[A65536].End(xlUp))
d(a & a.Offset(, 1)) = d(a & a.Offset(, 1)) + 1
d1(a & "") = "": If a.Offset(, 1) <> "成績" Then d2(a.Offset(, 1).Value) = ""
Next
End With
With Sheet1
.[B6:M65536] = ""
.[B5].Resize(d1.Count, 1) = Application.Transpose(d1.keys)
.[C5].Resize(, d2.Count) = d2.keys
ar = d2.keys
For Each a In .Range(.[B6], .[B65536].End(xlUp))
For i = 0 To UBound(ar)
a.Offset(, i + 1) = d(a & ar(i)): cnt = cnt + d(a & ar(i))
Next
a.Offset(, UBound(ar) + 2) = cnt: cnt = 0
Next
Set a = .[B65536].End(xlUp).Offset(1, 0)
For i = 0 To UBound(ar)
a.Offset(, i + 1) = Application.Sum(.Range(.[B5].Offset(, i + 1), a.Offset(-1, i + 1)))
Next
End With
End Sub