利用Access製作成績報表(四)(自訂函數的應用----設計模擬Excel2010統計函數PERCENTRANK.INC)

2012-01-07

前言：在前一篇文章(利用Access製作成績報表(三)(函數的應用))中我們談到利用Access系統內建函數去達成我們所要的運算，雖然Access已經提供了很多函數，但是面對使用者種種不同的需求，很難完全滿足使用者，所以Access提供使用者自訂函數的功能。

(利用"運算式建立器"輸入函數建立運算式)

如何建立自訂函數？

在Office的系列軟體都提供VBA的環境，我們可以利用它來建立自訂函數，打開Access資料庫執行"建立/巨集與程式碼/模組"

執行之後會在資料庫中加入一個VBA模組，預設的名稱為"Module1"

在右邊視窗Option Compare Database 之下加入程式碼。

因為Access並未提供類似Excel2010統計函數PERCENTRANK.INC函數，所以在前文中(利用Access製作成績報表(三)(函數的應用))我們要計算學生考試成績佔全體考生的百分比，是直接在Excel2010中先利用PERCENTRANK.INC函數計算出來，再將資料表連結至資料庫中，所以如果使用者沒有Excel2010基本上無法得到正確的計算結果，所以一勞永逸的作法，這裡我們自己寫一個模擬PERCENTRANK.INC的自訂函數，這樣使用者可以不受限於2010才可使用。

先聲明作者並未受過正統的演算法訓練，筆者設計之函數演算法與Excel2010統計函數PERCENTRANK.INC的函數演算法是否相同不得而知，計算的結果與Excel2010統計函數PERCENTRANK.INC的函數所計算的結果近似，如需精確之計算結果請斟酌使用。

我所設計之模擬Excel2010統計函數PERCENTRANK.INC的函數程式碼如下:


'作者:楊煥謀
'歡迎引用請註明出處
'2011/12/25 寫於耶誕節
Function getrankp(ByVal dbtable As String, ByVal dbfield As String, ByVal x As Variant, Optional ByVal bolinc As Boolean = True) As Single
Dim k As Long, i As Long, j As Long
Dim var() As Variant
Dim ob As Database
Dim strSQL As String
Dim rs As Recordset
Dim lngrs As Long

Set ob = Application.CurrentDb

strSQL = "SELECT " & dbfield & " FROM " & dbtable & " ;"
Set rs = ob.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
     rs.MoveLast
    lngrs = rs.RecordCount - 1
ReDim var(lngrs)
rs.MoveFirst
For i = 0 To lngrs

var(i) = rs.Fields(0)

rs.MoveNext

Next i




j = UBound(var)
k = 0

If bolinc = True Then '包括0與1
For i = 0 To j

If var(i) >= x Then

k = k + 1

End If

Next i


If k = 1 Then '代表第1名
getrankp = 1

Else

getrankp = ((j + 1 - k) / (j + 1))

End If


Else
'不包括0與1
For i = 0 To j

If var(i) > x Then

k = k + 1

End If

Next i


For i = 0 To j

If var(i) = x Then

k = k + 1

End If

Next i



getrankp = ((j + 2 - k) / (j + 2))




End If



End Function

設計好我們的函數要如何應用？

在(利用Access製作成績報表(三)(函數的應用))中我們是在報表設計中使用函數進行特定資料的運算，在這裡我們把自訂函數運用在產生查詢資料表中，為什麼如此？假設使用者的Excel資料表沒有先算好百分比與等級欄位(例如不是在Excel2010無法使用PERCENTRANK.INC)，如下表。