|
論壇上很少說到 加权 计算 方面的東東,我隨手撿了一個送上來:
Public Function WeightedMedianOfRst(RstName As String, fldName As String) As Double
Dim MedianTemp As Double
Dim ThisValue As Double
Dim NumRecs As Long
Dim RstOrig As Recordset
Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)
RstOrig.Sort = fldName
Dim RstSorted As Recordset
Dim RstFiltered As Recordset
Set RstSorted = RstOrig.OpenRecordset()
If RstSorted.RecordCount Mod 2 = 0 Then
RstSorted.AbsolutePosition = (RstSorted.RecordCount / 2) - 1
ThisValue = RstSorted.Fields(fldName).Value
RstOrig.Filter = "[" & fldName & "] = " & ThisValue
Set RstFiltered = RstOrig.OpenRecordset()
MedianTemp = ThisValue * RstFiltered.RecordCount
NumRecs = RstFiltered.RecordCount
RstSorted.MoveNext
ThisValue = RstSorted.Fields(fldName).Value
RstOrig.Filter = "[" & fldName & "] = " & ThisValue
Set RstFiltered = RstOrig.OpenRecordset()
NumRecs = NumRecs + RstFiltered.RecordCount
MedianTemp = MedianTemp + ThisValue * RstFiltered.RecordCount
MedianTemp = MedianTemp / NumRecs
|
|