vb实现类似excel的Trimmean函数
如果用vb实现如excel的Trimmean函数语法形式为TRIMMEAN(Array,Percent)
rray为需要进行筛选并求平均值的数组或数据区域,Percent为计算时所要除去的数据点的比例。
而且数组里有0不列入平均值计算 VBA的函数可以直接使用 Private Function GetAverage(Source() As Single, n As Integer) As Single '去除最大最小n个数求平均值
InsertSort Source
Dim i As Integer, Sum As Single
Dim k As Integer
'--0的个数-----
Dim n0 As Integer '为0
Dim nn0 As Integer '不为0
n0 = 0
nn0 = 0
For i = LBound(Source) To UBound(Source)
If Source(i) = 0 Then
n0 = n0 + 1
End If
Next
'-----------
For k = n0 + n To UBound(Source) - n
Sum = Sum + Source(k)
nn0 = nn0 + 1
Next
If nn0 > 0 Then
GetAverage = Sum / nn0
Else
GetAverage = -1
End If
End Function
Private Sub InsertSort(Source() As Single) '由小到大排序
Dim i As Long, j As Long, k As Long, c As Long
Dim tmpArr() As Single
ReDim n(LBound(Source) To UBound(Source)) As Single
c = LBound(Source)
For i = LBound(Source) To UBound(Source)
For j = LBound(Source) To c - 1
If Source(i) <= n(j) Then Exit For
Next
For k = c - 1 To j Step -1
n(k + 1) = n(k)
Next
n(j) = Source(i)
c = c + 1
Next
For i = LBound(Source) To UBound(Source)
Source(i) = n(i)
Next
End Sub
Private Sub Command1_Click()
Dim c(10) As Singlec(0) = 2
c(1) = 8
c(2) = 2
c(3) = 3
c(4) = 4
c(5) = 0
c(6) = 0
c(7) = 2
c(8) = 0
c(9) = 0
c(10) = 0
Text1.Text = GetAverage(c, 1)
End Sub
写了一个,得出2.75 这种函数很简单啊。。。。。。。。 那有没有更合理的算法
补充:VB , 基础类