此算法是从一列数据中去掉最大数值和最小数值,求出总和,平均值,计算出
在cell中填充去掉的最大值,最小值, 总和,平均值和方差.
算法效率有点低,主要解决业务问题了.
Public Sub aver()
Application.ScreenUpdating = False
Dim maxCount
maxCount = 10 //最大行数
For k = 0 To 12 //最大的列数
Dim largeIndex
Dim smallIndex
Dim largerValue
Dim smalValue
Dim index
Dim tAvgValue
Dim varianceValue As Double
index = 0
'Largervalue And SmallValue
For Each x In Range(Cells(1, k + 1), Cells(maxCount, k + 1))
If index = 0 Then
largerValue = x
smallValue = x
largerIndex = index
smallIndex = index
End If
If x > largerValue Then
largerValue = x
largerIndex = index
End If
If x < smallValue Then
smallValue = x
smallIndex = index
End If
‘Debug.Print "largerValue: " & largerValue & " x:" & x
index = index + 1
Next x
'Sum Value
Dim sum
sum = 0
Dim arr(0 To 7) //10个数的话,去掉最大,最小就是8的数组
Dim i
i = 0
Dim curIndex
curIndex = 0
For Each x In Range(Cells(1, k + 1), Cells(maxCount, k + 1))
If curIndex = largerIndex Then
ElseIf curIndex = smallIndex Then
Else
sum = sum + x
arr(i) = x
i = i + 1
End If
curIndex = curIndex + 1
Next x
tAvgValue = sum / (maxCount - 2)
Dim sum1 As Double
sum1 = 0
Dim temp
For Each y In arr
If Abs(y) > Abs(tAvgValue) Then
temp = Abs(y) - Abs(tAvgValue)
sum1 = sum1 + temp * temp
Else
temp = Abs(tAvgValue) - Abs(y)
sum1 = sum1 + temp * temp
End If
Next y
varianceValue = Sqr(sum1 / (maxCount - 2))
'MsgBox " largerValue: " & largerValue & "smallValue:" & smallValue & " the sum is :" & sum & " the Avg is :" & sum / 8
Worksheets("Sheet1").Cells(maxCount + 5, k + 1) = "LargerValue: " & largerValue
Worksheets("Sheet1").Cells(maxCount + 6, k + 1) = "smallValue: " & smallValue
Worksheets("Sheet1").Cells(maxCount + 7, k + 1) = "sum: " & sum
Worksheets("Sheet1").Cells(maxCount + 8, k + 1) = "Avg: " & tAvgValue
Worksheets("Sheet1").Cells(maxCount + 9, k + 1) = varianceValue
Next k
Application.ScreenUpdating = True
End Sub