- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
5#
發表於 2012-8-24 14:49
| 只看該作者
本帖最後由 GBKEE 於 2012-8-24 20:56 編輯
回復 4# skyutm - Option Explicit
- Sub Ex()
- Dim Rng As String, I As Integer, 分數間格 As Integer, Msg As String
- 分數間格 = 10 '也可以間格 5分
- Rng = Worksheets("期中評量").Range("c3", Range("c3").End(xlDown)).Address '計算範圍的Address
- 'Application.Min(Range(Rng)) :最低分-> 還要加1個分數間格 來執行迴圈
- For I = 100 To Application.Min(Range(Rng)) - 分數間格 Step -分數間格 '- : 負數迴圈遞減
- If I = 100 Then
- Msg = "100 = " & Application.Evaluate("SumProduct((" & Rng & ">=100)*1 )")
- Else
- Msg = Msg & Chr(10) & I + 分數間格 - 1 & "~" & I & " = " & Application.Evaluate("SumProduct((" & Rng & "<=" & I + 分數間格 - 1 & ")*(" & Rng & ">=" & I & " ))")
- End If
- Next
- MsgBox Msg
- End Sub
- Option Explicit
- Sub Ex_CountIf()
- Dim Rng As Range, 間隔 As Integer, A As Integer, B As Integer, I As Integer, AR(1 To 2)
- Set Rng = Range("A1:A" & [A1].End(xlDown).Row) '統計的範圍
- 間隔 = 10
- For I = 100 To Application.Min(Rng) - 間隔 Step -間隔
- B = Application.CountIf(Rng, ">=" & I)
- 'COUNTIF 工作表函數: 計算某範圍內符合某搜尋準則的儲存格個數
- If B - A > 0 Then
- AR(1) = AR(1) & "," & IIf(I < 100, I + 間隔 - 1 & "~", "") & I
- AR(2) = AR(2) & "," & B - A
- A = B
- End If
- Next
- AR(1) = Application.Transpose(Split(Mid(AR(1), 2), ","))
- AR(2) = Application.Transpose(Split(Mid(AR(2), 2), ","))
- [B:C] = ""
- [B1].Resize(UBound(AR(1))) = AR(1)
- [C1].Resize(UBound(AR(2))) = AR(2)
- End Sub
|
|
發表於 2012-8-23 22:35
| 
